SQL query statement needed

I need an SQL statement to do the following (im using access 2003):

Use the results of query1 and run another query using the history table to get results of all the room, core, and recore date history.
Example of the results I need:
Core, room, recore date
AB5, 101, 08/25/2010
AB5, 117, 06/23/2009


Glossary:  Key /core are truly interchangeable.  A key is what you stick into the cylinder(core) to open the door.  

Three tables

1.      Rooms{Room, key, date} –lists room and current core in use and the date it was installed. (no duplicates)
2.      Core{coreName}-- a list of all cores installed and not installed (no dups)
3.      History{ID , Roomf, Keyf, recoreDatef} – dups ok in all but ID field.  

Query1—selects all cores not in use at the present time.
SELECT Corename

FROM Core AS c

WHERE not exists

 (select *

FROM Rooms

where key=c.corename);


mheningAsked:
Who is Participating?
 
Arthur_WoodConnect With a Mentor Commented:
sorry , I missed the History part:


Try this:

Select  Keyf as Core, Roomf, Datef as [Recore Date] from History where Keyf in (Select Corename from [View Unassigned Cores]) Order by  History.Keyf, History.Datef Desc

AW
0
 
Arthur_WoodCommented:
try this:

Select  Rooms.Key as Core, Rooms.Room, Rooms.[Date] as [Recore Date] from Rooms where Key in (Select Corename from query1) Order by  Rooms.Key, Rooms.[Date] Desc

AW
0
 
mheningAuthor Commented:
I need to use the results from query 1 to pull all matching data from the History table.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
hnasrCommented:
Attach a sample database, with sample data and expected output.
0
 
mheningAuthor Commented:
Query 1 is named View unassigned cores. I need a statement that will give me the past history of unassigned cores.  Hope this makes better sense.
DOOR.mdb
0
 
mheningAuthor Commented:
Great, This works! I think.  Should I have more records when running the query?  I think I should 111 records.  But now, this is dummy data and I only have 138 records in History.  Yes it works!

Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.