Duplicate vaules dropped when summing

[CW]

I have an invoice form and table that allows 5 lines to be entered with the
relevant separate services and amounts.
Then I have a Union query that collects the amounts together and sums the
amounts (GroupBy) to arrive at a total for the order.
If any of the amounts on the lines are the same, e.g. 25.00 appears more
than once within an invoice, the summing only includes one of the duplicates.
How can I correct this?
Many thanks
CW

 

[Allen Browne]

Use a normalized design.

Instead of repeating fields (item1, amount1, item2, amount2, ...), you need
to use a related table where the line items for the invoice are stored. One
invoice header can have as many line items in the other table as needed.

Now you can really easily sum the related records to get the total for the
invoice. No UNION query is needed.

If that's a new idea, open the Northwind sample database that installs with
Access, and see how the Order and Order Detail tables fit together. You need
the same thing.

 

[John Spencer]

And if for some reason, you cannot redesign your table structure, try changing
the UNION in the Union query to UNION ALL. If that does not solve the
problem, then post the SQL of the queries you are using.

BUT the real solution is probably to redesign your table structure.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[CW]

Thanks, Allen. I'm pretty sure I follow what you are suggesting but I'll have
a look at the Northwind example just to be certain
Thanks and the Season's Greetings
CW

 

[CW]

Thank you, John - I'll try UNION ALL first as it would certainly save quite a
bit of work, if it resolves the issue. I've got quite a few queries and
reports running off the present structure so if I can avoid a re-design that
would be good.
Thanks and the Season's Greetings to you
CW

 

[Allen Browne]

If you can't find it, there's a screenshot of the Northwind relationships in
this article:
http://allenbrowne.com/AppRelReport.html

(Of course, you probably want to see how the forms work based on those
relations too.)