Duplicate Data in Query

[bburch1]

I've put together a very simple database to collect defect information. It only has two tables that are linked together. The Header table is made up of ID, Motor No, Date, and Total Packed fields. The detail is made up of ID, defect, and Qty fields. They are linked by the ID fields.

What I'm trying to get is a total of the [total packed] field on a report for a specific date range. What I get is duplicate data that I can't total.

Any help would be appreciated.

 

[SarahG]

Are you sure you need to include the Detail table? This doesn't sound like a technical problem, more a problem with data. Normally when there is a 'Header' record and a 'Detail' record, it means there could be more than one detail record per header record. The [Total Packed] field is in your header record so either the field has been added in the wrong place or you just need to look at the header record. I hope I've made sense!
Can you confirm a) the ID is unique within the header table, and b) whether or not ID is unique in the Detail table.

 

[PHV]

One SQL answer:
SELECT Sum(H.[total packed]) FROM tbl Header H WHERE H.Date Between [start date] And [end date];
You may consider posting in the Access reports forum if this answer isn't good for you.
You may also take a look at the DSum function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[bburch1]

Thanks for the quick responses. To answer Sarah's questions, a)yes the ID in the header is unique and b)the ID in the detail is not unique.

PHV, I'll try to add this to my query.