Query Totalling Bi-Monthly

[Joy]

I'm trying to make a query that totals bi-monthly rather then monthly, can't
quite get it. Any help would be greatly appreciated.
This is want I'm playing with

SELECT DISTINCTROW Format$([ReceiptDetails].[CDate],'mmm yyyy') AS [CDate
By Month], Sum(ReceiptDetails.Fee) AS [Sum Of Fee], Count(*) AS [Count Of
ReceiptDetails]
FROM ReceiptDetails
WHERE (((ReceiptDetails.CDate) Between [Forms]![OpeningPage]![List21] And
[Forms]![OpeningPage]![EndDate]))
GROUP BY Format$([ReceiptDetails].[CDate],'mmm yyyy'),
Year([ReceiptDetails].[CDate])*12+DatePart('m',[ReceiptDetails].[CDate])-1;

 

[Allen Browne]

Subtract 1 from the month number, and then perform integer division by 2.
This will give you a value from 0 to 5 representing the pairs of months in
the year.

So, something like this:

PARAMETERS [Forms]![OpeningPage]![List21] DateTime,
[Forms]![OpeningPage]![EndDate] DateTime;
SELECT Year([ReceiptDetails].[CDate]) AS TheYear,
(Month([ReceiptDetails].[CDate])-1) \ 2 AS TheBiMonth,
Sum(ReceiptDetails.Fee) AS [Sum Of Fee],
Count(*) AS [Count Of ReceiptDetails]
FROM ReceiptDetails
WHERE ReceiptDetails.[CDate] Between
[Forms]![OpeningPage]![List21]
And [Forms]![OpeningPage]![EndDate]
GROUP BY Year([ReceiptDetails].[CDate]),
(Month([ReceiptDetails].[CDate])-1) \ 2;

 

[Joy]

Thanks Allen, but I'm actually trying to the get the query to return
something that looks like this

C.Date By ??? Sum Of Fee Count of ReceiptDetails

Jul 15 $200 1
Jul 31 $250 4

--
Thank Heaps

Subtract 1 from the month number, and then perform integer division by 2.
This will give you a value from 0 to 5 representing the pairs of months in
the year.

So, something like this:

PARAMETERS [Forms]![OpeningPage]![List21] DateTime,
[Forms]![OpeningPage]![EndDate] DateTime;
SELECT Year([ReceiptDetails].[CDate]) AS TheYear,
(Month([ReceiptDetails].[CDate])-1) \ 2 AS TheBiMonth,
Sum(ReceiptDetails.Fee) AS [Sum Of Fee],
Count(*) AS [Count Of ReceiptDetails]
FROM ReceiptDetails
WHERE ReceiptDetails.[CDate] Between
[Forms]![OpeningPage]![List21]
And [Forms]![OpeningPage]![EndDate]
GROUP BY Year([ReceiptDetails].[CDate]),
(Month([ReceiptDetails].[CDate])-1) \ 2;

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I'm trying to make a query that totals bi-monthly rather then monthly,
can't
quite get it. Any help would be greatly appreciated.
This is want I'm playing with

SELECT DISTINCTROW Format$([ReceiptDetails].[CDate],'mmm yyyy') AS [CDate
By Month], Sum(ReceiptDetails.Fee) AS [Sum Of Fee], Count(*) AS [Count Of
ReceiptDetails]
FROM ReceiptDetails
WHERE (((ReceiptDetails.CDate) Between [Forms]![OpeningPage]![List21] And
[Forms]![OpeningPage]![EndDate]))
GROUP BY Format$([ReceiptDetails].[CDate],'mmm yyyy'),
Year([ReceiptDetails].[CDate])*12+DatePart('m',[ReceiptDetails].[CDate])-1;

 

[Allen Browne]

Try expressions like these in the Field row in query design:
Year([ReceiptDetails].[CDate])
Month([ReceiptDetails].[CDate])
Day([ReceiptDetails].[CDate]) < 16

The 3rd one returns True if the day-of-the-month is before the 16th.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen, but I'm actually trying to the get the query to return
something that looks like this

C.Date By ??? Sum Of Fee Count of ReceiptDetails

Jul 15 $200 1
Jul 31 $250 4

--
Thank Heaps

Subtract 1 from the month number, and then perform integer division by 2.
This will give you a value from 0 to 5 representing the pairs of months
in
the year.

So, something like this:

PARAMETERS [Forms]![OpeningPage]![List21] DateTime,
[Forms]![OpeningPage]![EndDate] DateTime;
SELECT Year([ReceiptDetails].[CDate]) AS TheYear,
(Month([ReceiptDetails].[CDate])-1) \ 2 AS TheBiMonth,
Sum(ReceiptDetails.Fee) AS [Sum Of Fee],
Count(*) AS [Count Of ReceiptDetails]
FROM ReceiptDetails
WHERE ReceiptDetails.[CDate] Between
[Forms]![OpeningPage]![List21]
And [Forms]![OpeningPage]![EndDate]
GROUP BY Year([ReceiptDetails].[CDate]),
(Month([ReceiptDetails].[CDate])-1) \ 2;

I'm trying to make a query that totals bi-monthly rather then monthly,
can't
quite get it. Any help would be greatly appreciated.
This is want I'm playing with

SELECT DISTINCTROW Format$([ReceiptDetails].[CDate],'mmm yyyy') AS
[CDate
By Month], Sum(ReceiptDetails.Fee) AS [Sum Of Fee], Count(*) AS [Count
Of
ReceiptDetails]
FROM ReceiptDetails
WHERE (((ReceiptDetails.CDate) Between [Forms]![OpeningPage]![List21]
And
[Forms]![OpeningPage]![EndDate]))
GROUP BY Format$([ReceiptDetails].[CDate],'mmm yyyy'),
Year([ReceiptDetails].[CDate])*12+DatePart('m',[ReceiptDetails].[CDate])-1;