5 replies
by dklippert
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.
by jleather
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
by parier
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.
by dliby
I was able to resolve the problem with queries
by dliby
This discussion is closed to new replies.
Trending Software posts
Sort by Trending