Combining queries not showing all results?

[adh123]

Final few snagging points on a CRM system I am building for everyone in the office to use, with the help from you guys so far I am almost there, so thanks!

Background: Sales will make calls and record data using a form (frmEnquirySpeak) which will save to tblSpeak. If the call results in a meeting being booked then it also updates tblMeeting with a date of the meeting (simple stuff so far!).

A sales person might record up to 14 types of action per day, qrySpeakPerAction counts the different types of call made on a day by day basis for reporting. qrySpeakGroupedAction summarises this down to 8 categories and applies a date filter selected on a reporting form (and this works well, thanks GinaWhipp for your help!).

I now want to include the qryMeetingsBookedPerDay (counts m_date_raised and groups by m_date_raised...eg 2 on 24/12/14, 4 on 28/12/14 etc etc) with the qrySpeakGroupedAction...

My effort so far has resulted in only dates which appear on BOTH queries showing. This may not always be the case.
Every day calls will be made, this may or may not result in a meeting. So I see there being 2 options:

1 - Query to show the count of calls on a date or date range and number of meetings booked each day that calls are made (prefer this method, it sounds simpler)
2 - qryMeetingsBookedPerDay should auto populate days which dont appear as 0 (this sounds messy and not sure what capabilities/speed are like when managers want to spot patterns/trends over multiple years?)

Please say if this doesn't make sense, any help much appreciated!

 

[plog]

Can you demonstrate your issue with data? I will need 2 sets:

A. Starting sample data from all relevant tables. Include table and field names and enough sample data to demonstrate all cases.

B. What data you expect your final query(s) to produce when you feed it A.

 

[adh123]

Hi Plog

Have found an issue which might need resolving first and fixing it could correct the problem I am having.

The first query run is called qrySpeakPerAction.

As you can see from the first screenshot, it only displays dates when there is data. Is there a way for this to run and fill in when there is no data against a date...i.e. 27th January 2015, 0 in each column?

Edit: On second thoughts, this may make the table unnecessarily large?

 

[plog]

A query can't create data that doesn't exist in its underlying sources. So, you need a data source that has every date you want to report on. Do you have one?

Is there another table that has all the dates you want to appear in the final output?

 

[adh123]

No, as it will be used every weekday by the sales team (and other staff for that matter).
if all dates are added each day then I think that the table would end up being too big...

All data is in the tblSpeak which has a date column (s_date) and tblmeeting (m_date)

The query I ran had the criteria for count meetings_booked (tblmeeting) where m_date = s_date (from the speak count query). As a test I created a meeting for today only, this was the only record which displayed...the previous days where calls were made but no meetings booked these were not displayed. Is there a way to put a '0' where there are no meetings but there is calls so it will be displayed?

 

[plog]

You started out with one question, then moved to another and now you are asking a third. You really need to focus on what you are trying to accomplish.

I really have no idea what this latest question you are asking pertains to.

 

[namliam]

If you want to show dates that exist in one but not in the other, you want to double click on the line between the two queries.
This brings up 3 options, the one you are already using is an INNER JOIN, which requires dates to be present in both tables.
Choosing one of the other options will allow data to be "missing", these are called LEFT OUTER JOIN and RIGHT OUTER JOIN depending on the option you choose.
Read them closely and/or try them out to see the different results.

P.S.
I doubt that 365 records per year to ensure each date exist will make any table too big, considering I work with tables larger than 100k records each without problems what so ever.

 

[adh123]

That's perfect, thanks Namliam