Query SQL Syntax question 1

[08211987]

Hi,
I have 2 tables and I need to update tbl_Vendor_Tracker.PI_Contact_Name with values from tbl_MDV_SDV_PI_CONTACT.PI_Contact_Name everytime a certain update process runs. How do I code the SQL for the join on Country if the values in tbl_MDV_SDV_PI_CONTACT.Country could be "SHARED" or "US ONLY" but the values in tbl_Vendor_Tracker.Country will only be "US ONLY or SHARED" (one value) without creating duplicatng rows in the table for the other fields in the join?

Below is my current SQL:

UPDATE tbl_MDV_SDV_PI_CONTACT

INNER JOIN tbl_Vendor_Tracker
ON (tbl_MDV_SDV_PI_CONTACT.Country = tbl_Vendor_Tracker.Country)
AND (tbl_MDV_SDV_PI_CONTACT.MSD_NBR = tbl_Vendor_Tracker.SDV_Number)
AND (tbl_MDV_SDV_PI_CONTACT.MDV_NBR = tbl_Vendor_Tracker.Division_Number)

SET tbl_Vendor_Tracker.PI_Contact_Name = [tbl_MDV_SDV_PI_CONTACT].[PI_Contact_Name];

Thanks in advance,
C

 

[lameid]

In theory at least you can have complicated logic in your joins... I used an In statement to keep the criteria shorter.

UPDATE tbl_MDV_SDV_PI_CONTACT 

INNER JOIN tbl_Vendor_Tracker 
ON (tbl_MDV_SDV_PI_CONTACT.Country = tbl_Vendor_Tracker.Country
[red]OR (tbl_MDV_SDV_PI_CONTACT.Country IN ("SHARED", "US ONLY") AND tbl_Vendor_Tracker.Country = "US ONLY or SHARED")[/red]) 
AND (tbl_MDV_SDV_PI_CONTACT.MSD_NBR = tbl_Vendor_Tracker.SDV_Number) 
AND (tbl_MDV_SDV_PI_CONTACT.MDV_NBR = tbl_Vendor_Tracker.Division_Number) 

SET tbl_Vendor_Tracker.PI_Contact_Name = [tbl_MDV_SDV_PI_CONTACT].[PI_Contact_Name];

 

[lameid]

There are also about 6 forums for Access. Your question should have been posted in forum701

 

[08211987]

Sorry about that - but thanks alot!

 

[08211987]

Mind if I ask another since this has started? If not I'll take it to the other forum. Is there an issue in displaying the query design view of this SQL in Access because I am getting the following: The top 2 suggestions are not true everything is spelled correctly.

"Microsoft Office Access can't represent tje join expression tbl_MDV_SDV_PI_CONTACT.Country = tbl_Vendor_Tracker.Country
OR (tbl_MDV_SDV_PI_CONTACT.Country IN ("SHARED", "US ONLY") AND tbl_Vendor_Tracker.Country = "US ONLY or SHARED")) in Design view.

*One or more fields may have been renamed
*The name of one of more fields or tables specified in the join expression may be misspelled.
*The join may use an operator that isn't supported in Design view, such as > or <."

 

[lameid]

Yes. Access can only represent Equi-joins (column_a = column_b) that and each comparison in the design view or QBE (query by example).

You can accomplish the same thing in your where clause since this is an inner join and it will be visible in design view. This will have the same result. One of them may run faster than the other especially if there is a lot of data. My hunch would be the where clause version below may be faster if there is a noticeable difference but I'm not making any promises.

One other thing worth mentioning is that changing to the Design view and back to SQL will likely cause the SQL to change. The design view likes an excessive amount of parenthesis in the SQL it generates. It will also happen if you are mixing and with or in criteria.... It will rewrite the where clause into a much longer but logically equivalent version (the design view treats each criteria row as something to "or" with the next row).

UPDATE tbl_MDV_SDV_PI_CONTACT 

INNER JOIN tbl_Vendor_Tracker 
ON (tbl_MDV_SDV_PI_CONTACT.MSD_NBR = tbl_Vendor_Tracker.SDV_Number) 
AND (tbl_MDV_SDV_PI_CONTACT.MDV_NBR = tbl_Vendor_Tracker.Division_Number) 

SET tbl_Vendor_Tracker.PI_Contact_Name = [tbl_MDV_SDV_PI_CONTACT].[PI_Contact_Name]
WHERE (tbl_MDV_SDV_PI_CONTACT.Country = tbl_Vendor_Tracker.Country
      OR tbl_MDV_SDV_PI_CONTACT.Country IN ("SHARED", "US ONLY"))
      AND tbl_Vendor_Tracker.Country = "US ONLY or SHARED";

 

[08211987]

It sure looks like it would work but now I'm not getting any updates not even for CANADA ONLY. I appreciate your help and you got me closer but I'm going to have to put his aside until Monday.
Thanks again.

 

[lameid]

I never remember order of operations with the logical operators but apparently OR has a higher precedence than AND... I inadvertantly messed with the parens when moving to the where... I removed the extra ones and made the absolutely necessary ones for the OR red. Typically I put parens in for logical stuff to make apparent the order as it means I don't have to remember (look up) the logical order of operations. In any case it is an excellent learning point.

UPDATE tbl_MDV_SDV_PI_CONTACT 

INNER JOIN tbl_Vendor_Tracker 
ON (tbl_MDV_SDV_PI_CONTACT.MSD_NBR = tbl_Vendor_Tracker.SDV_Number) 
AND (tbl_MDV_SDV_PI_CONTACT.MDV_NBR = tbl_Vendor_Tracker.Division_Number) 

SET tbl_Vendor_Tracker.PI_Contact_Name = [tbl_MDV_SDV_PI_CONTACT].[PI_Contact_Name]
WHERE tbl_MDV_SDV_PI_CONTACT.Country = tbl_Vendor_Tracker.Country
      OR [red]([/red]tbl_MDV_SDV_PI_CONTACT.Country IN ("SHARED", "US ONLY")
      AND tbl_Vendor_Tracker.Country = "US ONLY or SHARED"[red])[/red];

 

[08211987]

Well, I got the CANADA ONLY records to update but the those for US (SHARED, US ONLY) didn't update. But I've got a workaround.....Decided to change the value of the US records to be "SHARED" whether they are shared or US only and only checked for SHARED for the US. It is just something I'll need to make clear to the users so that they understand.

Thanks for all your help!

 

[lameid]

So neither query worked? Just surprised is all. I know you have a workaround but you could try switching to a select statment and test criteria to find the issue... Hard to guesss what is weird from afar.

 

[08211987]

I will try that when I can take a breather, thanks again for your help!