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);
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);
ASKER
I need to use the results from query 1 to pull all matching data from the History table.
Attach a sample database, with sample data and expected output.
ASKER
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
DOOR.mdb
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
Thanks!
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