report from a crosstab

[Dan]

Hi All, I have built a event driven item sales database which keeps track of
individual product sales by event(day). Everything works fine, but now I need
to pull sales reports. I have set up a crosstab query which shows item
category as a row header, event as column header, and sales as the value.

category Oct 15 )ct 16 Oct 17
hotdog $50 $67 $89
pop $44 $77 $23
etc..

how can i pull a report just for 1 event. Should I have a report for the
event which shows event, date, attendance etc with a subreport for category
sales?

 

[Duane Hookom]

What happens when you attempt to apply a criteria of a date or date range to
your query? You need to tell us where "attendance etc" are coming from.

Are events always dates? Are they consecutive? Can you share the SQL view of
your query?

There is a monthly crosstab report solution at
http://www.tek-tips.com/faqs.cfm?fid=5466 that should be easily adapted to an
interval of a single day rather than a month.

 

[Dan]

the main tables are:
tblevents
eventID
event (nameof event)
edate
attendance

tblitems
itemId
item
CategoryID
price
active (y/n)

tblitemsales
itemsalesID
ItemID
eventId
sales
tax

tbl category
categoryID
category

From this I created a select query:
SELECT qrysales.EventID, qrysales.Item, qrysales.Sales,
tblcategory.Creditcode, tblcategory.CategoryID, tblcategory.Category,
tblevent.Event, tblevent.EventclassID
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;

from this I created a crosstab query
TRANSFORM Sum(qryeventcategorysales.Sales) AS SumOfSales
SELECT qryeventcategorysales.Category, qryeventcategorysales.Creditcode,
qryeventcategorysales.CategoryID
FROM qryeventcategorysales
GROUP BY qryeventcategorysales.Category, qryeventcategorysales.Creditcode,
qryeventcategorysales.CategoryID
ORDER BY qryeventcategorysales.CategoryID
PIVOT qryeventcategorysales.EventID;

if I hard type an eventid in criteria than I do only show the one event, but
I dont want to hard type a new criteria everytime. (I am still very new to
queries and how they work).

I will take a look at the link you sent. Events are attached to dates, but
it could be possible to have 2 events on 1 day, so I do have unique eventids.

thanks

 

[Duane Hookom]

If you only want to display one event then using a crosstab gains nothing
since EventID is your column heading.

Assuming you have a form [frmRptCriteria] with a combo box bound to the
eventID [cboEventID]:

SELECT qryeventcategorysales.Category, qryeventcategorysales.Creditcode,
qryeventcategorysales.CategoryID, Sum(qryeventcategorysales.Sales) AS
SumOfSales
FROM qryeventcategorysales
WHERE EventID = Forms!frmRptCriteria!cboEventID
GROUP BY qryeventcategorysales.Category, qryeventcategorysales.Creditcode,
qryeventcategorysales.CategoryID
ORDER BY qryeventcategorysales.CategoryID;

 

[Dan]

I am thinking I dont need a crosstab for this. maybe just a way to total
sales for each category.

 

[Duane Hookom]

I posted the same conclusion about 5 minutes prior to your reply.

 

[Dan]

thanks, the crosstab will be nice for some other reports, but this one I was
trying to overcomplicate.