Correlated subquery with COUNT and TOP

[mattdrinks]

Hi,
I have been on this for a few days now and can not seem to get it working correctly. Any help would be great.
I have a single table and am trying to write some SQL to produce a summary query.

WHAT I HAVE IN THE TABLE
Part Group Range No1 No2 No3
BP1440 BPAD ONE 100 78 50
BP1450 BPAD ONE 100 78 50
BP1460 BPAD ONE 120 90 75
BP1470 BPAD ONE 100 78 50
DI1000 DISC DI2 115 85 60
DI1001 DISC DI2 115 85 60
DI1002 DISC DI2 115 85 60
DI1003 SPEC DI2 200 150 100
DI1004 SPEC DI2 200 150 100
only more records.

WHAT I AM TRYING TO ACHEIVE
Letters Group Range No1 No2 No3
BP BPAD ONE 100 78 50
DI DISC DI2 115 85 60

WHY
This is a summary of the data showing the most popular group, range, No1, No2, and No3 for groups of parts that start with the same letters, so that any new part can be assigned these values when it is added to the database.

WHAT I HAVE SO FAR
I have written a Custom function that returns the first set of letters of a string starting from the left. This bit works and the function is called LEFTLETTERS. I have used this in the following query.

SELECT Count(tblMAMExprod.[Supplier Part]) AS [CountOfSupplier Part], LEFTLETTERS([Supplier Part]) AS Letters, tblMAMExprod.[Product Group], tblMAMExprod.Range, tblMAMExprod.Mkon1, tblMAMExprod.Mkon2, tblMAMExprod.Mkon3
FROM tblMAMExprod
GROUP BY LEFTLETTERS([Supplier Part]), tblMAMExprod.[Product Group], tblMAMExprod.Range, tblMAMExprod.Mkon1, tblMAMExprod.Mkon2, tblMAMExprod.Mkon3;

This produces a row for each unique group with a total of how many parts are in that group, so for the above data it would return:

PartCount Letters Group Range No1 No2 No3
3 BP BPAD ONE 100 78 50
1 BP BPAD ONE 120 90 75
3 DI DISC DI2 115 85 60
2 DI SPEC DI2 200 150 100

WHAT I WOULD LIKE
The query to return just the highest count for each group eg. for above data:

3 BP BPAD ONE 100 78 50
3 DI DISC DI2 115 85 60

I would like to to this with just one query I am sure it is possible with a correlated subquery using TOP 1, but so far when I have tried it Access returns errors such as:
Aggreate function not permitted as part of Sub query
and:
Subquery returns only one row

Does anyone know the SQL that will do this?

 

[r937]

add the following to your existing query:

HAVING Count(tblMAMExprod.[Supplier Part])
= ( select TOP 1
Count(tblMAMExprod.[Supplier Part])
FROM tblMAMExprod
GROUP
BY LEFTLETTERS([Supplier Part])
, tblMAMExprod.[Product Group]
, tblMAMExprod.Range
, tblMAMExprod.Mkon1
, tblMAMExprod.Mkon2
, tblMAMExprod.Mkon3
ORDER
BY Count(tblMAMExprod.[Supplier Part]) DESC
)

rudy

 

[mattdrinks]

Thanks for the quick response rudy,
that has almost done it (I was using WHERE instead of HAVING!)

But the query returns the group with highest count, I would like a row with the highest count for each group.

The current query now looks like this:
SELECT
Count(tblMAMExprod.[Supplier Part]) AS [CountOfSupplier Part],
LEFTLETTERS([tblMAMExprod].[Supplier Part]) AS Letters,
tblMAMExprod.[Product Group],
tblMAMExprod.Range,
tblMAMExprod.Mkon1,
tblMAMExprod.Mkon2,
tblMAMExprod.Mkon3
FROM tblMAMExprod
GROUP BY
LEFTLETTERS([tblMAMExprod].[Supplier Part]),
tblMAMExprod.[Product Group],
tblMAMExprod.Range,
tblMAMExprod.Mkon1,
tblMAMExprod.Mkon2,
tblMAMExprod.Mkon3
HAVING (((Count(tblMAMExprod.[Supplier Part]))=
(SELECT TOP 1 Count(tblMAMExprod.[Supplier Part])
FROM tblMAMExprod
GROUP BY
LEFTLETTERS(tblMAMExprod.[Supplier Part]),
tblMAMExprod.[Product Group],
tblMAMExprod.Range,
tblMAMExprod.Mkon1,
tblMAMExprod.Mkon2,
tblMAMExprod.Mkon3
ORDER BY
Count(tblMAMExprod.[Supplier Part]) DESC
)));

I think I need a WHERE in the subquery so it returns just the rows that match the current row from the main query.
I tried this:

SELECT
Count(tblMAMExprod.[Supplier Part]) AS [CountOfSupplier Part],
LEFTLETTERS(tblMAMExprod.[Supplier Part]) AS Letters,
tblMAMExprod.[Product Group],
tblMAMExprod.Range,
tblMAMExprod.Mkon1,
tblMAMExprod.Mkon2,
tblMAMExprod.Mkon3
FROM tblMAMExprod, tblMAMExprod AS tblMAMExprod_1
GROUP BY
LEFTLETTERS(tblMAMExprod.[Supplier Part]),
tblMAMExprod.[Product Group],
tblMAMExprod.Range,
tblMAMExprod.Mkon1,
tblMAMExprod.Mkon2,
tblMAMExprod.Mkon3
HAVING (((Count(tblMAMExprod.[Supplier Part]))=
(SELECT TOP 1 Count(tblMAMExprod.[Supplier Part])
FROM tblMAMExprod
WHERE
LEFTLETTERS([tblMAMExprod].[Supplier Part]) =
LEFTLETTERS([tblMAMExprod_1].[Supplier Part])
GROUP BY
LEFTLETTERS(tblMAMExprod.[Supplier Part]),
tblMAMExprod.[Product Group],
tblMAMExprod.Range,
tblMAMExprod.Mkon1,
tblMAMExprod.Mkon2,
tblMAMExprod.Mkon3
ORDER BY
Count(tblMAMExprod.[Supplier Part]) DESC )));

