Question Multiple table into one report

[ph00086]

Hi, thanks everyone for the help.

I have three tables (one for each production line) which people input data into everyday and the data is different each day depending on the part numbers and number produced. I want to create a report that shows me the part numbers and amount produced by each line for a certain day.

I've tried putting all three in the same query put i get 300 repeating results when i should only get about 20 unique ones. I've also tried making three seperate queries and combining them into one but this gives the same result.

Also I've tried having sub-reports for each table for individual queries, but this then just prompts me to enter the date multiple times or gets stuck in a continuous loop of date entry.

Any ideas?

Thanks

 

[plog]

Yes, structure your data properly. Those 3 tables should only be 1 table. You need to normalize your data: http://phlonx.com/resources/nf3/

 

[Pat Hartman]

As plog said, your schema is incorrect. You need a single table with a column that identifies the line. That change will propagate to your forms and queries. It's a pain now but will save you trouble in the long run. Your queries will need criteria that tells them which line to select data for. That means you can either prompt for the line or if you don't forsee more than 3 for a couple of years, you create three functions and run those functions from the menu. Each function would use the OpenForm Method to open the form and the where argument to specify which line should be selected.