Top Values Query combined with a Sum Query

[Guest]

Hello,

I am stumped, I think using the Top values query I should be able to pull a
query that will list a Customer, as well as the Top three Types of Orders (we
call them styles) they have ordered as well as how many times each was
ordered.

I am trying to use the method provided on this KB
http://support.microsoft.com/kb/q153747/ but maybe it won't work in this
situation.

What I have is two queries , that are listed below, can anybody tell me if
this is possbile, I have tried every combination I can think of and the query
only pulls the Top 3 Values, regardless of Customer.

aaaaaqryGetTop3Styles:

SELECT tblOrders.CustomerID, tblOrders.PGStyle, Count(tblOrders.PGStyle) AS
CountOfPGStyle
FROM tblOrders, tblTempFilterQueries
WHERE (((tblOrders.OrderConfirmDate) Between [TempFilterstartdate] And
[TempFilterEndDate]))
GROUP BY tblOrders.CustomerID, tblOrders.PGStyle
ORDER BY Count(tblOrders.PGStyle) DESC;

And then aaaaaqryTop3Dealer to pull the values by each Customer:

SELECT tblCustomers.CustomerID, aaaaaqryGetTop3Styles.CountOfPGStyle,
aaaaaqryGetTop3Styles.PGStyle
FROM tblCustomers INNER JOIN aaaaaqryGetTop3Styles ON
tblCustomers.CustomerID = aaaaaqryGetTop3Styles.CustomerID
WHERE (((aaaaaqryGetTop3Styles.CountOfPGStyle) In (Select Top 3
[CountOfPGStyle] From aaaaaqryGetTop3Styles Where
[aaaaaqryGetTop3Styles].[CustomerID]=[CustomerID] Order By [CountOfPGStyle]
DESC)))
ORDER BY tblCustomers.CustomerID, aaaaaqryGetTop3Styles.CountOfPGStyle;

Thanks for any assitance, Rick.

 

[Guest]

Sorry just bumping this to the top, I am really stumped as too why this
doesn't work, any help would be saintly at this point!

-Rick

 

[David S via AccessMonster.com]

Hi Rick,

The only difference I can see between your query and the Microsofot one is
that theirs works off a table joib, whereas yours is all off the one query. I
can't imagine why this would be different, but I suspect that it may be
getting a bit confused about as to which Customer ID it shoudl be mapping on.
It seems to be finding the Top 3 for any customer, and then matching this
back to the Count in your select.

I tried this, and it seemed to work:
SELECT aaaaaqryGetTop3Styles.CustomerID, aaaaaqryGetTop3Styles.CountOfPGStyle,
aaaaaqryGetTop3Styles.PGStyle
FROM aaaaaqryGetTop3Styles
WHERE (((aaaaaqryGetTop3Styles.PGStyle) In (Select Top 3 [PGStyle] From
aaaaaqryGetTop3Styles as aaaaaqryGetTop3Styles_1 Where
[aaaaaqryGetTop3Styles_1].[CustomerID]=[CustomerID] Order By [CountOfPGStyle]
DESC)))
ORDER BY aaaaaqryGetTop3Styles.CustomerID, aaaaaqryGetTop3Styles.
CountOfPGStyle DESC;

The difference is that I've asked it to get the matching PGStyles instead of
on the counts...

 

[Guest]

David,

Thanks, I see my error with matching the wrong fields. But it seems to work
correct, but when you test the data the Sub Query is pulling the Top 3 Styles
regardless of Customer, and then the counts of those Styles in the Main
Query. Which makes sense, but by using
[aaaaaqryGetTop3Styles_1].[CustomerID]=[CustomerID] in the WHERE condition
shouldn't the sub query pull only the top three for that specific customer.
Do I need to somehow make sure the [CustomerID] is from this query? Something
like [aaaaaqryGetTop3Styles_1].[CustomerID]=[ThePresentQuery].[CustomerID]?

It's probally difficult to tell with out my data, but does this make any
sense? Thanks again for any help!

-Rick