CODEWITHSUNDEEP
sqlms-access
Patrick Beardmore
Patrick Beardmore

Reputation: 1032

Query in Access (adding missing values)

I've got two tables, Guides and Availability. Guides is a list of guides, and Availability is a list of dates for which information is known about a particular guide. For instance 03/03/14 - Guide 3 - Available or 05/03/14 - Guide 1 - Busy. If a guide's availability on a particular day isn't know yet, there isn't an entry in Availability at all for that day.

I want to write a query that will return a list of the statuses of all the guides during the next three days. If the status isn't known it will say Unknown.

I'm sure in SQL this task is definitely doable, but I am constrained by having to write a Web App in Access 2013. This means I must use the query editor in Access. Can anyone with expertise offer some guidance? If anything is unclear in the question, do say so.

Example query output:
03/03/2013 - Guide 1 - Busy
03/03/2013 - Guide 2 - Unknown
03/03/2013 - Guide 3 - Unknown
04/03/2013 - Guide 1 - Available
04/03/2013 - Guide 2 - Available
04/03/2013 - Guide 3 - Unknown
05/03/2013 - Guide 1 - Unknown
05/03/2013 - Guide 2 - Unknown
05/03/2013 - Guide 3 - Unknown

Table: Guides
Guide 1
Guide 2
Guide 3

Table: Availability
03/03/2013 - Guide 1 - Busy
04/03/2013 - Guide 1 - Available
04/03/2013 - Guide 2 - Available

P.S.: Someone has suggested I might need to make a third table listing all the dates for the next 10 years. This wouldn't be a problem if it helps with a solution

Upvotes: 1

Views: 1636

Answers (1)

cha
cha

Reputation: 10411

If you are limited to Access, you won't be able to use recursive CTE's (google it to find out about them, if interested).

therefore, you would have to create a table with all dates. Provided the table is called AllDates, and the date field is Dte, the query will look like this (you may need to filter by date range to limit the number of returned rows, otherwise you will see the records for all 10 years):

SELECT Availability.Dte, Availability.Guide, Availability.Status
FROM Availability INNER JOIN Guides ON Availability.Guide = Guides.Guide
UNION ALL
SELECT  AllDates.Dte, Guides.Guide, 'Unknown' AS Status
FROM AllDates, Guides
WHERE NOT EXISTS (SELECT 1 FROM Availability WHERE Availability.Dte = AllDates.Dte And Availability.Guide = Guides.Guide)
ORDER BY 1, 2;

For the web-version of Access you will need to use 2 queries. The first one (DatesGuides) will list all guides for all dates, and its syntax is as simple as:

SELECT Guides.Guide, AllDates.Dte
FROM AllDates, Guides;

The second query will be LEFT JOINED with the first one. Its syntax is:

SELECT DatesGuides.Dte, DatesGuides.Guide, Nz([Status],'Unknown') AS Expr1
FROM Availability RIGHT JOIN DatesGuides ON Availability.Dte = DatesGuides.Dte AND Availability.Guide = DatesGuides.Guide;

Upvotes: 1

Related Questions