Need Help With SQL UnionStatement for Three+ Crosstab Queries 2

[RobertIngles]

Hi All;

I am still trying to get the hang of union queries and am not having much luck on my own with what I think should be a very simple SQL union statement in Access. Obviously it's the Union line itself I am screwing up as each crosstab works fine. The end result is to create a union of 10 crosstabs to use for charts in a connected Excel spreadsheet so that I am not running 10 seperate queries each time I update the data. Here is last version of code I tried

SELECTTBL_Project_Phase2_Devices.EncryptionProduct, Count(TBL_Project_Phase2_Devices.DeviceName) AS CountOfDeviceName
FROM TBL_Project_Phase2_Devices
GROUP BY TBL_Project_Phase2_Devices.EncryptionProduct, TBL_Project_Phase2_Devices.DEPT1_Device
HAVING (((TBL_Project_Phase2_Devices.EncryptionProduct) Is Not Null) AND ((TBL_Project_Phase2_Devices.DEPT1_Device)=True)

UNION ALL

SELECT TBL_Project_Phase2_Devices.EncryptionProduct, Count(TBL_Project_Phase2_Devices.DeviceName) AS CountOfDeviceName
FROM TBL_Project_Phase2_Devices
GROUP BY TBL_Project_Phase2_Devices.EncryptionProduct, TBL_Project_Phase2_Devices.DEPT2_Device
HAVING (((TBL_Project_Phase2_Devices.EncryptionProduct) Is Not Null) AND ((TBL_Project_Phase2_Devices.DEPT2_Device)=True))

UNION ALL

SELECT TBL_Project_Phase2_Devices.EncryptionProduct, Count(TBL_Project_Phase2_Devices.DeviceName) AS CountOfDeviceName
FROM TBL_Project_Phase2_Devices
GROUP BY TBL_Project_Phase2_Devices.EncryptionProduct, TBL_Project_Phase2_Devices.[DEPT_3_Device]
HAVING (((TBL_Project_Phase2_Devices.EncryptionProduct) Is Not Null) AND ((TBL_Project_Phase2_Devices.[DEPT_3_Device])=True));

Thanks for your help!

 

[largiemargie]

Just try "UNION" instead of "UNION ALL

 

[dhookom]

Where do crosstabs get involved with this?
There might be a better solution if we understood your specifications, tables, records, etc.

Duane
Hook'D on Access
MS Access MVP

 

[SkipVought]

Hi,

Your basic problem is that your table seems NOT to be normalized...

SELECT
  EncryptionProduct
, Count(DeviceName) AS CountOfDeviceName

FROM TBL_Project_Phase2_Devices

Where (((EncryptionProduct) Is Not Null)
  AND (([b]DEPT1_Device[/b])=True)

GROUP BY
  EncryptionProduct

All those DEPTn_Device etc are the culprits.

As a general note, your HAVING clause criteria really belongs in the WHERE clause, as the HAVING clause should only have criteria based on aggregates.

Also when adding UNIONs, remove aliases.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[dhookom]

Skip,
"All those DEPTn_Device etc are the culprits" could be the result of a crosstab where the actual data is normalized.

Hopefully RobertIngles will provide some context etc. Clearly there is not enough information to more than guess at the resolution since RobertIngles didn't even suggest what the problem is.

Duane
Hook'D on Access
MS Access MVP

 

[SkipVought]

True, however the field TBL_Project_Phase2_Devices.DEPT1_Device seems very much like a TABLE field and not a QUERY field. I needed some exercise, so I jumped to a conclusion. ;-)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[dhookom]

It's too bad we have to jump. My WAG was the crosstab was used to make a table ;-).

Duane
Hook'D on Access
MS Access MVP

 

[SkipVought]

Stand by...

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RobertIngles]

Thanks Margie!

 

[RobertIngles]

Thanks guys- Margie's tip worked out however you are correct - I am still not getting the results I was looking for. I have been swamped with other tasks I need to finish up but will post additional detail asap and I will continue to try to accomplish my goals - don't like bothering you guys for easy answers - need to learn as well.

Solved or not, I will repost under this thread by Tuesday - if I haven't been able to figure it out by then - hope you will still be willing to help!!!! PLEASE check back just in case

 

