returning records by comparing 2 diff fields of diff records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to right a query that compares 2 different records based on 2
diff. fields all in the same table.

ex.
column 1 Column2

22334 66555
22171 void
3425 22334recl1
44876 softranact

returns 22334 66555

I want the query to select the first record based on the information
provided in column 2 b/c column 2 has the same number*.
 
Select * from tablename as a, tablename as b
where a.col1 = b.col2
 
twoodmore via AccessMonster.com said:
Select * from tablename as a, tablename as b
where a.col1 = b.col2


--
Message posted via AccessMonster.com


i tried that query but it is not returning any records, so then I changed it to just give me the first 5 charactors but it is still returning 0 records. Also in both cases it is asking me for a parameter but I just want it do a search through the table
Click to expand...
 
I forgot to put the syntax:

SELECT dbo_BSTRN_HEADER1.TRAN_ID as a, dbo_BSTRN_HEADER1.REFERENCE_DATA as b
FROM dbo_BSTRN_HEADER1
WHERE ((([a].[TRAN_ID])=Left(.[REFERENCE_DATA],6)));
 
Your query is looking in the SAME record.

SELECT dbo_BSTRN_HEADER1.TRAN_ID as a, dbo_BSTRN_HEADER1.REFERENCE_DATA as b
FROM dbo_BSTRN_HEADER1
WHERE ((([a].[TRAN_ID])=Left(.[REFERENCE_DATA],6)));

You could try
SELECT A.TRAN_ID , B.REFERENCE_DATA
FROM dbo_BSTRN_HEADER1 as A INNER JOIN dbo_BSTRN_HEADER1 AS B ON
[a].[TRAN_ID]=Left(.[REFERENCE_DATA],6)

OR perhaps this variation.

SELECT A.TRAN_ID , B.REFERENCE_DATA
FROM dbo_BSTRN_HEADER1 as B INNER JOIN dbo_BSTRN_HEADER1 AS A ON
B.[REFERENCE_DATA] LIKE A.Tran_ID & "*"


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

Freida said:
I forgot to put the syntax:

SELECT dbo_BSTRN_HEADER1.TRAN_ID as a, dbo_BSTRN_HEADER1.REFERENCE_DATA as
b
FROM dbo_BSTRN_HEADER1
WHERE ((([a].[TRAN_ID])=Left(.[REFERENCE_DATA],6)));
Click to expand...
 
those queries almost work, but the only thing is that when I looked at my
data some were 5 charactors and some were 6 so I tried it without the left
function but can't seem to get it work properly. Also, I think I was wrong
in my return example
please see updated below. Thx
column 1 Column2
Click to expand...
774611 321156recl1
321156 774611abc321156 321156recl1

John Spencer said:
Your query is looking in the SAME record.

SELECT dbo_BSTRN_HEADER1.TRAN_ID as a, dbo_BSTRN_HEADER1.REFERENCE_DATA as b
FROM dbo_BSTRN_HEADER1
WHERE ((([a].[TRAN_ID])=Left(.[REFERENCE_DATA],6)));

You could try
SELECT A.TRAN_ID , B.REFERENCE_DATA
FROM dbo_BSTRN_HEADER1 as A INNER JOIN dbo_BSTRN_HEADER1 AS B ON
[a].[TRAN_ID]=Left(.[REFERENCE_DATA],6)

OR perhaps this variation.

SELECT A.TRAN_ID , B.REFERENCE_DATA
FROM dbo_BSTRN_HEADER1 as B INNER JOIN dbo_BSTRN_HEADER1 AS A ON
B.[REFERENCE_DATA] LIKE A.Tran_ID & "*"


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

Freida said:
Freida said:
:

Select * from tablename as a, tablename as b
where a.col1 = b.col2

Freida wrote:
I am trying to right a query that compares 2 different records based
on 2
diff. fields all in the same table.

ex.
column 1 Column2

22334 66555
22171 void
3425 22334recl1
44876 22171crate

returns 22334 66555

I want the query to select the first record based on the information
provided in column 2 b/c column 2 has the same number*.

--
Message posted via AccessMonster.com


i tried that query but it is not returning any records, so then I
changed it to just give me the first 5 charactors but it is still
returning 0 records. Also in both cases it is asking me for a
parameter but I just want it do a search through the table
Click to expand...
I forgot to put the syntax:

SELECT dbo_BSTRN_HEADER1.TRAN_ID as a, dbo_BSTRN_HEADER1.REFERENCE_DATA as
b
FROM dbo_BSTRN_HEADER1
WHERE ((([a].[TRAN_ID])=Left(.[REFERENCE_DATA],6)));
Click to expand...
Click to expand...
 
