Writing an Update Query

[painterz]

Hi All,

I realized I need another table in my established db to go between two other tables. The new table, tblFamily, has one PK and two FK for the other two tables:

tblAddress--->tblFamily-->tblPeople

tblAddress: PK AddressID

tblFamily: PK FamilyID
tblFamily: FK AddressID
tblFamily: FK PeopleID

tblPeople: PK PeopleID
tblPeople: FKAddressID

I used an append query on tblFamily to fill in the AddressID from tblAddress.

I'm trying to write an UPDATE query using SELECT DISTINCT to copy the PeopleID into tblFamily.PeopleID. I need the first PeopleID from each unique AddressID from tblPeople.

My update query says it can't find database B. Why is it looking outside of the current db? If it were looking in the correct place, would this work?

UPDATE B.PeopleID
SET PeopleID = (SELECT DISTINCT A.PeopleID
FROM tblPeople A
INNER JOIN tblFamily B
ON A.AddressID = B.AddressID)
WHERE B.PeopleID is null

Thanks for your help.

 

[pbaldy]

Is this in Access or SQL Server (or whatever)? That looks more like the SQL Server syntax for the join. In Access it would be like

UPDATE OneTable INNER JOIN OtherTable...

You might try building this in the design grid which will show you the correct syntax. Or:

http://www.fmsinc.com/microsoftaccess/query/snytax/update-query.html#UPDATE_Query_SQL_Syntax

 

[CJ_London]

for aliasing in access sql you use

.....
FROM tblPeople AS A
INNER JOIN tblFamily AS B
.....

just tested and not true, you don't need the AS, my bad

added note - lots of other issues

I seem to recall you cannot use subqueries in update queries so you may get a message something like 'must use an updateable query'

And if you get through that, your SELECT DISTINCT could return more than one record so the query will fail - use SELECT TOP 1 instead

next, query will definitely fail because your update is wrong

try

Update tblFamily
SET PeopleID=.....

Finally - why do you need the subquery at all?

you are updating tblFamily.PeopleID with A.AddressID which according to the subquery criteria will equal B.AddressID so you could simply have

UPDATE tblFamily
SET PeopleID = AddressID
WHERE PeopleID is null

 

[painterz]

Yes, it's in Access. I've been online trying to find anything that might work and this is the closest I came to finding something that did anything. I'll incorporate both your changes and see if I can get any further. Thank you!

 

[CJ_London]

you might find this link helpful regarding update queries

https://msdn.microsoft.com/en-us/library/aa198446.aspx

also, have added more to my post - you may not have seen it

 

[painterz]

Hi CJ,

The link suggested by both you and pbaldy are clear as mud; I'm just not getting it. I did make the alias change you suggested, and my Access query is still looking for another db. It's been a long time since I've written SQL statements so have no idea where I'm going wrong. Thanks for your help.

 

[CJ_London]

reread my amended original post properly - in particular pay attention to

try

Update tblFamily
SET PeopleID=.....

 

[painterz]

"Finally - why do you need the subquery at all?

you are updating tblFamily.PeopleID with A.AddressID which according to the subquery criteria will equal B.AddressID so you could simply have

UPDATE tblFamily
SET PeopleID = AddressID
WHERE PeopleID is null"

Hi CJ,

My query isn't correct and I've confused myself. I need to update tblFamily.PeopleID with tblPeople.PeopleID, where tblFamily.AddressID=tblPeople.AddressID choosing the first PeopleID for each AddressID.

So you're right, I don't need tblAddress but I still don't know the correct snytax for my statement above. I'll give it another shot using SELECT TOP 1. Now that I'm down to 2 tables instead of 3, I'll try the design grid pbaldy suggested.

Thanks for your help.

 

[CJ_London]

in that case I would try

 UPDATE tblFamily INNER JOIN tblPeople ON tblFamily.AddressID = tblPeople.AddressID SET tblFamily.PeopleID = tblPeople.PeopleID
 WHERE tblFamily.PeopleID is null

however this does not necessarily address your issue

choosing the first PeopleID for each AddressID

because first (or last, next, previous) means nothing in databases without an order of some sort - might be an entry date or a alphabetical sort of name or numerical sort of an PK for example, otherwise you will just get a 'random' first.

So tell me how you define first and we can look at it further. If you mean 'the first one it finds' then the above code will be fine

 

[painterz]

Hi CJ, you're right. I need the first PeopleID for each unique AddressID. I'm going to spend a couple of hours and do it manually. Thank you for your help. I'll be back for my next query challenge. :0

 

[painterz]

Big head thunk! I can use tblPeople.PkRelationship=1 to pull the first person (Primary household member) to match each AddressID.

UPDATE tblFamily1 INNER JOIN tblPeople ON tblFamily1.AddressID = tblPeople.AddressID SET tblFamily1.PeopleID = tblPeople.PeopleID
WHERE tblPeople.PKRelationship=1

I think it worked! I'll need to verify my data.

 

[painterz]

Nope, didn't work…I have two tblPeople.AddressID = 79 -- PeopleID = 146 and 2382 (two families at one address) and the query duplicated tblFamily1.PeopleID = 2382. I'll try it again, using no duplicates in the field def of tblFamily1.PeopleID. The query also said it was updating 15 rows when there were only 9 rows to update--so not trusting the results on a large scale.

 

[painterz]

Scrapped my test data and started over. Ran a query to pull all primary PeopleID out of tblPeople and appended it to a fresh tblFamily1. That should simplify the remaining info from tblAddress that's needed--another update query but should be straightforward, especially since you've given me the framework.

However, I just realized I have a logic problem in the way my data is designed so going back to the drawing board.