Query to check state of multiple chckboxes

[Bobbingbob]

Hi,

I'm a bit of a access newbie. I've been messing around with queries and ran across an odd issue that I can't seem to resolve.

I have a single table with many records. Each record contains a reference number and multiple check boxes that can be used in any combination.

For example:
Reference - Function 1 - Function 2 - Function 3 - Function 4
Ref_1 ----------X------------X---------------X-------------------
Ref_2 -----------------------X-----------------------------------


Ref_3 ----------X------------------------------------------X-----
Ref_4 -----------------------------------------------------------
Ref_5 ----------X------------X--------------X--------------X-----

My goal here is to generate a query that returns the record if function 1, function 2, function 3, function 4 or any combination of them are used. So in the above example, my goal would be for ref_4 to not be returned because there are no X's associated with it but all other records would.

I thought this would be simple by using the "or" line in design view, but listing out all functions on the or line causes only those with all four functions marked as 'true' to return.
Also, when setting one function in the criteria line, it seems that only the lines that are either marked as true for that specific function OR are marked true for all other functions are returned.

I can't figure out what's going on here..any ideas?

[pbaldy]

Your design doesn't sound normalized, but what you want is the criteria for each function to be on a different line. In SQL view that creates the OR test.

[Bobbingbob]

Aha - that's what it was. I was putting it all on the same line, thank you!

You're correct that the data isn't normalized. The Reference number mentioned actually isn't unique and can be duplicated many times. In addition, it seems silly to me to need to have all the functions listed horizontally rather than using one field.

Unfortunately, I'm in a situation where I'm trying to unify differing information from other databases that are maintained via dynamically updated excel sheets (that's just how it is). Since the data is output into excel sheets, this means I'll need to update by access DB by importing the information into it from those sheets periodically. So far as I've been able to tell, there doesn't seem to be any way to automatically normalize data in access that's imported from external spreadsheets.

[pbaldy]

Happy to help! There isn't a built-in way to normalize, but it is likely possible. A union query could work, or code. It would depend on the specifics, but I'm guessing it's too late anyway.

[Bobbingbob]

Thanks pbaldy,

I've gone through and tried to normalize the data a bit, but I'm running into another issue that normalization doesn't seem to be solving...

I'm looking to get only a single list of reference numbers and a list of all functions that reference number could possible use.

Using the above example, what I'm trying to get is:

Table:
Reference - Function 1 - Function 2 - Function 3 - Function 4
Ref_2 ----------X----------------------------X-------------------
Ref_2 -----------------------------------------------------X------
Ref_4 ----------X------------------------------------------X-----
Ref_4 ------------------------------------------------------------
Ref_4 ----------X------------X--------------X--------------X-----

Query Output (goal):
Reference - Function 1 - Function 2 - Function 3 - Function 4
Ref_2 ----------X---------------------------X--------------X----
Ref_4 ----------X------------X--------------X--------------X-----

But I keep getting duplicate values. I have the query setup to 'group by' the reference number (from clicking on the totals button) in addition to the OR structure previously mentioned, but it seems to return the same reference number if it's found that two records have a different set of functions. So what I end up with looks something like this:

Query Output (what I'm getting):
Reference - Function 1 - Function 2 - Function 3 - Function 4
Ref_2 ----------X---------------------------X-------------------
Ref_2 -----------------------------------------------------X-----
Ref_4 ----------X------------X--------------X-------------X-----

I attempted to normalize the data by pushing out the reference numbers to a different table, but I ended up with the same result. Is there another simple fix for this as well?

[pbaldy]

Can you post the db here? If the data is actually normalized, a crosstab query could get that look.

[Bobbingbob]

Sure, thanks for the help - here it is:

Test_Ref.accdb

'main' is main dataset. Reference_Num is a table with all the reference numbers pulled out. SadQuery is the query I used that yielded the duplicate reference numbers

[pbaldy]

Well, normalizing would mean not having numbered fields, something like

ReferenceNum  Function
   10          1
   10          2
   11          1
   11          3

In any case, see if this returns what you want:

SELECT Reference_Num.[Reference number], Min(main.[Function 1]) AS [MinOfFunction 1], Min(main.[Function 2]) AS [MinOfFunction 2], Min(main.[Function 3]) AS [MinOfFunction 3], Min(main.[Function 4]) AS [MinOfFunction 4]
FROM Reference_Num INNER JOIN main ON Reference_Num.Ref_ID = main.Ref_Num_ID
WHERE (((main.[Function 1])=True)) OR (((main.[Function 2])=True)) OR (((main.[Function 3])=True)) OR (((main.[Function 4])=True))
GROUP BY Reference_Num.[Reference number]

[Bobbingbob]

Hi pbaldy,

Sorry for not replying to thank you sooner - thank you!

The above does seem to work, but I've since been playing with the data and have decided to reformat it in an attempt to normalize it properly.

I've modified the source to display the reference numbers and function numbers as you listed; however, this does cause there to be multiple entries for a single revision of the same reference number. so If reference 10 used function 1 and 2, then that will be two records with all the exact same information in that record aside from that one field. I have this setup for multiple different functions which caused there to be many additional records added to the database.

In attempting to analyze the data, it /appears/ to work ok, but the total count of total reference numbers is inflated due to there being multiple records.

Am I on the right track to normalizing and/or formatting the data properly? After splitting things into new tables, it seems that importing data is going to be much trickier.