Crosstab-Query

  • Thread starter Thread starter AMANDA
  • Start date Start date
A

AMANDA

Good Morning!

As usual I need help. I work in a sales office and we keep
a daily record of the sales each rep makes. I make a
detailed report every week of each reps sales broken down
by day and use a pop up box in my query to select the
dates I want on the report.

My boss however likes a report with just weekly totals.
She wants it spreadsheet style with the dates along the
top and the names along the left. The only way that I know
how to do this is a crosstab query.

But I can't get a pop up box to work and I tried setting
parameters but it just pulled all the records, I don't
think I was doing it correctly...I was just guessing. Also
after I manually go in and change the dates in the query.
I than have to change all of the controls manually in the
report I have linked to the crosstab query.

Thanks for your help in advance,
Amanda
 
Hello,

The query I am using is based on a select query which is
based on a table. The SQL is as follows:

TRANSFORM Sum([DAILY SALES FOR DAVID REPORT].PRICE) AS
[The Value]
SELECT [DAILY SALES FOR DAVID REPORT].[SALES REP], Sum
([DAILY SALES FOR DAVID REPORT].PRICE) AS [Total Of PRICE]
FROM [DAILY SALES FOR DAVID REPORT]
WHERE (((Format([DATE],"Short Date")) Between #10/13/2003#
And #10/17/2003#))
GROUP BY [DAILY SALES FOR DAVID REPORT].[SALES REP]
PIVOT Format([DATE],"Short Date");
 
I would use a control on a form Forms!frmRpt!txtEndDate to allow the user to
enter a date in the ending week of the report. Then use a column heading
property of:
ColHead:"Wk" & DateDiff("ww",[Date],Forms!frmRpt!txtEndDate)
Select Query|Parameters and enter
Forms!frmRpt!txtEndDate Date/Time
Then set the Column Headings property to: "Wk0", "Wk1", "WK2",...

This will group by weeks with the Wk0 column being the save week as the
entered date. Wk5 would always be 5 weeks earlier.

--
Duane Hookom
MS Access MVP


AMANDA said:
Hello,

The query I am using is based on a select query which is
based on a table. The SQL is as follows:

TRANSFORM Sum([DAILY SALES FOR DAVID REPORT].PRICE) AS
[The Value]
SELECT [DAILY SALES FOR DAVID REPORT].[SALES REP], Sum
([DAILY SALES FOR DAVID REPORT].PRICE) AS [Total Of PRICE]
FROM [DAILY SALES FOR DAVID REPORT]
WHERE (((Format([DATE],"Short Date")) Between #10/13/2003#
And #10/17/2003#))
GROUP BY [DAILY SALES FOR DAVID REPORT].[SALES REP]
PIVOT Format([DATE],"Short Date");

-----Original Message-----
Post your SQL.


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-




.
Click to expand...
Click to expand...
 
Back
Top