Access Query 1

[formrec]

Hi,

I'm doing a simple report whereby I am querying a table sorting the data,etc, but I want it to display all the possible values, rather than a subset (as its ultimatley going to be output to a chart)

For example at the moment I am getting this back

val Count
1 5
3 6

But would like it to give me

val Count
1 5
2 0
3 6
4 0

Any ideas would be greatly appreciated.

Regards

Jamie

 

[MDTekUser]

Without looking at your query, and it might be a good idea to post it, you might do a left join on the table that has the val column. A left join would display all values...

 

[formrec]

Great thanks... I'll give that a go!

 

[formrec]

Ok....

Heres the query...

Tables

Main (all data)
Codes (values - with 4 options 1 - 4)

SELECT Codes.Code, Count(Main.S2_Q1) AS Count FROM Main INNER JOIN Codes ON Main.S2_Q1 = Codes.Val GROUP BY Codes.Code, Codes.Val ORDER BY Codes.Val;

Tried a left join but probably did it wrong....

Help please!

 

[w860098]

The implication appears to be that you have joined 2 (or more) tables in the query.
If you have, then you will need to ensure that the join properties for the appropriate links have been suitably modified, e.g. it may be necessary to establish the correct 'ALLnly' relationships.

 

[PHV]

SELECT Codes.Val, Count(*) AS Count
FROM Main RIGHT JOIN Codes ON Main.S2_Q1 = Codes.Val
GROUP BY Codes.Val
ORDER BY Codes.Val

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MDTekUser]

Can you post a few sample records from the main and codes table?

 

[formrec]

Sorry for not replying yesterday.

PHV's response is pretty much as far as I got, which in theory should work, but it seems to give slightly eratic data.

For example I have set the field S2_Q1 to all being 1, for 20 rows. The table Code.Val has the codes I will ultimaltely be using which has the values 1, 2, 3 and 4.

When I run this query it gives the following output.

Val Count
1 20 correct
2 1 ?
3 1 ?
4 1 ?

Any ideas why?

Thanks

Jamie

 

[PHV]

Sorry for the typo:
SELECT Codes.Val, Count(Main.S2_Q1) AS Count
FROM Main RIGHT JOIN Codes ON Main.S2_Q1 = Codes.Val
GROUP BY Codes.Val
ORDER BY Codes.Val

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[formrec]

Thanks PHV. Works great now! It's easy when you know how!

Cheers

Jamie

 

[formrec]

Ahh, next bit. When I use this its fine, but if I add a HAVING or WHERE clause it seems to throw it back to how it was originally. Which does kind of make sense....

SELECT Codes.Code, Count(Main.S2_Q2) AS [Count]
FROM Main RIGHT JOIN Codes ON Main.S2_Q2 = Codes.Val
GROUP BY Main.HolRef, Codes.Code, Codes.Val
HAVING (((Main.HolRef)='abc1'))
ORDER BY Codes.Val;

This give

Code Count
Excellent 6
Good 6

but I want...

Code Count
Excellent 6
Good 6
Ok 0
Poor 0

Any help is appreciated!

Jamie

 

[PHV]

Perhaps this ?
SELECT Codes.Code, Count(Main.S2_Q2) AS [Count]
FROM Main RIGHT JOIN Codes ON Main.S2_Q2 = Codes.Val
WHERE Nz(Main.HolRef,'abc1')='abc1'
GROUP BY Codes.Code, Codes.Val
ORDER BY Codes.Val;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886