Complex? Finding both Fields using a parameter with either or 2 va

G

Guest

I want to Find any record using the ‘Run_No’ as the User parameter, where it
will find any other ‘Run_No’ with a matching Keycode for Run_From_Postcode
or/and Run_To_Postcode.

The twist is that the Keycode from a given Run_No can have 2 different
Keycodes; as in the Run_No 1 example




Run_No KeyCode Run_From_Postcode Run_To_Postcode

1 N1 N4 N1
1 N4 N4 N1
1 N1 N4 N1
1 N4 N4 N1


80 N4 N2 N4
80 N4 N2 N4
80 N4 N2 N4
37 N4 N3 N4
37 N4 N3 N4
41 N1 N2 N4
41 N1 N2 N4


This is my SQL without any criteria:

SELECT FindCodes.KeyCode, FindCodes.Run_No, FindCodes.Run_From_Postcode,
FindCodes.Run_To_Postcode, FindCodes.Run_From, FindCodes.Run_To,
FindCodes.Run_Point_Venue_A, FindCodes.Run_Point_Address_A
FROM FindCodes
ORDER BY FindCodes.Run_No;
 
J

John Spencer

PERHAPS the following will work for you.

SELECT A.KeyCode, A.Run_No, A.Run_From_Postcode,
A.Run_To_Postcode, A.Run_From, A.Run_To,
A.Run_Point_Venue_A, A.Run_Point_Address_A
FROM FindCodes as A, FindCodes as B
WHERE A.KeyCode =B.Run_To_PostCode
OR A.KeyCode = B.Run_From_PostCode
AND B.Run_No = 1
ORDER BY FindCodes.Run_No;

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
G

Guest

John,

I tried that, and it returned 20,784 records!. This from a baseline of 1280
records, which breaks down to:

80 [Run_No] x 16 [Keycodes]

could you take another look at the SQL please?

regards

Eric
 
J

John Spencer

Forgot some all important parentheses. I'm still not sure this will
work and I think you got an answer elsewhere.


SELECT A.KeyCode, A.Run_No, A.Run_From_Postcode,
A.Run_To_Postcode, A.Run_From, A.Run_To,
A.Run_Point_Venue_A, A.Run_Point_Address_A
FROM FindCodes as A, FindCodes as B
WHERE (A.KeyCode =B.Run_To_PostCode
OR A.KeyCode = B.Run_From_PostCode)
AND B.Run_No = 1
ORDER BY FindCodes.Run_No;

---
John Spencer
Access MVP 2001-2005, 2007

John,

I tried that, and it returned 20,784 records!. This from a baseline of 1280
records, which breaks down to:

80 [Run_No] x 16 [Keycodes]

could you take another look at the SQL please?

regards

Eric

John Spencer said:
PERHAPS the following will work for you.

SELECT A.KeyCode, A.Run_No, A.Run_From_Postcode,
A.Run_To_Postcode, A.Run_From, A.Run_To,
A.Run_Point_Venue_A, A.Run_Point_Address_A
FROM FindCodes as A, FindCodes as B
WHERE A.KeyCode =B.Run_To_PostCode
OR A.KeyCode = B.Run_From_PostCode
AND B.Run_No = 1
ORDER BY FindCodes.Run_No;

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
Click to expand...
Click to expand...
 
G

Guest

Hello John,

Yes, essentialy speaking your SQL does work, but it gives a copy of each
record 16 times, which also happens to be the number of locations/records per
run...

I would be grateful it if you could look again at the SQL and tell me how to
adjust it so that I get the one single instance of each record. The other
thing is that the dialogue box comes up twice and asks me for the Run No to
find, when the user only need to be asked once.

Yes, I did get an answer elsewhere, good as the solution is, it takes 2 mins
to resolve the query each time. Your SQL solution in comparison does it in
about 2 secs!. So I hope you agree that it is not a duplication of effort for
the sake of it. I would naturally prefer the solution that offers the
quickest Query, as just trying to design a report for the 2 mins per preview
is painful.

regards

Eric


John Spencer said:
Forgot some all important parentheses. I'm still not sure this will
work and I think you got an answer elsewhere.


SELECT A.KeyCode, A.Run_No, A.Run_From_Postcode,
A.Run_To_Postcode, A.Run_From, A.Run_To,
A.Run_Point_Venue_A, A.Run_Point_Address_A
FROM FindCodes as A, FindCodes as B
WHERE (A.KeyCode =B.Run_To_PostCode
OR A.KeyCode = B.Run_From_PostCode)
AND B.Run_No = 1
ORDER BY FindCodes.Run_No;

---
John Spencer
Access MVP 2001-2005, 2007

John,

I tried that, and it returned 20,784 records!. This from a baseline of 1280
records, which breaks down to:

80 [Run_No] x 16 [Keycodes]

could you take another look at the SQL please?

regards

Eric

John Spencer said:
PERHAPS the following will work for you.

SELECT A.KeyCode, A.Run_No, A.Run_From_Postcode,
A.Run_To_Postcode, A.Run_From, A.Run_To,
A.Run_Point_Venue_A, A.Run_Point_Address_A
FROM FindCodes as A, FindCodes as B
WHERE A.KeyCode =B.Run_To_PostCode
OR A.KeyCode = B.Run_From_PostCode
AND B.Run_No = 1
ORDER BY FindCodes.Run_No;

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I want to Find any record using the 'Run_No' as the User parameter, where
it
will find any other 'Run_No' with a matching Keycode for Run_From_Postcode
or/and Run_To_Postcode.

The twist is that the Keycode from a given Run_No can have 2 different
Keycodes; as in the Run_No 1 example




Run_No KeyCode Run_From_Postcode Run_To_Postcode

1 N1 N4 N1
1 N4 N4 N1
1 N1 N4 N1
1 N4 N4 N1


80 N4 N2 N4
80 N4 N2 N4
80 N4 N2 N4
37 N4 N3 N4
37 N4 N3 N4
41 N1 N2 N4
41 N1 N2 N4


This is my SQL without any criteria:

SELECT FindCodes.KeyCode, FindCodes.Run_No, FindCodes.Run_From_Postcode,
FindCodes.Run_To_Postcode, FindCodes.Run_From, FindCodes.Run_To,
FindCodes.Run_Point_Venue_A, FindCodes.Run_Point_Address_A
FROM FindCodes
ORDER BY FindCodes.Run_No;
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Hello John,

I'm pleased to tell you that your SQL worked brilliantly, though I had to
make some adjustments (Please see my previous post). I have to admit I did
the changes more from intution than knowledge; but hey, it works!.

Two other crucial problems that I failed to mention in my last post were
that it always included Run No: 1 in the results, and ommited the actual
search 'Run_No' in the final results; the one that the user would input.

Realising that this default Run_No 1 assigned to Table B seemed to be your
key/baseline parameter for cross referencing against the Table A version, I
made some adjustments.


First, I had to change the query fields to totals, and grouped the one's
marked Show; then I had to create two proxy names for [Run_No] from tables A,
B. making them Run_No 1st and 2nd respectively. I now have a Query that asks
me for just Run_No_2nd (I'll rename it to something friendlier after I post
you the SQL), and away it goes; just a few seconds later, it delivers a List
of all other Run_No's with the same From or To postcodes (or both). Exactly
what I was after, something Ken kindly took the time to solve, only this
version is significantly quicker.

If you're in the mood, and have the time, it would be useful to know how you
came up with the original engine, and how it works. For example I didn't
realise that it's possible to create 'meta' tables? such as A & B. I'm still
getting my head around the syntax of SQL and seeing something relatively
'real life' is easier to understand than the text boox stuff.


My SQL adjustments:

SELECT A.KeyCode, A.Run_No AS Run_No_1st, A.Run_From_Postcode,
A.Run_To_Postcode, A.Run_From, A.Run_To, A.Run_Point_Venue_A,
A.Run_Point_Address_A
FROM FindCodes AS A, FindCodes AS B
WHERE (((A.KeyCode)=.[Run_To_PostCode] Or
(A.KeyCode)=.[Run_From_PostCode]) AND ((B.Run_No)=[Run_No_2nd]))
GROUP BY A.KeyCode, A.Run_No, A.Run_From_Postcode, A.Run_To_Postcode,
A.Run_From, A.Run_To, A.Run_Point_Venue_A, A.Run_Point_Address_A
ORDER BY A.Run_No;

Thanks for all your help.

regards

Eric
 
J

John Spencer

Adding DISTINCT to the query may solve the problem for you.

