Calculating sales based on date range

[FOE2272]

I am working on a report that will Sum the Sales (Bid Price Field) that are still Active (Bid Status Field) for the past
1 Week,
2 Weeks,
3 Weeks,
4 Weeks,
30 Days,
60 Days,
90 Days,
120 Days,
150 Days,
180 Days &
over 180 Days
using the (Bid Date Field) from the current date back.

I have a query set up to only show Active bids, figured that was a good place to start, but how to I get it to return the Sum of the Sales for each of the date ranges?

[MMcCarthy]

OK this is not straightforward but lets try some stuff out to see if we can get it to work. You don't give the date field you reference so I just refer to it as [DateField].

 SELECT Sum([Bid Price Field]) A SumBidPrice, 
IIf(DateDiff("d",[DateField],Date())<=7,"1 Week",
IIf(DateDiff("d",[DateField],Date())>7 And DateDiff("d",[DateField],Date())<=14,"2 Weeks",
IIf(DateDiff("d",[DateField],Date())>14 And DateDiff("d",[DateField],Date())<=21,"3 Weeks",
IIf(DateDiff("d",[DateField],Date())>21 And DateDiff("d",[DateField],Date())<=28,"4 Weeks",
IIf(DateDiff("d",[DateField],Date())>28 And DateDiff("d",[DateField],Date())<=30,"30 days",
IIf(DateDiff("d",[DateField],Date())>30 And DateDiff("d",[DateField],Date())<=60,"60 days",
IIf(DateDiff("d",[DateField],Date())>60 And DateDiff("d",[DateField],Date())<=90,"90 days",
IIf(DateDiff("d",[DateField],Date())>90 And DateDiff("d",[DateField],Date())<=120,"120 days",
IIf(DateDiff("d",[DateField],Date())>120 And DateDiff("d",[DateField],Date())<=150,"150 days",
IIf(DateDiff("d",[DateField],Date())>150 And DateDiff("d",[DateField],Date())<=180,"180 days",
"> 180 Days")))))))))) AS NumDays
FROM Table3
GROUP BY IIf(DateDiff("d",[DateField],Date())<=7,"1 Week",
IIf(DateDiff("d",[DateField],Date())>7 And DateDiff("d",[DateField],Date())<=14,"2 Weeks",
IIf(DateDiff("d",[DateField],Date())>14 And DateDiff("d",[DateField],Date())<=21,"3 Weeks",
IIf(DateDiff("d",[DateField],Date())>21 And DateDiff("d",[DateField],Date())<=28,"4 Weeks",
IIf(DateDiff("d",[DateField],Date())>28 And DateDiff("d",[DateField],Date())<=30,"30 days",
IIf(DateDiff("d",[DateField],Date())>30 And DateDiff("d",[DateField],Date())<=60,"60 days",
IIf(DateDiff("d",[DateField],Date())>60 And DateDiff("d",[DateField],Date())<=90,"90 days",
IIf(DateDiff("d",[DateField],Date())>90 And DateDiff("d",[DateField],Date())<=120,"120 days",
IIf(DateDiff("d",[DateField],Date())>120 And DateDiff("d",[DateField],Date())<=150,"150 days",
IIf(DateDiff("d",[DateField],Date())>150 And DateDiff("d",[DateField],Date())<=180,"180 days","> 180 Days"))))))))));
 

[FOE2272]

Thank you, That worked great. One more question.

I am also counting the # of bids in the query

How do I get the sums of each Bid Price (from the previous question) and the Count of Bids (I have it in the query by each of the "NumDays" values) into a report so that I can have each result in a different textbox. (for running calculations)

Example:
the "SumBidPrice" for "1 Week" in a textbox,
the "SumBidPrice" for "2 Weeks" in a different textbox,
ETC...

Becuase I will need the "summed" and "separate" on the report.

[MMcCarthy]

Thank you, That worked great. One more question.

I am also counting the # of bids in the query

How do I get the sums of each Bid Price (from the previous question) and the Count of Bids (I have it in the query by each of the "NumDays" values) into a report so that I can have each result in a different textbox. (for running calculations)

Example:
the "SumBidPrice" for "1 Week" in a textbox,
the "SumBidPrice" for "2 Weeks" in a different textbox,
ETC...

Becuase I will need the "summed" and "separate" on the report.

OK change the query to show count of bids as follows

 
SELECT Sum([Bid Price]) As SumBidPrice, Count([Bid Price] As CountBids
IIf(DateDiff( ......
 

I'm afraid this is the best I can do with a crosstab query.

 
TRANSFORM Sum([SumBidPrice]) AS SumOfSumBidPrice
SELECT Sum([SumBidPrice]) AS [Total Of SumBidPrice], CountBids
FROM QueryName
GROUP BY CountBids
PIVOT NumDays In ("1 Week", "2 Weeks","3 Weeks","4 Weeks", "30 days", "60 days", "90 days", "120 days", "150 days", "180 days", ">180 days");
 

[FOE2272]

Thank You, Things are working fine.

[MMcCarthy]

Thank You, Things are working fine.

You're welcome. Glad its working for you.

Mary