If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Report combiones all records
Access 2007 on WinXP
Report creted based on query: SELECT tblParticipantTrx.EmpID, tblSessions.StrtDate, tblSessions.StartTime, tblClasses.ClassName, tblParticipants.EmpFname, tblParticipants.EmpLname, tblSessions.MaxParticipants FROM (tblClasses INNER JOIN tblSessions ON tblClasses.ClassID = tblSessions.ClassID) INNER JOIN (tblParticipants INNER JOIN tblParticipantTrx ON tblParticipants.EmpID = tblParticipantTrx.EmpID) ON tblSessions.SessionID = tblParticipantTrx.SessionID WHERE (((tblSessions.StrtDate) Between [Begin] And [end])) ORDER BY tblSessions.StrtDate, tblSessions.StartTime; My problem is that I have more than one session on some strtdate's. I want the report to give totals only for each session. I can't seem to find teh right procedural syntax get this result. I keep getting totlas for a complete day, whcih appear to be correct. Since my query has both a strtdate and starttime, isn't there some way to instruct the report to generate totals based on the combination of the strtdate adn starttime. Admittedly, I do use the report wizard method of report generation as opposed coding! |
#2
|
|||
|
|||
Report combiones all records
JR Hester,
You should be able to do that. You need to use the Grouping and Sorting Dialog box to set up grouping on StrtDate and StrtTime StrtDate --- Group Footer = Yes --- Sort Ascending StrtTime --- Group Footer = Yes --- Sort Ascending You wrote: I want the report to give totals only for each session. Totals of what? I'll assume a total of persons attending... =Count(EmpID) in the StrtDate Footer would yield the count for the day. =Count(EmpID) in the StrtTime Footer would yield the count for the individual session. -- hth Al Campagna Microsoft Access MVP 2007-2009 http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "JR Hester" wrote in message ... Access 2007 on WinXP Report creted based on query: SELECT tblParticipantTrx.EmpID, tblSessions.StrtDate, tblSessions.StartTime, tblClasses.ClassName, tblParticipants.EmpFname, tblParticipants.EmpLname, tblSessions.MaxParticipants FROM (tblClasses INNER JOIN tblSessions ON tblClasses.ClassID = tblSessions.ClassID) INNER JOIN (tblParticipants INNER JOIN tblParticipantTrx ON tblParticipants.EmpID = tblParticipantTrx.EmpID) ON tblSessions.SessionID = tblParticipantTrx.SessionID WHERE (((tblSessions.StrtDate) Between [Begin] And [end])) ORDER BY tblSessions.StrtDate, tblSessions.StartTime; My problem is that I have more than one session on some strtdate's. I want the report to give totals only for each session. I can't seem to find teh right procedural syntax get this result. I keep getting totlas for a complete day, whcih appear to be correct. Since my query has both a strtdate and starttime, isn't there some way to instruct the report to generate totals based on the combination of the strtdate adn starttime. Admittedly, I do use the report wizard method of report generation as opposed coding! |
#3
|
|||
|
|||
Report combiones all records
Al,
Thanks for your reply and directions. I, too, thought that should do the trick. I even went back and followed your instruction set again just to make sure. Regardless of these settings, the report continues to show only the first class of the day and total all particiapnts for the entire day. One pointI should have included. Even though I am currently using Access07 to manipulate this database, it was originally written in AccessXP and is still in the MDB format. Could this be part of my problem? "Al Campagna" wrote: JR Hester, You should be able to do that. You need to use the Grouping and Sorting Dialog box to set up grouping on StrtDate and StrtTime StrtDate --- Group Footer = Yes --- Sort Ascending StrtTime --- Group Footer = Yes --- Sort Ascending You wrote: I want the report to give totals only for each session. Totals of what? I'll assume a total of persons attending... =Count(EmpID) in the StrtDate Footer would yield the count for the day. =Count(EmpID) in the StrtTime Footer would yield the count for the individual session. -- hth Al Campagna Microsoft Access MVP 2007-2009 http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "JR Hester" wrote in message ... Access 2007 on WinXP Report creted based on query: SELECT tblParticipantTrx.EmpID, tblSessions.StrtDate, tblSessions.StartTime, tblClasses.ClassName, tblParticipants.EmpFname, tblParticipants.EmpLname, tblSessions.MaxParticipants FROM (tblClasses INNER JOIN tblSessions ON tblClasses.ClassID = tblSessions.ClassID) INNER JOIN (tblParticipants INNER JOIN tblParticipantTrx ON tblParticipants.EmpID = tblParticipantTrx.EmpID) ON tblSessions.SessionID = tblParticipantTrx.SessionID WHERE (((tblSessions.StrtDate) Between [Begin] And [end])) ORDER BY tblSessions.StrtDate, tblSessions.StartTime; My problem is that I have more than one session on some strtdate's. I want the report to give totals only for each session. I can't seem to find teh right procedural syntax get this result. I keep getting totlas for a complete day, whcih appear to be correct. Since my query has both a strtdate and starttime, isn't there some way to instruct the report to generate totals based on the combination of the strtdate adn starttime. Admittedly, I do use the report wizard method of report generation as opposed coding! . |
#4
|
|||
|
|||
Report combiones all records
Al,
Thanks again for your response. Your reply was more help than I first realized, becasue it was that "Gibbs slap to the back of the head" I needed to take a fresh look at my methods. I tried a couple of new approaches and found one that will work. In addition to the main grouping on session, which combines date/time/and classname; I added two more GROUPINGS, Startdate and start time-- which gave me the results I wanted. Thanks fro bringing me back into reality! "Al Campagna" wrote: JR Hester, You should be able to do that. You need to use the Grouping and Sorting Dialog box to set up grouping on StrtDate and StrtTime StrtDate --- Group Footer = Yes --- Sort Ascending StrtTime --- Group Footer = Yes --- Sort Ascending You wrote: I want the report to give totals only for each session. Totals of what? I'll assume a total of persons attending... =Count(EmpID) in the StrtDate Footer would yield the count for the day. =Count(EmpID) in the StrtTime Footer would yield the count for the individual session. -- hth Al Campagna Microsoft Access MVP 2007-2009 http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "JR Hester" wrote in message ... Access 2007 on WinXP Report creted based on query: SELECT tblParticipantTrx.EmpID, tblSessions.StrtDate, tblSessions.StartTime, tblClasses.ClassName, tblParticipants.EmpFname, tblParticipants.EmpLname, tblSessions.MaxParticipants FROM (tblClasses INNER JOIN tblSessions ON tblClasses.ClassID = tblSessions.ClassID) INNER JOIN (tblParticipants INNER JOIN tblParticipantTrx ON tblParticipants.EmpID = tblParticipantTrx.EmpID) ON tblSessions.SessionID = tblParticipantTrx.SessionID WHERE (((tblSessions.StrtDate) Between [Begin] And [end])) ORDER BY tblSessions.StrtDate, tblSessions.StartTime; My problem is that I have more than one session on some strtdate's. I want the report to give totals only for each session. I can't seem to find teh right procedural syntax get this result. I keep getting totlas for a complete day, whcih appear to be correct. Since my query has both a strtdate and starttime, isn't there some way to instruct the report to generate totals based on the combination of the strtdate adn starttime. Admittedly, I do use the report wizard method of report generation as opposed coding! . |
Thread Tools | |
Display Modes | |
|
|