Relationships/Lookups

[MEG]

I have come across a problem in my database that I did not foresee. Each product has a set of "testing areas" that we need to watch for. For example "Product A" has 4 testing areas that may fail. So when you are entering information for "Product A" and "Test 1"fails you need to enter the number of product A's that failed in the "Test 1" area. Down the road as a "Product A" changes it's "Testing areas" will change. What is the best way to set this up so that when "Product A's " testing areas change, I can make the changes to the "Testing areas" and the old records will stay the same?, and the old reports will stay the same? Basically, I need to be making changes to certain fields on let's say a monthly basis and keep past data reporting and collecting the same.

[danjacoby]

In general (lots of specifics to deal with yet, but...):

List the "testing areas" in a separate file. Then, in the main file, have the "testing areas" field (or fields) perform an auto-enter lookup or calculation to check against the info in the other file.

This way, your testing areas field(s) will remain stet once the info is entered, but each new record will check against the current testing areas.

HTH,

Dan

[MEG]

Thank you very much for the information you have given me. I have run into a new problem. I created my relationships and lookups and I needed to create some formulas in my report to grab the appropriate information. I am noticing that when it grabs the appropriate information for the report it will say 4 records found and you page through them and it is the same record as the first. Example - I search for product "z" that was completed in March. The totals go into the appropriate areas for March but you have 4 records found all the information stays on the first record. Which is okay unless product "z"'s "testing area" changes in the middle of the month. On the report the "testing area" label changes but the result for that "area" is on the first record with the old "testing area" label. Is there a way around this or will I need to somehow "tag" those records differently.

To give you an idea of how my report is set up: I have the needed header information (product catalog and digit number, date, and title) in the "header" part, the bulk of the report is in the subsummary with the break field by date, number of fallouts for the product in each testing area and then a calculation that gets the summary and calculates each group of found fallouts into a percentage. And that is all for now. My report looks like an excel spreadsheet with columns and rows. I hope I am not confusing you

[MEG]

I will give you an explanation of the database. When a product comes into the building it is logged in and sent through processing steps (which is TFMT1 database), after they are done with processing in TFMT1 the product then is put through another process and information is entered into TFMT2. TFMT2 does a lookup to TFMT1 and enters the product, date, and codes. The processing data is then entered in TFMT2.. Each product has it's own "Tests". The number of fallouts (parts that did not pass) is entered in database by the number of fallouts and where the fallout happened. After the product is finished and a "Run Date" is entered the record is considered complete. When the record is complete I have a calculation that creates an arbitrary date for the record. From TFMT2 I generate 2 reports.

Report 1 - Takes the information from TFMT2 . The report is monthly and is set up to find the product and then break the information by the arbitrary date(cfirst of month). The information is displayed by Test Area(rows) and the area it failed (columns). For each row and column there is a field that shows the summary number of the fallouts for the month on that product. In a field next to the summary box there is a calculation the gets the summary number, divides it by the lot qty for the product that month, multiplies it by 100% for percentage, and rounds it 2 decimals. (All of these formulas and summaries are in a "subsummary part" on my report)

Round((GetSummary(scon7, cfirst of month)/

GetSummary(sum of lot, cfirst of month)*100),2)

My problem is that the "Tests" that are run on these products can change. It happens about 1 time every 6 months on a product. So in TFMT2 I created lookups for the test areas. When I report the information from TFMT2 I get the appropriate information on the first record (sometimes it will tell me 4 records found, which is right since the product came in and was produced at 4 different times throughout the month), but when a "test" changes the results does not display with the changed field. The information stays with the first record. So I need to know if I should somehow "tag" the record in TFMT2 when there is a change to the test area or if there is a better way.

Report 2- Takes the information from TFMT2 again. And needs to display each of the products, their lot qty's, their qty's after processing (good qty), the fallout number and develop a Yield Percentage. This needs to be done on one report with all parts on the report for that month. So I need to break the information in TFMT2 by date (arbitrary date), and then by product, and get all of the products on the one report. I am working on this report by using the subsummary part with the break field date. So far it is breaking up by date and only giving me 1 product for the month. I am working on this yet.

Thank You,

Meg