Results 1 to 6 of 6
  1. #1
    dezenzerrick is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Aug 2015
    Posts
    7

    Grouping by and Expressions for Reports

    Hi all,



    I have a database that tracks employee training. When a person walks up and asks "Who doesn't have this/that training?" I am kind of stuck. I can only display who has what training, while not being able to tell them who doesn't have that training.

    For instance. I can generate a report (rptCalendarSearchSummary) that will, if a person has "X" training, displays a 1.
    I would like this report to include all employees, regardless of whether or not they have the training, and just display a 0 if they do not.
    I'll attach pictures to give a better idea.
    Is there something I have to change in the way the data is grouped? Or add something else to the "CurrentAppointments: Sum(1)" field?

    PS, the names are blurred for company reasons. Try to imagine a list of names
    Click image for larger version. 

Name:	Untitled.png 
Views:	17 
Size:	34.9 KB 
ID:	23607Click image for larger version. 

Name:	Untitled2.png 
Views:	17 
Size:	30.4 KB 
ID:	23608

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you need an OUTER JOIN. (you would dbl-click a join line above to set outer join query)

    If you have a list of all training classes, you would outer join this and ask for:
    all persons, but only some classes. Then for criteria ask for : tClasses.Classname = null
    This will show what a person did NOT take.

  3. #3
    NTC is offline VIP
    Windows 7 64bit Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    in other words what R256 is saying;; in order to report on what's NOT in the database you start with the universe of all people. That's the outer join - a join arrow with a pointer from the People table so that all People are always included. At a high level you'll have a value for those that took the course, maybe a date or grade or something...and so you'll use the criteria 'Is Null' to result in just those with no data.

  4. #4
    dezenzerrick is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Aug 2015
    Posts
    7
    Alright, I was able to switch the join around successfully. Initially it gave me an ambiguous join, and I had to change the type of join on "qryAppointmentsLifetime" (this just shows how much training classes someone has). As such, I am having trouble getting the people who don't have the test display a 0 instead of a 1, see pictures.

    Basically, in the first photo, the ones who have a number in the "Lifetime Appointments" category are the people who have taken the test that I searched for (RU LPI). All the other people should be displaying a 0, instead of a 1. I can't figure out how to do the null thing
    (sorry, I am not the best at access, I am trying to learn it more for my company.)

    Click image for larger version. 

Name:	Untitled2.png 
Views:	9 
Size:	13.9 KB 
ID:	23704Click image for larger version. 

Name:	Untitled.png 
Views:	9 
Size:	26.2 KB 
ID:	23705

  5. #5
    dezenzerrick is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Aug 2015
    Posts
    7
    If anyone can help, I am still trying to figure this out

  6. #6
    NTC is offline VIP
    Windows 7 64bit Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    I don't follow because I see generic names like 'query results' and 'appointments'. So I can't answer specifically, but the core idea is:
    All People Table joining to All Training Done Table (joining on the person ID) with the arrow point from All People to All Training so that All people display. Then those that did not have Training will have a blank in the All Training Field. This is the key set up.

    You can first filter the All Training Done to 1 specific class if you want. Then in order to get a result of just those without training you want just the records with that blank - so you put the criteria of this query to be "Is Null"

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Crystal Reports: Mapping Expressions
    By Dan Hugos in forum Reports
    Replies: 12
    Last Post: 04-01-2015, 07:42 AM
  2. Reports: Feilds vs Expressions
    By colleenvr in forum Reports
    Replies: 5
    Last Post: 08-13-2014, 10:35 PM
  3. Grouping Expressions
    By GraeagleBill in forum Reports
    Replies: 5
    Last Post: 05-10-2013, 05:58 PM
  4. Grouping in Reports
    By EWatts in forum Reports
    Replies: 1
    Last Post: 04-07-2012, 07:06 PM
  5. Grouping reports
    By jfrazer22 in forum Reports
    Replies: 1
    Last Post: 08-26-2010, 01:18 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums