Append Query Error

[Charlie2]

Im trying to make an append query to add the weekly rent amouts from one table called tblProperty_List to another field called Amounts in another table named tblRent.
I made the query thinking oh this is simple enough, but upon running the query I received an error message stating the records could not be copied due to key violations bla bla bla.
The field values are both set to currency, I cant see what I am doing wrong.
Would someone take a look at my file to see if they can fathom it please.

Charlie

 

[KevinM]

tblRent requires a TenantID as there is an enfoced 1to 1 relationship set up with tblTenants.

You need to append this too.

 

[Charlie2]

Ahh

Thanks mate, I think you mean tblProperty_List requires a Tenant_ID because the tblRent has one already, at least Im hoping thats what the problem is.


Charlie

 

[Charlie2]

Tried It

I tried adding a column called Tenant_Id to the Property_List table and running the append query to update the amounts of rents in the rents table and got the same error message.
Am I taking your advice or have I screwed it up once again?
I have updated my file.

 

[KevinM]

Still looks pretty much the same.

A TenantID is required, you can't just append Amount on its own.

You sure it's not Update Query you require. This will update existing existing records (all the amount fields in tblRent are null).

An Append query will ADD records.

 

[Charlie2]

Yeah

An Append query is what I want.What puzzles me from your post is that the append

A TenantID is required, you can't just append Amount on its own.

So which table do I get the tenant ID from tblProperty_List doesnt have that column.

Charlie

 

[Charlie2]

Tried This

This is as close as I can get to what you said:

INSERT INTO tblRent ( Amount, Paid_On, Tenant_Id )
SELECT tblRent.Amount, tblRent.Paid_On, tblOccupancy.Tenant_Id
FROM tblRent INNER JOIN (tblOccupancy INNER JOIN tblProperty_List ON tblOccupancy.Property_Id = tblProperty_List.Property_Id) ON tblRent.Tenant_Id = tblOccupancy.Tenant_Id
WHERE (((tblRent.Paid_On) Is Not Null));

This still doesnt work, I dont know if this is exactly what you recommend, I am beginning to think using an append query for this is impossible. I know I can do it with an update query but I want to use an append.

Charlie

 

[Rich]

I don't have your version of access so I can't open the file, but I think your relationship is incorrect from your description.
tblRents should have a one to many relationship with Tenants. What are you appending and why. Rents rec'd should be entered directly into the rent details via a subform

 

[Charlie2]

I think so too

Yeah Rich I think the problem lies in the one to one relations myself.
The reason I want to use an append query is plain and simple, I am learning about append queries at the moment so I want to include one in my database.

Charlie

 

[Rich]

Whilst attempting to learn various methods in Access is laudable, using one for the wrong reason isn't.
Far more beneficial for the type of db your constructing would be to use append queries to move archived records to an archive table.
Pat Hartman has posted an example of how to do this.