Disappearing relationship

[officeguru]

Hello,
I am trying to create a report with the report wizard based on two related tables. Access (97) tells me I am trying to include records from two unrelated tables.

[tt] You have chosen fields from these tables: Service Parts, BOM Rejections; one or more of the tables isn't related to the others. Click OK to edit system relationships (you'll need to restart the wizard). Click Cancel to return to the wizard and remove some fields.[/tt][/color green]

The relationship window shows the relationship. Any Clues?

I tried the Repair Database option which changed nothing, and I used the Analyze tool which gave me the curious reccomendation that my main table should be related to itself.

 

[BNPMike]

Relationships dop not connect tables. They seem to confuse a lot of people. You need to make a query with the two tables. Access will then note your relationships and volunteer a join to connect the tables. It is this join that draws the data together, not anything you put in the relationships screen.

 

[lespaul]

You're right, relationship do not connect tables, but what they CAN DO is enforce referential integrity between the two tables. If you have referential integrity enforced between two fields in the table then you CANNOT enter invalid data. Yes there are other ways to eliminate invalid data, but most Access users aren't aware of the techniques or they aren't advanced enough. Access also transfers those relationships to queries to make query building easier for the basic users.

Now, are you basing this report on the tables or on a query based on those tables? If it's a query, then you need to make sure that in the query design view that the relationship exists within the QUERY.

HTH

Leslie

 

[BNPMike]

It is an interesting question as to why Access does not do more with relationships. If you create a form with a subform the subform wizard will automatically connect the forms if a relationship is declared, so why not when officeguru puts his two tables on a report?

One reason is Access can't always predict what you want to do. You can have more than one relationship betwen two tables eg Department[DeptNo,DeptName] and Employee[EmpId,HomeDept,WorkDept]. Here there are two connections between employee and department. Access wouldn't know which one you were using if you did a report EmpId, by Department. This is because relational databases don't have a naming method for relationships (unlike eg IDMS). You have to specify to SQL which connection (join) you are using at run-time. Having said that it is not obvious why Access doesn't guess when there is only one relatonship, which must be the vast majority of the time. On other occasions it could raise an error message "Ambiguous join due to multiple relationships"