Help with an update query

[Corey-g via AccessMonster.com]

Hi All,

I have the following structure:
An import table (import_mpl - local access table) that has data loaded from
an Excel spreadsheet, and 2 lookup tables that are updated / appended to from
this imported data (Supplier & Channel - linked Oracle tables). I had
originally written an append query that worked for moving 'new' data into the
2 tables, but I had to change the structure and I'm now trying to change the
query as well.

So the import table has all of the data as text, but I want to move this data
into the 'base' table (also a linked Oracle table) and use references to the
lookup tables - common practice - but I am having some troubles with writing
the query. I am much better at writing SQL for Oracle, and that wouldn't be
a problem - but I can't seem to get the result I'm looking for in the Access
Query designer.

So I want to update all records in the supplier table, and add a reference to
the channel tables pk (channel_id).

my relationships are that the import tables channel is related to the channel
tables channel_name field, and the import tables supplier field is related to
the supplier tables supplier_name field. The channel is the new part to the
structure - so the supplier table already has records for each supplier
listed in the import table, but I need to update it to also have the
channel_id.

Hope that all makes sense.

If I was to write this for oracle, it would look something like this:

update supplier set channel_id = channel.channel_id
where channel.channel_name = import_mpl.channel
and import_mpl.supplier = supplier.name

TIA,

Corey

 

[taurus via AccessMonster.com]

Maybe this will help:

UPDATE (import_mpl INNER JOIN channel ON import_mpl.channel = channel.
channel_name) INNER JOIN supplier ON import_mpl.supplier = supplier.name
SET channel_id = channel.channel_id;

 

[Corey-g via AccessMonster.com]

Thanks for taking the time to reply Taurus.

That is exactly what I tried, but I end up with a completely blank list for
'channel_id' and the number of rows in the returned dataset is the same as
the number of rows in the import_mpl table. I have looked at all of the
relationships, and they all appear to be correct (only include where joined
fields from both tables are equal).

Is it the join order that is giving me these results? Should I start with a
query that returns a list of suppliers and their channel_id, then use that is
the update query? Or maybe if I started with supplier and did the inner
joins - something like:

UPDATE supplier INNER JOIN import_mpl ON supplier.name= import_mpl.supplier)

INNER JOIN channel ON import_mpl.channel = channel.channel_name
SET channel_id = channel.channel_id;

I just tried the above before posting - and I get the same result...

I can't believe that I'm having this much trouble with this, so thank you
again for the help...

Corey

 

[Smartin]

Thanks for taking the time to reply Taurus.

That is exactly what I tried, but I end up with a completely blank list for
'channel_id' and the number of rows in the returned dataset is the same as
the number of rows in the import_mpl table. I have looked at all of the
relationships, and they all appear to be correct (only include where joined
fields from both tables are equal).

Is it the join order that is giving me these results? Should I start with a
query that returns a list of suppliers and their channel_id, then use that is
the update query? Or maybe if I started with supplier and did the inner
joins - something like:

UPDATE supplier INNER JOIN import_mpl ON supplier.name= import_mpl.supplier)

INNER JOIN channel ON import_mpl.channel = channel.channel_name
SET channel_id = channel.channel_id;

I just tried the above before posting - and I get the same result...

I can't believe that I'm having this much trouble with this, so thank you
again for the help...

Corey

I'm going out on a limb to say this has something to do with the fact
you are not joining using key fields.

Take a look at this: http://preview.tinyurl.com/yy8klh

Similar?