Solved

query - access- sql

Posted on 2014-04-28
1
253 Views
Last Modified: 2014-04-29
ID   Room           Name               Date_Started  

1      200              paul                 10/2/2012              
2      200              mathew           9/2/2013              
3      200              george             12/13/2013            
4      100              scott                1/1/2012            
5      100              sam                 10/13/2013              
6      300              jino                  1/1/2012            


Hi Experts, I have to find the all records as of a date from the above table data, for example if i generate a report for the date 9/3/2013 I need to get the records mathew(200) scott(100) and Jino(300). Here we can consider the last date of a person worked in a particular room as the beginning date of the new person came in- if not the current date. Is this a good table structure to satisfy those kind of reports. Please kindly help.
Thanks
0
Comment
Question by:gtmathewDallas
1 Comment
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 40028548
To get the data for the most recent record for each room, prior to a particular date, your best bet would be a query like:

SELECT yourTable.*
FROM yourTable
INNER JOIN (
SELECT Room, Max(Date_Started) as MaxDate
FROM yourTable
WHERE [Date_Started] < #9/3/13#
GROUP BY Room
) as MostRecentRoomAsmt
On yourTable.Room = MostRecentRoomAsmt.Room
AND yourTable.[Date_Started] = MostRecentRoomAsmt.MaxDate
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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

The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

840 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