Report from Crosstab with Select Dates Possible?

  • Thread starter Thread starter pcbins
  • Start date Start date
P

pcbins

I have a table of sales:

SalesRep RptDate Prod_A1 Prod_A2 Prod_B1 Prod_B2 Prod_C1
Jones 1/2/2009 15 2 5 1 1
Rogers 1/2/2009 14 1 3 0 0
Smith 1/2/2009 16 3 6 1 2
Jones 2/2/2009 0 1 20 5 5
Rogers 2/2/2009 2 0 16 2 1
Smith 2/2/2009 1 0 21 3 6
Jones 3/1/2009 5 0 15 2 20
Rogers 3/1/2009 4 0 13 1 17
Smith 3/1/2009 6 0 16 2 21

I have a Union Query to Reformat it into:

SalesRep ReportDate PRODUCT QTY
Jones 02-Jan-09 A 15
Jones 02-Jan-09 B 5
Jones 02-Jan-09 C 1
Rogers 02-Jan-09 A 14
Rogers 02-Jan-09 B 3
Smith 02-Jan-09 A 16
Smith 02-Jan-09 B 6
Smith 02-Jan-09 C 2
Jones 02-Feb-09 A 1
Jones 02-Feb-09 B 20
Jones 02-Feb-09 C 1
Rogers 02-Feb-09 A 2
Rogers 02-Feb-09 B 2

And a CrossTab Query to Display the information the way I need it
(it also asks for 2 dates to display):

SalesRep PRODUCT 1/2/2009 3/1/2009
Jones A 17 5
Jones B 6 17
Jones C 1 23
Rogers A 15 4
Rogers B 3 14
Rogers C 19
Smith A 19 6
Smith B 7 18
Smith C 2 24

BUT I STILL CAN GET IT INTO A REPORT THAT LOOKS LIKE THIS:

SalesRep: JONES
1/2/09 3/1/09
PROD A 15 16
PROD B 5 6
PROD C 1 2

SalesRep: ROGERS
1/2/09 3/1/09
PROD A 0 1
PROD B 20 21
PROD C 5 6

SalesRep: SMITH
1/2/09 3/1/09
PROD A 5 6
PROD B 15 16
PROD C 20 21

(these numbers might be different, sorry)

Do I need to go back to school to understand how to make this simple report?
:(
 
In report design view click on Grouping and Sorting. Select SalesRep with
group header. Select Product, no header.

Insert SalesRep in the group header.
Insert Product and Date fields in the detail.
 
I am trying to create a report from a CROSSTAB.
If I use the wizard, it gives me no fields to choose from in my crosstab
query.
All the help I have found for this problem are so complex and complicated. I
don't have a lot of variables. Its just a simple crosstab and a simple
report.

Its almost impossible for me to try to get the examples I've found to fit
into something simple like my report. I just select two dates to get
information from and there it is in my crosstab, nice and neat. But getting
it into a report I have to stand on my head while balancing on top of a
highwire?

I did manage to sift through the unnecessary garble enough to make some
generic headings, but I cannot use that in the report! "Date1" and "Date2" ?
That is not going to satisfy the directors! They want to know WHICH dates we
are comparing.
 
Back
Top