Relatioanal Count

[LtCmdrData]

I have been asked to compile a chart to cross reference all fields within my database of 3 related tables with each other and show a count of how many fields can be matched on the basis that they are not of a null value.

for example

Col_1 Col_2 Col_3 Col_4
abc ABC 1 1
def DEF 2
ghi 3 3
jkl JKL

would look like this ultimatley

Col_1 Col_2 Col_3 Col_4
Col_1 1 3 3 2
Col_2 3 1 2 1
Col_3 3 2 1 2
Col_4 2 1 2 1


I am banging my head against a wall on this one, and to be honest i do not even know if it is possible. Any help at all would be greatly appreciated. I do very much appreciate i have not exactly explained myself very well in this matter but am happy to give it another go.

 

[Craig0201]

How are you calculating these figures? It makes little sense how you derived the second table.

 

[LtCmdrData]

Having just reread what i posted the second tanle should show as
Col_1 Col_2 Col_3 Col_4
Col_1 x 3 3 2
Col_2 3 x 2 1
Col_3 3 2 x 2
Col_4 2 1 2 x


The number results are generated from:
Col_1 where there is data in the field has data also in Col_3 on 3 instances,
Col_2 where there is data in the field has data also in Col_3 on 2 instances,

as you can appreciate with a database of over 70 fileds spread over 3 tables this is going to take a long while to do manually. Have i explained myself properly or am i still managing to talk in strange circles?

 

[Craig0201]

I'd use VBA and a loop to create queries like

SELECT Count(Table1.Col_2) AS CountOfCol_2
FROM Table1
WHERE (((Table1.Col_1) Is Not Null))
HAVING (((Count(Table1.Col_2)) Is Not Null));

to insert into a new table listing pairs of field names and number of records

I'd then use the new table's data to export to Excel and use a pivot table to present.

Not neat but it would work.

 

[LtCmdrData]

never really had any experience in VBA but i am guessing it is not that bad to use for simple queries and i think there might be a copy of VBA for dummies or some such literature in the office cupboard...

thankyou very much for your help it is certainly going to make my job a lot easier than going through by hand

 

[dhookom]

You can also use union queries like:
quniOne

SELECT 1 AS ColA, Count(tblLtCmdrData.Col_2) AS TheCount, 2 AS ColB
FROM tblLtCmdrData
WHERE Col_1  Is Not Null AND  Col_2 is not null
GROUP BY 1, 2
UNION ALL
SELECT 1, Count(tblLtCmdrData.Col_3),3
FROM tblLtCmdrData
WHERE Col_1  Is Not Null AND  Col_3 is not null
GROUP BY 1, 3
UNION ALL
SELECT 1, Count(tblLtCmdrData.Col_4),4
FROM tblLtCmdrData
WHERE Col_1  Is Not Null AND  Col_4 is not null
GROUP BY 1, 4
UNION ALL
SELECT 2, Count(tblLtCmdrData.Col_3),3
FROM tblLtCmdrData
WHERE Col_2  Is Not Null AND  Col_3 is not null
UNION ALL
SELECT 2, Count(tblLtCmdrData.Col_4),4
FROM tblLtCmdrData
WHERE Col_2  Is Not Null AND  Col_4 is not null
UNION ALL
SELECT 3, Count(tblLtCmdrData.Col_4),4
FROM tblLtCmdrData
WHERE Col_3  Is Not Null AND  Col_4 is not null;

Then another union query:
quniTwo

SELECT ColA, ColB, TheCount
FROM quniOne
UNION ALL
SELECT ColB, ColA, TheCount
FROM qunione;

Finally create a crosstab that displays your results

TRANSFORM First(quniTwo.TheCount) AS FirstOfTheCount
SELECT "Col_" & [ColA] AS [Column]
FROM quniTwo
GROUP BY "Col_" & [ColA]
PIVOT "Col_" & [ColB];

[tt][blue]
Column Col_1 Col_2 Col_3 Col_4
Col_1 3 3 2
Col_2 3 2 1
Col_3 3 2 2
Col_4 2 1 2
[/blue][/tt]

Duane MS Access MVP
Now help me support United Cerebral Palsy