Joins with Derived tables?

[mtbchef]

Hi All:

I have good T-SQL2K experience, but a bit lacking in Access's query language.

Does Access support JOIN-ing on derived tables (as in T-SQL2K)?

Any examples or recommendations on where to learn the syntax is greatly appreciated. Thanks in advance.
ciao,
mtbchef

 

[Pat Hartman]

I'm not sure what a derived table is but Access uses tables and queries interchangably for most purposes. So you can join tables, queries, or a table to a query. Almost any place you would use a table, you can use a query. In fact in several places, queries are preferred because they give you more control. RecordSources for forms/reports should be queries with select clauses to limit the number of records selected as should RowSources for combo and listboxes.

 

[mtbchef]

Hi Pat:
Thanks for the reply. In SQL, using a subquery as a derived table is a great solution to addressing the problem of aggregate functions. When building an aggregate query, every field (column) must participate in the aggregate function in some way (either as a GROUP BY column, or an aggregate function, SUM(), COUNT(), etc.). This makes returning additional information tricky.

The fix is to perform the aggregate function(s) in a subquery and pass the rows returned (as a derived table), to the outer query. The outer query can then return any fields desired.

For example, two tables in a database, one called Contact and one called ContactHistory. Contact contains all the static information about a person: ContactID, InsertDate, First Name, Last Name, Gender, Date of Birth. ContactHistory contains ContactID (foreign key to Contact table), InsertDate, Address, City, State, Zip, Phone, etc. There are many rows in ContactHistory for each row in Contact, as one person may have lived at many different addresses over time.

To return a person's name, gender, date of birth, and CURRENT address, city, state, etc only; use the MAX(InsertDate) in a subquery as a derived table, join this to the outer query. The outer query SELECTs the additional fields necessary.

The subquery to give the derived table:
SELECT Contact.ContactID, MAX(Contact.InsertDate) AS InsertDate FROM Contact GROUP BY Contact.ContactID

The whole query to return the current address for each contact in Contact table:
SELECT
c.NameFirst
, c.NameLast
, c.Gender
, c.DOB
, c.InsertDate
, ContactHistory.Address
, ContactHistory.City
, ContactHistory.State
FROM
ContactHistory
JOIN Racers c ON c.ContactID = ContactHistory.ContactID
JOIN ( --here's the subquery
SELECT h.ContactID, MAX(h.InsertDate) AS InsertDate FROM ContactHistory h GROUP BY h.ContactID
) AS DERIVED ON DERIVED.InsertDate = ContactHistory.InsertDate

Does MS Access's query engine support this derived table concept? If so, can you point out where I can learn the syntax?

Thanks in advance for your time and attention.
Keith

 

[Pat Hartman]

No it doesn't. You need to use nested queries but they won't solve the problem of trying to update a table with aggregated values. To do that, you need to create a temporary table yourself.

 

[mtbchef]

Hi Pat:

Thanks, I'll give that a shot this evening...
Keith