Filter by sum of counts

[runnik]

I have a query that contains 3 items. The first is the vendor name,
the second is a text field containing one of seven different options,
and the third is a count of how many of each option there are. What I
would like to do is to be able to filter the data based on the TOTAL
number of all the options for one vendor.

For example, if I have:

Vendor1 Option A 2
Vendor1 Option B 1
Vendor1 Option C 1
Vendor2 Option A 3
Vendor2 Option B 4

So for Vendor1, there is a total of 4 option instances. And for
Vendor2, there is a total of 7 option instances. What I would like to
do is only show vendors that have, for example, 5 or more total option
instances. So in my example data, only Vendor2 would show up.

How do I do this?

Jason

 

[Dale Fye]

Is the data provided below the output of the query you are talking about, or
is it the information that is in a table? If you save this query
(qry_VenOptCounts) and use it as the source in a new query, you might get
what you are looking for with:

SELECT Vendor, Option, OptionCount
FROM qry_VenOptCount
WHERE Vendor = (SELECT Vendor _
FROM qry_VenOptCount
GROUP BY Vendor
HAVING Count(Option) >= 5)

HTH
Dale

 

[Michel Walsh]

SELECT vendor, SUM(optionQty)
FROM tableName
GROUP BY vendor
HAVING SUM(optionQty) >= 5



Hoping it may help,
Vanderghast, Access MVP

 

[John Spencer]

Try Three queries.

SELECT Vendor
FROM YourTable
GROUP BY Vendor
HAVING Count(*) >=5

SELECT Vendor, Option, Count(Option) as CountofOptions
FROM YourTable
GROUP BY Vendor, Option

SELECT q2.Vendor, q2.Option, q2,CountOfOptions
FROM Q2
WHERE q2.Vendor in (Select Q1.Vendor FROM Q1)


You may be able to do this in less than 3 queries, but three queries
should work no matter how you have named your tables and fields.

A single query (using a subquery in the from clause) might look like the
following.

SELECT Vendor, Option, Count(Option) as CountofOptions
FROM YourTable INNER JOIN
(SELECT Vendor
FROM YourTable
GROUP BY Vendor
HAVING Count(*) >=5) as q2
On YourTable.Vendor = Q2.Vendor
GROUP BY Vendor, Option


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[runnik]

The data is the output of a query, which is required to get a count of
each option.

When I tried that code, it only showed me which vendor had at least
one from five different option groups (there was only one).

 

[runnik]

John,

Thanks for your reply. The three-query method worked for me, but I
couldn't get the single-query method to work (and I don't know
anything about SQL to try and make it work). So, I'll just go with
using three queries to accomplish what I want.

Jason

 

[Dale Fye]

I misinterpretted what you were looking for. The way I read your OP, I had
the impression that you only wanted those vendors that had at least 5 records
(Options A, B, C, D, E, F, ...) in your query, not where the sum of the
number in the right had column totaled 5 or more. I see that Johns three
query solution worked for you.

Good Luck


--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[runnik]

That's OK. Yes, his solution worked for me, but now I have another
question/problem. In my underlying table, I have a date field. I
figured out that by putting a parameter asking for a starting date and
ending date into the first two queries, that I can limit my results to
just the dates that are inputted by the user. However, when I try to
create a chart based on the third query, it states that the Microsoft
Jet database engine does not recognize '[Enter a starting date]' as a
valid field name or expression. I know this is because the third query
does not contain the parameter input, but I don't know how to get
around that. Any suggestions?

Jason

 

[Dale Fye]

Add the same parameters clause that you have in the other query to the
beginning of the 3rd query.

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

That's OK. Yes, his solution worked for me, but now I have another
question/problem. In my underlying table, I have a date field. I
figured out that by putting a parameter asking for a starting date and
ending date into the first two queries, that I can limit my results to
just the dates that are inputted by the user. However, when I try to
create a chart based on the third query, it states that the Microsoft
Jet database engine does not recognize '[Enter a starting date]' as a
valid field name or expression. I know this is because the third query
does not contain the parameter input, but I don't know how to get
around that. Any suggestions?

Jason

I misinterpretted what you were looking for. The way I read your OP, I had
the impression that you only wanted those vendors that had at least 5 records
(Options A, B, C, D, E, F, ...) in your query, not where the sum of the
number in the right had column totaled 5 or more. I see that Johns three
query solution worked for you.

Good Luck

 

[runnik]

I would, but the third query doesn't have the date field available to
it, since it's not a displayed field in the other two queries.

Jason

Add the same parameters clause that you have in the other query to the
beginning of the 3rd query.

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

That's OK. Yes, his solution worked for me, but now I have another
question/problem. In my underlying table, I have a date field. I
figured out that by putting a parameter asking for a starting date and
ending date into the first two queries, that I can limit my results to
just the dates that are inputted by the user. However, when I try to
create a chart based on the third query, it states that the Microsoft
Jet database engine does not recognize '[Enter a starting date]' as a
valid field name or expression. I know this is because the third query
does not contain the parameter input, but I don't know how to get
around that. Any suggestions?

Jason

I misinterpretted what you were looking for. The way I read your OP, I had
the impression that you only wanted those vendors that had at least 5 records
(Options A, B, C, D, E, F, ...) in your query, not where the sum of the
number in the right had column totaled 5 or more. I see that Johns three
query solution worked for you.
Good Luck

 

