Query and report one-to-many 1

[sblanche]

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.

slb

 

[dhookom]

You can create a crosstab query that set the Row Headings to your tblMain fields, Column heading to DateItem, and Max of [Date] as the value.

BTW: Date is the name of a function and should be avoided as a field name.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[sblanche]

Works Great--Thank you very much