Creating a select query to find the MAX volume between certain dates

[Christina Wise via AccessMonster.com]

I have a database with the following fields:
Date Volume

Specifically, I am trying to write a query that will select the record
(Volume) if it is the highest in the last three months.

I created a new column called Date2 contains the date three months prior to
the orignal date (DateAdd("m",-3,[Date])

But I am not having much success writing a select query using the MAX
function and limiting it by dates. Am I approaching this incorrectly? Any
help would be greatly appreciated.

Christina

 

[fredg]

I have a database with the following fields:
Date Volume

Specifically, I am trying to write a query that will select the record
(Volume) if it is the highest in the last three months.

I created a new column called Date2 contains the date three months prior to
the orignal date (DateAdd("m",-3,[Date])

But I am not having much success writing a select query using the MAX
function and limiting it by dates. Am I approaching this incorrectly? Any
help would be greatly appreciated.

Christina

No need for the Date2 column.

SELECT Max(YourTable.[Volume]) AS MaxOfVolume
FROM YourTable
HAVING (((YourTable.DateField) Between Date() And
DateAdd("m",-3,Date())));


NOTE!!!!
Date is a reserved Access/VBA/Jet word and should not be used as a
field name. (It will get confused between [Date] and Date().)
See the Microsoft KnowledgeBase article for your version of Access for
other reserved words:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

Change your field named "Date" to some other name, perhaps "SalesDate"
or "dteDate".

 

[Christina Wise via AccessMonster.com]

Fred,
Thanks so much for the help, if you have a few minutes would you mind a
follow-up question?

I tried it but am messing something up. I changed the field name "Date" to
"DateField". The table's name is "Microsoft." I created a seperate
column/field named Volume2 and in the "Field" line of the query typed in
the following:

Volume2: SELECT Max(Microsoft.[Volume]) AS MaxOfVolume
FROM Microsoft
HAVING (((Microsoft.DateField) Between Date() And
DateAdd("m",-3,Date())))

When I run it I get a syntex error message. I'm not sure if I'm missing a
parentheses somewhere or am messing up something else. I really do
appreciate your help as I've spent the better part of the afternoon trying
to get this thing to work.

Christina.

 

[John Vinson]

I have a database with the following fields:
Date Volume

Specifically, I am trying to write a query that will select the record
(Volume) if it is the highest in the last three months.

I created a new column called Date2 contains the date three months prior to
the orignal date (DateAdd("m",-3,[Date])

But I am not having much success writing a select query using the MAX
function and limiting it by dates. Am I approaching this incorrectly? Any
help would be greatly appreciated.

Christina

I think a somewhat different Subquery would be appropriate here.

Try

SELECT <whatever>
FROM [Microsoft]
WHERE Volume =
(SELECT Max([Volume]) FROM [Microsoft] AS M
WHERE M.[Date] BETWEEN DateAdd("m", -3, Date()) AND Date())

This will show all tied records if there is a tie for largest volume
over the last three months.

John W. Vinson[MVP]

 

[fredg]

Fred,
Thanks so much for the help, if you have a few minutes would you mind a
follow-up question?

I tried it but am messing something up. I changed the field name "Date" to
"DateField". The table's name is "Microsoft." I created a seperate
column/field named Volume2 and in the "Field" line of the query typed in
the following:

Volume2: SELECT Max(Microsoft.[Volume]) AS MaxOfVolume
FROM Microsoft
HAVING (((Microsoft.DateField) Between Date() And
DateAdd("m",-3,Date())))

When I run it I get a syntex error message. I'm not sure if I'm missing a
parentheses somewhere or am messing up something else. I really do
appreciate your help as I've spent the better part of the afternoon trying
to get this thing to work.

Christina.

What I sent you was meant to be typed directly into the query SQL, not
in the query QBE grid.

Open the Query SQL window by clicking View + SQL
Then delete

Volume2:

It should then work.

If you wish to name the new column Volume2 instead of MaxOfVolume,
then change the select statement to:

SELECT Max(Microsoft.[Volume]) AS Volume2
etc.

 

[Christina Wise via AccessMonster.com]

Thanks to both of you. I got it to work using both formulas. Sorry for the
stupidity on my part about writing it as an SQL statement vs. writing it in
the design grid. I'm still something of a novice at this. In anycase, I
really appreciate the help. Christina.