SQL query statement needed

Posted on 2011-04-23
Medium Priority
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);

Question by:mhening
  • 3
  • 2
LVL 44

Expert Comment

ID: 35456285
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


Author Comment

ID: 35456491
I need to use the results from query 1 to pull all matching data from the History table.
LVL 31

Expert Comment

ID: 35457189
Attach a sample database, with sample data and expected output.
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.


Author Comment

ID: 35457357
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.
LVL 44

Accepted Solution

Arthur_Wood earned 2000 total points
ID: 35457572
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


Author Comment

ID: 35457819
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!


Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question