Query Issue

[Leesha]

Hi,
I have a user who needs to adjust their billing based on days closed for COVID. The data they receive from the stores comes as the day of the month number not the actual date. I've created a table to hold this data. I've created a second table with their sales data for the month (which does have month dates) and am linking on the day of the month. When I do this, it filters out stores. When I just check for the total closed days in the COVID table (tblCOVIDDaysUnion) there are 8149 closed days. When I join it to tblCOVIDSalesByDayNumber it filters down to 214 days in qryCOVIDSalesDataCombined. It should show all 8149 days. I stipped down the database to upload. qryCOVIDSalesDataCombined is returning only 2 closed stores and there should 46.
Thanks!
Leesha

[HansV]

I will get back to you later, it's dinner time here.

[HansV]

The tblCOVIDDaysClosedUnion table shows that store #3, for example, was closed on day 1, 2 and 3.
The tblCOVIDSalesByDayNumber table does not have records for store #3 on those days.
So if you join the tables on StoreID and day number, you won't get any records for store #3 and days 1 to 3.
To get around this, you need a left join.
Small additional problem: you accidentally created two joins on the day number. The lines for these joins are on top of each other, so you don't see that. Delete the visible join line on day number and you'll see that there still is a join on those fields.
Click each of the two join lines and select the option to return all records from tblCOVIDDaysClosedUnion.
You will then get 46 records.
See the attached version.

COVID.zip

[Leesha]

Hi Hans,
I don't know what happened but I had to create a new profile to get in. Just wanted to let you know that Leesha16 is me. Any, I should have realized re store 3 as the COVID data is only for 3/4/20 - 3/31/20. Great catch on the two links. However when I deleted the second one I still only get 2 rows of data (214 in the real DB with all of the data). The problem with changing the join to all records from tblCOVIDDaysClosedUnion is that there is a great deal of info missing for the table containing the sales data. I need to be able to see all service dates and net sales. I don't understand why the data is being filtered out when there is a corresponding day in each table.
Leesha

[HansV]

Hi Leesha,

I hope you'll be able to get in again with your original user. If you succeed, I can assign the above reply to Leesha instead of Leesha16.

There are only 2 records in tblCOVIDSalesByDayNumber for a date on which the store was closed: store #122 on 3/22 and store #127 on 3/17. All other records in that table are for dates that the stores were open. Hence only 2 records in your original query.
We could calculate the date from the date number, and set missing sales amounts to 0. See the attached version.

COVID.zip

[Leesha]

I "think" I'm logged in as Leesha again. I reset my password.

I will go back and check the live data to verify that all is showing up.

Thanks Hans!
Leesha