-
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.
-
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.
-
-
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
-
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?
-
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.
-
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.
-
-
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.
-
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.
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
-
Forum Rules