Ken, The first one did the trick.
SELECT *
FROM FindCodes AS FC1
WHERE EXISTS
(SELECT *
FROM FindCodes AS FC2
WHERE FC2.Run_No = [Enter Run Number:]
AND (FC2.Run_From_PostCode = FC1.KeyCode
OR FC2.Run_To_PostCode = FC1.KeyCode))
ORDER BY Run_No;
I have 80 Runs, with 16 drops. 8 at each end. I wanted the User to offer a
Run No, for the query to return any other Run No that has a matching
[Run_From_Postcode] or [Run_To_Postcode] at either end of the Run; but also
returns within the results the original Run No. The above SQL does that.
Example: (Pasted from Excel, so it should paste back for you)
KeyCode Run_No From Run_From_Postcode To Run_To_Postcode Run_Point_Venue_A Run_Point_Address_A
N1 1 Rail Stn. N4 Regional HQ N1 Abingdon Books Abbot St, N1
N1 1 Rail Stn. N4 Regional HQ N1 Craces Chemist Abbotsford St, N1
N4 1 Rail Stn. N4 Regional HQ N1 Broome & Co Abby Rd, N4
N4 1 Rail Stn. N4 Regional HQ N1 Commerce Ltd Aberdeen St, N4
N1 2 Nursery N1 Local Office WC1 Library Acacia Rd, N1
N1 2 Nursery N1 Local Office WC1 Hester Café Academy Ct, N1
N1 2 Nursery N1 Local Office WC1 Lexington News Academy Rd, N1
N1 2 Nursery N1 Local Office WC1 Mapes & Son Academy Ter, N1
N1 16 Boat House E15 Ferry Tml E8 Park Sofas Academy Hill Rd, N1
N1 16 Boat House E15 Ferry Tml E8 Jeeves Cars Acadia St, N1
N1 57 Wharehouse N16 Factory N19 Union Ins. Accolon Way,N1
N1 77 Power Stn N22 Sub Stn N5 Gastro Pub Achorn Cir,N1
N1 78 School N5 College N17 BastilleGrill Ackley Pl,N1
N1 78 School N5 College N17 Kerwley Station Acorn St,N1
N4 80 Main Depot N2 Local Depot N4 Stanhope Press Acton St,N4
N4 80 Main Depot N2 Local Depot N4 American Embassy Ada St,N4
As you can see, the above Run No was 1. From that, your SQL found all other
run numbers that had either, or both N1 or N4 contained with the
[Run_Point_Address_A] field which are the same keycodes from Run No 1. But
using the [Run_From_Postcode] or [Run_To_Postcode] fields for referencing.
It worked perfctly, whereas the 2nd SQL you gave, found ALL Run No's with
matching From/To postcodes, the difference with this one though, is it found
all 16 drops, even though though their [Run_Point_Address_A] field didn't
contain either of the essential KeyCodes. For example:
KeyCode Run_No From Run_From_Postcode To Run_To_Postcode Run_Point_Venue_A Run_Point_Address_A
N4 1 Airport N2 Heliport WC1 Main Building Any St,WC1
Well, Mission Accomplished!. I am truly grateful for your help on this, and
like to think you got as much satisfaction in conquering what to me, seemed
an insurmountable problem.
regards
Eric
Ken Sheridan said:
I'm still not sure I fully understand what's wanted, but in answer to "Is
there a way of making it accept 1 also?" I think you just need to remove :
Run_No <> [Enter Run Number:]
AND
from the outer query so it reads
WHERE EXISTS
Ken Sheridan
Stafford, England
efandango said:
Hello Ken,
error on my side, I gave From & To, when in fact the fields were supposed to
be Run_From & Run_To. I tried to make the columns line up as best as possible
within this window. (I wish Microsoft would give us Rich Text for this great
forum). I have renamed them in the source query. The 2nd mistake I made was
not being patient enough, for after waiting quite a while, they both returned
data, which I think is correct, I need to check, it takes so long for the SQL
to run and my head is reeling from astonishment that it actually works! (it's
been painful attempting something beyond my grasp), and having to deal with
various feeder SQLs. However, there is one slight correction.
When you say:
"Run 1 itself is not included in the result set of course as you said in
your original post you wanted "any other Run No’s that have the same
postcodes at either end"
My original post should probably have said:
"any other Run No’s that have the same KEYCODE at either or ANY end" (i'm
not shouting btw, just a lack of Rich text), which all the other data returns
correctly with your SQL.
Which means: If I used Run No:1, i would get
KeyCode Run_No From To FromPostcode ToPostcode Run_Point_Venue_A Run_Point_Address_A
N1 1 Depart 1 Arrive 1 N1 N4 Hospital. Main St, N1
N4 80 Depart 1 Arrive 1 N1 N4 Bus Stn Lower Lane, N4
I have only tested one example, Run No: 80 which should and does return the
correct numbers!
Is there a way of making it accept 1 also?
Many, Many x a great deal of thanks!...
Ken Sheridan said:
That's how they would be portrayed in query design view, but a query like
this can only realistically be written in SQL as the real meat is in the
subquery. When I run the queries against the five rows of data you gave in
your original post both return an identical result set as below when 1 is
entered as the run number at the parameter prompt.
KeyCode Run_No From To Run_From_Postcode Run_To_Postcode Run_Point_Venue_A Run_Point_Address_A
N1 43 HQ Local Office N1 N4 Harbour Water Rd, N1
N4 59 Regional Main Depot N1 N4 Rail Stn Steam St, N4
N4 71 Depot 1 Depot 2 N1 N4 Nursery Kinder Plc, N4
If I change Run 71 so its from N2 to N5, however then the version which does
not reference the KeyCode column in the subquery returns:
KeyCode Run_No From To Run_From_Postcode Run_To_Postcode Run_Point_Venue_A Run_Point_Address_A
N1 43 HQ Local Office N1 N4 Harbour Water Rd, N1
N4 59 Regional Main Depot N1 N4 Rail Stn Steam St, N4
The version which does reference the KeyCode requires the KeyCode value in
the table for Run 71 also to be changed of course or it returns the same as
before.
Run 1 itself is not included in the result set of course as you said in your
original post you wanted "any other Run No’s that have the same postcodes at
either end".
Theses results seem to fit your original statement of your requirements as
far as I can see, but are they what you'd expect?
As far as the performance goes queries with correlated subqueries can be
slow in Access. Good indexing helps, in this case the KeyCode, Run_No,
Run_From_Postcode and Run_To_Postcode columns should be indexed non-uniquely
in the table design. The number of rows in the table will also have a
bearing of course.
Ken Sheridan
Stafford, England
:
Hello Ken,
I tried both corrected SQL, and they both just ran blank. Something was
going on in the background, because I had to press esc to get my
mouse/control back.
They both show up identical in the QBE Grid as:
Field: Run_No Exists (SELECT*
Table: FC1 blank
Sort: Ascending blank
Show (no tick) (no tick)
Criteria: <>[Enter Run Number:] <>False
No other columns.
(I also tried it with ticks on; no joy...)
:
The error arose from spaces which had crept into one or two column names when
pasted in (it comes from using Word to draft replies; I keep meaning to see
if there's a setting in Word which stops it inserting spaces when pasting in
a string immediately after a character). See if these give you the desired
result:
SELECT *
FROM FindCodes AS FC1
WHERE Run_No <> [Enter Run Number:]
AND EXISTS
(SELECT *
FROM FindCodes AS FC2
WHERE FC2.Run_No = [Enter Run Number:]
AND (FC2.Run_From_PostCode = FC1.KeyCode
OR FC2.Run_To_PostCode = FC1.KeyCode))
ORDER BY Run_No;
or:
SELECT *
FROM FindCodes AS FC1
WHERE Run_No <> [Enter Run Number:]
AND EXISTS
(SELECT *
FROM FindCodes AS FC2
WHERE FC2.Run_No = [Enter Run Number:]
AND (FC2.Run_From_PostCode = FC1.Run_From_PostCode
OR FC2.Run_From_PostCode = FC1.Run_To_PostCode
OR FC2.Run_To_PostCode = FC1.Run_From_PostCode
OR FC2.Run_To_PostCode = FC1.Run_To_PostCode))
ORDER BY Run_No;
I've taken the table and column names from your SQL statements, but check
that I have used the correct names.
Ken Sheridan
Stafford, England
:
Hello Ken,
when i paste your SQL in both examples, i get error messages for what seems
to be the 'Run_No <> [Enter Run Number:]' lines,
can you help/advise?
:
I think you'll need to use a subquery for this. Looking at your second query
the following would seem to equate to what I think you are trying to do there:
SELECT *
FROM FindCodes AS FC1
WHERE Run_No <> [Enter Run Number:]
AND EXISTS
(SELECT *
FROM FindCodes AS FC2
WHERE FC2.Run_No = [Enter Run Number:]
AND (FC2.Run_From_PostCode = FC1.KeyCode
OR FC2.Run _To_PostCode = FC1.KeyCode))
ORDER BY Run_No;
But from your description of your requirements it doesn't look to me like
the KeyCode column need come into play at all:
SELECT *
FROM FindCodes AS FC1
WHERE Run_No <> [Enter Run Number:]
AND EXISTS
(SELECT *
FROM FindCodes AS FC2
WHERE FC2.Run_No = [Enter Run Number:]
AND (FC2.Run_From_PostCode = FC1.Run_From_PostCode
OR FC2.Run _From_PostCode = FC1.Run_To_PostCode
OR FC2.Run_To_PostCode = FC1.Run_From_PostCode
OR FC2.Run _To_PostCode = FC1.Run_To_PostCode))
ORDER BY Run_No;
I'm not sure I've interpreted your requirement correctly, however. I'm
taking it as :
Return all rows other than those where the Run_No is that entered as the
parameter where either the To_PostCode or the From_PostCode is the same as a
To_PostCode or From_PostCode in any row where the Run_No is that entered as
the parameter.
Ken Sheridan
Stafford, England
:
I’m not sure if I have got the title right; I wanted to say "Using two
criteria to find records that relate to a third field with values in either
field", but the title box reached it's limit...
here is the Problem:
I have a table with a fixed number of runs, from one postcode area to
another area.
I want the user to be able to supply a ‘Run No’ to a parameter query, that
looks up the postcode values of the Run No selected and finds any other Run
No’s that have the same postcodes at either end.
The Run No column contains postcodes that can have one of two values
(incorporating the from and to values), for example: (Note Run 1 has the
example two postcodes)
KeyCode Run_No From To FromPostcode ToPostcode Run_Point_Venue_A Run_Point_Address_A
N1 1 Depart 1 Arrive 1 N1 N4 Hospital. Main St, N1
N4 1 Depart 1 Arrive 1 N1 N4 Bus Stn Lower Lane, N4
N1 43 HQ Local Office N1 N4 Harbour Water Rd, N1
N4 59 Regional Main Depot N1 N4 Rail Stn Steam St, N4
N4 71 Depot 1 Depot 2 N1 N4 Nursery Kinder Plc, N4
I have tried all kinds of AND/OR configurations with no success… I can set a
criteria for one column; either [FromPostcode] or [ToPostcode], but not both.
Can this be done in a query?
This is my SQL with one postcode criteria:
SELECT FindCodes.KeyCode, FindCodes.Run_No, FindCodes.Run_From,
FindCodes.Run_To, FindCodes.Run_To_Postcode, FindCodes.Run_From_Postcode,
FindCodes.Run_Point_Venue_A, FindCodes.Run_Point_Address_A
FROM FindCodes
WHERE (((FindCodes.Run_No)=[Run No?]) AND
((FindCodes.Run_From_Postcode)=[KeyCode]))
ORDER BY FindCodes.Run_No;
and this is the one with both Postcode Criteria, which returns blank.
SELECT FindCodes.KeyCode, FindCodes.Run_No, FindCodes.Run_From,
FindCodes.Run_To, FindCodes.Run_To_Postcode, FindCodes.Run_From_Postcode,
FindCodes.Run_Point_Venue_A, FindCodes.Run_Point_Address_A
FROM FindCodes
WHERE (((FindCodes.Run_No)=[Run No?]) AND
((FindCodes.Run_To_Postcode)=[KeyCode]) AND
((FindCodes.Run_From_Postcode)="KeyCode"))
ORDER BY FindCodes.Run_No;
