Pivot Table needed that groups part numbers and dates

  • Thread starter Thread starter Chris
  • Start date Start date
C

Chris

I have a long list of part numbers with additional data such as quantity in
stock, qty on PO's, qty on SO's, etc. (total of 6 different data types).
Most of these have different due dates from each other. I would like to put
these into a pivot table that summaries by stock code (in rows) and then
groups by dates (in the column headings). These dates need to be grouped
into weekly buckets. Then I want to see the quantities in the data section
where the appropriate stock code and date intersect. I'm able to do this in
Excel but it's very manual. I'd like to have Access do the work once the
queries/reports are set up.

Example:

Raw Data;
Stock Code QtyonPO QtyinWH DueDate
EM0001-00 1 8/10/09
EM0001-00 1 8/17/09
EM0001-00 1 8/24/09
EM0001-00 1 8/10/09
EM0002-00 3 8/17/09
EM0002-00 5 8/24/09
EM0002-00 1 8/10/09
EM0002-00 1 8/17/09

Summary I'm looking for;
8/10 8/17 8/24
Stock Code
EM0001-00
QtyonPO 1 1 1
QtyinWH 1
EM0002-00
QtyonPO 3 5
QtyinWH 1 1

Anyone out there with some helpful hints?
Thx,
Chris
 
You have a problem with your data structure. You need to normalize the
data using a Union query and then use that as the source for a crosstab
(pivot) query.

A union query can only be built using the SQL view. It would look like
the following:

SELECT [Stock Code], "QtyOnPo" as QtyType, QtyOnPO as Qty, DueDate
FROM [Table Name]
UNION ALL
SELECT [Stock Code], "QtyInWH" as QtyType, QtyinWH as Qty, DueDate
FROM [Table Name]
UNION ALL
....
UNION ALL
SELECT [Stock Code], "QtyOnSO" as QtyType, QtyOnSO as Qty, DueDate
FROM [Table Name]

Now with that saved query you could make a crosstab query
TRANSFORM Sum(Qty) as Amount
SELECT [Stock Code]
FROM SavedUnionQuery
GROUP BY [Stock Code]
PIVOT DueDate

A report based on this is a bit more problematic, since the DueDates
will change over time. There are ways to handle that by using the query
to give you relative weeks (Week01, WeekO2, etc) and then adjusting the
column labels in the report. The query to get the relative weeks might
look like the following.

TRANSFORM Sum(Qty) as Amount
SELECT [Stock Code]
FROM SavedUnionQuery
GROUP BY [Stock Code]
WHERE DueDate >= #2009-08-10#
PIVOT "Week" & Format(1+DateDiff("d",#2009-08-10#,DueDate)/7,"00")

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
John, Thanks, I will make an attempt to try what you suggest.
Chris


John Spencer said:
You have a problem with your data structure. You need to normalize the
data using a Union query and then use that as the source for a crosstab
(pivot) query.

A union query can only be built using the SQL view. It would look like
the following:

SELECT [Stock Code], "QtyOnPo" as QtyType, QtyOnPO as Qty, DueDate
FROM [Table Name]
UNION ALL
SELECT [Stock Code], "QtyInWH" as QtyType, QtyinWH as Qty, DueDate
FROM [Table Name]
UNION ALL
....
UNION ALL
SELECT [Stock Code], "QtyOnSO" as QtyType, QtyOnSO as Qty, DueDate
FROM [Table Name]

Now with that saved query you could make a crosstab query
TRANSFORM Sum(Qty) as Amount
SELECT [Stock Code]
FROM SavedUnionQuery
GROUP BY [Stock Code]
PIVOT DueDate

A report based on this is a bit more problematic, since the DueDates
will change over time. There are ways to handle that by using the query
to give you relative weeks (Week01, WeekO2, etc) and then adjusting the
column labels in the report. The query to get the relative weeks might
look like the following.

TRANSFORM Sum(Qty) as Amount
SELECT [Stock Code]
FROM SavedUnionQuery
GROUP BY [Stock Code]
WHERE DueDate >= #2009-08-10#
PIVOT "Week" & Format(1+DateDiff("d",#2009-08-10#,DueDate)/7,"00")

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I have a long list of part numbers with additional data such as quantity in
stock, qty on PO's, qty on SO's, etc. (total of 6 different data types).
Most of these have different due dates from each other. I would like to put
these into a pivot table that summaries by stock code (in rows) and then
groups by dates (in the column headings). These dates need to be grouped
into weekly buckets. Then I want to see the quantities in the data section
where the appropriate stock code and date intersect. I'm able to do this in
Excel but it's very manual. I'd like to have Access do the work once the
queries/reports are set up.

Example:

Raw Data;
Stock Code QtyonPO QtyinWH DueDate
EM0001-00 1 8/10/09
EM0001-00 1 8/17/09
EM0001-00 1 8/24/09
EM0001-00 1 8/10/09
EM0002-00 3 8/17/09
EM0002-00 5 8/24/09
EM0002-00 1 8/10/09
EM0002-00 1 8/17/09

Summary I'm looking for;
8/10 8/17 8/24
Stock Code
EM0001-00
QtyonPO 1 1 1
QtyinWH 1
EM0002-00
QtyonPO 3 5
QtyinWH 1 1

Anyone out there with some helpful hints?
Thx,
Chris
Click to expand...
Click to expand...
 
Back
Top