SQL query statement needed

Posted on 2011-04-23
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
    LVL 44

    Expert Comment

    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

    I need to use the results from query 1 to pull all matching data from the History table.
    LVL 30

    Expert Comment

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

    Author Comment

    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

    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

    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!


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    This article describes some very basic things about SQL Server filegroups.
    Read about achieving the basic levels of HRIS security in the workplace.
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now