SELECT DISTINCT A.KeyCode
, A.Run_No
, A.Run_From_Postcode
, A.Run_To_Postcode
, A.Run_From
, A.Run_To
, A.Run_Point_Venue_A
, A.Run_Point_Address_A
FROM FindCodes as A, FindCodes as B
WHERE (A.KeyCode =B.Run_To_PostCode
OR A.KeyCode = B.Run_From_PostCode)
AND B.Run_No = 1
ORDER BY FindCodes.Run_No;

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

efandango said:
Hello John,

Yes, essentialy speaking your SQL does work, but it gives a copy of each
record 16 times, which also happens to be the number of locations/records
per
run...

I would be grateful it if you could look again at the SQL and tell me how
to
adjust it so that I get the one single instance of each record. The other
thing is that the dialogue box comes up twice and asks me for the Run No
to
find, when the user only need to be asked once.

Yes, I did get an answer elsewhere, good as the solution is, it takes 2
mins
to resolve the query each time. Your SQL solution in comparison does it in
about 2 secs!. So I hope you agree that it is not a duplication of effort
for
the sake of it. I would naturally prefer the solution that offers the
quickest Query, as just trying to design a report for the 2 mins per
preview
is painful.

regards

Eric


John Spencer said:
Forgot some all important parentheses. I'm still not sure this will
work and I think you got an answer elsewhere.


SELECT A.KeyCode, A.Run_No, A.Run_From_Postcode,
A.Run_To_Postcode, A.Run_From, A.Run_To,
A.Run_Point_Venue_A, A.Run_Point_Address_A
FROM FindCodes as A, FindCodes as B
WHERE (A.KeyCode =B.Run_To_PostCode
OR A.KeyCode = B.Run_From_PostCode)
AND B.Run_No = 1
ORDER BY FindCodes.Run_No;

---
John Spencer
Access MVP 2001-2005, 2007

John,

I tried that, and it returned 20,784 records!. This from a baseline of
1280
records, which breaks down to:

80 [Run_No] x 16 [Keycodes]

could you take another look at the SQL please?

regards

Eric

:

PERHAPS the following will work for you.

SELECT A.KeyCode, A.Run_No, A.Run_From_Postcode,
A.Run_To_Postcode, A.Run_From, A.Run_To,
A.Run_Point_Venue_A, A.Run_Point_Address_A
FROM FindCodes as A, FindCodes as B
WHERE A.KeyCode =B.Run_To_PostCode
OR A.KeyCode = B.Run_From_PostCode
AND B.Run_No = 1
ORDER BY FindCodes.Run_No;

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I want to Find any record using the 'Run_No' as the User parameter,
where
it
will find any other 'Run_No' with a matching Keycode for
Run_From_Postcode
or/and Run_To_Postcode.

The twist is that the Keycode from a given Run_No can have 2
different
Keycodes; as in the Run_No 1 example




Run_No KeyCode Run_From_Postcode Run_To_Postcode

1 N1 N4 N1
1 N4 N4 N1
1 N1 N4 N1
1 N4 N4 N1


80 N4 N2 N4
80 N4 N2 N4
80 N4 N2 N4
37 N4 N3 N4
37 N4 N3 N4
41 N1 N2 N4
41 N1 N2 N4


This is my SQL without any criteria:

SELECT FindCodes.KeyCode, FindCodes.Run_No,
FindCodes.Run_From_Postcode,
FindCodes.Run_To_Postcode, FindCodes.Run_From, FindCodes.Run_To,
FindCodes.Run_Point_Venue_A, FindCodes.Run_Point_Address_A
FROM FindCodes
ORDER BY FindCodes.Run_No;
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

hello John,

I have a slight problem with the SQL. Is it possible to make it 'seperate'
the Key Input Run No from the sorting process. The reason for this is that
when i run a report from the SQL, Access correctly places the groups in
order, which is fine except that I have no way of reminding the user what Key
Run No he used as his reference.

I want to be able to run the report that for example say's in the title:

"Other points matching for [Run No] 80:"

Run_No KeyCode Run_From_Postcode Run_To_Postcode

80 N4 N2 N4
80 N4 N2 N4
80 N4 N2 N4

(then rest then in Run No Order)

1 N1 N4 N1
1 N4 N4 N1
1 N1 N4 N1
1 N4 N4 N1
37 N4 N3 N4
37 N4 N3 N4
41 N1 N2 N4
41 N1 N2 N4


