Append Query Error

[Charlie]

I have been trying to make an append query in Access 2002. I can do this fine until I run the query, then I get an error messge stating that the query failed to update 10 rows.
I have done a few simple append queries before but I have never come accross this problem.
The data I wish to append is contained in a table named tblProperty_List and the column of data is named W_Rent.
The table I wish the data appended to is named tblRent and the column is called Amount.
The file is located at:
http://homepage.ntlworld.com/charlie_chuck/Rental.zip

I would really appreciate some good advice on this one peeps, I may run into this problem in the future.

Charlie

 

[Dugantrain]

OK, I see why you're getting this error: You're trying to append 10 completely new records into the tblRent table with the only data in each record being the Amount. In order for this to work, you'd also have to append to the TenantID field, as it is a foreign key of the TenantID field in the tblTenants table and any value in this field must match a value in the corresponding tblTenants table. I'm not quite sure exactly what you're trying to accomplish with this Append Query; give us an idea of the task you're trying to perform and we'll be able to help a bit better.

 

[Charlie]

Why

Well the task I am trying to achieve is append the rental sums in one table to the tblRents.
Thanks for the advice.

Charlie

 

[blueshark]

It looks like an update query may be more appropriate. The append query didn't work because it was trying to add rows with only the rent in them. Since tenant_id is your primary key, it cannot be null which is what was causing your error. Below is the SQL of the update query I used on your data.

UPDATE tblRent INNER JOIN (tblProperty_List INNER JOIN tblOccupancy ON tblProperty_List.Property_Id = tblOccupancy.Property_Id) ON tblRent.Tenant_Id = tblOccupancy.Tenant_Id SET tblRent.Amount = [tblProperty_List].[W_Rent];

PS thanks for the download. It makes it a lot easier to see what people are talking about.

 

[Dugantrain]

Also, I'm curious about your table structures. Would you not want a one-to-many relationship between tblTenants and tblRents? That way you could show a tenant's payment history and perform aggregates to show rollover balances, history of delinquent payments, etc. rather than just a snapshot of their most current info.