Sales Per caps CrossTab report

  • Thread starter Thread starter Dan
  • Start date Start date
D

Dan

I have a sales database which tracks event based sales. Each event is broken
into classifications of "gold" "silver"and Bronze " depending on event and
expected attendance. The Item sales are summarized into categories of Misc.,
Draft, Wine etc. I have a crosstab query/report which summarizes this:
Category Bronze Gold Silver
Misc $21,071.38 $20,054.32 $18,349.67
Draft $21,104.83 $21,427.45 $19,414.9
Beer $14,200.95 $13,990.02 $12,359.73
Wine $2,092.87 $2,213.50 $1,999.75
Liqour $12,646.80 $13,723.86 $11,131.03
N/A Bev $6,848.30 $6,885.82 $6,142.04

NOw I need 2 things, 1- I need to be able to pull the report based on a date
range. 2 - I need to incorporate total attendance for each class
(gold,silver, bronze) so I can calculate a per cap (avg. of what each attndee
spent while at the event) sales divided by attendance.

Not sure how to accomplish this. do I need a second crosstab for attendance
and create a subreport?

thanks for your help
 
Lets focus on the dates first. I already have a form called frmWhatDates
which has 2 unbound text boxes - txtStartDate and txtendDate. which I use to
pull other reports.

My crosstab query is based on a select query:
SELECT qrysales.EventID, qrysales.Item, qrysales.Net,
tblcategory.Creditcode, tblcategory.CategoryID, tblcategory.Category,
tblevent.Event, tblevent.EventclassID, qrysales.Servicefee15,
qrysales.Servicefee2, qrysales.GST, tblevent.Edate, tblevent.Eattendance
FROM tblcategory INNER JOIN ((qrysales INNER JOIN tblitems ON
qrysales.ItemsID=tblitems.ItemsID) INNER JOIN tblevent ON
qrysales.EventID=tblevent.EventID) ON
tblcategory.CategoryID=tblitems.CategoryID
ORDER BY tblcategory.Category;

so I added Edate to the crosstab query:
TRANSFORM Sum(qryeventcategorysales.Net) AS SumOfSales
SELECT qryeventcategorysales.Category, qryeventcategorysales.Creditcode,
qryeventcategorysales.CategoryID
FROM qryeventcategorysales
GROUP BY qryeventcategorysales.Category, qryeventcategorysales.Creditcode,
qryeventcategorysales.CategoryID, qryeventcategorysales.Edate
ORDER BY qryeventcategorysales.CategoryID
PIVOT qryeventcategorysales.EventclassID;

when I add the Edate to the query, the look of the cross tab changes. Looks
like a row for each category for each date. I just want a total sales for
each category, not sure how to do this, and how would I "define data type of
your parameters"

thanks
 
Try SQL like the following which uses the dates entered to filter the
crosstab. Right-click in the background of the top of the query design and
choose "Parameters" to see where they are entered.

PARAMETERS Forms!frmWhatDates!txtStartDate DateTime,
Forms!frmWhatDates!txtEndDate DateTime;
TRANSFORM Sum(qryeventcategorysales.Net) AS SumOfSales
SELECT qryeventcategorysales.Category, qryeventcategorysales.Creditcode,
qryeventcategorysales.CategoryID
FROM qryeventcategorysales
WHERE Edate Between Forms!frmWhatDates!txtStartDate AND
Forms!frmWhatDates!txtEndDate
GROUP BY qryeventcategorysales.Category, qryeventcategorysales.Creditcode,
qryeventcategorysales.CategoryID
ORDER BY qryeventcategorysales.CategoryID
PIVOT qryeventcategorysales.EventclassID;
 
I believe I have the date issue solved, but created a new issue. its
possible a range of dates does not have a classification of "gold", but my
report is still looking for "gold"numbers which is causing errors. whats the
best work around for this
 
Find the Column Headings property of the crosstab query and enter:
Column Headings: "Bronze";"Gold";"Silver"
 
So part 1 of my problem is fix and working, thanks for your help. Now for
part 2, how do I get a total attendance for each Classification of Gold,
silver, bronze events. do I need a subreport, or can this be incorporated in
the crosstab? Once I have total attendance I can figure out the per cap.

thanks again
 
Can you provide the SQL view? Usually you only need to add another Row
Heading that sums similar to your Value of the crosstab.
 
