Consolidating Fields

[Hendrix10]

I have a table where there are one person can have multiple records. I
would like to consolidate the each clients records to one field with
an additional field showing the total of all the clients invoices. Is
that possible?

Sample:

Invoice# Client invoice_amt
12345 X $5
45767 X $8
89787 X $6


My desired result

Client invoice_total
X $19


Is this possible?

 

[Armen Stein]

I have a table where there are one person can have multiple records. I
would like to consolidate the each clients records to one field with
an additional field showing the total of all the clients invoices. Is
that possible?

Sample:

Invoice# Client invoice_amt
12345 X $5
45767 X $8
89787 X $6


My desired result

Client invoice_total
X $19


Is this possible?

Yes. What you're looking for is a Totals query. Create a new query
in Design view based on your table, and change it to a Totals query.
You can Group By the Client and Sum the Invoice_Amt.


Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Larry Daugherty]

A rule for Relational Database design is that thee be one table for
each entity type and that every entity of that type belongs in that
one table - exactly once!

As much as you have given I infer that you should have a table
~tblClient that would hold one record for each client. You should
then have a 2nd table ~tblInvoice related to tblClient in a
one-to-many relationship. Referential integrity should be enforced
and Cascading Deletes turned on.

You should not store the sum of all invoices for a client in a table
but should calculate it each time you need to display it in a Form or
Report.

HTH