Maximum Sales

[Sandra]

I have a database with the following fields: year, month,
location1, location2, location3, etc. "Location1"
contains the sales figures for a particular division.
Location2 contains the sales figures for another
division, etc.) There are 12 years listed under the year
field.

I want to show which year has the most sales for June for
a particular location. Which year has the most sales
for July for a particular location. And for each other
month for each location. In other words, which was the
best June for all time? Which was the best August for
all time?

I see "Max" in the expression builder, but I don't know
how to use it.

Help!!

 

[John Spencer (MVP)]

With you non-normalized structure this would be tough to do for all the columns
at one time. This simple query should work for getting one column

SELECT TOP 1 [Year], [Month], Location1
FROM TheTable
ORDER BY Location1 DESC

What you need to do is to normalize the data. In the meantime try using a union
query as the source of another query

SELECT [Year], [Month], Location1, "Loc1" as Division
FROM TheTable
UNION ALL
SELECT [Year], [Month], Location2, "Loc2"
FROM TheTable
UNION ALL
SELECT [Year], [Month], Location3, "Loc3"
FROM TheTable

Saved as QNormal. Then you use that in a query something like the following
UNTESTED SQL.

SELECT Division, [Year], [Month], Location1
FROM QNormal as Q
WHERE Q.Location1 =
(SELECT Max(T.Location1)
FROM QNormal as T
WHERE T.Division = Q.Division)

 

[Sandra]

I'm sorry to be so dumb. But where would I put this code?
I only know how to put info in the form that comes up
when you choose query - drag fields and put a few things
in the criteria line. I can do a LITTLE in the expression
builder.

Please don't give up on me. I can learn!
Thanks.

-----Original Message-----
With you non-normalized structure this would be tough to do for all the columns
at one time. This simple query should work for getting one column

SELECT TOP 1 [Year], [Month], Location1
FROM TheTable
ORDER BY Location1 DESC

What you need to do is to normalize the data. In the meantime try using a union
query as the source of another query

SELECT [Year], [Month], Location1, "Loc1" as Division
FROM TheTable
UNION ALL
SELECT [Year], [Month], Location2, "Loc2"
FROM TheTable
UNION ALL
SELECT [Year], [Month], Location3, "Loc3"
FROM TheTable

Saved as QNormal. Then you use that in a query something like the following
UNTESTED SQL.

SELECT Division, [Year], [Month], Location1
FROM QNormal as Q
WHERE Q.Location1 =
(SELECT Max(T.Location1)
FROM QNormal as T
WHERE T.Division = Q.Division)

I have a database with the following fields: year, month,
location1, location2, location3, etc. "Location1"
contains the sales figures for a particular division.
Location2 contains the sales figures for another
division, etc.) There are 12 years listed under the year
field.

I want to show which year has the most sales for June for
a particular location. Which year has the most sales
for July for a particular location. And for each other
month for each location. In other words, which was the
best June for all time? Which was the best August for
all time?

I see "Max" in the expression builder, but I don't know
how to use it.

Help!!

.