Building Table using the results of a query

[penmetsa]

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

 

[The_Doc_Man]

Look up help topic "Crosstab" or "Crosstab query." It can do the counts and averages etc. for you.

This is the third or fourth time you have talked about building a table where a query would be preferable for normal uses. You seem to be obsessed with writing a table every time. While you certainly could do so, you are better off using a query in any place where you would have wanted to use this table you want to write.

To answer the explicit question, look in the help files for a MakeTable query (or Make Table query, with a space...). That is how you make a table from a query.

 

[penmetsa]

Hi DocMan,

I am not obsessed or anything.

After trying a lot in query thingie, I moved on to create a table becoz its not working out.

Each "cell" in the query/table I want to create has a different expression.

This expression is the formula for counting the results of another query.

Can u just tell me the proceedure clearly or write a SOL code in Access query to come up with what I required?

Because I have tried help and the crosstab query too.

Thanks very much for the advise.

Bye

Penmetsa

 

[The_Doc_Man]

Each "cell" in the query/table I want to create has a different expression.

Somehow I doubt this, but if it is literally true, you don't have a situation where Access is an appropriate tool for the job. Excel, in this case, would really be better.

What I suspect is that what you call a different expression is actually the same expression with different contributing terms. If that is the case, then you can write a summation query. Start with a SELECT query using the query grid (Query Design View) and then click the Summation icon in the toolbar. It looks like the Greek letter SIGMA.

In each column you can select a field name, then choose functions such as sum, average, count, standard deviation, minimum, maximum, etc. You can ALSO choose "Group By" so that your query, when in datasheet view, will split out the "Group By" fields according to their separate values and give you the counts for each field value or combination of values Group'ed By.

To actually make a table, try to build the summation query first. If you can't get that right you will not get the table you want by anything except a brute-force method. If you can build the query right, then you can base a second MakeTable query on this summation query.

If you don't go through the query first, I'm not sure I can offer you enough help on any other methodology. 'cause if the query really isn't working no matter what you do, then I don't understand your problem well enough to advise you on it.