Query using same cell multiple times with different date range

[Lisa]

I am trying to create a query based on $ amounts for a quarterly report.
Basically, I enter this $ amount daily, but I want my query to have 13
different fields with each field being populated with a weeks worth of $
amounts.

 

[Jeff Boyce]

Lisa

It all starts with the data...

I'm afraid I don't understand what you are starting with nor what you want
to end up with.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[KARL DEWEY]

Try this --
TRANSFORM Sum(Lisa_A.Amount) AS SumOfAmount
SELECT Format([Daily],"yyyy q") AS [Year and Quarter], Sum(Lisa_A.Amount) AS
[Total Of Amount]
FROM Lisa_A
GROUP BY Format([Daily],"yyyy q")
PIVOT "Week " & (Val(Format([Daily],"ww")) Mod 13)+1 IN("Week 1", "Week 2",
"Week 3", "Week 4", "Week 5", "Week 6", "Week 7", "Week 8", "Week 9", "Week
10", "Week 11", "Week 12", "Week 13" );

 

[Jerry Whittle]

First things first. You need to create a query to fum the data weekly and
only get the last 13 weeks. Something like below with the proper table and
field names MIGHT do the job.

SELECT Year([DateList]) AS TheYear,
DatePart("ww",[DateList],1) AS TheWeek,
Sum(tblDateList.Sales) AS SumOfSales,
Min(tblDateList.DateList) AS MinOfDateList
FROM tblDateList
WHERE (((tblDateList.DateList)>Date()-91))
GROUP BY Year([DateList]),
DatePart("ww",[DateList],1)
ORDER BY Year([DateList]), DatePart("ww",[DateList],1);

Why the MIGHT?

First off the 1 in DatePart("ww",[DateList],1) can make a difference on what
is the first week of the year. Things can get strange around January 1st. You
might want to test 2 or 3 in place of the 1. Check out DatePart in Help to
see the differences.

Then there's the -91 which might not get you the properly weeks correctly
depending on when you run the query, like a Friday vs Monday, or leap years.

After you get something like the above query worked out to your satifaction,
use it as the basis for a crosstab query. That might be a problem due to the
years and weeks being in different columns.

 

[Lisa]

Basically, It is for collections purposes. We enter the $ amt collected on a
daily basis, then calculate the hourly $ amount average from that. We are
trying to get the hourly average for the quarter broken down by week in a
query.

 

[Jeff Boyce]

Lisa

That sounds like something a spreadsheet could easily handle.

Is there a reason you aren't exporting the data to a spreadsheet?

Regards

Jeff Boyce
Microsoft Office/Access MVP