Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2006
    Posts
    126

    Unanswered: Summary of "What Isn't"

    In most reporting you want a report of "what is", not "what isn't". However, thats roughly what I'm trying to do.
    I have 3 tables: Person demographics, Active Badges, and a linking table connecting each person to the badges they are authorized for.

    I have lots of reports of who is signed up for which badge(s).

    What I'm trying to do is produce a report of those badges for which no-one has been authorized, followed by badges for which only one person has been authorized.

    So far I've tried a select query where the join takes every badge and only those persons that have that badge. That produces a line for each badge/person and a single line with "null person" for those with no-one signed up I could use that and then set the criteria for only those badges that are blanknull for the person field. That would give me those that are zero count. I would then have to create a different report for those that have only one person. I'd prefer to do it as a single report because I can see in the future someone asking for "How many are signed up for each badge in order from 0 to N".

    I've looked at cross-tab queries, but not actually tried one because they appear to work only with "what is" not "what isn't".

    What say you gentlemen and ladies?
    Pete Townsend
    PGT

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    GROUP BY and HAVING COUNT(*) = 1 should get you where you want to go for badges only assigned to one person.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    If you want to find badges without authorisers, you need an outer join. To create this in the query interface, add your badges table and your intersection table, and double-click on the join line between them to open the "Join Properties" box. Select the option that will give you all records from the badges table and only those records from the intersection table where the joined fields are equal. Click "OK", and the join line will turn into an arrow pointing from the badges table to the intersection table.

    You can then select all the fields that you want from the badges table, and include a field from the intersection table with the criterion, "Is Null". This will only return records from the badges table that do not have any corresponding records in the intersection table.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  4. #4
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    and, the crosstab may actually do what you want. You'll have to be tricky and "seed" the cross tab with all of the badges first then you can run it and look for zeros, ones, twos, etc.

    Here's an example of what I mean: I have a sales report that totals quotes and crosstabs the results by day on one axis and by sales rep on the other. During a given week, if each person made at least one quote and there was at least one quote each day, then it works great and looks good.

    However, if Tracy didn't send any quotes this week, no column shows up for her. This presents a problem when I have a nicely formatted report that is looking for a column for each sales rep - no data for Tracy means no field called "Tracy" and the report has an error. Even if I trap the error and work around it, now the columns are out of order. I recognize there are fancy ways of fixing this through code, but I use a very simple, elegant method.

    I created a select query that contained all of the data I want in the crosstab and then used a UNION to add 1 record for each sales rep (with a null value for the sales amount). This is what I run the crosstab on and it works like a charm - the resulting crosstab query has a field for each sales rep and if they didn't send and quotes, the null is formatted to a dash to make it look nice.

    Code:
    SELECT * FROM qryBadges UNION SELECT * FROM qryBadgeList;
    Also, if you simply want to know which badges are not authorized, use the outer join described by weejas to include all badges and only those on the linking table where the values match, group by badgeID and count people.
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  5. #5
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    Change your select query to a group by query. Group by badge and sum iif(nz([person],0)=0,0,1). This will give you a list of all badges and a count of persons for each badge.

  6. #6
    Join Date
    Aug 2006
    Posts
    126
    Thanks to all so far. Thanks weejas, the outer join is what I was describing in the 4th paragraph, I just didn't know it was an outer join. So everyone seems to agree on the way to get the list of zero-people. I'll have to look in more detail at tcace's and rogue's ideas.
    Rogue: The group-by query will indeed give a count for each badge, but do you know of a way to then sort the groups so the badges with 0 count print first, followed by the badges with 1 count, followed by 2 count, followed by... etc. ?
    Pete
    PGT

  7. #7
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    Sort ascending by the calculated field (iif statement field) in the query and/or report.

  8. #8
    Join Date
    Aug 2006
    Posts
    126
    Rogue
    Thanks
    PGT

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •