[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

query - access- sql

Posted on 2014-04-28
1
Medium Priority
?
259 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 48

Accepted Solution

by:
Dale Fye earned 2000 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

What’s Wrong with Your Cloud Strategy ?

Even as many CIOs are embracing a cloud-first strategy, the reality is that moving to the cloud is a lengthy process and the end-state is likely to be a blend of multiple clouds—public and private. Learn why multicloud solutions matter in this webinar by Nimble Storage.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

650 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