query setup

[verdes]

I'm using access 2007

I have 3 tables: entry authorization - roe_tbl, contact -contact_tbl, and address - addr_tbl.

the roe_tbl has ID, addrID, contactID and a few other fields.

the addr_tbl has ID and the streetno, streetname, addrtype.
addrtype is CNT for contact or ROE for authorization

the contact_tbl has ID, contact name, and addrID.

For each roe_tbl record there is 1 addr_tbl record with type="ROE" and 1 contact_tbl record. For each contact_tbl record there is 1 addr_tbl record with type="CNT"

How do I make a query to get the 2nd reference of the addr_tbl for the contact_tbl (the "CNT" address type record)? The sql for the query I have so far includes everything except the address info for the contact table. Currently it looks like this:

SELECT roe_tbl.*, address_tbl.*, contact_tbl.*
FROM contact_tbl RIGHT JOIN (address_tbl RIGHT JOIN roe_tbl ON address_tbl.addressID = roe_tbl.roeAddrID) ON contact_tbl.contactID = roe_tbl.roeContactID;

 

[plog]

Can you post sample data from your tables along with what you expect the query to return based on that sample data?

 

[jdraw]

Can you give us a 5 line overview of the purpose of your database and WHAT you are trying to do in plain English? My guess is you will get specific advice/comments once readers have some idea of what you're trying to do.

 

[verdes]

I am tracking work done at particular locations. Sometimes the locations are privately owned residences or buildings or stuctures. I have service requests for employees to do work at the property address of these locations.

They can't do the work until we receive a paper form (roe) that grants the employee the right to enter a person's property. The property address is listed on the form and the contact person's address, which is different from the property address most of the time.

Data example: I have a right to enter form for property located at 219 Northern Street owned by Thomas Jefferson at 2432 Redwood Ave.

roe_tbl:
ID
addrID (fk to addr_tbl)
date
contactID (fk to contact_tbl)

addr_tbl:
ID
street_no
street_prefix
street_name
street_suffix
property_desc
addr_type ("CNT" or "ROE")

contact_tbl
ID
first_name
last_name
company_name
addrID (fk to addr_tbl)

Hope this helps.

 

[jdraw]

Seems there may be more pieces to this

Employees
Service/Work Requests (with status)
-WorkScheduled but awaiting ROE
-WorkUnderway
-WorkCompleted
Locations
Contacts
ROE

There are some service related models at www.databaseanswers.org

Here's one that may help with structures.
http://www.databaseanswers.org/data_models/service_call_tracking/index.htm