Results 1 to 4 of 4
  1. #1
    Kirsti is offline Competent Performer
    Windows 7 32bit Access 2003
    Join Date
    Jan 2012
    Location
    New Zealand
    Posts
    172

    More efficient way to create a query?

    I need some thoughts/suggestions.

    I have a form that populates a table, monitoring Health & Safety Checks on the Facilities owned by the company I work for. There are approx 40 fields that the person doing the H&S check has to fill in with either "yes" or "no". If "no" is chosen, then there is follow-up action required.

    I now need to create a report that shows any Facility with any "no" on any one of the 40 fields.



    If I create a SELECT query using Where [Field_1] = "No" or [Field_2] = "No" or [Field_3] = "No" etc etc up to Field_40, then I get the data I want, however of course I also get the "Yes" data that I don't want to report on.

    The only way I can think of achieving what I want, is to create 40 different SELECT queries, bringing through one field at a time, and then joining them all together with a UNION query.

    However, obviously this isn't an efficient solution!

    Does anyone have any ideas on how to handle this type of issue?

    Cheers,
    Kirsti

  2. #2
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    Was wondering :

    tblCompany
    CompanyID - PK
    CompanyName

    tblFacilities
    FacilityID - PK
    FacilityName
    CompanyID - FK

    tblTests
    TestID - PK
    TestName

    tblFacilitiesTestsResults
    FacilitiesTestsResultsID - PK
    FacilityID - FK
    TestID - FK
    TheCheckResult - Yes/No
    CheckedBy
    CheckedOnDate

    Thanks

  3. #3
    Kirsti is offline Competent Performer
    Windows 7 32bit Access 2003
    Join Date
    Jan 2012
    Location
    New Zealand
    Posts
    172
    Ahh, data normalisation!! Yes, I'm sure that will make a big difference

    Will give it a go & see how I get on.

    Thank you.

  4. #4
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    Quote Originally Posted by Kirsti View Post
    Ahh, data normalisation!!
    I really don't know. I frequently read on it & then my smoked out brain forgets it. Perhaps, reading it again & again might have installed it in my sub-conscious. I generally think along relationships, one-one, one-many, many-many, sets and sub-sets.
    All the same Best Of Luck.

    Thanks

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

Similar Threads

  1. Replies: 1
    Last Post: 06-19-2012, 06:12 PM
  2. More efficient way of repeating code
    By karmacable in forum Programming
    Replies: 2
    Last Post: 09-12-2011, 12:43 AM
  3. Replies: 7
    Last Post: 08-03-2011, 08:00 AM
  4. Efficient Process?
    By compooper in forum Database Design
    Replies: 1
    Last Post: 06-14-2011, 03:01 PM
  5. Are text primary keys less efficient than autonumbers?
    By bar tomas in forum Database Design
    Replies: 4
    Last Post: 05-11-2009, 09:37 AM

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