Query to insert missing rows from another query/table (1 Viewer)

baseballrock17

New member
Local time
Today, 07:28
Joined
Oct 8, 2013
Messages
2
First let me thank you all for the countless time your forum has helped me in the past. I've been unable to crack this one from previous posts... Here's the breakdown:

I am using an Access 2010 DB to keep track of a schedule. Essentially, at least one person needs to be signed up to work for every hour of every day in a week.

Tables:
Days with 7 records
Hours with 24 records
Workers with as many people that sign up to work the different hours
Schedule signifying the worker, day, and hour which are signed up.

As of now i have a query that relates these results and gives me a line detailing the worker/time information for the slots that are signed up for.

What I'm TRYING to do is to create a query that gives me BLANK worker info when there is no one signed up for a particular hour.

Currently my Schedule table has the following:

WorkerID | DayID | HourID
----------+---------+--------
1 | 5 | 12
4 | 5 | 13
16 | 5 | 15


What I'm looking to do is have this table matched up with another table (or query) that provides every combination of day/hour. When an day/hour combination is skipped, the query will be able to "fill in the blank" with a row. Like this:

WorkerID | DayID | HourID
----------+---------+--------
1 | 5 | 12
4 | 5 | 13
| | 14
16 | 5 | 15
 

plog

Banishment Pending
Local time
Today, 09:28
Joined
May 11, 2011
Messages
11,613
Let me drop some jargon to sound like someone who wants to be perceived as smart: To accomplish this you need a LEFT JOIN from a Cartesian Product (http://en.wikipedia.org/wiki/Cartesian_product) sub-query, to your Schedule table.

Essentially, that means you are going to create a datasource with all the possible Day/Hour permutations and then bump your records against it to see which has matches. To do this, first create that sub-query I mentioned using this SQL:

Code:
SELECT  HourID AS ScheduledHours, DayID AS ScheduledDays FROM Days, Hours

Paste that into a new query and save it with the name 'sub_ScheduledHours'. Then open it in design view and you will see it has 2 tables, but they aren't linked--that's a Cartesian Product. You are matching every hour against every Day (the size of this query will be the number of records in Days times the number of records in Hours).

Next create a new query based on sub_ScheduledHours and Schedule. You will bring in all the data from sub_ScheduledHours and just the matching data in Schedule. This will be that SQL:

Code:
SELECT ScheduledHours, ScheduledDays, WorkerID FROM sub_ScheduledHours
LEFT JOIN Schedule ON (Schedule.HourID = sub_ScheduledHours AND Schedule.DayID = sub_ScheduledHours.ScheduledDays

That final query will give you what you want.
 

baseballrock17

New member
Local time
Today, 07:28
Joined
Oct 8, 2013
Messages
2
Plog,
Let me thank you for your insight! You really took a month-long puzzle and simplified it VERY quickly. Thanks so much for your time and input.

I have one quick edit on the second query you provided for the sake of anyone else who's trying to do an LEFT JOIN from the aforementioned "Cartesian Product"

Code:
SELECT ScheduledHours, ScheduledDays, WorkerID FROM sub_ScheduledHours
LEFT JOIN Schedule ON (Schedule.HourID = sub_ScheduledHours[B].ScheduledHours[/B] AND Schedule.DayID = sub_ScheduledHours.ScheduledDays[B])[/B]

Once again... THANK YOU, PLOG!

Once i had the new Schedule query results as you laid them out, I initially had issues with relating the Workers table. But similarly, i did this with additional LEFT JOINs for the WorkerID.
 

Users who are viewing this thread

Top Bottom