Joining Tables Based on Date

[kestefon]

I have a number of tables with different metrics. However, they all have a "Date" field in common, broken out at the daily level. Since some of my tables have different start and end dates, I'd like missing values to simply appear as "N/A". Any idea how I would do this with SQL?



I've attached a screenshot to give you an idea of the layout I'm going for. Each metric field (i.e. "Metric1", etc.) would be pulled in from a different table.

[June7]

You need a master dataset of all possible date values. This dataset can be created with a UNION of the date fields from all the metrics tables.

Then each metrics table can be joined to the master. Join type "Include all records from {master dataset name} and only those from {metric table name} that match"

However, if a date is not in any of the metrics tables, there will be no record for that date in the final query.

[kestefon]

Thanks, that works!

Quick syntax question: How do I left join to multiple tables? The code below correctly joins my "AllDates" query to the first dataset (UniqueVisitors), but I need to include fields from a number of other tables as well. For example, let's say I want to add Metric2 from Table2, and Metric3 from Table3.

SELECT dt.mDate, uv.DailyUniqueVisitors
FROM AllDates AS dt LEFT JOIN UniqueVisitors as uv
ON dt.mDate = uv.Date;

[June7]

Use the Access query builder to construct the SQL. Just add tables to the window and link on the key fields. Then if you want SQL in VBA procedure, copy/paste and make appropriate edits.

[kestefon]

Hi June7,

Is it possible to do a UNION query using the Access query builder? I need to find all of the dates across about 20 tables...

[June7]

UNION must be typed or copy/paste in SQL View window of query builder. Limit of 50 SELECT lines.