There is NO way to do this!

[Freehal04]

This is ridiculous. I've tried so many different ways, except the right way.
But here goes. 4 tables. 1st one, Contracts, PK is ContractID, 2nd table,
TaskOrders, PK is TOID, 3rd table, Reports, PK is ReportsID, 4th table,
ReportDetails, PK is DetailsID. Each PK is a FK in the table that follows
it. I have a 1 to many relationship with all of them, but the last one.
Access makes it a one to one. Why?

I'm sure that this relationship affects my form w/subforms I have created.
I wanted it to show the Contract number and ID, then in a subform show
TaskOrder number and ID, then in the third subform show Reports, with Report
Number and ID and all the details. Well the first two work great, but when
it gets to the Reports and the details, bam it goes flat.

Contract NX1 has two Task Orders, 0001 and 0002. I wanted the Report
subform to show all the Reports associated with Task Order 0001 when the Task
Order subform was focused. Then when 0002 was focused it would show all the
Reports associated with 0002. Well I can't get it to do that. I can type in
the data, and the tables save all the info the right way, but the forms
REFUSE to show it after I type it in. Doesn't matter what I do with the
joins either.

What am I missing? Here is how I envisioned it:

Contract Number NX1
(subform)Task Order 0001
(subform) Reports/Details A001, detail, detail, detail

Then if you focused on Task Order 0002 on the task order subform, it would
show all the reports for TO 0002:

Contract Number NX1
(subform)Task Order 0002
(subform)Reports/Details A001, detail, detail, detail


Is this possible or do I need to rethink it? This is driving me nuts.
Thanks.

 

[tina]

start with the relationships. when Access creates a one-to-one relationship,
it is because the join field(s) in each table has a unique index - see Field
Properties in table Design view, Indexed property set to Yes (No
Duplicates). the *foreign* key field in ReportDetails needs to have the
Indexed Property set to Yes (Duplicates OK).

try fixing the relationship between Reports and ReportDetails first, then
post back if you continue to have problems with the form/subforms setup.

hth

 

[John W. Vinson]

Access makes it a one to one. Why?

Because you have a unique Index on the foreign key field in the Details table.

John W. Vinson [MVP]

 

[Freehal04]

Thanks, now it works perfectly. That one was really kicking my butt.
Appreciate the help.