Need help with query

[Guest]

I have 4 tables that I'm querying, and have the basic data that I need, I
just can't figure out how to 'fine tune' to get the exact info into a report.
I have:

Dept Emp# Event Date
001 123 I 01/05/2005
001 123 O 01/15/2005
001 123 R 01/19/2005
001 123 L 02/05/2005
021 789 I 03/11/2005
021 789 O 03/14/2005
021 789 R 03/24/2005
021 789 L 04/03/2005

I need to figure out the date difference between I & O, O & R, R & L, for
each EMP# and put them into 3 columns. Eventually, I would like to end up
with a report that shows this info, and if it needs to be done while setting
up the report, I'm not sure how to do it, but I'm thinking this could be done
within the existing query.

I hope I haven't totally confused anyone...

Thanks in advance for any help!

 

[Guest]

I'm a bit rushed but the answer is simply to use a query... in a query... in
a query. You can use these in queries just like tables.

Use the Criteria (or Where statement) to make individual queries for I, O
and R and L even.

You common relationship is between the Emp#

DateDiff is the function you need, if you can read it on this ugly forum its:

ColumnName: DateDiff("d", startDate, endDate)

That returns days, but type DateDiff and press F1 in help.

Finally, I said I was rushed. Your start date would be Query1[Date] and an
example endDate would be Query2[Date] etc.

 

[Guest]

See "Query Multiple Dates" 5 above your post... I'm feeling lazy, but isn't
my final solution what you're after too....?

You need Query on Query... perhaps a subReport might do it... but I'd build
it in Queries, with a final query bringing them all together on the Emp#

If it isn't clear I'm not doing anything tomorrow!

Cheers