Results 1 to 10 of 10
  1. #1
    LandShark506 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2017
    Posts
    6

    Query Help


    I have been struggling with this and don't know how to build this query. I have a large file and want to take 4 columns and determine if an item appears in 3 out of 4 columns for each entry. All the columns are in the same table and the combinations could be that a person missed the first election, but voted in three in a row or then voted in election 1 and 2 missed 3 and voted in 4. Basically, the file I have is a voter file and I want to take each person listed in the file and query only people who voted in 3 out of 4 specific elections. What query would I need to run to make that happen? I'm not sure how to get this to work in a query. I am not super great with the programing, but if you can help with the criteria that would be super helpful. Thank you so much.

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Show us your table design.

    Have you tried to write down/identify the logic of what you are trying to accomplish?

    Forget programming for the moment and build a flow chart/logic diagram.

  3. #3
    LandShark506 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2017
    Posts
    6
    How do I do that?

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    as described by you, the data is not normalised which makes any query complex if not impossible.

    in this case you have described having four columns set to true or false and you want to select all records where there are three (or three or more?) set to true

    the query would be something like

    SELECT *
    FROM myTable
    WHERE col1+col2+col3+col4=-3

    for explanation, true/false is stored as -1 for true and 0 for false

    because it is not normalised, you will need to modify the query as you add more columns

  5. #5
    LandShark506 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2017
    Posts
    6
    AJAX that is great so basically I need to create a table and call the new column voter score. Then basically give the person a score. If they have voted call that true which will give a value of -1. Then in the voter score file add up the total. Then just query all the values that show a -3 or -4 value and I have my universe.

    Sound correct?

  6. #6
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I've seen similar files, if you've got it linked into access ok try something like

    TestCount: iif(len(trim(field1)) =0, 0, 1) + iif(len(trim(field2)) =0, 0, 1) + iif(len(trim(field3)) =0, 0, 1) + iif(len(trim(field4)) =0, 0, 1)

    then set the criteria for your query to be >=3 in the TESTCOUNT formula field.

    these voter files are notoriously bad in my state so you may end up with spaces " " in your fields as opposed to null values (no value at all) so that's why I've used len(trim(fieldname)) to identify whether or not there's a value present rather than the isnull(fieldname) function.

  7. #7
    LandShark506 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2017
    Posts
    6
    Thanks rpeare... This gives me another idea. Basically the results for whether the person voted could be changed to yes or No. This helps a lot. I knew there was not one simple method. I figured normalizing the field would be my best bet and then getting the list. I am teaching myself how to do this to save money and not be the slave to another person to access the data.

    I just did not know how to combine the data from those tables to be able to pull the results I want. I should have thought of this before.

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    that was my thinking

  9. #9
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    you could easily normalize this data with a union query as well if that's the route you wanted to go, then do a record count query (aggregate query), you'd just have to plan for the same potential problem " " is not the same as a null value.

  10. #10
    LandShark506 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2017
    Posts
    6
    rpeare.... That is an idea too. I can make all the null values no as well. I'll figure it out. This was so helpful to break down the barrier I was having.

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

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