this is what I have:
PARAMETERS [Forms]![frmWhatDates]![txtStartDate] DateTime,
[Forms]![frmWhatDates]![txtendDate] DateTime;
TRANSFORM Sum(qryeventcategorysales.Net) AS SumOfSales
SELECT qryeventcategorysales.Category, qryeventcategorysales.Creditcode,
qryeventcategorysales.CategoryID, Sum(qryeventcategorysales.Eattendance) AS
SumOfEattendance
FROM qryeventcategorysales
WHERE (((qryeventcategorysales.Edate) Between
[Forms]![frmWhatDates]![txtStartDate] And
[Forms]![frmWhatDates]![txtendDate]))
GROUP BY qryeventcategorysales.Category, qryeventcategorysales.Creditcode,
qryeventcategorysales.CategoryID
ORDER BY qryeventcategorysales.CategoryID
PIVOT qryeventcategorysales.EventclassID In
("Platinum","Gold","Silver","Bronze","Exhibition");
 
I assume you want the sum of the Net field. If so, try:

PARAMETERS [Forms]![frmWhatDates]![txtStartDate] DateTime,
[Forms]![frmWhatDates]![txtendDate] DateTime;
TRANSFORM Sum(qryeventcategorysales.Net) AS SumOfSales
SELECT qryeventcategorysales.Category, qryeventcategorysales.Creditcode,
qryeventcategorysales.CategoryID, Sum(qryeventcategorysales.Eattendance) AS
SumOfEattendance,Sum(qryeventcategorysales.Net) As SumNetSales
FROM qryeventcategorysales
WHERE (((qryeventcategorysales.Edate) Between
[Forms]![frmWhatDates]![txtStartDate] And
[Forms]![frmWhatDates]![txtendDate]))
GROUP BY qryeventcategorysales.Category, qryeventcategorysales.Creditcode,
qryeventcategorysales.CategoryID
ORDER BY qryeventcategorysales.CategoryID
PIVOT qryeventcategorysales.EventclassID In
("Platinum","Gold","Silver","Bronze","Exhibition");


--
Duane Hookom
Microsoft Access MVP


Dan said:
this is what I have:
PARAMETERS [Forms]![frmWhatDates]![txtStartDate] DateTime,
[Forms]![frmWhatDates]![txtendDate] DateTime;
TRANSFORM Sum(qryeventcategorysales.Net) AS SumOfSales
SELECT qryeventcategorysales.Category, qryeventcategorysales.Creditcode,
qryeventcategorysales.CategoryID, Sum(qryeventcategorysales.Eattendance) AS
SumOfEattendance
FROM qryeventcategorysales
WHERE (((qryeventcategorysales.Edate) Between
[Forms]![frmWhatDates]![txtStartDate] And
[Forms]![frmWhatDates]![txtendDate]))
GROUP BY qryeventcategorysales.Category, qryeventcategorysales.Creditcode,
qryeventcategorysales.CategoryID
ORDER BY qryeventcategorysales.CategoryID
PIVOT qryeventcategorysales.EventclassID In
("Platinum","Gold","Silver","Bronze","Exhibition");


Duane Hookom said:
Can you provide the SQL view? Usually you only need to add another Row
Heading that sums similar to your Value of the crosstab.
Click to expand...
Click to expand...
 
I think I need to rethink how my info relates to each other. I should have
1400 people attending each event (for example) , so 5 gold events should have
a total attendance of 7000 people. My current query thinks I have 1400
attendance for each Item. so 36 items x 1400 = 50400 for attendance.

so I do not think I can do this is one query. any suggestions?
 
I don't recall you providing any information about your tables and
specifications that would allow anyone to provide more than basic syntax
comments.

--
Duane Hookom
Microsoft Access MVP


Dan said:
I think I need to rethink how my info relates to each other. I should have
1400 people attending each event (for example) , so 5 gold events should have
a total attendance of 7000 people. My current query thinks I have 1400
attendance for each Item. so 36 items x 1400 = 50400 for attendance.

so I do not think I can do this is one query. any suggestions?

Duane Hookom said:
I assume you want the sum of the Net field. If so, try:

PARAMETERS [Forms]![frmWhatDates]![txtStartDate] DateTime,
[Forms]![frmWhatDates]![txtendDate] DateTime;
TRANSFORM Sum(qryeventcategorysales.Net) AS SumOfSales
SELECT qryeventcategorysales.Category, qryeventcategorysales.Creditcode,
qryeventcategorysales.CategoryID, Sum(qryeventcategorysales.Eattendance) AS
SumOfEattendance,Sum(qryeventcategorysales.Net) As SumNetSales
FROM qryeventcategorysales
WHERE (((qryeventcategorysales.Edate) Between
[Forms]![frmWhatDates]![txtStartDate] And
[Forms]![frmWhatDates]![txtendDate]))
GROUP BY qryeventcategorysales.Category, qryeventcategorysales.Creditcode,
qryeventcategorysales.CategoryID
ORDER BY qryeventcategorysales.CategoryID
PIVOT qryeventcategorysales.EventclassID In
("Platinum","Gold","Silver","Bronze","Exhibition");
Click to expand...
Click to expand...
 
