QUERY CALCULATION

[jascoh]

I'm working with a query and have the following three data fields - color, country and weight.
I have selected a range criteria for weight of between 5 and 10 lbs. I can get the query to display that criteria (only the entries between 5 and 10 lbs) but I want to have another field that simply displays the total number of found records within that criteria.
For example, the query returns 7 items between 5 and 10 pounds but shows them individually. How do I create a field in the query that just shows the total number of found records (7)? Can this be done?
-Jason

 

[Melagan]

Something like this?

SELECT Count(t.weight) as CountOfWeight
FROM tblYourTable as t
WHERE t.weight BETWEEN 5 and 10;

~Melagan
______
"It's never too late to become what you might have been.

 

[Melagan]

Reading your post more carefully - I do not believe it is possible to get what you want. You're probably wanting your query display to look like a spreadsheet with a "Total Row".

The best way to accomplish that will be in a report; use your regular SELECT query as a report's recordsource, then put a text box in the report footer with the following as it's control source:

=Count(*)

That should give you a total count for the amount of records on your report.


~Melagan
______
"It's never too late to become what you might have been.

 

[jascoh]

What about a secondary query that totals the records found in the first query?

 

[lespaul]

sure you use a union query.

What's the first query that you are trying to get "totals" for?




Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[jascoh]

I have three fields. One for color, one for country and one for weight. I can see in a query how many blue units were sent to Japan that weighed 5 lbs. I can do that with simple criteria 'blue' under color, 5 lbs. under weight and 'Japan' under country. I can also select another column and pick 'COUNT' under GroupBy which will tell me (count)how many of each entry exists (and that's what I want to total) so....

Here's the part I can't figure out.

I want to know how many entries are between 5 and 10 pounds (just the total number of entries between 5 and 10 pounds) or how many entries were for Japan (just the total number of entries for Japan).

Does this help? Can I do this all within a single query, multiple queries or do I need a report?

 

[lespaul]

can you provide some sample data and expected results from that data?

Do you know what you want from the query? you stated:

I want to know how many entries are between 5 and 10 pounds (just the total number of entries between 5 and 10 pounds) or how many entries were for Japan (just the total number of entries for Japan).

I'm not sure what you mean by "I need this OR this". Do you need to allow a user to select what they want (build your SQL dynamically) or are you building a report to show specific information?

Sorry I'm not more helpful, but I'm still not sure what the problem is.

leslie

 

[jascoh]

Sorry...it does not need to be dynamic. Using the above three fields (color, weight, country), how do I construct a single query that returns a summary value of each recurrent entry or selected criteria?

Example:

Japan, Blue, 2 lbs.
Japan, Blue, 2 lbs.
England, Red, 1 lbs.
Norway, Green, 5 lbs.
Japan, Blue 2 lbs.
England, Red, 1 lbs.

I want the query to tell me:

3 records for Japan, Blue, 2 lbs.
2 records for England, Red, 1 lbs.
1 record for Norway, Green 5 lbs.

I can get this data if I use a query/report combination, but I can't seem to get the same information in a single query.

Does this help?

 

[lespaul]

you mean like:

SELECT Country, weight, color, count(*) From TableName
GROUP BY Country, weight, color