Sales Per caps CrossTab report

[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

 

[Duane Hookom]

You can use text boxes on a form to filter the dates returned in the crosstab
query. You may need to define the data type of your parameters.

It is possible to return multiple values in a crosstab with a solution like
the one found at http://www.tek-tips.com/faqs.cfm?fid=4524

 

[Dan]

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

 

[Duane Hookom]

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;

 

[Dan]

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

 

[Duane Hookom]

Find the Column Headings property of the crosstab query and enter:
Column Headings: "Bronze";"Gold";"Silver"

 

[Dan]

Thanks, that worked

Find the Column Headings property of the crosstab query and enter:
Column Headings: "Bronze";"Gold";"Silver"

 

[Dan]

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

 

[Duane Hookom]

Can you provide the SQL view? Usually you only need to add another Row
Heading that sums similar to your Value of the crosstab.

 

[Dan]

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]

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

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");

Can you provide the SQL view? Usually you only need to add another Row
Heading that sums similar to your Value of the crosstab.

 

[Dan]

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]

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

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");

 

[Dan]

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

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

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");

 

[Duane Hookom]

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

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

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

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");