Relationships and Queries

[spn200286]

When i attempt to create a query between two perticular tables (Bookings/Extras and Bookings) a iget an error message about Type Mismatch, all field types are the same though .

On relationships the Bookings Bookings/Extra relationship is a Intermediate relationship, could this be what is causing the problems?, i am atempting to link two primary keys (BookingID) together could this be the problem, any help would be apreciated.

I am using Access XP (2002)

Chears

... Scott...

 

[mresann]

If both datatypes are the same, you should have no problem. Do this:

• Create a new db
• Copy both tables. During copy, select "Structure Only" opton to prevent data from being copied
• Past tables into new db
• Zip Db and post it in this thread

I suspect the problem is more to do with indexing than with datatype.

 

[mwn1218]

You should not be linking two Promary Keys together. It should look like this

tblBooking
BookingID (PK)

tblBookingExtra
BookingExtraID (PK)
BookingID (FK)[/I

BookingID should be a foriegn key in table tblBookingExtra

 

[spn200286]

Thanks for the help, ill do the above points and post my results,


Chears

...Scott...

 

[spn200286]

While im here could somebody PLEASE tell me what an Intermediate relationship is

 

[spn200286]

You should not be linking two Promary Keys together. It should look like this

tblBooking
BookingID (PK)

tblBookingExtra
BookingExtraID (PK)
BookingID (FK)[/I

BookingID should be a foriegn key in table tblBookingExtra

I did what was sugested and i amstill getting the errors, any help you can give would be apreciated

I will post a screen shot of the tables/relationships adn could this help you tell me why my queroes are not working

 

[mwn1218]

That would be helpful.

 

[Pat Hartman]

While im here could somebody PLEASE tell me what an Intermediate (sic) relationship is

A relationship is Indeterminate if Jet cannot determine its cardinality. This usually happens when the "1-side" table does not have a primary key.

When you join two tables on their primary keys, you should end up with a 1-1 relationship. If you use an autonumber for the main table, the pk of the related table must be defined as Long Integer. Also make sure that the main table ends up as the "parent" in any relationships so that you can set the enforce RI and cascade delete options.

If the relationship is 1-1, it should be:

tblBooking
BookingID (PK)

tblBookingExtra
BookingID (PK)(FK)

If the relationship is 1-many, it should be:

tblBooking
BookingID (PK)

tblBookingExtra
BookingExtraID (PK)
BookingID (FK)