Results 1 to 4 of 4
  1. #1
    jim wv is offline Novice
    Windows XP Access 2007
    Join Date
    Jul 2011
    Posts
    4

    query to show "missing data" like excel pivot table?

    Hello,

    In Excel pivot tables, there is an option to show items with missing data. I often use this to create a new dataset with zeros to export for analysis. I try and show a small example below. I feel there probably is a way to do this directly in access, and I've tried to left-join two queries but to no effect. Can anyone suggest an approach to do this? I do this so many times that it would be a great help.

    Thank you, hope this is clear.

    Jim
    Morgantown, WV


    Example of entered data with a survey plot, the species found, and how many:

    Plot 1, Species A, 7
    Plot 1, Species B, 3
    Plot 1, Species C, 3
    Plot 2, Species B, 2


    Plot 3...

    Wish for in an access select query:

    Plot 1, Species A, 7
    Plot 1, Species B, 3
    Plot 1, Species C, 3
    Plot 2, Species A, 0
    Plot 2, Species B, 2
    Plot 2, Species C, 0
    Plot 3...
    Last edited by jim wv; 07-21-2011 at 02:51 PM. Reason: clarity

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You would need a master table of all possible plot and species combinations then use this table in query with jointype of 'Show all records from MasterTable ..." Where the join does not have matching record, data field will show a null. Handle the null with an expression.
    Qty: Nz(datafield,0)
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    You don't actually need a master table, it's just probably better practice to do so. Here's an example database doing what you asked.

  4. #4
    jim wv is offline Novice
    Windows XP Access 2007
    Join Date
    Jul 2011
    Posts
    4
    Thanks, that did it. I did it via queries too but if dataset gets too big I'll have to make a master table I think

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

Similar Threads

  1. Replies: 1
    Last Post: 04-25-2011, 12:36 PM
  2. Replies: 1
    Last Post: 04-06-2011, 04:33 AM
  3. Query Table for "*" and then Count all instances
    By Steven.Allman in forum Queries
    Replies: 14
    Last Post: 09-10-2010, 07:45 PM
  4. Fields do not show in "Form View"
    By hawzmolly in forum Forms
    Replies: 4
    Last Post: 01-09-2010, 06:27 PM
  5. Replies: 0
    Last Post: 09-17-2009, 12:21 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