Flatten Detail Rows in "Crosstab" Calendar Report

Z

zwestbrook

I have data that includes dated events classified by type. I am
creating a crosstab/calendar style report the will display at a glance
the events for the year by month and type. I went the crosstab query/
report route but that only gives me the number of events by type per
month...I want to see the actual event title and date in that field,
not the count.

I have been able to create something very close to what I want but
cannot format correctly. I have 12 fields (1 for each month) in the
Detail row with each one only showing events for the corresponding
month. Data is showing, but the report only displays 1 record per row.
How can I merge down or flatten the detail sections?

What I want:
Person A
----------------- jan feb mar apr
type 1 XXX XXX XXX
type 2 XXX XXX XXX
tyep 3 XXX

What I am getting:
Person A
----------------- jan feb mar apr
type 1 XXX
XXX
XXX
type 2 XXX
XXX
XXX
tyep 3 XXX
 
Z

zwestbrook

I have data that includes dated events classified by type. I am
creating a crosstab/calendar style report the will display at a glance
the events for the year by month and type. I went the crosstab query/
report route but that only gives me the number of events by type per
month...I want to see the actual event title and date in that field,
not the count.

I have been able to create something very close to what I want but
cannot format correctly. I have 12 fields (1 for each month) in the
Detail row with each one only showing events for the corresponding
month. Data is showing, but the report only displays 1 record per row.
How can I merge down or flatten the detail sections?

What I want:
Person A
----------------- jan  feb  mar  apr
type 1         XXX       XXX  XXX
type 2         XXX XXX XXX
tyep 3                            XXX

What I am getting:
Person A
----------------- jan  feb  mar  apr
type 1         XXX
                              XXX
                                     XXX
type 2         XXX
                             XXX
                                     XXX
tyep 3                            XXX
Click to expand...

Ok, found out how to flatten the detail section (add Me.MoveLayout =
False in the onFormat event of the Detail) but now my records for the
months print on top of each other (some months may have multiple
events).
 
K

KARL DEWEY

I want to see the actual event title and date in that field, not the count.
I think a crosstab can give you what you want.
Post your table structure - field names and datatype along with sample data.
 
Z

zwestbrook

I think a crosstab can give you what you want.
Post your table structure - field names and datatype along with sample data.
Click to expand...
<snip>

Thank you for your response. There are additional fields; here are the
pertinent ones (each has a unique id):

tbl_contacts
fullName (txt)

tbl_supplierNames
supplierName (txt)

tbl_supplierEventCalendar
supplierID (fk - tbl_supplierNames, num)
title (txt)
begin (date/time)
end (date/time)

tbl_supplierInfo
supplierID (fk - tbl_supplierNams, num)
esmrmID (fk - tbl_contacts, num) **this is the person

qry_supplierInfo_ESMRM **matches up the person assigned to the
supplier
SELECT tbl_supplierInfo.supplierID, ESMDB_SupplierNames.SupplierName,
tbl_supplierInfo.wbESMRMID, ESMDB_Contacts.FullName
FROM ESMDB_Contacts RIGHT JOIN (ESMDB_SupplierNames RIGHT JOIN
tbl_supplierInfo ON
ESMDB_SupplierNames.ID=tbl_supplierInfo.supplierID) ON
ESMDB_Contacts.ID=tbl_supplierInfo.wbESMRMID;

qry_calendar_events **matches up the supplier names and their events
SELECT ESMDB_SupplierEventCalendar.ID,
ESMDB_SupplierNames.SupplierName, ESMDB_SupplierEventCalendar.Title,
ESMDB_SupplierEventCalendar.EventType,
ESMDB_SupplierEventCalendar.Begin, ESMDB_SupplierEventCalendar.End,
ESMDB_SupplierEventCalendar.Description
FROM ESMDB_SupplierNames INNER JOIN ESMDB_SupplierEventCalendar ON
ESMDB_SupplierNames.ID = ESMDB_SupplierEventCalendar.Supplier;

qry_events_with_ESMRM **combines previous two queries
SELECT qry_supplierInfo_ESMRM.FullName,
qry_calendarEvents.SupplierName, qry_calendarEvents.Title,
qry_calendarEvents.EventType, qry_calendarEvents.Begin,
qry_calendarEvents.End, qry_calendarEvents.Description
FROM qry_calendarEvents INNER JOIN qry_supplierInfo_ESMRM ON
qry_calendarEvents.SupplierName = qry_supplierInfo_ESMRM.SupplierName;

