Query Help

[Guest]

Thank you for looking at this post

I have a table with 4 fields [Supplier], [Group], [AU], [Spend]

I would like to query this data an have a list of suppliers and the AU they
spend the most money with and how much money they spent with that AU. I have
tried to make this work, I just am not getting it. I have included a sample
table at the bottom of this post.

The second step (for my data) is to actually have the top5 AU's that a
supplier spend money with and then how much money they spend with that AU.
The data below doesn't contain enough unique values...but I can easily email
anyone enough data.

I am not sure of the best way to answer...I asked this question preciously
and I was never able to get the provided answer to work. What would work
best for me is a fully working version that I could either paste into an
empty query in SQL view or a screen shot of the Access query builder showing
how to set it up. Basically anything would actually work so long as the
query works. Once I get a working query I should be able to break it down
and figure out what I was doing wrong.

Thank you again for looking...any assistance would be greatly appreciated.

-John

Supplier Group AU Spend
Supplier1 Group1 AU1 155.81
Supplier3 Group1 AU1 226.05
Supplier5 Group1 AU1 542.91
Supplier2 Group1 AU1 244.34
Supplier2 Group1 AU2 89.68
Supplier4 Group1 AU2 127.32
Supplier1 Group1 AU2 383.94
Supplier3 Group1 AU2 363.46
Supplier3 Group1 AU3 392.89
Supplier5 Group1 AU3 67.95
Supplier2 Group1 AU3 248.86
Supplier4 Group1 AU3 360.77
Supplier4 Group1 AU4 300.22
Supplier1 Group2 AU4 347.78
Supplier3 Group2 AU4 14.59
Supplier5 Group2 AU4 311.73
Supplier5 Group2 AU5 49.13
Supplier2 Group2 AU5 549.12
Supplier4 Group2 AU5 95.01
Supplier1 Group3 AU6 359.73
Supplier3 Group3 AU6 405.95
Supplier5 Group3 AU6 9.12
Supplier2 Group3 AU7 146.1
Supplier4 Group3 AU7 477.67
Supplier1 Group3 AU7 353.62

 

[John Spencer]

First question is do you have only one record (at most) for each combination
of Supplier and AU?

If so, then to get the AU that the supplier spends the most money with is

SELECT Supplier, AU, Spend
FROM YourTableName
WHERE Spend =
(SELECT MAX(Spend)
FROM YourTableName as x
WHERE X.Supplier = YourTableName.Supplier)

To get the top 5 with ties for each supplier
SELECT Supplier, AU, Spend
FROM YourTableName
WHERE Spend =
(SELECT TOP 5 Spend
FROM YourTableName as x
WHERE X.Supplier = YourTableName.Supplier
ORDER BY Spend Desc)


Post back if you have records like the following where there are more than
one record for each combination of Supplier and AU

Supplier Group AU Spend
Supplier1 Group1 AU1 155.81
Supplier1 Group1 AU1 383.94

Supplier2 Group1 AU1 244.34
Supplier2 Group1 AU1 89.68

Supplier3 Group1 AU1 226.05
Supplier3 Group1 AU1 363.46
Supplier3 Group1 AU1 392.89

Supplier4 Group1 AU1 127.32

Supplier5 Group1 AU1 67.95
Supplier5 Group1 AU1 542.91

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

Thank you for looking at this post

I have a table with 4 fields [Supplier], [Group], [AU], [Spend]

I would like to query this data an have a list of suppliers and the AU
they
spend the most money with and how much money they spent with that AU. I
have
tried to make this work, I just am not getting it. I have included a
sample
table at the bottom of this post.

The second step (for my data) is to actually have the top5 AU's that a
supplier spend money with and then how much money they spend with that AU.
The data below doesn't contain enough unique values...but I can easily
email
anyone enough data.

I am not sure of the best way to answer...I asked this question preciously
and I was never able to get the provided answer to work. What would work
best for me is a fully working version that I could either paste into an
empty query in SQL view or a screen shot of the Access query builder
showing
how to set it up. Basically anything would actually work so long as the
query works. Once I get a working query I should be able to break it down
and figure out what I was doing wrong.

Thank you again for looking...any assistance would be greatly appreciated.

-John

Supplier Group AU Spend
Supplier1 Group1 AU1 155.81
Supplier3 Group1 AU1 226.05
Supplier5 Group1 AU1 542.91
Supplier2 Group1 AU1 244.34

Supplier2 Group1 AU2 89.68
Supplier4 Group1 AU2 127.32
Supplier1 Group1 AU2 383.94
Supplier3 Group1 AU2 363.46

Supplier3 Group1 AU3 392.89
Supplier5 Group1 AU3 67.95
Supplier2 Group1 AU3 248.86
Supplier4 Group1 AU3 360.77

Supplier4 Group1 AU4 300.22
Supplier1 Group2 AU4 347.78
Supplier3 Group2 AU4 14.59
Supplier5 Group2 AU4 311.73

Supplier5 Group2 AU5 49.13
Supplier2 Group2 AU5 549.12
Supplier4 Group2 AU5 95.01