Update Query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

THINKINGWAY - 26 Nov 2007 15:31 GMT

I have three tables: tblVendors, tblForecast and tblTemp

tblTemp
VENDOR_NAME, SERVICESUITE_NAME, Q1, Q2, Q3, Q4, REGION_NAME

tblForecast
VENDOR_ID, FHIST, SERVICESUITE_ID, REGION_NAME, Q1, Q2, Q3, Q4

tblVendors
VENDOR_ID, VENDOR_NAME, VENDOR_DESC, REGION_NAME

I am trying to populate (append) records into the tblForecast from tblTemp
by matching VENDOR_NAME in tblVendors with the VENDOR_NAME in tblTemp. If a
match occurs I want to write the entire tblTemp record to the tblForecast and
add the VENDOR_ID from tblVendors to tblForecast.

PK in tblVendors = [VENDOR_ID]
PKin tblForecast = COMPOSITE key of [VENDOR_ID,FHIST, SERVICESUITE-ID]

Reply to this Message

Dale Fye - 26 Nov 2007 15:54 GMT

Create a new query

Select tblTemp and tblVendors from the table list and add them to the query
grid.

Join these two tables on the Vendor_Name field.

Select the fields you want to import into tblForcast

Run the select query. This will tell you where your matches are, if you
have any.

Change the query type to an Append query and select tblForcast from the list
of tables in the combo box.

Save the query if you will need to do this again in the future.

When are you going to delete the records from tblTemp?

HTH
Dale

Signature

Don''t forget to rate the post if it was helpful!

Email address is not valid.
Please reply to newsgroup only.

> I have three tables: tblVendors, tblForecast and tblTemp
>
[quoted text clipped - 14 lines]
> PK in tblVendors = [VENDOR_ID]
> PKin tblForecast = COMPOSITE key of [VENDOR_ID,FHIST, SERVICESUITE-ID]

Reply to this Message

THINKINGWAY - 26 Nov 2007 18:06 GMT

Thank you for your help. The issue that I have encountered at this point is
that some of the tblTemp records are SELECTED twice in the query results.
tblTemp will be deleted after each successful import executes.

> Create a new query
>
[quoted text clipped - 36 lines]
> > PK in tblVendors = [VENDOR_ID]
> > PKin tblForecast = COMPOSITE key of [VENDOR_ID,FHIST, SERVICESUITE-ID]

Reply to this Message