How to make query to retrieve multiple count permutations

[and]

Hi - I am learning as I go in Access...so far so good, but now I feel I am not seeing the best way to do this.

Each record in my DB has the following two fields:
Qlevel - possible values 1 - 9
Dcode - possible values a - h

I want to produce a query that will tell me counts of how many records have where:
Qlevel = 1 AND Dcode = a
Qlevel = 1 AND Dcode = b
Qlevel = 1 AND Dcode = c
....etc etc

..and then:
Qlevel = 2 AND Dcode = a
Qlevel = 2 AND Dcode = b
Qlevel = 2 AND Dcode = c
...etc etc...through all the possible permutations.

I can see how I can do this by building all the individual queries in design view but this seems rather labourious and am I sure there must be a nice easier way to do this. Am I just not understanding fully how to use SQL? Seems that way to me....

Can anyone enlighten me?

TIA

Andrew.

 

[nabiS2K]

You may want to try a Cross Tab query. You can use the Query Design Wizard to help you create one.

Let me know if this helps.

 

[and]

Thanks. I am trying this Cross tab query lark but don't seem to be getting it.

My DB has:
Qlevel : values range from 1 - 9

I have 8 other fields in the same table. Call them A, B, C, D, E, F, G, H.

I need a count of records where:
Qlevel = 1 AND field A = Yes
Qlevel = 1 AND field B = Yes
Qlevel = 1 AND field C = Yes
...and so on.......Can I do this with one or many Cross-tab queries?

I need it to return a count of zero if no records match.
I want this query to be the source for a report.

I'm just not getting this so if you or anyone can give my tiny brain the bright spark it needs I'd be very grateful!

Thanks,

Andrew.

 

[lespaul]

So you stated incorrectly when you said:

Each record in my DB has the following two fields:
Qlevel - possible values 1 - 9
Dcode - possible values a - h

you don't have two fields as stated above you have:

Qlevel
DCode1 with a YES/NO
DCode2
DCode3
...
DCode8

I would first recommend that if possible you normalize this database. Anytime you have numbered fields like that your table isn't normalized. For more information see 'The Fundamentals of Relational Database Design'

If you are unable to restructure this table correctly, then I would create a query that normalizes the data and then run the cross tab query on THAT query.

qryNormalizeQLevels:

SELECT QLEVEL, 'A' As DCODETYPE WHERE DCODE1 = True
UNION
SELECT QLEVEL, 'B' WHERE DCODE2 = True
UNION
....
SELECT QLEVEL, 'H' WHERE DCODE8 = True

now use qryNormalizeQLevels and the source for the cross tab.

HTH

Leslie

 

[and]

Thanks Leslie,

Right. Yes, sorry, I realise I have been misleading.

I just named them so for ease in the post, but I guess that was a bad idea...

My fields and values actually are:

Qlevel: 1-9
Ethnicity: BME or white
Gender: male/female
LoneP: yes/no
Homeless: yes/no
offend: yes/no
ref: yes/no
asy: yes/no
disab: yes/no

I want to know a count of how many Males are at Qlevel1, Males at Qlevel2 etc....all possible permutations...

Do I set up one crosstab for each cross-reference of data ie: values of Qlevel as row headings with Male and Female (gender) as Column headings....and then another cross tab for the next field?

How can I get it to show all the values of Qlevel in the row headings, ie including the values that have a count of zero and therefore are not showing?

I am an idiot. I can't get my head round how this works.

TIA if you can lift the fog.....

Andrew.

 

[JonFer]

Another option is to use a table to do the translation of the columns into rows at the same time the crosstab is doing the Qlevel field into columns. Set up a table with the same T/F columns (Gender, LoneP, Ref, etc) but make them Integer type. Add a field called Attribute and then add a row for each T/F field as follows where the cell for the matching Attribute and column is -1 but all others are 1.

Attribute   Gender   LoneP  Ref
 Gender       -1      1     1
 LoneP         1     -1     1
 Ref           1      1    -1

Drop this table into a query with your main table and use these fields:

Select Attribute,
-Sum((a.Gender=b.Gender)+(a.LoneP=b.LoneP)+(a.Ref=b.Ref)) as AttributeCount
From YourTable as A,
NewTable as B
Group by Attribute

Because True values are -1 internally, the (a.Gender=b.Gender) condition will return True (-1) if the Gender field in your table is True when the query joins the "Gender" row to a row in your table. The 1 value in the Attribute table for the other fields will not match a True or False value so you'll get a False (0) from the other conditions in the sum. The end result is a 1 where the matching column is True and you can use this for a count.

If you want both True and False counts, you'll need to add two rows per Attribute and probably add a Value field (which you would include in your Select and Group By clauses):

Attribute Value   Gender   LoneP  Ref
 Gender   True       -1      1     1
 Gender   False       0      1     1

You could also set this up to do multiple levels of Attribute checking by adding columns to the new table:

Attribute1  Attribute2  Value   Gender   LoneP  Ref
 Gender     LoneP       T/T       -1      -1     1
 Gender     LoneP       T/F       -1       0     1