Blank Records Mass

[beginner33]

Hi!

I have 3 "sub" queries (EVENT_TYPE_1, EVENT_TYPE_2, EVENT_TYPE_3) for counting some events by quarters of a year. Each has columns ID, Qtr1, Qtr2, Qtr3, Qtr4.
They are joined by ID in query MASTER_SUM:


SELECT EVENT_TYPE_1.Qtr1 AS EVENT_TYPE_1_Qtr1, EVENT_TYPE_1.Qtr2 AS EVENT_TYPE_1_Qtr2, EVENT_TYPE_1.Qtr3 AS EVENT_TYPE_1_Qtr3, EVENT_TYPE_1.Qtr4 AS EVENT_TYPE_1_Qtr4, EVENT_TYPE_2.Qtr1 AS EVENT_TYPE_2_Qtr1, EVENT_TYPE_2.Qtr2 AS EVENT_TYPE_2_Qtr2, EVENT_TYPE_2.Qtr3 AS EVENT_TYPE_2_Qtr3, EVENT_TYPE_2.Qtr4 AS EVENT_TYPE_2_Qtr4, EVENT_TYPE_3.Qtr1 AS EVENT_TYPE_3_Qtr1, EVENT_TYPE_3.Qtr2 AS EVENT_TYPE_3_Qtr2, EVENT_TYPE_3.Qtr3 AS EVENT_TYPE_3_Qtr3, EVENT_TYPE_3.Qtr4 AS EVENT_TYPE_3_Qtr4
FROM (EVENT_TYPE_1 INNER JOIN EVENT_TYPE_2 ON EVENT_TYPE_1.ID = EVENT_TYPE_2.ID) INNER JOIN EVENT_TYPE_3 ON EVENT_TYPE_1.ID = EVENT_TYPE_3.ID;

This MASTER_SUM query is used to print graph or whatever. It should have one record with all counted events for each quarter, as you can see above.

My problem is:
Everything works fine until one of the queries EVENT_TYPE has no records, because there were simply no such events this year. In that case, the MASTER_SUM query also has no records, but I want to just have blanks or zeros in fields, that have no events. In another words: I want to have always one record in MASTER_SUM, no mater if some fields are blank.

I can't find workaround. Please help.

I am not very skilled in SQL and maybe this is funny question, but it drives me crazy

Thanks in advance for your help.

Igor

 

[Mark_]

OK,

I will start with one assumptions; you do a look up into an EventMaster table for the Event Field in your records. For each of your subqueries, they should be run off of the EventMaster. Join FROM the EventMaster TO your individual event records, sorted by event dates. From there turn it in to a sum query to return the number of matching records in the date range. Once you get ONE to work right this should tell you how to do the others. This will generate ONE record per event even if there are no event records in the date range.