Did you try the second variation? That one should ignore length and just
match on REFERENCE_DATA starting with the characters in TRAN_ID


SELECT A.TRAN_ID , B.REFERENCE_DATA
FROM dbo_BSTRN_HEADER1 as B INNER JOIN dbo_BSTRN_HEADER1 AS A
ON B.[REFERENCE_DATA] LIKE A.Tran_ID & "*"


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

Freida said:
those queries almost work, but the only thing is that when I looked at my
data some were 5 charactors and some were 6 so I tried it without the left
function but can't seem to get it work properly. Also, I think I was
wrong
in my return example
please see updated below. Thx
column 1 Column2
Click to expand...
774611 321156recl1
321156 774611abc
returns 22334 22334recl1
Click to expand...
321156 321156recl1
Freida said:
:

I want the query to select the first record based on the
information
provided in column 2 b/c column 2 has the same number*.
SELECT dbo_BSTRN_HEADER1.TRAN_ID as a, dbo_BSTRN_HEADER1.REFERENCE_DATA
as
b
FROM dbo_BSTRN_HEADER1
WHERE ((([a].[TRAN_ID])=Left(.[REFERENCE_DATA],6)));
Click to expand...
Click to expand...
Click to expand...
 
i am actually doing

SELECT A.TRAN_ID , B.REFERENCE_DATA
FROM dbo_BSTRN_HEADER1 as B INNER JOIN dbo_BSTRN_HEADER1 AS A
ON B.[REFERENCE_DATA] LIKE A.Tran_ID & "*recl*" b/c I only want the ones
Click to expand...
with *recl* in the reference data column but the begin numbers have to start
with the Tran_ID number.

here is an example of what I am getting

Tran_ID Reference_Data
10 100039recl1
10 103010recl1
100039 100039recl1
103010 103010recl1

all I want is the
100039 100039recl1
103010 103010recl1

I am only supposed to have 50 records I am getting a ridiculus amount of
records when ever I click the button to go to the last record access stops
responding.
John Spencer said:
Did you try the second variation? That one should ignore length and just
match on REFERENCE_DATA starting with the characters in TRAN_ID


SELECT A.TRAN_ID , B.REFERENCE_DATA
FROM dbo_BSTRN_HEADER1 as B INNER JOIN dbo_BSTRN_HEADER1 AS A
ON B.[REFERENCE_DATA] LIKE A.Tran_ID & "*"


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

Freida said:
those queries almost work, but the only thing is that when I looked at my
data some were 5 charactors and some were 6 so I tried it without the left
function but can't seem to get it work properly. Also, I think I was
wrong
in my return example
please see updated below. Thx
column 1 Column2

22334 66555
22171 void
3425 22334recl1
44876 22171crate
Click to expand...
774611 321156recl1
321156 774611abc
returns 22334 22334recl1 321156 321156recl1


:

I want the query to select the first record based on the
information
provided in column 2 b/c column 2 has the same number*.
SELECT dbo_BSTRN_HEADER1.TRAN_ID as a, dbo_BSTRN_HEADER1.REFERENCE_DATA
as
b
FROM dbo_BSTRN_HEADER1
WHERE ((([a].[TRAN_ID])=Left(.[REFERENCE_DATA],6)));
Click to expand...
Click to expand...
Click to expand...
 
SELECT A.TRAN_ID , B.REFERENCE_DATA
FROM dbo_BSTRN_HEADER1 as B INNER JOIN dbo_BSTRN_HEADER1 AS A
ON B.[REFERENCE_DATA] LIKE A.Tran_ID & "*recl*"

If possible, make sure you have indexes on Reference_Data and Tran_ID.

Beyond that perhaps you can add a condition to Tran_Id where you specify it
must be of some minimum length.

WHERE A.Tran_ID Like "?????*"

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

Freida said:
i am actually doing

SELECT A.TRAN_ID , B.REFERENCE_DATA
FROM dbo_BSTRN_HEADER1 as B INNER JOIN dbo_BSTRN_HEADER1 AS A
ON B.[REFERENCE_DATA] LIKE A.Tran_ID & "*recl*" b/c I only want the ones
Click to expand...
with *recl* in the reference data column but the begin numbers have to
start
with the Tran_ID number.

here is an example of what I am getting

Tran_ID Reference_Data
10 100039recl1
10 103010recl1
100039 100039recl1
103010 103010recl1