[RobertIngles]

To heck with it - the other tasks can wait and I know your time is important.

The Dept1, Dept2 etc are True False fields. I use them to identify members of that particular department. I can't use a text based "Department" column because there is crossover between the departments. A single device (computer) could be a member of more than one departmentdepending on the software they are running.

The organization I am working with is medical based and and the devices within these departments are running patient critical software - we are installing new encryption software.

The other two columns in the query are the devices/computer ID's (for the count/value) and the encryption software they are running.

I am currently running 10 crosstab seperate queries to update the data in my charts in Excel (each department has it's own progress chart).

What I wanted to accomplish was to create a single querie that would return

1. The department name (remember this is a field/column name and membership in that department is indicated by a True/False checkbox)
2. The possible current encryption softare application being run (4 possible results from the contents of the field "Encryption Product". This means the query needs to recognize the True checkmark report back the count of devices running each type of software.)
3. Report results for all 10 departments in one query something like:

Dept 1 - Count Dept 2 - Count Dept 3 Count
Encrypt App 1 100 89 56
Encrypt App 2 2 76 36
Encrypt App 3 47 56 84
Encrypt App 4 0 22 9

 

[dhookom]

What are the structure(s) of your table(s)? Do you really have department names as field names rather than storing a department in a field?

Duane
Hook'D on Access
MS Access MVP

 

[RobertIngles]

Yes D, the Department name is the Feild Name and Yes/No as a possible choice - not text string. Not by choice - had I been aware of these devices running "special" software" at the beggining of the project I might have designed my table structure differently. Once I found out about them it began to be design on the fly to save time. It became a function of Made aware of a new App to be careful of? Throw in a new Yes/No column!

Every device has an offical departmnet name as a string under the Department Field but some devices are running critical applications that others are not. I guess the best way to describe is that these are devices running specific software and are residing within one or more departments - so in a department with 100 devices, 10 might need to be classified and identified in some way so as to exclude them from general software pushes as we push by devicename and department. The software they are running makes them a device require special hadling when it comes to installing new software due to the critical nature of the function.

In hindsite it would probably be more intelligent to have created 10 classification fields but I would still be stuck with 10 fields to try to create this report. At the time it seemed to simplify my planning by simply using "False" as my selection criteria to ensure we didn't push software to these computers.
The structure of the tables pertinient fields is (I have changed the title Dept 1 etc to "Software Criteria to try to simplify:

DeviceName - text - Primary Key
Encryption Product - text
Department - text
Software App 1 - Yes/No
Software App 2 - Yes/No
Software App 3 - Yes/No
.
.
.
Software App 10 - Yes/No

As I am reading this bsck, I suspect you are going to tell me that I should have invested the time to create seperate criteria fields/columns to accomodate each software ID and that Yes/No fields should basically not be used ever. However even doing this I am wondering if my single querie idea would be feasible.

If you think this might help me, I will start tonight and report back my progress on acheiving my report results.

Thanks D - good to know you are always there!

 

[dhookom]

I would have preferred real table and field names. Consider starting by normalizing your data with a union query so it isn't a spreadsheet.

SELECT DeviceName, [Encryption Product],[Software App 1] As Running, "Dept 1" as Dept
FROM tblUnNormalized
UNION ALL
SELECT DeviceName, [Encryption Product],[Software App 2], "Dept 2"
FROM tblUnNormalized
UNION ALL
SELECT DeviceName, [Encryption Product],[Software App 3], "Dept 3"
FROM tblUnNormalized
UNION ALL
--  etc ---
SELECT DeviceName, [Encryption Product],[Software App 10], "Dept 10"
FROM tblUnNormalized;

You should then be able to create a crosstab or whatever with the result of the union query.

Duane
Hook'D on Access
MS Access MVP

 

[RobertIngles]

Thanks Duane - I am going to use your suggestion as a tutorial project to become more familiar with using SQL as my query builder.

I found a workaround for the problem. I had summary queries for each department and created a small table of encryption product possibilities. I then created a query that daisy chained each department summary query back to the encryption product possibilities table and was able to get the results I needed.

Thanks again!!