Complex reporting task

[Jim Conrady]

Folks, I hope all is well.

I inherited an Access Front End to a SQL server DB... I am using Access 03
and the SQL system is SS2000. I am trying to create what I think should be a
simple crosstab style report, but so far have not figured out how to do it.

The table I am reporting on is about 35,000 records. It reflects sales
data... Each record can have from 0 to 6 different sales indicated, and each
of those sales has its own sales date.

What I am trying to do is create a simple sales report based on the
Month/Year of each sale. The end result needs to look something like this:

Product 1 Product 2 Product 3
Nov08 35

 

[Jim Conrady]

I inadvertently his send on the below email before it was done.

Continuing, the report needs to look something like this:

Product1 Product2 Product3
Nov 08 75 195 101
Dec 08 116 285 167
..
etc....

I tried a crosstab query, but the fact that each row contains independent
data on sales that may occur several months apart has so far stumped me.

I am far from expert level, but I have written quite a few Access VB macros
and numerous Access Reports. If any of you have any tips, I would love to
hear them!

Regards,

Jim

 

[John Spencer]

First I hope you don't have more than 254 products. If so, this query
is doomed to fail (too many columns)

You need to build a union query to normalize your data. For example
this would work with three sets of fields in one row. UNION queries can
only be built in the SQL view.

SELECT DateField1, ProductName1, SaleDate1
FROM YourTable
UNION ALL
SELECT DateField2, ProductName2, SaleDate2
FROM YourTable
UNION ALL
SELECT DateField3, ProductName3, SaleDate3
FROM YourTable

NOW use this UNION query as the source for a crosstab query.



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

 

[Jim Conrady]

John, thanks for the quick response... it worked well.

Um, I tried a modification. Let's say I want to report only on sales for a
particular period... Is there a way to filter some of the data? I tried a
where clause (specific to each SELECT... FROM... set), and it quit without
doing the union... Can you think of a way to restrict the data?

Thanks again... I appreciate your help.

Jim

 

[John Spencer]

Adding date criteria should not be a problem

SELECT SaleDate1, ProductName1, Amount1
FROM YourTable
WHERE SaleDate1 Between #2008-01-01# and #2008-12-31#
UNION ALL
SELECT SaleDate2, ProductName2, Amount2
FROM YourTable
WHERE SaleDate2 Between #2008-01-01# and #2008-12-31#
UNION ALL
SELECT SaleDate3, ProductName3, Amount3
FROM YourTable
WHERE SaleDate3 Between #2008-01-01# and #2008-12-31#

If you have a query that is failing, it is a good idea to copy and paste
the exact SQL from the SQL window. That makes it easier (usually) to
diagnose the problem.

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

 

[Duane Hookom]

If the Product list is dynamic, you can use the Crosstab sample download at
http://www.rogersaccesslibrary.com/forum/forum_topics.asp?FID=4&SID=624bdf6zf43517f79ff33z2123z5e534.
This solution allows for any number of different column headings for each
group.

 

[Jim Conrady]

Well, I don't know what I was doing wrong the first time, but I added in
criteria roughly the way you have it entered and it works. I have somewhere
around 230,000 records coming out of the union query without the where
clause. The Where clause took it down to about 18,000: the right answer!

John, I answered "yes" to the "did this answer the question" prompt. Thanks
much for your help. Duane, I also saw your post... I do not have a dynamic
requirement, but am going to go out and study the samples.

Thanks to both of you!

Jim