append query 3 tables

[mrrayj60]

I run this query from a form and it adds a record with all the information needed from the residents file, but I also want to take information from the forms source table, visitorlog and put it in the vrc table. When I try any field data it wants to add the the total rows of the visitorlog table, 688. For instance take visitorlog.state and put it in vrc.notes, once I add that it wants to append 688 rows, without I get one row with good data.
Thanks for reviewing, help files don't seem to address it, my next option would be to run a second append qry after this one.


INSERT INTO vrc ( UBL, NAME, PHONE, ADDR1, CITY, STATE, ZIP, SNAME, ADDR2, CITY2, STATE2, ZIP2, COUNTRY2, RENTALUNIT, OTHERUBL, STATUS, STATUS_DATE, UNIT, COMPLAINT_DATE )
SELECT DISTINCTROW residents.num, residents.name, residents.phone, residents.addr201, residents.city01, residents.state01, residents.zip01, residents.sname, residents.addr202, residents.city02, residents.state02, residents.zip02, residents.cntry02, residents.uleased, residents.special, "OPEN" AS Expr1, Date() AS Expr2, residents.UBL AS Expr3, Date() AS Expr4
FROM vrc, visitorlog, residents
WHERE ((([Forms]![frmVisit]![txtubl])=[residents]![num]));

 

[jzwp22]

It looks like your FROM clause is causing the problem

FROM vrc, visitorlog, residents

It looks like all of the fields are coming from the residents table, so I'm guessing that the FROM clause should simply be

FROM residents

Out of curiosity, why are you duplicating info that is already stored in a table in another table? It is generally not a good database practice to do so. Could you explain further?

 

[mrrayj60]

Thanks, This is the reason. Residents is a table that usually changes weekly, owners in a home owners Assocaitions. visitorlog is a table of people who come thru the gate as visitors telling us they are going to an address. vrc is a violation table. When security radars a car speeding or parking violation they have a license number and look up the tag in the violation table. That tells them they are going to a specific address. The address tells him who to send the violation to, and logs in thevrc table. Now we take the current owner, resident table info, copy to vrc, and generate the letter. I want to also take the record from visitorlog and record it somewhere in the vrc table. The vrc is an individual complaint on a property with specific details of the complaint. So if the property changes hands since last month, the previous complaint no longer applies to this owner. 2nd complaint would be greater penalties. I guess you may think I should just relate to the current residents and maybe just bring the name instead of all the addresses and stuff, that may be right...pondering that, new to access and was in a rush...Thanks, Ray

 

[jzwp22]

It sounds like you can improve your structure so you won't have to do the multiple appends

tblPeople
-pkPeopleID primary key, autonumber
-txtFName
-txtLName
-fkPeopleTypeID foreign key to tblPeopleType

tblPeopleType (2 records: Resident, Visitor)
-pkPeopleTypeID primary key, autonumber
-txtPeopleType

tblHomes (a table to hold all of the addresses that you are monitoring)
-pkHomeID primary key, autonumber
-txtAddress

Now associate a resident to a particular address

tblHomeResidents
-pkHomeResID primary key, autonumber
-fkPeopleID foreign key to tblPeople
-dteStart (date they became a resident at this address)
-dteEnd (date they left this residence)

Can a visitor use different vehicles when visiting? I assume so


tblVisitorVehicles
-pkVisVehiclesID primary key, autonumber
-fkPeopleID foreign key to tblPeople
-tagno

tblVisitorLog
-pkVisitorLogID primary key, autonumber
-fkVisitorVehiclesID foreign key to tblVisitorVehicle
-fkHomeResID foreign key to tblHomeResidents (address being visited and associated with a particular person)
-dteArrive (date & time of arrival)
-dteDeparture (date & time of departure)
whatever other fields for your log

tblViolations
-pkViolationID primary key, autonumber
-fkVisitorLogID foreign key to tblVisitorLog


With this set up, you do not need to store the data in multiple tables and you can use a query to extract all of the data you need to link a violation--vehicle--visitor and address & person being visited etc.