Append queries

  • Thread starter Thread starter Lars
  • Start date Start date
L

Lars

I have two almost identical append queries:

This one does not append the expected records:

INSERT INTO Komp ( Unit, Tag, Komp )
SELECT ICobj.Unit, ICobj.Tag, "-M1" AS Komp
FROM ICobj LEFT JOIN Komp ON (ICobj.Tag = Komp.Tag) AND
(ICobj.Unit = Komp.Unit)
WHERE (((ICobj.ElHookUp) Like "M3*") AND ((Komp.Unit) Is
Null) AND ((Komp.Tag) Is Null) AND ((([Komp].[Komp])="-
M1") Is Null));

This one does:

INSERT INTO ElForbr ( Unit, Tag, Komp, AfgType )
SELECT ICobj.Unit, ICobj.Tag, "-M1" AS Komp,
ICobj.ElHookUp
FROM ICobj LEFT JOIN Elforbr ON (ICobj.Tag = Elforbr.Tag)
AND (ICobj.Unit = Elforbr.Unit)
WHERE (((ICobj.ElHookUp) Like "M3*") AND ((Elforbr.Unit)
Is Null) AND ((Elforbr.Tag) Is Null) AND ((([Elforbr].
[Komp])="-M1") Is Null));

What's the difference?

Basically the purpose of the "... is null" part of each
of these queries is, that I want to check if the record
is already there, before I try to add it.

I really don't understand why one of these append queries
work, and the other one doesn't. Can anyone help me)

regards
Lars
 
Lars,

Personally, I like the ISNULL function

WHERE ICobj.ElHookUp Like "M3*"
AND ISNULL(Komp.Unit)
AND ISNULL(Komp.Tag)
AND ([Komp].[Komp])="-M1") Is Null

However, I find the last condition in both of the queries extremely
confusing. I don't know why you are comparing Komp.Komp to "-M1". This
comparison will return one of three values; True/False if Komp.Komp actually
has a value in it, and Null if there is nothing in it (no record in
Komp.Komp to match the ICobj table. What exactly are you trying to do in
this portion of the statement?

Dale
 
I want to add the record only if there isn't already any
record, where these three fields match.

I don't actually have any direct relation between the
tables ICobj, Komp and Elforbr. However they are
inderectly related with a kind of a one-to-many relation
between ICobj and Komp and between ICobj and Elforbr.
For each record in ICobj there can be several records in
Komp and Elforbr. Unit and Tag are key fields in all of
these tables, and Komp is an additional key field in
tables Komp and Elforbr.

I'm not a SQL expert. Originally i made these queries in
query design view. With one-to-one relations I have
often used "left join" and "is null", when I want to
check, if a record exists in a certain table, but with
one-to-many relations I had a problem to figure out how
to include the third key field - the one that doesn't
exist in the "mother" table - only in the "daugther"
tables. I did some experiments (trial and error), before
I found a solution, that seemed to work - at least
sometimes.

I think, that I have understood as much of your answer,
that the difference between these situation probably are,
that in the table Komp I already have records, where the
first two key fields match, but no fields, where all
three key fields match. This doesn't work. In the table
Elforbr I also teoretically could have records, where the
first two fields match, but actually I don't have.
Therefore - and only therefore - it works in this case.

What I am looking for, is a method that works both, when
there are records in the "daugther" tables, where the
first two key fields (out of three) match, and when there
aren't. At least I have got some idea of, what the
problem is, so I can try to go on from here.

Thank you for the answer.

Lars

-----Original Message-----
Lars,

Personally, I like the ISNULL function

WHERE ICobj.ElHookUp Like "M3*"
AND ISNULL(Komp.Unit)
AND ISNULL(Komp.Tag)
AND ([Komp].[Komp])="-M1") Is Null

However, I find the last condition in both of the queries extremely
confusing. I don't know why you are comparing Komp.Komp to "-M1". This
comparison will return one of three values; True/False if Komp.Komp actually
has a value in it, and Null if there is nothing in it (no record in
Komp.Komp to match the ICobj table. What exactly are you trying to do in
this portion of the statement?

Dale


Lars said:
I have two almost identical append queries:

This one does not append the expected records:

INSERT INTO Komp ( Unit, Tag, Komp )
SELECT ICobj.Unit, ICobj.Tag, "-M1" AS Komp
FROM ICobj LEFT JOIN Komp ON (ICobj.Tag = Komp.Tag) AND
(ICobj.Unit = Komp.Unit)
WHERE (((ICobj.ElHookUp) Like "M3*") AND ((Komp.Unit) Is
Null) AND ((Komp.Tag) Is Null) AND ((([Komp].[Komp])="-
M1") Is Null));

This one does:

INSERT INTO ElForbr ( Unit, Tag, Komp, AfgType )
SELECT ICobj.Unit, ICobj.Tag, "-M1" AS Komp,
ICobj.ElHookUp
FROM ICobj LEFT JOIN Elforbr ON (ICobj.Tag = Elforbr.Tag)
AND (ICobj.Unit = Elforbr.Unit)
WHERE (((ICobj.ElHookUp) Like "M3*") AND ((Elforbr.Unit)
Is Null) AND ((Elforbr.Tag) Is Null) AND ((([Elforbr].
[Komp])="-M1") Is Null));

What's the difference?

Basically the purpose of the "... is null" part of each
of these queries is, that I want to check if the record
is already there, before I try to add it.

I really don't understand why one of these append queries
work, and the other one doesn't. Can anyone help me)

regards
Lars
Click to expand...


.
Click to expand...
 
Back
Top