Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    New Jersey
    Posts
    103
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Display current and prior record (Access 2000)

    I have a Position History Table and a Job History Table that lists all records in the database. What I would like to do is create a query that will give me only the most current and the record prior to that one. Does anyone have any ideas on how I can do this?
    <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>

    Thank you in advance!

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Display current and prior record (Access 2000)

    Sort on descending data order and make it a Top values query returning the top 2 values.
    Charlotte

  3. #3
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    New Jersey
    Posts
    103
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: Display current and prior record (Access 2000)

    Hello Charlotte,

    Is this what you mean. I've never used this syntax before but here it goes...

    SELECT dbo_veqPersonBasePay.PersonBasePayStartDate, dbo_vPerson.LastName, dbo_vPerson.FirstName, dbo_veqPersonBasePay.PersonBasePayEndDate, dbo_veqPersonBasePay.PersonBasePayAmount
    FROM dbo_veqPersonBasePay INNER JOIN dbo_vPerson ON dbo_veqPersonBasePay.PersonGUID = dbo_vPerson.PersonGUID
    WHERE (((dbo_veqPersonBasePay.PersonBasePayStartDate) In (select top 2 PersonBasePayStartDate from dbo_veqPersonBasePay)))
    ORDER BY dbo_veqPersonBasePay.PersonBasePayStartDate DESC;

    It doesn't seem to work with an inner join. What can be the problem?

    Thanks!

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Display current and prior record (Access 2000)

    No, that isn't what I meant because I didn't quite understand what you were asking. It was not self-evident that the "query that will give me only the most current and the record prior to that one" was intended to return more than 2 records overall. Why don't you explain in more detail what you're trying to accomplish, and then perhaps someone can help.
    Charlotte

  5. #5
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    New Jersey
    Posts
    103
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: Display current and prior record (Access 2000)

    (Edited by HansV to put data in tables.)

    I apologize for the confusion. I'll try to explain it using a few examples. I have a table "PositionHist" which looks like this:

    <table border=1><td>Emp</td><td>Position</td><td>Position Desc</td><td>Position Date</td><td align=right>1234</td><td align=right>88888</td><td>Administrator</td><td align=right>01/10/2002</td><td align=right>1234</td><td align=right>11113</td><td>Assistant</td><td align=right>03/05/2000</td></table>
    Then, I have a Salary table which has:

    <table border=1><td>Emp</td><td>Salary</td><td>Salary Date</td><td align=right>1234</td><td align=right>30,000</td><td align=right>03/01/2002</td><td align=right>1234</td><td align=right>20,000</td><td align=right>01/01/2000</td></table>
    When I link the two tables I get a list of 4 records for each employee. What I would like is to show it as follows:

    <table border=1><td>Emp</td><td>Position</td><td>Position Desc</td><td>Position Date</td><td>Salary</td><td>Salary Date</td><td align=right>1234</td><td align=right>88888</td><td>Administrator</td><td align=right>01/10/2002</td><td align=right>30,000</td><td align=right>03/01/2002</td><td align=right>1234</td><td align=right>11113</td><td>Assistant</td><td align=right>03/05/2000</td><td align=right>20,000</td><td align=right>01/01/2000</td></table>
    I can do it easily for the current record by using topn but I can't get the prior record.

    HELP <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Display current and prior record (Access 2000)

    In sofar as I understand your question, it is completely different from what you asked earlier. Try this SQL:

    SELECT tblPosition.*, tblSalary.Salary, tblSalary.[Salary Date]
    FROM tblPosition INNER JOIN tblSalary ON tblPosition.Emp = tblSalary.Emp
    WHERE tblSalary.[Salary Date]=(SELECT Max([Salary Date]) FROM tblSalary WHERE [Salary Date] <= tblPosition.[Position Date]);

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •