Table Relationships (1 Viewer)

Hello All

I have a question. I currently am using Access 2007.

Basically I have a excel report I receive from another dept with incomplete data. I import the excel to a table I call Lease.

The Lease table has 3 fields,
ID (Auto-Number)
Equipment (Hardware Description)
SerialNum (serial number of hardware)
Rent (cost of rent).

I have another table called INV, which is manually updated
it has the following fields:
ID (Auto-number)
Location (Location of Hardware)
Type (Hardware Type)
Ser_num (serial number of hardware)
Lease (This has a one to many relationship with Lease via the ID field)

The excel file from which lease is imported from is always missing some data, either Hardware Descriptions or Serial Numbers. I created a one to many relationship between the Lease table and INV.

The problem I am having is connecting the two tables via the serial numbers. I created a query called Match, which does this for me but it will not allow me to edit my data. Because Lease is imported from excel and sometimes missing data the two tables wont match up unless I do a query, but I can not edit the query because access is having problems with my relationships.

Is there a better way to create a relationship in order for me to build a query which will allow me edit data was needed? I have included my Database with some data. Any help will be appreciated.

In your description, you said that the relationship is between Lease and the ID of the Lease table.

When you join two tables on random data fields (serial number to serial number in this case), the query is not updateable because the database engine thinks the result will be a Cartesian Product.

If the relationship should be on Serial number then get rid of the ID field in the Lease table and make SerialNumber the PK. You will then be able to join the two tables and obtain an updateable query.