Help with complex sorting (1 Viewer)

[David_P]

I am need help with a sort query that seems to be complex and I can't seem to get the query to sort in the certain order I need. I am not sure how to go about it.

I have 6 fields that I am using as to sort with. We will call them Fields A thru F. Field A=Job, B=Employee, C=IN/OUT status, D=Date, E=time, F=AM/PM status.

I can get the query to sort by A, B & D fields. But I can't get it to pair the IN/OUTs together. For example Jane logs into a job (Sample) @ 8 am on 8/8/10 and works on it until 1 PM. Jim logs into the same job (Sample) @ 10:15 8/8/10 am and works on until 12:30.
I want the query to look like this:
Sample Jane OUT 8/8/10 1:00 pm
Sample Jane IN 8/8/10 8:00 am
Sample Jim OUT 8/8/10 12:30 pm
Sample Jim IN 8/8/10 10:15 am

I will be using this information in a report later on so don't know if I should run it in a query or a report or even put it in a table to use for the report. I just don't want to have to run the query on all the data every time I need it for a report.

Thanks for you help

David

 

[vbaInet]

You could:

Group By Job, then Employee

Followed by the Sorts:

Job (ASC), Employee (ASC), In/Out (DESC), Date (DESC), Time (DESC)

Why do you have AM/PM as a separate field?

 

[David_P]

When I try to only group Job and Employee, it won't allow me to just group only those two. I wants each column to have a choose, what do I use in the other colums?

When I do sort it, it groups the Ins together and the outs together.

The date and time is actually from 1 field that I split into the date and time columns and the time part of that field isn't in military format but either am or pm.

I am using Access 2010 if that helps.

 

[vbaInet]

That's correct. That was just to tell you the sequence of Grouping. Just ensure that you have those two grouped first (in that sequence) followed by the others.

 

[David_P]

I have search the web but can find a solution to the problem of converting the time to a 24hr format so I can get rid of the am/pm field. It would also make grouping my ins and outs better I think. It probable is just a command but can't seem to figure out proper format.

 

[vbaInet]

You could probably use:

Format([FieldName], "Short Time")

 

[David_P]

Ok, so far so good on creating my report. I got the report to group the Job and person together. I got it to display the time in military time so it should be easier to do calcumation on it. Now comes the more complex part. I need it to find the difference between the in time and the out time.

Sample Jane OUT 8/8/10 13:00
Sample Jane IN 8/8/10 8:00
5 hrs

Sample Jim OUT 8/8/10 12:30
Sample Jim IN 8/8/10 10:15
2.25 hrs

Sample2 John OUT 8/8/10 16:15
Sample2 John IN 8/8/10 14:45
1:30 hrs

So is there a way to group the ins and outs together? and then calculate the time difference?

Thanks for all your help.

 

[vbaInet]

If you want to group the INs and OUTs then you can use GROUPINGS & ORDERINGS that comes with reports. Look into that.

As for calculating the date difference it's been covered on here so you would need to do some (advanced) searching to find it.