[Dale Fye]

Doesn't matter.

Your third query (in SQL view) should look something like:

PARAMETERS [Enter a starting date] DateTime;
SELECT * FROM qry2;

If you have more than one parameter, just separate them by commas. Do you
know how to define parameters in the query grid?

If not,
1. open the query in design mode.
2. right click in the grey area next to the box that displays qry2 and the
fields that are in it
3. Select Parameters from the popup menu
4. In the Parameter column, enter your parameter, exactly the way it used
in your query. In your case, it will look like:

[Enter a starting date]

5. In the DataType column, enter Date/Time

HTH
Dale

I would, but the third query doesn't have the date field available to
it, since it's not a displayed field in the other two queries.

Jason

Add the same parameters clause that you have in the other query to the
beginning of the 3rd query.

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

That's OK. Yes, his solution worked for me, but now I have another
question/problem. In my underlying table, I have a date field. I
figured out that by putting a parameter asking for a starting date and
ending date into the first two queries, that I can limit my results to
just the dates that are inputted by the user. However, when I try to
create a chart based on the third query, it states that the Microsoft
Jet database engine does not recognize '[Enter a starting date]' as a
valid field name or expression. I know this is because the third query
does not contain the parameter input, but I don't know how to get
around that. Any suggestions?

Jason

I misinterpretted what you were looking for. The way I read your OP,
I had
the impression that you only wanted those vendors that had at least 5
records
(Options A, B, C, D, E, F, ...) in your query, not where the sum of
the
number in the right had column totaled 5 or more. I see that Johns
three
query solution worked for you.

Good Luck

 

[runnik]

I had no clue you could define parameters using the popup menu. I
don't think I ever learned that in any class I ever took. But that
appears to have worked for me. Thanks for your help.

Jason

Doesn't matter.

Your third query (in SQL view) should look something like:

PARAMETERS [Enter a starting date] DateTime;
SELECT * FROM qry2;

If you have more than one parameter, just separate them by commas. Do you
know how to define parameters in the query grid?

If not,
1. open the query in design mode.
2. right click in the grey area next to the box that displays qry2 and the
fields that are in it
3. Select Parameters from the popup menu
4. In the Parameter column, enter your parameter, exactly the way it used
in your query. In your case, it will look like:

[Enter a starting date]

5. In the DataType column, enter Date/Time

HTH
Dale

I would, but the third query doesn't have the date field available to
it, since it's not a displayed field in the other two queries.

Jason

Add the same parameters clause that you have in the other query to the
beginning of the 3rd query.
HTH
Dale
--
Don''t forget to rate the post if it was helpful!
email address is invalid
Please reply to newsgroup only.
:
That's OK. Yes, his solution worked for me, but now I have another
question/problem. In my underlying table, I have a date field. I
figured out that by putting a parameter asking for a starting date and
ending date into the first two queries, that I can limit my results to
just the dates that are inputted by the user. However, when I try to
create a chart based on the third query, it states that the Microsoft
Jet database engine does not recognize '[Enter a starting date]' as a
valid field name or expression. I know this is because the third query
does not contain the parameter input, but I don't know how to get
around that. Any suggestions?
Jason
I misinterpretted what you were looking for. The way I read your OP,
I had
the impression that you only wanted those vendors that had at least 5
records
(Options A, B, C, D, E, F, ...) in your query, not where the sum of
the
number in the right had column totaled 5 or more. I see that Johns
three
query solution worked for you.
Good Luck

 

[Dale Fye]

Glad I could help

--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

I had no clue you could define parameters using the popup menu. I
don't think I ever learned that in any class I ever took. But that
appears to have worked for me. Thanks for your help.

Jason

Doesn't matter.

Your third query (in SQL view) should look something like:

PARAMETERS [Enter a starting date] DateTime;
SELECT * FROM qry2;

If you have more than one parameter, just separate them by commas. Do you
know how to define parameters in the query grid?

If not,
1. open the query in design mode.
2. right click in the grey area next to the box that displays qry2 and the
fields that are in it
3. Select Parameters from the popup menu
4. In the Parameter column, enter your parameter, exactly the way it used
in your query. In your case, it will look like:

[Enter a starting date]

5. In the DataType column, enter Date/Time

HTH
Dale

I would, but the third query doesn't have the date field available to
it, since it's not a displayed field in the other two queries.

Add the same parameters clause that you have in the other query to the
beginning of the 3rd query.

HTH
Dale

email address is invalid
Please reply to newsgroup only.

:
That's OK. Yes, his solution worked for me, but now I have another
question/problem. In my underlying table, I have a date field. I
figured out that by putting a parameter asking for a starting date and
ending date into the first two queries, that I can limit my results to
just the dates that are inputted by the user. However, when I try to
create a chart based on the third query, it states that the Microsoft
Jet database engine does not recognize '[Enter a starting date]' as a
valid field name or expression. I know this is because the third query
does not contain the parameter input, but I don't know how to get
around that. Any suggestions?

Jason

I misinterpretted what you were looking for. The way I read your OP,
I had
the impression that you only wanted those vendors that had at least 5
records
(Options A, B, C, D, E, F, ...) in your query, not where the sum of
the
number in the right had column totaled 5 or more. I see that Johns
three
query solution worked for you.

Good Luck