Hi,
The left join is suspicious. You would replace that data in the left
table with NULL just because there is no match in the right table? even more
if the updated columns are new ones, so probably filled with NULL already...
Don't you intend a RIGHT JOIN instead? it is more "standard", with Jet,
since it has, as effect, to add the new rows in the left table (in the
unpreserved table while you update that unpreserved table)... and that will
be it with a RIGHT JOIN.
I don't say the LEFT join is wrong, I just find it suspicious, even if it
may have its uses.
Vanderghast, Access MVP
Ken Snell said:
Try this:
UPDATE tblMasterTable AS A
LEFT JOIN [tbl ABI Lab Info] AS B
ON A.SNPID=B.SNPID AND A.ordernum=B.[order #]
SET A.fwdprimer=B.[fwd primer], A.revprimer=B.[rev primer];
--
Ken Snell
<MS ACCESS MVP>
Stuart E. Wugalter said:
Ken:
I was not clear when I said "add columns." I actually want to add
these
new
columns to Table A. I created two fields in Table A that are to be
filled
in
from Table B. I was trying the following without success:
UPDATE tblMasterTable AS A
SET A.fwdprimer=B.[fwd primer], A.revprimer=B.[rev primer]
FROM [tbl ABI Lab Info] AS B
WHERE (A.SNPID=B.SNPID) AND (A.ordernum=B.[order #]);
TIA Stuart
Try this:
SELECT A.*, B.x, B.y
FROM [Table A] AS A
LEFT JOIN [Table B] AS B
ON A.id = B.id AND A.order = B.[order#];
--
Ken Snell
<MS ACCESS MVP>
Ken: This worked great. Thank you very much.
Now, I have a slightly different query I want to do. Let's say I have
two
tables that look like this:
Table A has fields: id order genename
Tabel B has fields: id order# x y
I want to add columns x and y to Table A where there is a match on id
and
order#. I want to keep records in Table A even if they don't have a
match
in
Table B
Thanks in Advance, Stuart
Try this:
SELECT Hap_CYP17_0019_29july03.Field1 AS WELLID,
Hap_CYP17_0019_29july03.Field2 AS LABID,
Hap_CYP17_0019_29july03.Field6
AS
TAQCALL, SEQUENOME.GENE AS SEQCALL
FROM Hap_CYP17_0019_29july03 INNER JOIN SEQUENOME
ON Hap_CYP17_0019_29july03.Field2=SEQUENOME.LABID
WHERE Hap_CYP17_0019_29july03.ID>9 AND SEQUENOME.SNPID=[Enter an
SNPID];
--
Ken Snell
<MS ACCESS MVP>
I have two tables that I want to conditionally merge into a query.
The
following code produces what I believe is called a "Cartesian Join."
Instead
of only one record per SNPID, giving me about 84 records, it gives
me
about
30k+ records. TIA
Stuart E. Wugalter
Statistician II
Keck School of Medicine
University of Southern California
(e-mail address removed)
SELECT Hap_CYP17_0019_29july03.Field1 AS WELLID,
Hap_CYP17_0019_29july03.Field2 AS LABID,
Hap_CYP17_0019_29july03.Field6
AS
TAQCALL, SEQUENOME.GENE AS SEQCALL
FROM Hap_CYP17_0019_29july03, SEQUENOME
WHERE (((Hap_CYP17_0019_29july03.ID)>9) AND
((SEQUENOME.SNPID)=[Enter
an
SNPID])) AND ((SEQUENOME.LABID)=[LABID]);
