Hi all,

I have a database in MS Access.
I built a query to obtain a subset of the database.
Now I have the query.

I want to summarize the results of the query in "summary" table in the same database. This summary would be as follows:

Destination Purpose Count Percent

Home “Expression”
Work
Shopping
School
Other
Total

I want to write an "expression" in the "summary" table cell, to obtain the count of a field of various individual values (Home,Work, Shopping........) like
=count([queryname].[fieldname],'Home').

Can we write like above as we write in Excel.

Please tell me about the syntax of the expression and do we have to do anything regarding normalization?

Thanks in advance for any help.

Penmetsa

Penmetsa,

Storing calculated values is generally frowned upon. But, I understand... It was one of my first questions in this forum.

Queries act like tables so there is no need to duplicate the data to another table. If you are thinking that you need to store historical data, consider instead having an "as at" date in your records.

For example, I have to calculate commissions paid (monthly) in a currency other than Australian dollars. So I store the monthly average exchange rate in a table, but calculate the query based on the "as at" date.

I hope this makes sense!

Averil