Crosstab report with data from multiple locations 1

[greenche]

I am trying to generate a crosstab report for monthly sales grouped by product category that looks like this:

ReportingGroup   Jan   Feb   Mar   Apr   May   Jun  ...
Group1          2000  4000
Group2                3050  3000
Group3
Group4          4000  5000  5000  5000  5000  5000
Group5

The numbers come from tables like this:

tblOrders
JobNumber    OrderDate    OrderValue   ProductCode
20027159     12/28/2002   2000         A
20037001     01/03/2003   3000         B
20037002     01/06/2003   4000         C
20037003     01/07/2003   3500         A

tblChangeOrders
JobNumber    ChangeID   ChangeDate   ChangeValue
20027159     1          01/10/2003    -440
20037003     1          02/05/2003    1000
20037003     2           2/12/2003     -50

tblProductCodes
ProductCode   ReportingGroup
A             Group1
B             Group1
C             Group2
D             Group2

I have a working crosstab report that is based only on new orders (the first table), but I can't figure out how to get the ChangeValue in the second table to be included based on the month of the ChangeDate and the ProductCode of the associated original order.

 

[randysmid]

Hi,
I would recommend using a series of queries, before you convert the data into the crosstab format. Personally, I like to use make-table queries, because it is then easy to "append" additional records to the table. I wrote a few FAQ's that should be able to help immensely.
faq703-2885 (Strategy for a complex report)
faq703-2868 (Understand the elements of a crosstab query)
faq703-2696 (Use a form and query to ....)

The key to all of this is to work backward from the desired report as I explain in the "Strategy..." FAQ.

HTH,
Randy Smith, MCP
California Teachers Association

 

[greenche]

Based on your suggestions Randy, I ended up with a make-table query for orders, an append query for change orders, and a crosstab query based on the resulting table. I wasn't sure where to put the macro code shown in your FAQ for complex reports, but I managed to do the same thing with a macro that is called by the "On Open" event in the crosstab report.

The only thing I don't like about this solution is that I have to enter the year that I want results for on both the make-table query and the append query. So it's possible to enter two different years and get an incorrect report.

The other thing that I wish I could do is have all of the rows (ReportingGroups) shown in the crosstab query and report, even when there are no sales for that group. (Like Group3 and Group5 in my sample report above.)

 

[randysmid]

Hi,
First, you can set up a form for the user to select the desired year, and then set the criteria for each of the queries to use that year.
Second, to get all groups showing, you will need to set the "join properties" in the query. To do this, simply right click on the line connecting the two tables, and select "join properties". You will be given 3 choices: a) the default will only select records if there is a match in both tables; b) select all records from the 1st table (such as groups) plus those matching records in the 2nd table (sales); and c) select all records from the 2nd table (sales) and those records that match from the 1st (groups). Make sense?

HTH,
Randy Smith, MCP
California Teachers Association

 

[greenche]

When I set the join properties, I get a message that "The SQL statement couldn't be executed because it contains ambiguous outer joins."

 

[dhookom]

I would create this with a series of queries.

==qselChangeOrders====
SELECT ChangeDate, ChangeValue, ProductCode
FROM tblOrders INNER JOIN tblChangeOrders ON tblOrders.JobNumber = tblChangeOrders.JobNumber;

==quniOrderValues=====
SELECT OrderDate, OrderValue, ProductCode
FROM tblOrders
UNION
SELECT ChangeDate, ChangeValue,ProductCode
FROM qselChangeOrders;

==qxtbFinal=======
TRANSFORM Sum(quniOrderValues.OrderValue) AS SumOfOrderValue
SELECT tblProductCodes.ReportingGroup
FROM tblProductCodes LEFT JOIN quniOrderValues ON tblProductCodes.ProductCode = quniOrderValues.ProductCode
GROUP BY tblProductCodes.ReportingGroup
PIVOT Format([OrderDate],"mmm&quot In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"

 

[greenche]

Thank you dhookom! That was just what I needed. I also added
WHERE (((Year([ChangeDate]))=[Enter Year]))
to the first two queries so that I would get the results for only the year of interest.