Using an update query to load one table from another

[Guest]

Hi,

I am trying to "autopopulate" a table based on another. Basically, one table (People) contains a list of people and the other (Mailings) a list of who got what mailings. They join on the ID field of the person.

What I want is for the update to run through all of the people in the People (this is the first mailing, others may have other criteria) and insert a row into Mailings for each person with the Person.ID in the Mailings.Mailed_To field and some fixed data for the remaining fields (i.e., they all have the same mailing date, mailed by, mail service, etc.).

Right now, my QBE looks like this:

Field ID Mailing ID Mailed By ... etc.
Table People Mailings Mailings
Update To "1" "PRS"
Criteria Like "*"

The associated SQL is (included because it is easier to copy and paste in here than the whole QBE table):

UPDATE People INNER JOIN Mailings ON People.ID = Mailings.[Mailed To] SET Mailings.[Mailed To] = [People]![ID], Mailings.[Mailing ID] = "1", Mailings.[Mailed When] = #6/21/2004#, Mailings.[Mailed By Who] = "PRS", Mailings.[Mailing Method] = "US Mail", Mailings.[Mail Record Add Date] = #6/22/2004#
WHERE (((People.ID) Like "*"));

Is this right?

Thanks muchly in advance. Since this is my first try at such a query, I wanted a second set of eyes before I "pull the trigger".

Best,

Glenn

 

[John Vinson]

What I want is for the update to run through all of the people in the People (this is the first mailing, others may have other criteria) and insert a row into Mailings for each person with the Person.ID in the Mailings.Mailed_To field and some fixed data for the remaining fields (i.e., they all have the same mailing date, mailed by, mail service, etc.).

Rather than an Update query - which is designed to update *existing*
records - I'd suggest a simple Append query to do this. Just create an
Append query based on People, with calculated fields for the
additional data; change it to an Append query and load the mailings
table.

Your update query will not work as you wish because it's based on an
Inner Join of the two tables; it would update only those records which
already exist in Mailings - and there aren't any!