Crosstab query for up to 16 Yes/No fields

[skydivetom]

Hello:

I would like to get some recommendations with setting up a crosstab query. I believe a have a data set that just "screams CROSSTAB" but I'm not entirely certain how to set it up given the large number of Boolean fields.

Background:
- Attached DB contains a table with 30 sample records.
- Table [tblExampleData] contains 18 fields where [ID] contains distinct autonumber AND [SECTION_NUM] includes 10 records = "1" and 20 records = "2".
- The remaining 16 fields are all Yes/No fields...


- ... where values across fields [DATA_TYPE_DATE], [DATA_TYPE_INT], [DATA_TYPE_REAL], [DATA_TYPE_VARCHAR] can only exist in one or the other of these 4 fields
- ... alternatively, for the remaining 12 fields ([NULL], [B], [C], [D], ..., ..., [F]), there can be any number of records = TRUE. **

** Please note the one-letter fields names, e.g., [B], [C], [D], etc. are abbreviations for longer fieldname strings. That's just how the customer shortened them.

Additional Information:
- I currently have a query "qry01_Count_for_B_With_SectionNumber" which only includes field [B] and its associated count of {10, 2, 18} for [SECTION_NUM] = {1, 2} & TRUE/FALSE.
- I think it would be perfectly fine to remove field [SECTION_NUM] from that query. Thus, "qry02_Count_for_B_Without_SectionNumber", I get values {2, 28} based on field [B] = TRUE/FALSE, respectively.
- The 2nd query (without [ID]) appears to be the preferred method... i.e., I don't think I need to separate my records by section number in the crosstab... unless it is very easy do to (i.e., include/exclude it).
- Well, [ID] maybe required for the CROSSTAB... not entirely certain here.

My question:
- How can I create a crosstab query that creates a matrix of the 16 Boolean fields?

Final Comments:
- Again, maybe somehow it is field [ID] by/over ([DATA_TYPE_INT], [DATA_TYPE_REAL], [DATA_TYPE_VARCHAR]).
- A 2nd query would be field [ID] by/over ([NULL], [B], [C], [D], ..., ..., [F]).
- However, if these two can be merged, that'll be fine too.

I'd welcome any recommendations how to best display/output this very simplified data set in a meaningful crosstab query.

Thank you,
EEH

[ranman256]

make a 'report' table

that has ALL fields needed.

run Q1 append your data to this table
run Q2 that will append a generic record so all fields get a value.

then run a crosstab query on the report table which now has all values.

[skydivetom]

Hi - what's the difference between the 'report' table and the existing table?

[June7]

Why mutually-exclusive yes/no fields? Why not 1 field with choices (a combobox)? In your case there would be 2 fields with choices, DataType and Data.

You want a matrix that counts yeses and noes for each?

Does this get what you want:

Query1:
SELECT Section_Num, "B" AS Src, B AS Data FROM tblExampleData
UNION ALL SELECT Section_Num, "C", C AS Data FROM tblExampleData
UNION ALL SELECT Section_Num, "D", C AS Data FROM tblExampleData
UNION ALL SELECT Section_Num, "E", C AS Data FROM tblExampleData
UNION ALL SELECT Section_Num, "F", C AS Data FROM tblExampleData
UNION ALL SELECT Section_Num, "G", C AS Data FROM tblExampleData
UNION ALL SELECT Section_Num, "L", C AS Data FROM tblExampleData
UNION ALL SELECT Section_Num, "O", C AS Data FROM tblExampleData
UNION ALL SELECT Section_Num, "R", C AS Data FROM tblExampleData
UNION ALL SELECT Section_Num, "S", C AS Data FROM tblExampleData
UNION ALL SELECT Section_Num, "P", C AS Data FROM tblExampleData;

Query2:
TRANSFORM Count(Query1.Data) AS CountOfData
SELECT IIf([Data],"Y","N") AS YN
FROM Query1
GROUP BY IIf([Data],"1","0")
PIVOT Query1.Src;

[skydivetom]

June7 -- thank you for the response.

Merging 4 fields (DataType) would work. Values are distinct.

I am hesitant to say the same for the other 12 fields.

I'll check out your queries when I return home.

[Gicu]

Hi Tom,

Not sure a crosstab is what you describe with your matrix, crosstabs take the values from a field and transpose them as columns, not from different fields as you
suggest.

Please review the attached file where I created a temporary table to hold the summary information for the yes\No fields (basically copied the source table and changed the data type from Yes\No to long integer to hold the counts).

Cheers,

[skydivetom]

June 7 -- when executing Query2, I get an Aggregate error. See attached JPG.

[skydivetom]

Vlad -- thanks for chiming in on this one... How was the TMP table created? I didn't see type of MakeTable query which would auto-generate it during the next update/change.

Just trying to gain a better understanding about this method. Thanks.

[Gicu]

Hi Tom,

I just manually copied the original table (structure only) then opened the new one in design view and removed the ID then changed the Yes\No fields to Number (long). It could be probably automated but I did not have enough info on the intended uses\specifics.

Cheers,

[June7]

I don't know why you would get that error. Posted queries work for me.

[skydivetom]

Hello:

I very much apologize for the delayed response... 'just one of "those" days. Anyhow, I had another look at the structure. Both recommendations (from June7 and Vlad) opened my aperture to rethink this slightly.

Attached is (I think) a working solution which outputs the counts by TRUE/FALSE values by fieldname. Again, the recordset containing only 30 records is just an example. It should/will work w/ the full data set (I'm confident).

Ultimately, I removed the ID number and include the TRUE/FALSE instead. Thanks for the recommendations and feedback, June7 & Vlad.

Cheers,
Tom

[skydivetom]

Vlad, June7:

Thank you for the assistance w/ this query. The attached results (see attached) appear to be sufficient and summarize the Boolean fields sufficiently.

Again, thanks for offering some great ideas.

Cheers,
Tom