additional problem with one-to-many and dates

[sblanche]

Previous, I had the problem below listed in the tek-tip thread. dhookom helped by giving me the answer of "Create a crosstab query that set the row headings to your tblMain fields, Column heading to DateItem, and Max of [Date] as value." Now I need to add another column that will calculate the time between date filed and DateComplaint, so the reports will appear as follows:

Case Name DateFiled DateComplaint days to complete
smith v.jones 1/1/05 1/5/05 4
john v. mary 1/5/05 1/15/05 10

Thank you for any help you can provide---

Previous tek-tip question thread 701-1196569

I have 2 main tables. TBLMain has the names of cases (law office). The second table, TBLDates, has all the dates of the cases. A one-to-many relationship. A single case can have many dates.

TBLMain - OCCNumber; CaseName; Case Attorney

TBLDates - Date; DateItem

-Date is the actual date
-DateItem is a "pick list" for the description of what the date is (In, Out, Date Filed, Date Discovery, DateComplaint, etc.)

This is the report that is needed.

Case Name DateFiled DateComplaint
smith v jones 1/1/05 2/2/05
john v. jane 2/4/05 2/9/05


When I do my query I get the following information.

Case Name Date DateItem
Smith v. jones 1/1/05 Date Filed
smith v. jones 2/2/05 Date Complaint
john v. jane 2/4/05 Date Filed
john v. jame 2/9/05 Date Complaint


How do I get the above data to appear in the format for the report.
Any help would be greatly appreciated.

 

[DogLover2006]

sblanche,

I would create a query to do the days to complete filed here is what I would put in the query Day_To_Complete: [Customers]![DateComplaint]-[Customers]![DateFiled]

as far the report goes use the query to create the report

 

[PHV]

When I do my query I get the following information
Could you please post the SQL code of this query ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886