General discussion

Locked

Access2000 tables

By dliby ·
I have table "A" that has multiple records with the same account number and different amounts of cash. The sum of these records needs to be updated to table "B" which has one record for the account number and total cash. How can I accomplish this task?

This conversation is currently closed to new comments.

5 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

by DKlippert In reply to Access2000 tables

Calculations should be avoided in tables. Tables should be used for raw data.
To summerize your data, produce averages or maybe top 10 sales, use Queries.
You can also use the query results in Reports and Forms.

Collapse -

by jleather In reply to Access2000 tables

I agree this should be a query, actually two queries, and not another table. First query is to get a list of all unique Account Numbers from your table. The second uses your table and the first query for the desired result. I tested with a database with a table, called TableA, with two fields: Account Number (long Integer) and Amount (currency).

To build the first query, open a new query in design view and include your table. Make sure Totals are shown (by clicking the sigma on the tool bar or through the menu View|Totals). Select the Account Number, in the Totals row have ?Group By? shown (from drop down list) and if wanted, choice the appropriate selection for the Sort row.

The second query is built with your table and the first query (which I called AccountNumbers) and also have Totals selected as above. Choice Account Number from the AccountNumbers query and Amount from TableA. For Account number have Group By selected for the Totals row and Sum for Amount. Finally drag the Account Number field in TableA to the Account Number field in AccountNumbers query. This should result in a line (link, relationship) between the two. Again if wanted choice the appropriate selection for the Sort row.

That should do it, just view in datasheet view!

Hope this helps, and I have the test mdb file if you want a copy.

John Leather

Collapse -

by parier In reply to Access2000 tables

You can use a make table query to create your results, but I recommend following the other two suggestions.

select distinct customer, sum(sales)
from customers
where sum > 0;

New Table...
SALES_REP SALES
BOB 65465.56
STEVE 55645.34
JOHN 64545.31


Hope this helps.

Collapse -

by dliby In reply to

I was able to resolve the problem with queries

Collapse -

by dliby In reply to Access2000 tables

This question was closed by the author

Back to Software Forum
5 total posts (Page 1 of 1)  

Related Discussions

Related Forums