If it can't be done in a query, is there an alternative method?


efandango said:
Hello John,

I'm pleased to tell you that your SQL worked brilliantly, though I had to
make some adjustments (Please see my previous post). I have to admit I did
the changes more from intution than knowledge; but hey, it works!.

Two other crucial problems that I failed to mention in my last post were
that it always included Run No: 1 in the results, and ommited the actual
search 'Run_No' in the final results; the one that the user would input.

Realising that this default Run_No 1 assigned to Table B seemed to be your
key/baseline parameter for cross referencing against the Table A version, I
made some adjustments.


First, I had to change the query fields to totals, and grouped the one's
marked Show; then I had to create two proxy names for [Run_No] from tables A,
B. making them Run_No 1st and 2nd respectively. I now have a Query that asks
me for just Run_No_2nd (I'll rename it to something friendlier after I post
you the SQL), and away it goes; just a few seconds later, it delivers a List
of all other Run_No's with the same From or To postcodes (or both). Exactly
what I was after, something Ken kindly took the time to solve, only this
version is significantly quicker.

If you're in the mood, and have the time, it would be useful to know how you
came up with the original engine, and how it works. For example I didn't
realise that it's possible to create 'meta' tables? such as A & B. I'm still
getting my head around the syntax of SQL and seeing something relatively
'real life' is easier to understand than the text boox stuff.


My SQL adjustments:

SELECT A.KeyCode, A.Run_No AS Run_No_1st, A.Run_From_Postcode,
A.Run_To_Postcode, A.Run_From, A.Run_To, A.Run_Point_Venue_A,
A.Run_Point_Address_A
FROM FindCodes AS A, FindCodes AS B
WHERE (((A.KeyCode)=.[Run_To_PostCode] Or
(A.KeyCode)=.[Run_From_PostCode]) AND ((B.Run_No)=[Run_No_2nd]))
GROUP BY A.KeyCode, A.Run_No, A.Run_From_Postcode, A.Run_To_Postcode,
A.Run_From, A.Run_To, A.Run_Point_Venue_A, A.Run_Point_Address_A
ORDER BY A.Run_No;

Thanks for all your help.

regards

Eric




efandango said:
I want to Find any record using the ‘Run_No’ as the User parameter, where it
will find any other ‘Run_No’ with a matching Keycode for Run_From_Postcode
or/and Run_To_Postcode.

The twist is that the Keycode from a given Run_No can have 2 different
Keycodes; as in the Run_No 1 example




Run_No KeyCode Run_From_Postcode Run_To_Postcode

1 N1 N4 N1
1 N4 N4 N1
1 N1 N4 N1
1 N4 N4 N1


80 N4 N2 N4
80 N4 N2 N4
80 N4 N2 N4
37 N4 N3 N4
37 N4 N3 N4
41 N1 N2 N4
41 N1 N2 N4


This is my SQL without any criteria:

SELECT FindCodes.KeyCode, FindCodes.Run_No, FindCodes.Run_From_Postcode,
FindCodes.Run_To_Postcode, FindCodes.Run_From, FindCodes.Run_To,
FindCodes.Run_Point_Venue_A, FindCodes.Run_Point_Address_A
FROM FindCodes
ORDER BY FindCodes.Run_No;
Click to expand...
Click to expand...
 
J

John Spencer

If you are using a parameter query - which you were then you can refer to
the parameter in the report as if it were another field. So in a control
you could assign the parameter as the control source. Your parameter was
Run_No_2nd if I understood correctly. If your report asks you a second time
for the parameter, then you have spelled it differently in the query and in
the Control source.

Control Source: = "Other Points Matching for " & [Run_No_2nd]

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

efandango said:
hello John,

I have a slight problem with the SQL. Is it possible to make it 'seperate'
the Key Input Run No from the sorting process. The reason for this is that
when i run a report from the SQL, Access correctly places the groups in
order, which is fine except that I have no way of reminding the user what
Key
Run No he used as his reference.

I want to be able to run the report that for example say's in the title:

"Other points matching for [Run No] 80:"

Run_No KeyCode Run_From_Postcode Run_To_Postcode

80 N4 N2 N4
80 N4 N2 N4
80 N4 N2 N4

