Adding quarters not in table 2

[georgesOne]

Dear All:

I have a query (Quarter is a string function (Quarter: Format(Startdate, "q"), Incidents is a count function of Startdate entries during this quarter in the underlying table resulting in

Tool Quarter Incidents
A 1 2
A 3 1
A 4 3
B 3 4
C 1 2

I want a result like

Tool Quarter Incidents
A 1 2
A 2 0 <-
A 3 1
A 4 3
B 1 0 <-
B 2 0 <-
B 3 4
B 4 0 <-
C 1 2
C 2 0 <-
C 3 0 <-
C 4 0 <-

because I want to create charts with identical layout for each quarter.
How would I do that...any tip?

Thanks a lot.
Georges

 

[KenReay]

Hi You do not show your exact query, so I cannot give exact answer, but one way is to consider using the UNION operator, something like

SELECT ... your existing query..
UNION
SELECT Tool, 1 as A, 0 as B From tblTools
UNION
SELECT Tool, 2 as A, 0 as B FROM tblTools
UNION
SELECT Tool, 3 as A, 0 as B From tblTools
UNION
SELECT Tool, 4 as A, 0 as B FROM tblTools

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[lespaul]

another option would be to create a "quarters" table:

tblQuarters
QuarterID
1
2
3
4

and then do a LEFT JOIN into this table on your expression: Format(Startdate, "q")

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[georgesOne]

Thanks a lot...
Both suggestions seem to get me closer.
I apologize for not sharing the details of the query. It is really complex with (too?) many joins and formulas etc. Would only distract from the real problem and probably create typos from my side.
Georges