Complex Report 1

[debbieg]

Part of my database is to track what events a student has attended, was excused from, or was absent. The tables look like this:

Events table:
EventID
EventDate
EventTypeCD
EventCD

EventTypeCD table:
EventTypeCD
EventType (Evening, Mandatory, Optional)

EventsStudents table:
SSN
EventID
EventStatusCD

EventsStatusCD table:
EventStatusCD
EventStatus (Present, Excused, Absent)

Students table:
SSN
Name
GY

My client is wanting a report that looks like this:


Evening Mandatory Optional
---------- --------- ---------
Name GY 2/2 2/9 2/3 2/7 2/4 2/6
John 2005 P E P P
Jane 2006 A P P P P
etc.

I have worked on this for many hours and cannot come up with a query that works. I can’t see how I can get the date as a header and then have each student be on one line. I thought I got close with a cross-tab query but then couldn’t get it to work in a report.

They also want reports like this:

Evening Mandatory Optional
---------------- ---------------- ----------------
Name Present Possible Present Possible Present Possible
John 2 4 3 3
Jane 3 4 2 3 1
etc.

and

Mandatory Optional
--------------------------------- ------------------
Name Present Excused Absent Tot. Poss. Present Tot. Poss.
John 5 1 1 7 0 2
Jane 6 1 0 7 1 1
etc.

I think I started with the hardest one but thought I'd show you what else I need.

Is it possible with Access to get this type of report? I am using Access XP.

Thanks (sorry this is so long),
Debbie

 

[dhookom]

There are several different crosstab report samples at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

There is also an FAQ faq701-4524 here that shows how to create a crosstab with multiple values.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[debbieg]

Thank you, Duane. I FAQ worked for me. I don't think I'm going to be able to give them the first report as they want it. I'll have to suggest another format or send it to Excel.

The other two reports were able to be done. However, I'm confused on one thing having to do with the Optional Events. Here's my code:

TRANSFORM Count(IIf([FldName]="Possible",Events!EventTypeCD,IIf([FldName]="Present" And [EventStatusCD]="P",[EventStatusCD]))) AS DaVal
SELECT [LastNM] & ", " & [FirstNM] & " " & [MI] AS Name
FROM tblXtabAttendancePercent, Students INNER JOIN (EventTypeCD INNER JOIN (Events INNER JOIN EventsStudents ON Events.EventID=EventsStudents.EventID) ON EventTypeCD.EventTypeCD=Events.EventTypeCD) ON Students.SSN=EventsStudents.SSN
GROUP BY [LastNM] & ", " & [FirstNM] & " " & [MI]
PIVOT [EventType] & " " & [FldName];

Since they are optional, they may or may not attend one. If they don't attend one, then then I don't get the number Possible nor a zero for Present. It seems I should at least get the number Possible. How do I tweek this to get numbers whether they have attended or not?

Thanks so much for the help.

Debbie

 

[dhookom]

I'm not sure. You may need to add Nz() or something in the transform part. It also looks like you are missing the last "If Not" from the IIf() function.

TRANSFORM Count(IIf([FldName]="Possible",
Events!EventTypeCD,
IIf([FldName]="Present" And [EventStatusCD]="P",
[EventStatusCD], ???? ))) AS DaVal

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[debbieg]

Duane,

I tried using the NZ and that didn't do the trick. I can see why it should work on the "Present" ones, but can't understand why the "Possible"s aren't showing up because it's counting something that is there.

If I put something in the last "if not", then the "Present"s become the same number as the "Possible"s. Weird, huh?

Any other tricks up your sleeve?

Debbie