Help with collating data from multiple tables

[jamesteague85]

Hi, Im relatively new to access and know the basics. My VBA is alright, but I really cant seem to get my table/query design right.

Im building a database for my work, heres the scenario.

We use a machine, creating items....these are identified by a product number.

The machine can make 4 products, one on each of the machines heads, they can all be different products though (usually the machine will make 2 different products across the 4 heads). The products made from the heads changes frequently too.


I want to store each of the heads outputs in a table...i.e date/product/output.

Thats the easy part.

The hard part comes when I try to create a query based on the 4 different heads, I want to end up with grand totals for each day.

a record for each product id and its output for each day.

so if the 4 machines were making 3 different products, you would have

3 records, each labelled with the date/product/output.

Forgive my ignorance if what im asking is stupid, or really easy.

Any help is greatly appreciated.

James

 

[Mr Smin]

You might only need
ProductsTable
ProductID. Product Description.

ProductionTable
HeadID, ProductID, Date, TotalProductionThisDate
A P1 9-12-10 100
B P1 9-12-10 100

 

[jamesteague85]

I have played around with similar table designs as youve mentioned.....Ive had tables where the heads are seperate, and tables where they are combined and identified by a head ID. The trouble is, when I try to query the tables so that I have totals, for each different product, for any one day. I dont know how to query the tables to get this result.

I always end up with separate records for each different heads product output. Not a single record for each product and a total output.

I know that I could create separate queries for each of the different products, and probably do it this way. But this would require a new query to be made every time a new product is being made.

Im really struggling with this one...

I did code a solution in VBA, but Im sure theres an easy way to do it using queries