tblevent:
Event
Edate
Eday
Etime
EventclassID
Eattendance

tblitemsales: (junction table between tblevent and tblitems)
ItemsalesID
EventID
ItemsID
Sold
Price

tblitems
ItemsID
Item
Price
CategoryID
Active

Duane Hookom said:
I don't recall you providing any information about your tables and
specifications that would allow anyone to provide more than basic syntax
comments.

--
Duane Hookom
Microsoft Access MVP


Dan said:
I think I need to rethink how my info relates to each other. I should have
1400 people attending each event (for example) , so 5 gold events should have
a total attendance of 7000 people. My current query thinks I have 1400
attendance for each Item. so 36 items x 1400 = 50400 for attendance.

so I do not think I can do this is one query. any suggestions?

Duane Hookom said:
I assume you want the sum of the Net field. If so, try:

PARAMETERS [Forms]![frmWhatDates]![txtStartDate] DateTime,
[Forms]![frmWhatDates]![txtendDate] DateTime;
TRANSFORM Sum(qryeventcategorysales.Net) AS SumOfSales
SELECT qryeventcategorysales.Category, qryeventcategorysales.Creditcode,
qryeventcategorysales.CategoryID, Sum(qryeventcategorysales.Eattendance) AS
SumOfEattendance,Sum(qryeventcategorysales.Net) As SumNetSales
FROM qryeventcategorysales
WHERE (((qryeventcategorysales.Edate) Between
[Forms]![frmWhatDates]![txtStartDate] And
[Forms]![frmWhatDates]![txtendDate]))
GROUP BY qryeventcategorysales.Category, qryeventcategorysales.Creditcode,
qryeventcategorysales.CategoryID
ORDER BY qryeventcategorysales.CategoryID
PIVOT qryeventcategorysales.EventclassID In
("Platinum","Gold","Silver","Bronze","Exhibition");
Click to expand...
Click to expand...
Click to expand...
 
I expect you need to create a query from the tblEvent and tblItemSales that
groups by the tblEvent fields, counts the ItemsID, and sums the (Sold *
Price). Then use this query in your crosstab.
--
Duane Hookom
Microsoft Access MVP


Dan said:
tblevent:
Event
Edate
Eday
Etime
EventclassID
Eattendance

tblitemsales: (junction table between tblevent and tblitems)
ItemsalesID
EventID
ItemsID
Sold
Price

tblitems
ItemsID
Item
Price
CategoryID
Active

Duane Hookom said:
I don't recall you providing any information about your tables and
specifications that would allow anyone to provide more than basic syntax
comments.

--
Duane Hookom
Microsoft Access MVP


Dan said:
I think I need to rethink how my info relates to each other. I should have
1400 people attending each event (for example) , so 5 gold events should have
a total attendance of 7000 people. My current query thinks I have 1400
attendance for each Item. so 36 items x 1400 = 50400 for attendance.

so I do not think I can do this is one query. any suggestions?

:

I assume you want the sum of the Net field. If so, try:

PARAMETERS [Forms]![frmWhatDates]![txtStartDate] DateTime,
[Forms]![frmWhatDates]![txtendDate] DateTime;
TRANSFORM Sum(qryeventcategorysales.Net) AS SumOfSales
SELECT qryeventcategorysales.Category, qryeventcategorysales.Creditcode,
qryeventcategorysales.CategoryID, Sum(qryeventcategorysales.Eattendance) AS
SumOfEattendance,Sum(qryeventcategorysales.Net) As SumNetSales
FROM qryeventcategorysales
WHERE (((qryeventcategorysales.Edate) Between
[Forms]![frmWhatDates]![txtStartDate] And
[Forms]![frmWhatDates]![txtendDate]))
GROUP BY qryeventcategorysales.Category, qryeventcategorysales.Creditcode,
qryeventcategorysales.CategoryID
ORDER BY qryeventcategorysales.CategoryID
PIVOT qryeventcategorysales.EventclassID In
("Platinum","Gold","Silver","Bronze","Exhibition");
Click to expand...
Click to expand...
Click to expand...
 
Back
Top