Crosstab queries

[macroapa]

Hi, I have the following SQL for a cross tab query:

TRANSFORM Count(Table2.DateIn) AS CountOfDateIn1
SELECT Table2.DateIn
FROM Table2
GROUP BY Table2.DateIn
PIVOT Table2.Process;

It works great and produces the dates down the left and the processes
across the top with the counting in teh main part.

However what I would love to achieve is teh same, but with the dates
down the left being the last 52 weekly periods from today.

Is this possible at all?

Main table structure:

ID Process DateIn
1 DDI 01/09/2008
2 DDO 02/09/2008
3 ddi 01/09/2008

CrossTab results

DateIn DDI DDO
01/09/2008 2
02/09/2008 1

What I would like it to look like:

DateIn DDI DDO
wk beg 15/09/2008 2
wk beg 08/09/2008 1
etc etc

 

[KARL DEWEY]

The plus 2 (+2) displays Mondays as Week_Date_In.
TRANSFORM Count(Table2.ID) AS CountOfID
SELECT ([DateIn]-Val(Format([DateIn],"w")))+2 AS Week_Date_In
FROM Table2
GROUP BY Format([DateIn],"ww"), ([DateIn]-Val(Format([DateIn],"w")))+2
ORDER BY Format([DateIn],"ww")
PIVOT Table2.Process;

 

[Dale Fye]

1. Start out by creating a table (tbl_Numbers) with one field (lngNumber)
which is declared as a long integer. Add 10 records to the table, with the
values 0 through 9

2. Create a query based on this table (queryDates):

SELECT DateAdd("d",-7*([Tens].[lngNumber]*10+[Ones].[lngNumber])-6,Date())
AS PeriodStartDate,
DateAdd("d",-7*([Tens].[lngNumber]*10+[Ones].[lngNumber])+1,Date()) AS
PeriodEndDate,
FROM tbl_Numbers AS Tens, tbl_Numbers AS Ones
WHERE ((([Tens].[lngNumber]*10+[Ones].[lngNumber])<=52))

Basically, what this query will do is give you two fields (PeriodStartDate,
PeriodEndDate) with dates that range back 52 weeks.

3. Create another query:

TRANSFORM Count(table2.ID) AS CountOfID
SELECT [PeriodEndDate]-1 AS WeekEnding
FROM table2, qryDates
WHERE (((qryDates.PeriodStartDate)<=[DateIn]) AND
((qryDates.PeriodEndDate)>[DateIn]))
GROUP BY [PeriodEndDate]-1
ORDER BY [PeriodEndDate]-1 DESC
PIVOT table2.Process;

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Ken Sheridan]

First add the following function to a standard module:

Public Function WeekStart(dtmDate As Date, intFirstDay As Integer) As Date

Dim dtmWeekStart As Date
dtmWeekStart = dtmDate

Do While Weekday(dtmWeekStart, intFirstDay) > 1
dtmWeekStart = DateAdd("d", dtmWeekStart, -1)
Loop

WeekStart = dtmWeekStart

End Function

Then, using Monday as the start of the week, try:

TRANSFORM COUNT(*)
SELECT WEEKSTART(DateIn,2) AS [Week Beginning]
FROM Table2
WHERE WEEKSTART(DateIn,2) >=
DATEADD("ww",-52,WEEKSTART(DATE(),2))
GROUP BY WeekStart(DateIn,2)
PIVOT Process;

Ken Sheridan
Stafford, England

 

[Dale Fye]

Karl,

In all the years I've been using Access, I don't ever recall seeing that
technique to get the date of a particular day in a week. But it makes a lot
of sense when you think about it.

Is there a reason you used: [DateIn] - Val(Format([DateIn], "w"))

instead of: [DateIn] - DatePart("w", [DateIn])

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

The plus 2 (+2) displays Mondays as Week_Date_In.
TRANSFORM Count(Table2.ID) AS CountOfID
SELECT ([DateIn]-Val(Format([DateIn],"w")))+2 AS Week_Date_In
FROM Table2
GROUP BY Format([DateIn],"ww"), ([DateIn]-Val(Format([DateIn],"w")))+2
ORDER BY Format([DateIn],"ww")
PIVOT Table2.Process;


--
KARL DEWEY
Build a little - Test a little

Hi, I have the following SQL for a cross tab query:

TRANSFORM Count(Table2.DateIn) AS CountOfDateIn1
SELECT Table2.DateIn
FROM Table2
GROUP BY Table2.DateIn
PIVOT Table2.Process;

It works great and produces the dates down the left and the processes
across the top with the counting in teh main part.

However what I would love to achieve is teh same, but with the dates
down the left being the last 52 weekly periods from today.

Is this possible at all?

Main table structure:

ID Process DateIn
1 DDI 01/09/2008
2 DDO 02/09/2008
3 ddi 01/09/2008

CrossTab results

DateIn DDI DDO
01/09/2008 2
02/09/2008 1

What I would like it to look like:

DateIn DDI DDO
wk beg 15/09/2008 2
wk beg 08/09/2008 1
etc etc

 

[KARL DEWEY]

Just what came to mind.
--
KARL DEWEY
Build a little - Test a little

Karl,

In all the years I've been using Access, I don't ever recall seeing that
technique to get the date of a particular day in a week. But it makes a lot
of sense when you think about it.

Is there a reason you used: [DateIn] - Val(Format([DateIn], "w"))

instead of: [DateIn] - DatePart("w", [DateIn])

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

The plus 2 (+2) displays Mondays as Week_Date_In.
TRANSFORM Count(Table2.ID) AS CountOfID
SELECT ([DateIn]-Val(Format([DateIn],"w")))+2 AS Week_Date_In
FROM Table2
GROUP BY Format([DateIn],"ww"), ([DateIn]-Val(Format([DateIn],"w")))+2
ORDER BY Format([DateIn],"ww")
PIVOT Table2.Process;


--
KARL DEWEY
Build a little - Test a little

Hi, I have the following SQL for a cross tab query:

TRANSFORM Count(Table2.DateIn) AS CountOfDateIn1
SELECT Table2.DateIn
FROM Table2
GROUP BY Table2.DateIn
PIVOT Table2.Process;

It works great and produces the dates down the left and the processes
across the top with the counting in teh main part.

However what I would love to achieve is teh same, but with the dates
down the left being the last 52 weekly periods from today.

Is this possible at all?

Main table structure:

ID Process DateIn
1 DDI 01/09/2008
2 DDO 02/09/2008
3 ddi 01/09/2008

CrossTab results

DateIn DDI DDO
01/09/2008 2
02/09/2008 1

What I would like it to look like:

DateIn DDI DDO
wk beg 15/09/2008 2
wk beg 08/09/2008 1
etc etc