Query Question

[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]

Onrey,
I hope you get a more detailed response than mine but I wanted to give you
something to start with that is simple. Use your query design wizard to
create a crosstab query. Follow the prompts but use the fields you
described. The I, O, and R should be column headings; EmployeeID and Dept,
the rows; and the dates should be the data. From there, you can tweak the
calculation required. You may want to start with DateDiff(). I am not a
real sophisticated user someone else may have a better suggestion.

Your crosstab qry results should be in the following column format
Dept EmployeeID I-Date O-Date R-Date L-Date

Go back to query design view and in 3 separate empty columns type:
NameofIDatecol1: DateDiff("d", [I-Date], [O-Date])
NameofDatecol2: DateDiff("d", [O-Date], [R-Date])
NameofDatecol3: DateDiff("d", [R-Date], [L-Date])

I hope this helps.
Capt OhNo