Re: Query To Find Records As Of Specified Date

From: Allen Browne (AllenBrowne_at_SeeSig.Invalid)
Date: 10/29/04 

Date: Fri, 29 Oct 2004 09:16:24 +0800

1. Create a query using both tables.

2. In the Field row, enter:
        [Last Day Of Month]

3. In the Criteria row under this field, enter:
        Between [tblEmployment].[DateHired] And
[tblEmployment].[DateLaidOff]

4. Choose Parameters on the Query menu. In the dialog enter:
        [Last Day Of Month] Date/Time

When you run the query, the user must enter a date such as 7/31/2004. The
query returns only those who were hired as of that date.

You should not get duplicates unless the person has two overlapping
employment records. 

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Sue" <smillin@notmyemail.com> wrote in message
news:i3ggd.10983$5i5.10229@newsread2.news.atl.earthlink.net...
> I'm working on a database that keeps track of employees hired by a general
> contractor on a project by project basis. These employees are hired to 
> work
> on a project and are then laid off either at the end of the work they were
> hired to do or at the end of the project. Any employee may be hired, laid
> off and rehired several times during the course of time. The tables I have
> and the critical fields to my question are:
> TblEmployee
> EmployeeID
> FirstName
> LastName
>
> TblEmployment
> EmploymentID
> EmployeeID
> DateHired
> DateLaidOff
>
> Any employee may have several records in TblEmployment. At the end of each
> month a report is needed showing all the employees on the payroll on the
> last day of the month. I need help creating the query for that report. The
> user must be able to specify the month and year for the report; ie, not 
> just
> the current or previous month but any year and month in the past. The 
> query
> must take into account that the user might specify a month in the previous
> year and that an employee might have been hired and laid off several times
> after that month.
>
> Thanks for all help!
>
> Sue

Relevant Pages

  • Re: Query To Find Records As Of Specified Date
    ... In the Field row, ... Choose Parameters on the Query menu. ... These employees are hired to ... I need help creating the query for that report. ...
    (microsoft.public.access.formscoding)
  • Re: Query To Find Records As Of Specified Date
    ... In the Field row, ... Choose Parameters on the Query menu. ... These employees are hired to ... I need help creating the query for that report. ...
    (microsoft.public.access.queries)
  • Re: OT: SQL
    ... query processing. ... FROM Employees e, Employees m, Management mgt ... Manager and Employee Salaries. ... The scheme used does not model database files in general, ...
    (sci.logic)
  • Re: Conditional Printing
    ... Consider posting the SQL of that "first" query. ... The report sums the hours for the weeks on this payroll. ... information but for ONLY those employees who submit more than 80 hours ...
    (microsoft.public.access.reports)
  • Re: OT: SQL
    ... said was a totally failed attempt to formalize and automate database ... query processing. ... create table Employees ... Manager and Employee Salaries. ...
    (sci.logic)