Solved

query - access- sql

Posted on 2014-04-28
1
248 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
MS Access 2003 or later To MySQL Migration Project Hello All, this is my second article in the category of MS-OFFICE Automation. In internet I am not able to find any comprehensive resource on the Migration of MS Access back-end to MySQL so I fin…
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…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

863 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

24 Experts available now in Live!

Get 1:1 Help Now