Simple query??

  • Thread starter Thread starter Leslie Isaacs
  • Start date Start date
L

Leslie Isaacs

Hello All

I have been wrestling for ages now with a query that I thought would be
simple .. but can't get the output I want!

I have a table [practice suppliers] with fields SUPPLIER and AUTO CATEGORY
(and others). The field SUPPLIER is not a unique (key) field, but in fact it
almost is: ~95% of the records in the table contain unique values in this
field.Of the remaining ~5%, about half of the records for a given SUPPLIER
value contain the same value of AUTO CATEGORY, and the other half contain
different values for AUTO CATEGORY.

I want a query that will return the number of different values of AUTO
CATEGORY for each SUPPLIER. For the ~90%, and the first half of the
remaining ~5% (as referred to above), the query should return '1' as the
number of different values of AUTO CATEGORY ... but for the other half of
the ~5% it should return the actual number of different AUTO CATEGORY values
for the SUPPLIER.

My attempts (which seem to keep coming back to the one below) keep returning
the total number of AUTO CATEGORY values for each SUPPLIER - e.g. where
there are 8 records in the table with the same value of SUPPLIER and AUTO
CATEGORY, my query returns '8' - whereas it should return '1'. If the same
SUPPLIER appears 8 times with, say, 5 different AUTO CATEGORY values, the
query should return '5'.

Hope someone can help - this is driving me nuts!

Thanks for any help.
Leslie Isaacs

My 'attempt':
SELECT [practice suppliers].SUPPLIER, Count([practice suppliers].[AUTO
CATEGORY]) AS [CountOfAUTO CATEGORY]
FROM [practice suppliers]
GROUP BY [practice suppliers].SUPPLIER;
 
Two query solution.

SELECT DISTINCT [practice suppliers].SUPPLIER
,[AUTO CATEGORY]
FROM [practice suppliers]

Use that query (Saved) as the source for the following query

SELECT SUPPLIER
, Count([AUTO CATEGORY]) AS [CountOfAUTO CATEGORY]
FROM [The Name of the Saved Query]
GROUP BY SUPPLIER;

If your field and table names consisted of only letter, number, and
underscore characters, you could do that in one query. One of many
reasons to name tables and fields without spaces or other
non-alphanumeric characters.

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

Thanks for your answer.
Does it have to be 2 queries then?
I can see that works, so will obviously use the 2 ... but curiosity drives
me to wonder how it could be done in one - even with my bad field names!

Thanks again
Les


John Spencer said:
Two query solution.

SELECT DISTINCT [practice suppliers].SUPPLIER
,[AUTO CATEGORY]
FROM [practice suppliers]

Use that query (Saved) as the source for the following query

SELECT SUPPLIER
, Count([AUTO CATEGORY]) AS [CountOfAUTO CATEGORY]
FROM [The Name of the Saved Query]
GROUP BY SUPPLIER;

If your field and table names consisted of only letter, number, and
underscore characters, you could do that in one query. One of many
reasons to name tables and fields without spaces or other non-alphanumeric
characters.

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


Leslie said:
Hello All

I have been wrestling for ages now with a query that I thought would be
simple .. but can't get the output I want!

I have a table [practice suppliers] with fields SUPPLIER and AUTO
CATEGORY (and others). The field SUPPLIER is not a unique (key) field,
but in fact it almost is: ~95% of the records in the table contain unique
values in this field.Of the remaining ~5%, about half of the records for
a given SUPPLIER value contain the same value of AUTO CATEGORY, and the
other half contain different values for AUTO CATEGORY.

I want a query that will return the number of different values of AUTO
CATEGORY for each SUPPLIER. For the ~90%, and the first half of the
remaining ~5% (as referred to above), the query should return '1' as the
number of different values of AUTO CATEGORY ... but for the other half of
the ~5% it should return the actual number of different AUTO CATEGORY
values for the SUPPLIER.