but get the error message:
You tried to execute a query that does not include the specified expression 'Supplier Part' as part of an aggregate function.

Any ideas?
Matt

 

[r937]

well, one thing you could do is take the query that produces the counts

PartCount Letters Group Range No1 No2 No3
3 BP BPAD ONE 100 78 50
1 BP BPAD ONE 120 90 75
3 DI DISC DI2 115 85 60
2 DI SPEC DI2 200 150 100

and save that query

then you can write your next query to select from the saved query

that should make it a lot easier to write, using a correlated subquery

i think

select PartCount, Letters, Group
, Range, No1, No2, No3
from savedquery XX
where PartCount=
( select max(Partcount)
from savedquery
where Letters=XX.Letters
and Group=XX.Group
and Range=XX.Range)


by the way, are there always 2 letters, or did you have to write the function because it varies?


rudy

 

[mattdrinks]

I think you are right two queries will be easier than one.

Unfortunatley there are not always two letters, there can be from 0 to 20 letters, they are not always at the start of the string and there can be more than one group seperated by numbers.
eg.
A123
A123A
123AA
AA12AA3
are all valid part numbers.

The function I use is

Public Function LEFTLETTERS(Text As String) As String

'custom function for SQL
'to return the first group of letters from a string
'starting from the left

On Error GoTo ErrorHandler

Dim i As Integer
Dim strTemp As String
Dim booFound As Boolean
Dim booEnd As Boolean

booFound = False
booEnd = False
i = 0

'loop through each char of the string one at time
Do
    i = i + 1
    'check asci code is related to a letter
    If (Asc(Mid(Text, i, 1)) >= 65 And Asc(Mid(Text, 1, 1)) <= 90) _
    Or (Asc(Mid(Text, i, 1)) >= 97 And Asc(Mid(Text, 1, 1)) <= 122) Then
        'if true then add letter to temp string
        booFound = True
        strTemp = strTemp & Mid(Text, i, 1)
    Else
        'if a char that is not a letter is found then
        'check to see if letters have been found
        If booFound = True Then
            'set loop to end as we don't want any more letters
            booEnd = True
        End If
    End If
    'check to see if end of string has been reached
    If i = Len(Text) Then
        'set loop to end
        booEnd = True
    End If
Loop Until booEnd = True

LEFTLETTERS = strTemp
Exit Function

ErrorHandler:

LEFTLETTERS = &quot;#ERR&quot;

End Function

I will also use the same query with a soundex function, and give the user the option of which to use.
The aim is to match Parts in a file provided by a supplier (Monthly update up to 20,000 parts, some just parts just price changes, others new parts.) to similar Parts already in the database so they go into the same group/range combinations.

The problem is that there are about 75 suppliers and NO standard file layout or type. Some files are CSV other Excel others Tab delimted.

The query is part of a wizard used to help keep the database up to date.

Thanks for you help rudy, I have read many of your posts and they have been very useful.

Matt

 

[mattdrinks]

I have finally managed to get this query working and thought I would show how I did just in case some else needs to do it at a later date.

Firstly I created a Temporary Table (tblTEMPSummary) that had the same fields as the count query plus one extra field that was of autonumber type to create a primary key.
Then I made the count query an append query so it added the results to the temporary query. The count query no looks like this:
INSERT INTO tblTEMPSummary
(CountOfParts,
GroupIdentifer,
[Product Group],
Range,
Mkon1,
Mkon2,
Mkon3)
SELECT
Count(tblMAMExprod.[Supplier Part]) AS [CountOfSupplier Part],
LEFTLETTERS([Supplier Part]) AS Letters,
tblMAMExprod.[Product Group],
tblMAMExprod.Range,
tblMAMExprod.Mkon1,
tblMAMExprod.Mkon2,
tblMAMExprod.Mkon3
FROM tblMAMExprod
GROUP BY
LEFTLETTERS([Supplier Part]),
tblMAMExprod.[Product Group],
tblMAMExprod.Range,
tblMAMExprod.Mkon1,
tblMAMExprod.Mkon2,
tblMAMExprod.Mkon3;

This left me with a table looking something like this:
Auto PartCount Letters Group Range No1 No2 No3
1 3 BP BPAD ONE 100 78 50
2 1 BP BPAD ONE 120 90 75
3 3 DI DISC DI2 115 85 60
4 2 DI SPEC DI2 200 150 100

I then used the following query to select the top row for each group:
SELECT
tblTEMPSummary.AutoKey,
tblTEMPSummary.CountOfParts,
tblTEMPSummary.GroupIdentifer,
tblTEMPSummary.[Product Group],
tblTEMPSummary.Range,
tblTEMPSummary.Mkon1,
tblTEMPSummary.Mkon2,
tblTEMPSummary.Mkon3
FROM tblTEMPSummary
WHERE tblTEMPSummary.AutoKey In (
SELECT TOP 1
T1.AutoKey
FROM
tblTEMPSummary as T1
WHERE
T1.GroupIdentifer = tblTEMPSummary.GroupIdentifer
ORDER BY
T1.CountOfParts DESC
);

This also seems to execute faster as the correlated subquery is run against the summary table which has a lot less rows in it compared to the orginal data table.

This works very well for me although I am sure there is probably other ways to do it.

Thanks for the help
Matt