You need to produce a table with one record for each hose. Are you able to do that? There are scripted methods for looping through your records and merging/deleting duplicate records. There are also ways to exclude duplicates during an import.
A link from that table to your merged table of testing data should enable you t pull up the entire testing history for that hose an either a portal or a list view report could be used to display such data.
Well i think already have such a table with HOZTRAK, its one record per hose.The duplicates i can get around by importing just the tested records to the database to "HOZREPERTS"
Would it be best to create tables for each year? I have a script in "hozreports" that doesnt allow the creation of duplicates, probably not the best thing to have now though as i would need to see how many times the hose was tested over the years. kind of catch 22...
Then maybe show records with that serial number of that hose throught the years in, like you said a list view?
I just want to secure the actions of the user so the dont end up creating multiple hose reports but of the same test in "HOZREPORTS" then im just back to square one.
Tables for each year is functionally much the same as files for each year--what you are trying to put an end to.
I don't fully understand what kind of data is in Hozreports as compared to hoztrak. It would seem you need a one to many relationship between the two:
HozTrak::__pkHozTrakID = HozReports::_fkHozTrakID
Note that __pkHozTrakID, ideally should not be a manufacturer's serial number but rather an internally generated serial number--something you may have to change over to after you get your data properly organized in these two tables.
It has the info about the hose test the pressure the hose was tested the test method ie. hydrostatic / nitrogen etc.
What i have a the moment is: If i have searched a po# in HOZTRAK lets say 10 records come up. If a customer wants that batch of hoses tested the user clicks a button which triggers a script to import those records to HOZREPORTS.Thean after thy're tested they just add in the data.
If a customer rings and says he's sending a batch of 50 in for testing there is a button on the HOZREPORTS layout which asks the user to define the amount of records to create, and the batches code number, and they can also enter the next test due or the technician etc. There is now a total of 60 test reports in HOZREPORTS where there are 5000 in HOZTRAK.
The system they had before was to create multiple blank records in Hoztrak then when they're tested they add in the serial number etc wheather they passed etc. Here is the issue, if i look for a serial number of a hose i get 2 in HOZTRAK.One made one tested.
I want the HOZREPORT table to be independent(Which is probably the total wrong way to do it) but just to have a list of all the time that hose was tested in the past x amount of years.
Is it possible to have a script to find the duplicate serial numbers and auto enter in the portal? Or would you have to enter at least the serial number and then do a lookup?
A script can certainly find duplicate serial numbers but I'm not sure what you mean by "auto enter in the portal". Portal to what? For what purpose?
To fiind duplicate values you can enter find mode and enter a ! in the serial number field, then sort your records by the serial number field so that duplicate values are in adjacent records.
But I strongly recommend that you get a table set up where a serial number appears exactly once, not multiple times. It sounds like this is something that will take some work due to past practices that are generating duplicate entries. One simple way to take a large mass of records and produce a table were the value in a specified field is unique in every record is this:
Define a new table with just the fields that are needed to describe the hose--not any test results. At very least, you'll want a Manufacturer's serial number and an auto-etnered serial number field, the __pk field I mentioned earlier. I can guess you might need other fields such as date of manufacture, manufacturer, type of hose etc, but I leave that determination up to you. Then, set the MFG Serial Number field to be validated as "unique values", "validate always". Now import all the records from your current table into this new table, matching up fields to get the right data into the right fields. Enable auto-entry options (there's a check box for this in the last small dialog that pops up before you start the import) so that the __pk field will generate a unique internal use serial number as part of the import process.
This produces a table where you have exactly one record for each hose and with a new primary key field you can eventually use to link to other tables such as a table of testing results for your test reports. The validation settings will omit records during import that would produce the duplicate entrees you want to avoid. You can then link records in this table to your table of test results, first by MFG Serial number, but you can then transition to using the __pk field once you get the original set up working for you. (You can use a relationship based on MFG Serial Number to copy over the __pk values into a newly added _fkfield, then change your relationshp to use the __pk = _fk relationship.)
If i was to find the records that have been tested more than once could i have the portal be populated with the said serial number? Lets say the length in 2006 was 4000mm and in 2008 in was shortened to 3000mm. However they have the same serial number.. Im probably looking to do something that cant be done...
I will create a copy of my database and do as you said as i know this will be a very time consuming database to correct and pull together.
It would probably be best to do it right from scratch.
Thanks for your help
But what is the purpose of the portal once you get it?
Using the table I described earlier, you can set up a layout based on the new table and then a portal based on the following relationship will list all record in the original table with the same MFG Serial Number:
newTable::MFGSerialNumber = OldTable::MFGSerialNumber
and once you successfully transition to using __pk to _fk relationships, you can modify the relationship to be:
NewTable::__pkHoseID = OldTable::_fkHoseID
Data, such as the length of the hose, that changes over time, should be recorded in a related table separate from the data I am recommending that you import into your new table.
I did as you said and its working exactly as i hoped. However as it is in a new table isnt there a way i could copy my HOZTRAK TABLE and that be the new one and the old hoztrak would then work as my portal in a layout? Just so i have all my fields rather than just he bare minimum or is that the idea? To have little amount of fields?
You could do that quite easily. It can even be a "self join" where the table in the portal and the table in the layout refer to different occurrences of the same table. BUT, the idea is to have a table with only the fields storing data that does not change as time passes. Data that changes, data from different test results, etc. should be stored in a related table or tables. This isn't to provide any special advantage for the immediate need of seeing all the records for a given hose, but to move you towards a properly normalized data model that will make it simpler/easier to get what you need from your database.
Yes i see what you mean.
I go into more depth with it and see if can make this database easier to work with. Thank you so much for your help ive learned alot.