Results 1 to 4 of 4
  1. #1
    Bill Morris is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    May 2019
    Posts
    2

    Filtering out records with empty attributes

    Hi

    First post!



    I am new to Access so forgive me if what I am asking is really basic.

    I have a form which allows the user to add in some basic info (eg First Name, Surname) and also a series of check boxes. Each check box relates to a test (test 1, test 2, etc).

    I have set up a couple of queries:


    • Query 1 will show the basic info plus Tests 1 & 2.
    • Query 2 shows the basic info plus Tests 3 & 4.


    I want to filter it so that if the user checks boxes for Test 1 and/or Test 2 then this data appears in the Query 1 table, but not in the Query 2 table as these tests were not requested/checked.

    Thanks in advance.

  2. #2
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    This is a case where you have committed "spreadsheet" in your design thinking.
    It's very common in newcomers to access, but can lead to all sorts of issues, as writing queries against un-normalised data becomes harder and harder.

    What you have should be stored in two or ideally three tables, tblUsers , tblTests and finally tblUserTests.

    You store the test types in table tests, and then the tests the user has in taken in the junction table tblUserTests.

    See the attached for an idea.
    TestsDatabase.accdb
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    Bill Morris is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    May 2019
    Posts
    2
    Hi Minty.
    Thank you for your reply. You are correct in me thinking Excel rather than Access. I'll give that a go and let you know how I get on 👍

  4. #4
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Good luck with your project.
    Using the correct data layout ensures when test 5 or 6 or 99 arrives you don't have to redesign the whole thing.

    Have a read up about Normalization. It will get you thinking about storing data "vertically" (database method), not "horizontally". (Spreadsheet !)
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

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

Similar Threads

  1. Replies: 21
    Last Post: 03-14-2016, 01:33 PM
  2. empty a table/delete all records
    By Jen0dorf in forum Access
    Replies: 3
    Last Post: 02-22-2016, 02:53 PM
  3. Replies: 2
    Last Post: 10-04-2015, 02:45 PM
  4. Replies: 16
    Last Post: 07-15-2014, 08:52 AM
  5. Filtering Master Records and Subform Child Records
    By Nerther in forum Programming
    Replies: 6
    Last Post: 10-01-2013, 05:24 PM

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
  •  
Other Forums: Microsoft Office Forums