Crosstab query with date range (2 years)

[Cassim]

Hi Guys,



I am trying to run a crosstab query in which I want to see all the transections for last 2 years (from today's date), I have tried all sorts of online solutions but had no luck so far....

Currently the headers are yy/mm format (to keep months in the right order).

Please advise.

Thanks

[June7]

Not much there to go on, need something to analyze.

Why doesn't it work - error message, wrong results, nothing happens?

Show the attempted query.

Provide sample data.

[Cassim]

Crosstab query currently shows 2 years of data in monthly buckets (headers are yy/mm in order to keep the results in the right order)

..........13/06....|.......13/07.......|......17/08.....|....etc.....
PN1......1..........|........1...........|........ ..........|.............
PN2.................|.........1...........|....... .2..........|...........
PN3......1..........|.....................|....... ..............|...........

I am already using
FIELD: Format([Part Used Date],"yy/mm")
TOTAL: WHERE
CRITERIA: Between [Today's Date yy/mm] And [25M's End Date yy/mm]

to limit the number of months that I want to see in the query.

What I now want is to count number of months where a part (PN1,2,3) was used in last 12 months and then another column to count for 13-24 months .

Thanks in advance.

[Dal Jeanis]

Assuming this table layout...

tblPartsUsed
   PartUsedPK   AutoKey
   PartNum      Text
   UsedCount    Number 
   UsedDate     Date     [Part Used Date]

If you're just looking for number of months with parts used, you don't need a crosstab.

SELECT Q1PartNum, Count(Q1.UsedMonth) As UsedMonths
    (
     SELECT PartNum, Format(UsedDate,"yy/mm") As UsedMonth, Sum(UsedCount) AS UsedCount
     FROM tblPartsUsed
     GROUP BY PartNum, Format(UsedDate,"yy/mm")
     HAVING Sum(UsedCount) > 0
    ) AS Q1 
WHERE Q1.UsedMonth BETWEEN {somedate} AND {anotherDate};

[Cassim]

Many thanks Dal.

I am not use to SQL statements in Access and normally design queries using the "Design View" only so I am gonna need bit more help .
I need the crosstab query because the information is displayed in statistical format and its user friendly for reporting.

The SQL code for my crosstab query is below, can you please advise how to add "last 12 months count" & last "13-24 months count" columns to it, (to count number of months a part was used, NOT number of occassions. i.e. the part may have more than one transection in a month (in the table) but I want the calculation to count these as once only for that month.......) using the above code.

TRANSFORM Sum([Total Usage].[Quantity Used]) AS [SumOfQuantity Used]
SELECT [Total Usage].[Part Number], [Total Usage].[Description], Sum([Total Usage].[Quantity Used]) AS [Total Of Quantity Used]
FROM [Total Usage], [Calender]
WHERE (((Format([Used Date],"yy/mm")) Between [Today's Date yy/mm] And [25M's End Date yy/mm]))
GROUP BY [Total Usage].[Part Number], [Total Usage].[Description]
PIVOT Format([Used Date],"yy/mm");

I also have
[12M's End Date yy/mm]
[13M's Start Date yy/mm]

in my Calender Query.

Many thanks once again.

[Dal Jeanis]

Okay, so you're saying it absolutely *MUST* be in a single crosstab query with all that other gunk? Blegh. You people are going to make me master crosstabs if it kills me.

Hmmm. A challenge. Hmmm. That means you're going to need to create at least two different kinds of records... I think it's going to be easiest to break the inner query into three kinds of records, one for the monthly totals, one for counting the first twelve months, and one for counting the second.

Well, the underlying source query needs to be summed at the month level, since that's what we'll end up counting. This inner query will create one record for each month, with the Total of Quantity used. (We're not cutting down the dates yet.) So that will look like this:

   SELECT 
      TU1.[Part Number], 
      TU1.[Description], 
      Format(TU1.[Used Date],"yy/mm") AS UsedYYMM,
      Sum(TU1.[Quantity Used]) AS UsedQuant
   FROM 
      [Total Usage] AS TU1
   GROUP BY 
      TU1.[Part Number], 
      TU1.[Description],
      Format(TU1.[Used Date],"yy/mm")

Now, we need three copies of that data, and each copy will then be limited to a date range.

Change your calendar format to this:

tblCalendar2
  Fieldname   Field Type         
  Crossname   Text
  CrossDate1  Date/Time
  CrossDate2  Date/Time

Make three tblCalendar2 records, with this data in them:

 CrossName          CrossDate1               CrossDate2
 "UsedDate"         [Today's Date yy/mm]     [25M's End Date yy/mm] 
 "Months 1-12"      [Today's Date yy/mm]     [12M's End Date yy/mm]
 "Months 13-24"     [13M's Start Date yy/mm] [25M's End Date yy/mm]

Later, you could add a fourth record for Months 1-24 if you wanted to, and the code will all work the same.

Finally, paste in this query and run it:

TRANSFORM Sum(IIF(TC2.CrossName="UsedDate",TU2.UsedQuant,1)) AS TheNumber
SELECT 
   TU2.[Part Number], 
   TU2.[Description],
   Sum(IIF(TC2.CrossName="UsedDate",TU2.UsedQuant,0)) AS [Sum of Quantity Used]
FROM 
   tblCalendar2 AS TC2,
   (SELECT 
      TU1.[Part Number], 
      TU1.[Description], 
      Format(TU1.[Used Date],"yy/mm") AS UsedYYMM,
      Sum(TU1.[Quantity Used]) AS UsedQuant
   FROM 
      [Total Usage] AS TU1
   GROUP BY 
      TU1.[Part Number], 
      TU1.[Description],
      Format(TU1.[Used Date],"yy/mm")
   ) AS TU2
WHERE 
   (TU2.UsedYYMM Between TC2.CrossDate1 And TC2.CrossDate2)
GROUP BY 
   TU2.[Part Number], 
   TU2.[Description]
Pivot IIF(TC2.Crossname="UsedDate",TU2.UsedYYMM,TC2.CrossName);

The sample output looks good, so that should work for you.

Explanation:
The Pivot will splay out each month's data, leaving two final columns for the monthly counts.

The Transform will sum up the totals, which will only matter in the case of the two monthly counts, since we already grouped the selected records by month in the inner query.

In each case, the records will be limited to the dates requested in the tblCalendar records.

Note that the sum column in the SELECT is slightly different than the sum column in the transform, to avoid adding a count of months to the sums of actual usage numbers.

[Cassim]

Dal,

Many thanks

I got there in the end but had to do it slightly differently from what you suggested due to
1) I am terrible @ SQL
2) I wanted to have today's date factured into the report so it always (automatically) work from today's date.

So this is what I eneded up doing (briefly)

1) Calaneder Query - that calculates all the dates (today's, 12M's end, 13m's start, 24M's end)
2) Part Used Query 1 - All the parts for 1 to 12 months using PartsUsed table & Calander Query (similar to what you suggested) and added another column called "Uniques" where yy/mm & part number were in the same column
3) Part Used Query 1.1 - Counts number of "Uniques" againt a part number
4) Part used Query 2 - All the part for 13 to 24 months (as per 1)
5) Part used Query 2.1 - Counts (as per 1.1)
6) Part used Query 3 - Combination of 1.1 & 1.2 where using PartsUsed table lists all the counts againt all the part numbers (shows count in 2 columns 1-12 & 13-24)
7) FINAL - Crosstab query using PartsUsed table & Part used Query 3

Call me old fasion but I really had to break it down to get to crosstab query, now I am thinking of designing a macro so everytime I open DB the system runs all these in the right orders :-).

Once again thank you very much for your help.

[Dal Jeanis]

Very well done! The most important design consideration is that you understand what you did, because you are the one who has to support the thing.

As such, your way is the exact right way!