Now, I made the last query a crosstab and it looks GREAT but I dont
know how to modify it from the wizard to display the date and title of
the events ([Begin] and [Title]). It forces me to use Count, AVG, SUM,
etc. I am sure there must be a way around it, but I am not that well
versed with crosstabs and their modification.

qry_events_with_esmRM_Crosstab **crosstab version of previous query
TRANSFORM Count(qry_events_with_esmRM.Title) AS CountOfTitle
SELECT qry_events_with_esmRM.FullName,
qry_events_with_esmRM.SupplierName
FROM qry_events_with_esmRM
GROUP BY qry_events_with_esmRM.FullName,
qry_events_with_esmRM.SupplierName
PIVOT Format([Begin],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

THANK YOU for your assistance!!!

-Zac
 
K

KARL DEWEY

How is this ---
TRANSFORM First([Begin] & " " & [Title]) AS Expr1
SELECT qry_events_with_esmRM.FullName, qry_events_with_esmRM.SupplierName
FROM qry_events_with_esmRM
GROUP BY qry_events_with_esmRM.FullName, qry_events_with_esmRM.SupplierName,
Format([Begin],"mmm")
ORDER BY qry_events_with_esmRM.FullName, qry_events_with_esmRM.SupplierName
PIVOT Format([Begin],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

One limitation is that you only get one entry per month per row or use this
alternate --
TRANSFORM IIf(First([Begin] & " " & [Title])=Last([Begin] & " " &
[Title]),First([Begin] & " " & [Title]),First([Begin] & " " & [Title]) & " -
" & Last([Begin] & " " & [Title])) AS Expr1
SELECT qry_events_with_esmRM.FullName, qry_events_with_esmRM.SupplierName
FROM qry_events_with_esmRM
GROUP BY qry_events_with_esmRM.FullName, qry_events_with_esmRM.SupplierName,
Format([Begin],"mmm")
ORDER BY qry_events_with_esmRM.FullName, qry_events_with_esmRM.SupplierName
PIVOT Format([Begin],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

--
KARL DEWEY
Build a little - Test a little


zwestbrook said:
I think a crosstab can give you what you want.
Post your table structure - field names and datatype along with sample data.
Click to expand...
<snip>

Thank you for your response. There are additional fields; here are the
pertinent ones (each has a unique id):

tbl_contacts
fullName (txt)

tbl_supplierNames
supplierName (txt)

tbl_supplierEventCalendar
supplierID (fk - tbl_supplierNames, num)
title (txt)
begin (date/time)
end (date/time)

tbl_supplierInfo
supplierID (fk - tbl_supplierNams, num)
esmrmID (fk - tbl_contacts, num) **this is the person

qry_supplierInfo_ESMRM **matches up the person assigned to the
supplier
SELECT tbl_supplierInfo.supplierID, ESMDB_SupplierNames.SupplierName,
tbl_supplierInfo.wbESMRMID, ESMDB_Contacts.FullName
FROM ESMDB_Contacts RIGHT JOIN (ESMDB_SupplierNames RIGHT JOIN
tbl_supplierInfo ON
ESMDB_SupplierNames.ID=tbl_supplierInfo.supplierID) ON
ESMDB_Contacts.ID=tbl_supplierInfo.wbESMRMID;

qry_calendar_events **matches up the supplier names and their events
SELECT ESMDB_SupplierEventCalendar.ID,
ESMDB_SupplierNames.SupplierName, ESMDB_SupplierEventCalendar.Title,
ESMDB_SupplierEventCalendar.EventType,
ESMDB_SupplierEventCalendar.Begin, ESMDB_SupplierEventCalendar.End,
ESMDB_SupplierEventCalendar.Description
FROM ESMDB_SupplierNames INNER JOIN ESMDB_SupplierEventCalendar ON
ESMDB_SupplierNames.ID = ESMDB_SupplierEventCalendar.Supplier;

qry_events_with_ESMRM **combines previous two queries
SELECT qry_supplierInfo_ESMRM.FullName,
qry_calendarEvents.SupplierName, qry_calendarEvents.Title,
qry_calendarEvents.EventType, qry_calendarEvents.Begin,
qry_calendarEvents.End, qry_calendarEvents.Description
FROM qry_calendarEvents INNER JOIN qry_supplierInfo_ESMRM ON
qry_calendarEvents.SupplierName = qry_supplierInfo_ESMRM.SupplierName;

Now, I made the last query a crosstab and it looks GREAT but I dont
know how to modify it from the wizard to display the date and title of
the events ([Begin] and [Title]). It forces me to use Count, AVG, SUM,
etc. I am sure there must be a way around it, but I am not that well
versed with crosstabs and their modification.

qry_events_with_esmRM_Crosstab **crosstab version of previous query
TRANSFORM Count(qry_events_with_esmRM.Title) AS CountOfTitle
SELECT qry_events_with_esmRM.FullName,
qry_events_with_esmRM.SupplierName
FROM qry_events_with_esmRM
GROUP BY qry_events_with_esmRM.FullName,
qry_events_with_esmRM.SupplierName
PIVOT Format([Begin],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

THANK YOU for your assistance!!!

-Zac
Click to expand...
 
Z

zwestbrook

How is this ---
TRANSFORM First([Begin] & " " & [Title]) AS Expr1
SELECT qry_events_with_esmRM.FullName, qry_events_with_esmRM.SupplierName
FROM qry_events_with_esmRM
GROUP BY qry_events_with_esmRM.FullName, qry_events_with_esmRM.SupplierName,
Format([Begin],"mmm")
ORDER BY qry_events_with_esmRM.FullName, qry_events_with_esmRM.SupplierName
PIVOT Format([Begin],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

One limitation is that you only get one entry per month per row or use this
alternate --
TRANSFORM IIf(First([Begin] & " " & [Title])=Last([Begin] & " " &
[Title]),First([Begin] & " " & [Title]),First([Begin] & " " & [Title]) & "-
" & Last([Begin] & " " & [Title])) AS Expr1
SELECT qry_events_with_esmRM.FullName, qry_events_with_esmRM.SupplierName
FROM qry_events_with_esmRM
GROUP BY qry_events_with_esmRM.FullName, qry_events_with_esmRM.SupplierName,
Format([Begin],"mmm")
ORDER BY qry_events_with_esmRM.FullName, qry_events_with_esmRM.SupplierName
PIVOT Format([Begin],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
Click to expand...

Karl --

Thank you SO much...I used the second example...works perfect! Have to
add this to my code snippets for future reference. You're awesome!!! ++
+!
 
Z

zwestbrook

How is this ---
TRANSFORM First([Begin] & " " & [Title]) AS Expr1
SELECT qry_events_with_esmRM.FullName, qry_events_with_esmRM.SupplierName
FROM qry_events_with_esmRM
GROUP BY qry_events_with_esmRM.FullName, qry_events_with_esmRM.SupplierName,
Format([Begin],"mmm")
ORDER BY qry_events_with_esmRM.FullName, qry_events_with_esmRM.SupplierName
PIVOT Format([Begin],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
Click to expand...
One limitation is that you only get one entry per month per row or use this
alternate --
TRANSFORM IIf(First([Begin] & " " & [Title])=Last([Begin] & " " &
[Title]),First([Begin] & " " & [Title]),First([Begin] & " " & [Title]) &" -
" & Last([Begin] & " " & [Title])) AS Expr1
SELECT qry_events_with_esmRM.FullName, qry_events_with_esmRM.SupplierName
FROM qry_events_with_esmRM
GROUP BY qry_events_with_esmRM.FullName, qry_events_with_esmRM.SupplierName,
Format([Begin],"mmm")
ORDER BY qry_events_with_esmRM.FullName, qry_events_with_esmRM.SupplierName
PIVOT Format([Begin],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
Click to expand...

Karl --

Thank you SO much...I used the second example...works perfect! Have to
add this to my code snippets for future reference. You're awesome!!! ++
+!- Hide quoted text -

- Show quoted text -
Click to expand...

hmmm...now to have it work for more than 2 events...i'll see what i
can figure out. great start, though...thanks!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to specify a Crosstab report 0
How to getting text labels to show up in scatter chart. 0
formatting report using crosstab query 3
Custom Grouping 1
Columnar report for several credit unions 7
MULTIPLE COLUMNS within SUB-REPORT ONLY 1
sorting columns in crosstab query (xxx@0, xxx@1, xxx@2, ... , xxx@N) 3
Sum unique entries in report footer 2

Top