all I want is the
100039 100039recl1
103010 103010recl1

I am only supposed to have 50 records I am getting a ridiculus amount of
records when ever I click the button to go to the last record access stops
responding.
John Spencer said:
Did you try the second variation? That one should ignore length and just
match on REFERENCE_DATA starting with the characters in TRAN_ID


SELECT A.TRAN_ID , B.REFERENCE_DATA
FROM dbo_BSTRN_HEADER1 as B INNER JOIN dbo_BSTRN_HEADER1 AS A
ON B.[REFERENCE_DATA] LIKE A.Tran_ID & "*"


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

Freida said:
those queries almost work, but the only thing is that when I looked at
my
data some were 5 charactors and some were 6 so I tried it without the
left
function but can't seem to get it work properly. Also, I think I was
wrong
in my return example
please see updated below. Thx
column 1 Column2

22334 66555
22171 void
3425 22334recl1
44876 22171crate
774611 321156recl1
321156 774611abc

returns 22334 22334recl1
321156 321156recl1
Click to expand...
:

I want the query to select the first record based on the
information
provided in column 2 b/c column 2 has the same number*.
Click to expand...
SELECT dbo_BSTRN_HEADER1.TRAN_ID as a,
dbo_BSTRN_HEADER1.REFERENCE_DATA
as
b
FROM dbo_BSTRN_HEADER1
WHERE ((([a].[TRAN_ID])=Left(.[REFERENCE_DATA],6)));
Click to expand...
Click to expand...
Click to expand...
 
I think we got it, I just had to modify the syntax ever so slightly. I just
had to create a make table query, with only the Recl data take the * off in
front of recl, and then use the syntax you gave me.

SELECT A.TRAN_ID, B.REFERENCE_DATA
FROM [ref data] AS B INNER JOIN dbo_BSTRN_HEADER1 AS A ON B.REFERENCE_DATA
Like A.Tran_ID & "recl*"
WHERE A.Tran_ID like "?????*";

Thank you soooo much

John Spencer said:
SELECT A.TRAN_ID , B.REFERENCE_DATA
FROM dbo_BSTRN_HEADER1 as B INNER JOIN dbo_BSTRN_HEADER1 AS A
ON B.[REFERENCE_DATA] LIKE A.Tran_ID & "*recl*"

If possible, make sure you have indexes on Reference_Data and Tran_ID.

Beyond that perhaps you can add a condition to Tran_Id where you specify it
must be of some minimum length.

WHERE A.Tran_ID Like "?????*"

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

Freida said:
i am actually doing

SELECT A.TRAN_ID , B.REFERENCE_DATA
FROM dbo_BSTRN_HEADER1 as B INNER JOIN dbo_BSTRN_HEADER1 AS A
ON B.[REFERENCE_DATA] LIKE A.Tran_ID & "*recl*" b/c I only want the ones
Click to expand...
with *recl* in the reference data column but the begin numbers have to
start
with the Tran_ID number.

here is an example of what I am getting

Tran_ID Reference_Data
10 100039recl1
10 103010recl1
100039 100039recl1
103010 103010recl1

all I want is the
100039 100039recl1
103010 103010recl1

I am only supposed to have 50 records I am getting a ridiculus amount of
records when ever I click the button to go to the last record access stops
responding.
John Spencer said:
Did you try the second variation? That one should ignore length and just
match on REFERENCE_DATA starting with the characters in TRAN_ID


SELECT A.TRAN_ID , B.REFERENCE_DATA
FROM dbo_BSTRN_HEADER1 as B INNER JOIN dbo_BSTRN_HEADER1 AS A
ON B.[REFERENCE_DATA] LIKE A.Tran_ID & "*"


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

those queries almost work, but the only thing is that when I looked at
my
data some were 5 charactors and some were 6 so I tried it without the
left
function but can't seem to get it work properly. Also, I think I was
wrong
in my return example
please see updated below. Thx
column 1 Column2

22334 66555
22171 void
3425 22334recl1
44876 22171crate
774611 321156recl1
321156 774611abc

returns 22334 22334recl1
321156 321156recl1



:

I want the query to select the first record based on the
information
provided in column 2 b/c column 2 has the same number*.

SELECT dbo_BSTRN_HEADER1.TRAN_ID as a,
dbo_BSTRN_HEADER1.REFERENCE_DATA
as
b
FROM dbo_BSTRN_HEADER1
WHERE ((([a].[TRAN_ID])=Left(.[REFERENCE_DATA],6)));
Click to expand...
Click to expand...
Click to expand...
 
Back
Top