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

=dcount("field_id", "queryname", "fieldname='Home'")

Try this

firstly, Welcome to UA!

The first question you normally get here with this question is "Why do you need to write the query results to a table?"

The query is available any time you want it, and it can be treated like a table. If you have a summary table, the data will be incorrect if you forget to run the update or an event fails to run the update.

You can use DCount as above to gather these counts in the query.

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