We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

SQL query statement needed

mhening
mhening asked
on
Medium Priority
378 Views
Last Modified: 2012-05-11
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);


Comment
Watch Question

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

Author

Commented:
I need to use the results from query 1 to pull all matching data from the History table.
Hamed NasrRetired IT Professional
CERTIFIED EXPERT

Commented:
Attach a sample database, with sample data and expected output.

Author

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
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

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!
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.