Billy said:
Hi,
I have a table that contains a field called amount. I entre an amount paid
by a client and the clients name within the table.
I wish to count the totals paid by each client per month to include in an
invoice.
I have tried to do this but failed every time.
Any assistance would be appreciated.
Payments made2
Clients name Surname Date Voucher Number Amount
4 Weir 12-Dec-09 5 Cash £55
1 Smith 12-Dec-09 20 Cash £25
1 Smith 12-Dec-09 0 Cash £450
2 Jones 01-Jan-08 6 Cash £100
2 Jones 12-Dec-08 1 Cash £50
First thing I'd do would be "normalise" the data. You should break out
the Client information into a separate table with an ID field (Primary
Key) and the "Items" table should refer to this. This prevents you
getting wrong results because a name has been mis-typed. Structuring
your data properly is overwhelmingly the first thing to get right. See:
http://www.lynda.com/home/Player.aspx?lpk4=31001
... or ..
http://office.microsoft.com/trainin...ID=RC061183261033&CTT=6&Origin=RP061494301033
(you'll need to re-join the wrapped line).
I can't overemphasise how important this is.
Use the Relationships window to set up a relationship between your two
tables (so that Access is aware of it). Drag the ClientID field onto
the corresponding field in the Items table to do this.
Then create a query based on both tables in Design view. You should see
the line showing the relationship appear as you "Add" the second table.
Turn on "Total" in your query ("Totals" button on the toolbar - looks
like a weird E - actually the Greek sigma symbol). This will give your
a "Total" row in the query builder which wasn't there before. Select
"Group By" for ClientID, and "Sum" for the amount. This should give you
a very crude step forward.
Another (possibly easier approach) is to create your query using the
Query Wizard. If you have related tables, you'll be offered the option
of creating a "Summary" query - just experiment until you get what you
want (delete the first attempts!).
Eventually you'll want to learn how to use Forms to enter a new Item
(selecting the Client from a combo-box drop-down) and Reports to format
your output for printing, and you'll probably pick up just a little VBA
programming along the way. Powerful, and addictive.
For now, concentrate on understanding how to structure your data into
tables and fool around with Queries, using the excellent Wizards.
Hope that helps,
Phil, London
