What does this query do?

[Keith]

I've inherited an Access file in which there is the following query:

SELECT tblItem.Instanc eNo, "PL" &
Left([tblItem].[Label],InStr([tblItem].[Label],"-")-1) AS Parent
FROM tblItem, tblItem AS tblItem_1
WHERE ((("PL" &
Left([tblItem].[Label],InStr([tblItem].[Label],"-")-1))=[tblItem_1].[InstanceNo]));

So the same table is included twice and [InstanceNo] is used as criteria for
[Parent]. I would have thought that the query would return two identical
columns but it doesn't. My brain's not working today, which part of the
plot am I missing?

Many thanks,
Keith.

[Mike Preston]

On Thu, 29 Sep 2005 10:50:06 +0100, "Keith"
<ke*********@ba eAWAYWITHITsyst ems.com> wrote:

I've inherited an Access file in which there is the following query:

SELECT tblItem.Instanc eNo, "PL" &
Left([tblItem].[Label],InStr([tblItem].[Label],"-")-1) AS Parent
FROM tblItem, tblItem AS tblItem_1
WHERE ((("PL" &
Left([tblItem].[Label],InStr([tblItem].[Label],"-")-1))=[tblItem_1].[InstanceNo]));

So the same table is included twice and [InstanceNo] is used as criteria for
[Parent]. I would have thought that the query would return two identical
columns but it doesn't. My brain's not working today, which part of the
plot am I missing?

I would guess that it lists the Instance and Parent for each Parent
row that exists with respect to any row of the table. So, assume you
have 40 rows in the table. Assume that for a given row, say 39, the
Parent is defined as "PL001"; that is, the Label starts with "001-".
Row 39 is displayed once for each row of the 40 rows where Instance =
"PL001".

If row 39 is defining the parent for Instance number 859 (Instance =
"PL859") as "001" (Label starts with "001-") then list row 39 once for
each row in the table where Instance = "PL001".

mike

[David Mann]

Without any JOIN statement, this query matches every record to every other
record, so if you have, say, 10 records in the table, you will get 100
records in the result. However, the WHERE clause limits these, and I think
what happens is as follows:

If all the instance numbers are in the form "PLnn" where nn is a number,
and all the labels are in the form "mm-tt" where mm is another number and tt
is a string,
the 'parent' of PLnn whose label is mm-tt will be PLmm,
and this is what the query shows you.

Of course I don't know what your application does, and I might be completely
wrong.

HTH

David
"Keith" <ke*********@ba eAWAYWITHITsyst ems.com> wrote in message
news:43******** **@glkas0286.gr eenlnk.net...

I've inherited an Access file in which there is the following query:

SELECT tblItem.Instanc eNo, "PL" &
Left([tblItem].[Label],InStr([tblItem].[Label],"-")-1) AS Parent
FROM tblItem, tblItem AS tblItem_1
WHERE ((("PL" &
Left([tblItem].[Label],InStr([tblItem].[Label],"-")-1))=[tblItem_1].[Instanc
eNo]));
So the same table is included twice and [InstanceNo] is used as criteria for [Parent]. I would have thought that the query would return two identical
columns but it doesn't. My brain's not working today, which part of the
plot am I missing?

Many thanks,
Keith.

[Keith]

"David Mann" <dm***@ic24.net > wrote in message
news:IC******** *******@newsfe1-win.ntli.net...

If all the instance numbers are in the form "PLnn" where nn is a number,
and all the labels are in the form "mm-tt" where mm is another number and
tt
is a string,
the 'parent' of PLnn whose label is mm-tt will be PLmm,
and this is what the query shows you.

Thanks David and thanks to Mike too. I think I'm beginning to understand it
now. Cheers!

Keith.