creating a 3 month query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a databse that tracks hrs put into a days work. I require a query
that goes back 2 months from the current month and adds the hours of the
three months to create a total. It must do this regardless of the year. ie
in Jan of a year it will still go back the required 2 months.
 
Do you want three months to the day? For instance if it is now March 21st,
do you want to query from Jan 21st - Mar 21st or Jan - Mar?

Are you looking for all details or summary totals?
 
The query should reflect a grand total from the day of the current month,
regardless of the date, back to the first day of the month 2 months in
arrears. ie the 03 Mar 05 would go to 01 Jan 05, etc.
 
Try this:
WHERE Format([YourDateField],"yyyymm") BETWEEN
Format(DateAdd("m",-2,Date()),"yyyymm") And Format(Date(),"yyyymm")
 
Duane

This is what I'm using right now. Hope this helps. Inserting you info
generates a comma error.


SELECT DISTINCTROW Date() AS [As of], [Flight Stats].[Prime No],
Sum([Day]+[Night]) AS [90 Day Total], 30-[90 Day Total] AS Require
FROM [Flight Stats]
WHERE ((([Flight Stats].[Dates])>=DateSerial(Year(Date()),Month(Date())-2,1)))
GROUP BY [Flight Stats].[Prime No];


Duane Hookom said:
Try this:
WHERE Format([YourDateField],"yyyymm") BETWEEN
Format(DateAdd("m",-2,Date()),"yyyymm") And Format(Date(),"yyyymm")

--
Duane Hookom
MS Access MVP


rdwest01 said:
The query should reflect a grand total from the day of the current month,
regardless of the date, back to the first day of the month 2 months in
arrears. ie the 03 Mar 05 would go to 01 Jan 05, etc.
Click to expand...
Click to expand...
 
Are you currently getting an error message? I would think this should work:

SELECT Date() AS [As of], [Prime No],
Sum([Day]+[Night]) AS [90 Day Total], 30-Sum([Day]+[Night]) AS Require
FROM [Flight Stats]
WHERE [Dates]>=DateSerial(Year(Date()),Month(Date())-2,1)
GROUP BY Date(), [Prime No];


--
Duane Hookom
MS Access MVP
--

rdwest01 said:
Duane

This is what I'm using right now. Hope this helps. Inserting you info
generates a comma error.


SELECT DISTINCTROW Date() AS [As of], [Flight Stats].[Prime No],
Sum([Day]+[Night]) AS [90 Day Total], 30-[90 Day Total] AS Require
FROM [Flight Stats]
WHERE ((([Flight
Stats].[Dates])>=DateSerial(Year(Date()),Month(Date())-2,1)))
GROUP BY [Flight Stats].[Prime No];


Duane Hookom said:
Try this:
WHERE Format([YourDateField],"yyyymm") BETWEEN
Format(DateAdd("m",-2,Date()),"yyyymm") And Format(Date(),"yyyymm")

--
Duane Hookom
MS Access MVP


rdwest01 said:
The query should reflect a grand total from the day of the current
month,
regardless of the date, back to the first day of the month 2 months in
arrears. ie the 03 Mar 05 would go to 01 Jan 05, etc.

:

Do you want three months to the day? For instance if it is now March
21st,
do you want to query from Jan 21st - Mar 21st or Jan - Mar?

Are you looking for all details or summary totals?

--
Duane Hookom
MS Access MVP
--

I have a databse that tracks hrs put into a days work. I require a
query
that goes back 2 months from the current month and adds the hours of
the
three months to create a total. It must do this regardless of the
year.
ie
in Jan of a year it will still go back the required 2 months.
Click to expand...
Click to expand...
Click to expand...
 
Yes,thanks

Duane Hookom said:
Are you currently getting an error message? I would think this should work:

SELECT Date() AS [As of], [Prime No],
Sum([Day]+[Night]) AS [90 Day Total], 30-Sum([Day]+[Night]) AS Require
FROM [Flight Stats]
WHERE [Dates]>=DateSerial(Year(Date()),Month(Date())-2,1)
GROUP BY Date(), [Prime No];


--
Duane Hookom
MS Access MVP
--

rdwest01 said:
Duane

This is what I'm using right now. Hope this helps. Inserting you info
generates a comma error.


SELECT DISTINCTROW Date() AS [As of], [Flight Stats].[Prime No],
Sum([Day]+[Night]) AS [90 Day Total], 30-[90 Day Total] AS Require
FROM [Flight Stats]
WHERE ((([Flight
Stats].[Dates])>=DateSerial(Year(Date()),Month(Date())-2,1)))
GROUP BY [Flight Stats].[Prime No];


Duane Hookom said:
Try this:
WHERE Format([YourDateField],"yyyymm") BETWEEN
Format(DateAdd("m",-2,Date()),"yyyymm") And Format(Date(),"yyyymm")

--
Duane Hookom
MS Access MVP


The query should reflect a grand total from the day of the current
month,
regardless of the date, back to the first day of the month 2 months in
arrears. ie the 03 Mar 05 would go to 01 Jan 05, etc.

:

Do you want three months to the day? For instance if it is now March
21st,
do you want to query from Jan 21st - Mar 21st or Jan - Mar?

Are you looking for all details or summary totals?

--
Duane Hookom
MS Access MVP
--

I have a databse that tracks hrs put into a days work. I require a
query
that goes back 2 months from the current month and adds the hours of
the
three months to create a total. It must do this regardless of the
year.
ie
in Jan of a year it will still go back the required 2 months.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top