(then rest then in Run No Order)

1 N1 N4 N1
1 N4 N4 N1
1 N1 N4 N1
1 N4 N4 N1
37 N4 N3 N4
37 N4 N3 N4
41 N1 N2 N4
41 N1 N2 N4


If it can't be done in a query, is there an alternative method?


efandango said:
Hello John,

I'm pleased to tell you that your SQL worked brilliantly, though I had to
make some adjustments (Please see my previous post). I have to admit I
did
the changes more from intution than knowledge; but hey, it works!.

Two other crucial problems that I failed to mention in my last post were
that it always included Run No: 1 in the results, and ommited the actual
search 'Run_No' in the final results; the one that the user would input.

Realising that this default Run_No 1 assigned to Table B seemed to be
your
key/baseline parameter for cross referencing against the Table A version,
I
made some adjustments.


First, I had to change the query fields to totals, and grouped the one's
marked Show; then I had to create two proxy names for [Run_No] from
tables A,
B. making them Run_No 1st and 2nd respectively. I now have a Query that
asks
me for just Run_No_2nd (I'll rename it to something friendlier after I
post
you the SQL), and away it goes; just a few seconds later, it delivers a
List
of all other Run_No's with the same From or To postcodes (or both).
Exactly
what I was after, something Ken kindly took the time to solve, only this
version is significantly quicker.

If you're in the mood, and have the time, it would be useful to know how
you
came up with the original engine, and how it works. For example I didn't
realise that it's possible to create 'meta' tables? such as A & B. I'm
still
getting my head around the syntax of SQL and seeing something relatively
'real life' is easier to understand than the text boox stuff.


My SQL adjustments:

SELECT A.KeyCode, A.Run_No AS Run_No_1st, A.Run_From_Postcode,
A.Run_To_Postcode, A.Run_From, A.Run_To, A.Run_Point_Venue_A,
A.Run_Point_Address_A
FROM FindCodes AS A, FindCodes AS B
WHERE (((A.KeyCode)=.[Run_To_PostCode] Or
(A.KeyCode)=.[Run_From_PostCode]) AND ((B.Run_No)=[Run_No_2nd]))
GROUP BY A.KeyCode, A.Run_No, A.Run_From_Postcode, A.Run_To_Postcode,
A.Run_From, A.Run_To, A.Run_Point_Venue_A, A.Run_Point_Address_A
ORDER BY A.Run_No;

Thanks for all your help.

regards

Eric




efandango said:
I want to Find any record using the 'Run_No' as the User parameter,
where it
will find any other 'Run_No' with a matching Keycode for
Run_From_Postcode
or/and Run_To_Postcode.

The twist is that the Keycode from a given Run_No can have 2 different
Keycodes; as in the Run_No 1 example




Run_No KeyCode Run_From_Postcode Run_To_Postcode

1 N1 N4 N1
1 N4 N4 N1
1 N1 N4 N1
1 N4 N4 N1


80 N4 N2 N4
80 N4 N2 N4
80 N4 N2 N4
37 N4 N3 N4
37 N4 N3 N4
41 N1 N2 N4
41 N1 N2 N4


This is my SQL without any criteria:

SELECT FindCodes.KeyCode, FindCodes.Run_No,
FindCodes.Run_From_Postcode,
FindCodes.Run_To_Postcode, FindCodes.Run_From, FindCodes.Run_To,
FindCodes.Run_Point_Venue_A, FindCodes.Run_Point_Address_A
FROM FindCodes
ORDER BY FindCodes.Run_No;
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Hello John,

I did that, though I now refer to the proxy field as [Run_No_Entry]. I then
grouped on that field, and it gave me the same value down the new column
(which is what i needed)

I then called it in as a bound control, just on the report header (which is
incidently where I want it as it refers to just the User Input Run_No.) but
which has the dual benefit of only showing once. I didn't put the '&' in
though, as this was done prior to your reply (because I like to experiment
and learn).

It works a treat! (Thank you)

I can now reference just that single User Input field on the report Header.
Now if only I can get the corresponding [Run_From_Postcode] &
[Run_To_Postcode] fields on the same Header relating to that User [Run_No]...



John Spencer said:
If you are using a parameter query - which you were then you can refer to
the parameter in the report as if it were another field. So in a control
you could assign the parameter as the control source. Your parameter was
Run_No_2nd if I understood correctly. If your report asks you a second time
for the parameter, then you have spelled it differently in the query and in
the Control source.

