Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Relatioanal Count

Status
Not open for further replies.

LtCmdrData

Technical User
Jan 4, 2008
11
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.
 
How are you calculating these figures? It makes little sense how you derived the second table.
 
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?
 
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.
 
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
 
You can also use union queries like:
quniOne
Code:
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
Code:
SELECT ColA, ColB, TheCount
FROM quniOne
UNION ALL
SELECT ColB, ColA, TheCount
FROM qunione;
Finally create a crosstab that displays your results
Code:
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top