Using an update query to load one table from another

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

On Tue, 22 Jun 2004 14:36:01 -0700, "gglazer"
wrote:

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!

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

Thanks, John!

It took some trial and error and some playing around, but I eventually got it to work, saving me 904 rows of entry. :-0

I did start over from scratch and and I think the "gotcha" for me was what you hinted at in the join - I was confusing the table roles and making the query on AxB to append to B instead of query on A and append to B. The sequential, choose fields from A first and THEN make it an append query is way to avoid this problem.

The other thing I would recommend is making a copy of the database (New from) before trying some new action query. This way if you completely screw things up, no worries.

Best,

Glenn

"John Vinson" wrote:

On Tue, 22 Jun 2004 14:36:01 -0700, "gglazer"
wrote:

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!

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public