TOP/Group Query despair

[GJT]

Please help - cant seem to get this query right.

Basically, I have two tables : one table containing time stamped log entries for 10 machines, the other table contains staff identifiers. When a user uses that machine, a log entry is filed in the machines table where with the user id as the key to the employee name etc.

During the day upto 'n' users may use any of these 10 machines.

I need a query to return all 10 machines with the most recent user of each machine group. Any ideas pleaze?

 

[Rich]

Use a Totals query, Group by machine and Max on the time field

 

[GJT]

Rich

No-can-do. This table will grow substantially over time. Grouping still returns ALL records in the table - I only want the most recent item for each machine i.e. 10 machines - 10 records/1 per mc . I think this can only be achieved using the 'TOP' predicate - though quite how; I'm not too sure at the moment...........

 

[namliam]

not true... it will only return 1 record per machine.

Top would not work as you would expect. take this
mach1 12:00
mach1 11:59
mach2 12:01
mach3 11:00

Do select top 3 * from table order by time desc:
mach2 12:01
mach1 12:00
mach1 11:59
Will be your wanser which you do not want.

Try something like:

SELECT 
    Machine, 
    dateTime, 
    UserID
FROM 
    myTable INNER JOIN 
        (select 
            machine, 
            Max([dateTime]) as Maxi
        from 
            mytable 
        group by machine) AS Maxes 
    ON (myTable.dateTime = Maxes.Maxi) AND (myTable.machine = Maxes.Machine);

Note: This is Airware...

Regards

 

[GJT]

Rich

Ok - I was wrong - but I have just discovered that it is not just most recent record - it is the two most recent records per machine!

Now do we have to use the TOP predicate and if so how????

Thanks

 

[Rich]

Now it gets more complicated, JonK has posted many examples amongst others, I'd search here for Top

 

[namliam]

Check out attached file

See why you should use top. But try it my way instead.

Regards

 

[GJT]

Mailman

Could you possibly post the link in 97 format please? Some of us are rather behind the times .....!!!!!

I have read some of the other postings here - namely http://www.access-programmers.co.uk/forums/showthread.php?threadid=57085 , but this example seems to have all content in one table. I have around 5 source tables in order to return all necessary information for my groupings.... I have spent a mammoth amount of time on this now - in a nutshell I'm lost!

Help.

 

[namliam]

i will convert it tommorow, but my sample is with 1 table as well. Maybe it will help all of us (seeing that your case seems to be so complex) that you would post a db with the (allmost empty) tables concerned with a little test data. Including something like what you would like to see as an output....

Regards

 

[GJT]

Right - next thing! I have user level security in place on my OS, I have created a new / smaller DB with the tables in question but you will also need the MDW file along with user name/password to access the file. I would rather create a DB file only, any ideas how I can temporarily disable this feature, in order to upload a 'no user/password' copy?

Regards

 

[GUIDO22]

I have posted a copy of a simplified database that contains the bones of the problem I am facing. I would really appreciate your help on this one people - it has me stumped.



For full details of the problem / table relations please read the text in the 'ReadMe' module.

Thank you.

NB. GJT / GUIDO22 same person!

 

[Jon K]

I can find only four tables in your database. Using your Query1, I have added two queries.

Query2 groups Query1 by Machine and Type and Max Date. Query3 retrieves the records from Query1 based on the results of Query2. You can base your report on Query3.


Query2 returns 16 records, but Query3 returns 18 as Machine 4 and 6 have duplicate records with the same time stamps in table ASSY DETAILS.

You will see that instead of returning "Type", Query3 returns the field name "Query1.Type". You can supply an alias for this column if you want to. Date and Type are reserved keywords in Access and should not be used as field names.

 

[GJT]

Jon

With your help - my determination and a lot of tweaking (and hair pulling!), I have eventually got a 5 stage query structure returning the dataset I need!

Thanks for your assistance.

 

[namliam]

5 stage... wow... there must be a way to do it more efficient....

REgards