?
Solved

query - access- sql

Posted on 2014-04-28
1
Medium Priority
?
256 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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

762 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