Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-01-2005, 05:21 PM   #1
mtbchef
Guest
 
Posts: n/a
Joins with Derived tables?

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

  Reply With Quote
Old 01-02-2005, 08:01 PM   #2
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 25,630
Thanks: 12
Thanked 1,017 Times in 974 Posts
Pat Hartman is just really nice Pat Hartman is just really nice Pat Hartman is just really nice Pat Hartman is just really nice Pat Hartman is just really nice
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.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 01-03-2005, 11:44 PM   #3
mtbchef
Guest
 
Posts: n/a
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 :-)


Last edited by mtbchef; 01-04-2005 at 12:00 AM.
  Reply With Quote
Old 01-04-2005, 01:32 PM   #4
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 25,630
Thanks: 12
Thanked 1,017 Times in 974 Posts
Pat Hartman is just really nice Pat Hartman is just really nice Pat Hartman is just really nice Pat Hartman is just really nice Pat Hartman is just really nice
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.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 01-05-2005, 09:02 PM   #5
mtbchef
Guest
 
Posts: n/a
Hi Pat:

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

  Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump




All times are GMT -8. The time now is 12:12 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
(c) copyright 2010 Access World