Control Source: = "Other Points Matching for " & [Run_No_2nd]

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

efandango said:
hello John,

I have a slight problem with the SQL. Is it possible to make it 'seperate'
the Key Input Run No from the sorting process. The reason for this is that
when i run a report from the SQL, Access correctly places the groups in
order, which is fine except that I have no way of reminding the user what
Key
Run No he used as his reference.

I want to be able to run the report that for example say's in the title:

"Other points matching for [Run No] 80:"

Run_No KeyCode Run_From_Postcode Run_To_Postcode

80 N4 N2 N4
80 N4 N2 N4
80 N4 N2 N4

(then rest then in Run No Order)

1 N1 N4 N1
1 N4 N4 N1
1 N1 N4 N1
1 N4 N4 N1
37 N4 N3 N4
37 N4 N3 N4
41 N1 N2 N4
41 N1 N2 N4


If it can't be done in a query, is there an alternative method?


efandango said:
Hello John,

I'm pleased to tell you that your SQL worked brilliantly, though I had to
make some adjustments (Please see my previous post). I have to admit I
did
the changes more from intution than knowledge; but hey, it works!.

Two other crucial problems that I failed to mention in my last post were
that it always included Run No: 1 in the results, and ommited the actual
search 'Run_No' in the final results; the one that the user would input.

Realising that this default Run_No 1 assigned to Table B seemed to be
your
key/baseline parameter for cross referencing against the Table A version,
I
made some adjustments.


First, I had to change the query fields to totals, and grouped the one's
marked Show; then I had to create two proxy names for [Run_No] from
tables A,
B. making them Run_No 1st and 2nd respectively. I now have a Query that
asks
me for just Run_No_2nd (I'll rename it to something friendlier after I
post
you the SQL), and away it goes; just a few seconds later, it delivers a
List
of all other Run_No's with the same From or To postcodes (or both).
Exactly
what I was after, something Ken kindly took the time to solve, only this
version is significantly quicker.

If you're in the mood, and have the time, it would be useful to know how
you
came up with the original engine, and how it works. For example I didn't
realise that it's possible to create 'meta' tables? such as A & B. I'm
still
getting my head around the syntax of SQL and seeing something relatively
'real life' is easier to understand than the text boox stuff.


My SQL adjustments:

SELECT A.KeyCode, A.Run_No AS Run_No_1st, A.Run_From_Postcode,
A.Run_To_Postcode, A.Run_From, A.Run_To, A.Run_Point_Venue_A,
A.Run_Point_Address_A
FROM FindCodes AS A, FindCodes AS B
WHERE (((A.KeyCode)=.[Run_To_PostCode] Or
(A.KeyCode)=.[Run_From_PostCode]) AND ((B.Run_No)=[Run_No_2nd]))
GROUP BY A.KeyCode, A.Run_No, A.Run_From_Postcode, A.Run_To_Postcode,
A.Run_From, A.Run_To, A.Run_Point_Venue_A, A.Run_Point_Address_A
ORDER BY A.Run_No;

Thanks for all your help.

regards

Eric




:

I want to Find any record using the 'Run_No' as the User parameter,
where it
will find any other 'Run_No' with a matching Keycode for
Run_From_Postcode
or/and Run_To_Postcode.

The twist is that the Keycode from a given Run_No can have 2 different
Keycodes; as in the Run_No 1 example




Run_No KeyCode Run_From_Postcode Run_To_Postcode

1 N1 N4 N1
1 N4 N4 N1
1 N1 N4 N1
1 N4 N4 N1


80 N4 N2 N4
80 N4 N2 N4
80 N4 N2 N4
37 N4 N3 N4
37 N4 N3 N4
41 N1 N2 N4
41 N1 N2 N4


This is my SQL without any criteria:

SELECT FindCodes.KeyCode, FindCodes.Run_No,
FindCodes.Run_From_Postcode,
FindCodes.Run_To_Postcode, FindCodes.Run_From, FindCodes.Run_To,
FindCodes.Run_Point_Venue_A, FindCodes.Run_Point_Address_A
FROM FindCodes
ORDER BY FindCodes.Run_No;
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Using two criteria to find records that relate to a third field wi 10

Top