Solved

query - access- sql

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Creating a Temp Table in MS Access 5 47
VBA Delete sql "Too Few Parameters" 8 24
access to sql migration 5 24
Sort order not as I expected 13 21
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
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…

726 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