Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2003
    Posts
    45

    Unhappy Unanswered: Need help wit parameter query

    Hi everyone,
    I have a problem that I can't solve so ...
    PLEASE HELP ME :-)

    I have made parameter query that shows data about certain employees, but it's a kind of archive - it contains multiple records for every employee - for example parameters of PC that every person had in certain time (this is just an example, becouse it would take much time to explain my real database):

    Employee ID Date CPU MB
    100 01.01.02 Pentium MMX 200mh ... ...
    100 15.09.02 Pentium II ... ...
    100 26.03.03 Athlon 1700 ... ...
    101 20.02.02 ... ... ...
    101 14.11.02 ... ... ...
    102 ... ... ... ...

    I need to make that query show only one record for every person - the last record before certain date (in this example - the PC that employees had in certain moment - 01.01.03).

    To be honest I don't have any ideas how to do this, so if you know - PLEASE TELL ME!

    THANK YOU!!!

  2. #2
    Join Date
    Aug 2003
    Location
    Montreal, Québec
    Posts
    10
    i think this is, what you need...
    i type it in sql cauz it easier to me


    SELECT Last(TABLE.IDEMPLOYEE) AS LASTID, Last(TABLE.DATE) AS LASTDATE, TABLE.CpuSystem
    FROM TABLE
    GROUP BY TABLE.CpuSystem
    HAVING (((Last(TABLE.DATE))<="03.01.01"))
    ORDER BY Last(TABLE.IDEMPLOYEE), Last(TABLE.DATE);


    Okay for this exemple i just have one table... but remplace "TABLE" by your table name....
    Or save your query... and make another one ( this one ) based on Your first query

    I hope this will help you...
    Gigs

  3. #3
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    This will give you the last upgrade for each employee ID:

    SELECT table1.[Employee ID], table1.Upgrade_Date, table1.MB, table1.CPU
    FROM (SELECT Table1.[Employee ID], Max(Table1.Upgrade_Date) AS UpgDate
    FROM Table1
    GROUP BY Table1.[Employee ID]) MaxDate INNER JOIN table1 ON (MaxDate.UpgDate = table1.Upgrade_Date) AND (MaxDate.[Employee ID] = table1.[Employee ID]);

    It uses a nested query to get the max date for each employee,
    then pulls the matching record from the base table.

    Mark
    Inspiration Through Fermentation

  4. #4
    Join Date
    Jun 2003
    Posts
    45

    Smile

    THANK YOU BOTH GUYS!

    It all worked FANTASTIC!

    I'm just a WYSIWYG designer, but I guess that I should read a few SQL books if I want to write more complicated queries like that one (I guess that one is izzy enough for you ;-)

    Thanks again!

Posting Permissions

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