My attempts (which seem to keep coming back to the one below) keep
returning the total number of AUTO CATEGORY values for each SUPPLIER -
e.g. where there are 8 records in the table with the same value of
SUPPLIER and AUTO CATEGORY, my query returns '8' - whereas it should
return '1'. If the same SUPPLIER appears 8 times with, say, 5 different
AUTO CATEGORY values, the query should return '5'.

Hope someone can help - this is driving me nuts!

Thanks for any help.
Leslie Isaacs

My 'attempt':
SELECT [practice suppliers].SUPPLIER, Count([practice suppliers].[AUTO
CATEGORY]) AS [CountOfAUTO CATEGORY]
FROM [practice suppliers]
GROUP BY [practice suppliers].SUPPLIER;
Click to expand...
Click to expand...
 
Access won't let you do it in one query with your names.

If the names were changed to PracticeSuppier (or Practice_Supplier) for
the table and AutoCategory (or Auto_Category) for the field then the
query would look like the following.

SELECT SUPPLIER
, Count(AUTOCATEGORY) AS [CountOfAUTO CATEGORY]
FROM
(SELECT DISTINCT practiceSuppliers.SUPPLIER
, AUTO CATEGORY
FROM practiceSuppliers) as Tmp
GROUP BY SUPPLIER;

Which when it was saved would become

SELECT SUPPLIER
, Count(AUTOCATEGORY) AS [CountOfAUTO CATEGORY]
FROM
[SELECT DISTINCT practiceSuppliers.SUPPLIER
, AUTO CATEGORY
FROM practiceSuppliers]. as Tmp
GROUP BY SUPPLIER;

Access will not allow any square brackets in the sub-query in a from
clause. In my opinion, it should, but I don't have a controlling
interest in Microsoft so I doubt that I will be able to force a change.

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


Leslie said:
Hello John

Thanks for your answer.
Does it have to be 2 queries then?
I can see that works, so will obviously use the 2 ... but curiosity drives
me to wonder how it could be done in one - even with my bad field names!

Thanks again
Les


John Spencer said:
Two query solution.

SELECT DISTINCT [practice suppliers].SUPPLIER
,[AUTO CATEGORY]
FROM [practice suppliers]

Use that query (Saved) as the source for the following query

SELECT SUPPLIER
, Count([AUTO CATEGORY]) AS [CountOfAUTO CATEGORY]
FROM [The Name of the Saved Query]
GROUP BY SUPPLIER;

If your field and table names consisted of only letter, number, and
underscore characters, you could do that in one query. One of many
reasons to name tables and fields without spaces or other non-alphanumeric
characters.

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


Leslie said:
Hello All

I have been wrestling for ages now with a query that I thought would be
simple .. but can't get the output I want!

I have a table [practice suppliers] with fields SUPPLIER and AUTO
CATEGORY (and others). The field SUPPLIER is not a unique (key) field,
but in fact it almost is: ~95% of the records in the table contain unique
values in this field.Of the remaining ~5%, about half of the records for
a given SUPPLIER value contain the same value of AUTO CATEGORY, and the
other half contain different values for AUTO CATEGORY.

I want a query that will return the number of different values of AUTO
CATEGORY for each SUPPLIER. For the ~90%, and the first half of the
remaining ~5% (as referred to above), the query should return '1' as the
number of different values of AUTO CATEGORY ... but for the other half of
the ~5% it should return the actual number of different AUTO CATEGORY
values for the SUPPLIER.

My attempts (which seem to keep coming back to the one below) keep
returning the total number of AUTO CATEGORY values for each SUPPLIER -
e.g. where there are 8 records in the table with the same value of
SUPPLIER and AUTO CATEGORY, my query returns '8' - whereas it should
return '1'. If the same SUPPLIER appears 8 times with, say, 5 different
AUTO CATEGORY values, the query should return '5'.

Hope someone can help - this is driving me nuts!

Thanks for any help.
Leslie Isaacs

My 'attempt':
SELECT [practice suppliers].SUPPLIER, Count([practice suppliers].[AUTO
CATEGORY]) AS [CountOfAUTO CATEGORY]
FROM [practice suppliers]
GROUP BY [practice suppliers].SUPPLIER;
Click to expand...
Click to expand...
Click to expand...
 
John

Many thanks for that: I'll just email Mr Gates ...!

Thanks again
Les

John Spencer said:
Access won't let you do it in one query with your names.

If the names were changed to PracticeSuppier (or Practice_Supplier) for
the table and AutoCategory (or Auto_Category) for the field then the
query would look like the following.

SELECT SUPPLIER
, Count(AUTOCATEGORY) AS [CountOfAUTO CATEGORY]
FROM
(SELECT DISTINCT practiceSuppliers.SUPPLIER
, AUTO CATEGORY
FROM practiceSuppliers) as Tmp
GROUP BY SUPPLIER;

Which when it was saved would become

SELECT SUPPLIER
, Count(AUTOCATEGORY) AS [CountOfAUTO CATEGORY]
FROM
[SELECT DISTINCT practiceSuppliers.SUPPLIER
, AUTO CATEGORY
FROM practiceSuppliers]. as Tmp
GROUP BY SUPPLIER;

Access will not allow any square brackets in the sub-query in a from
clause. In my opinion, it should, but I don't have a controlling
interest in Microsoft so I doubt that I will be able to force a change.

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


Leslie said:
Hello John

Thanks for your answer.
Does it have to be 2 queries then?
I can see that works, so will obviously use the 2 ... but curiosity drives
me to wonder how it could be done in one - even with my bad field names!

Thanks again
Les


John Spencer said:
Two query solution.

SELECT DISTINCT [practice suppliers].SUPPLIER
,[AUTO CATEGORY]
FROM [practice suppliers]

Use that query (Saved) as the source for the following query

SELECT SUPPLIER
, Count([AUTO CATEGORY]) AS [CountOfAUTO CATEGORY]
FROM [The Name of the Saved Query]
GROUP BY SUPPLIER;

If your field and table names consisted of only letter, number, and
underscore characters, you could do that in one query. One of many
reasons to name tables and fields without spaces or other non-alphanumeric
characters.

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


Leslie Isaacs wrote:
Hello All

I have been wrestling for ages now with a query that I thought would be
simple .. but can't get the output I want!

I have a table [practice suppliers] with fields SUPPLIER and AUTO
CATEGORY (and others). The field SUPPLIER is not a unique (key) field,
but in fact it almost is: ~95% of the records in the table contain unique
values in this field.Of the remaining ~5%, about half of the records for
a given SUPPLIER value contain the same value of AUTO CATEGORY, and the
other half contain different values for AUTO CATEGORY.

I want a query that will return the number of different values of AUTO
CATEGORY for each SUPPLIER. For the ~90%, and the first half of the
remaining ~5% (as referred to above), the query should return '1' as the
number of different values of AUTO CATEGORY ... but for the other half of
the ~5% it should return the actual number of different AUTO CATEGORY
values for the SUPPLIER.

My attempts (which seem to keep coming back to the one below) keep
returning the total number of AUTO CATEGORY values for each SUPPLIER -
e.g. where there are 8 records in the table with the same value of
SUPPLIER and AUTO CATEGORY, my query returns '8' - whereas it should
return '1'. If the same SUPPLIER appears 8 times with, say, 5 different
AUTO CATEGORY values, the query should return '5'.

Hope someone can help - this is driving me nuts!

Thanks for any help.
Leslie Isaacs

My 'attempt':
SELECT [practice suppliers].SUPPLIER, Count([practice suppliers].[AUTO
CATEGORY]) AS [CountOfAUTO CATEGORY]
FROM [practice suppliers]
GROUP BY [practice suppliers].SUPPLIER;
Click to expand...
Click to expand...
Click to expand...
 
Back
Top