Query design help

[Tony in Michigan]

Hello,
I need to create a query for a user. I can't pre-process things to a temp
table

I have a very large table (main) and a field [S_Numb] S_numb is 18
charecters.

From this table the user needs to extract a subset of 13000 record sets.
The requested record sets are from an outside source
Table (request) field [S_numb_clean] which is 13 charecters.

If I were preprocessing the data, I'd add a field to the main table and add
data modified as such ....... Replace(Left([main]!S_numb,13),Chr(45),"")

My question, how would I create a query that will parse out the 18 char
field, and join it to the 13 char request field, so I can make a table of the
requested data?

Can i directly query [s/n] for [s/n clean] in their current state, while
removing the excess charecters and length?

 

[Ken Snell [MVP]]

You can build a non-equi-join query in SQL View of a query:

SELECT Table1.*, Table2.*
FROM Table1 INNER JOIN Table2
ON Left(Table1.FieldName, 13) = Table2.FieldName;

 

[Tony in Michigan]

Hi Ken,

I entered it exactly as below, and it returns a NULL response. I'm going to
presume that it works ... to a point. Now, to generate matching values, how
do I remove the CHR(45) as in my example?

Replace(Left([main]!S_numb,13),Chr(45),"")

When I add the above in line 3, in place of the left(...,13) it returns a
data mismatch in critera error. both fields are text.

You can build a non-equi-join query in SQL View of a query:

SELECT Table1.*, Table2.*
FROM Table1 INNER JOIN Table2
ON Left(Table1.FieldName, 13) = Table2.FieldName;


--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Hello,
I need to create a query for a user. I can't pre-process things to a temp
table

I have a very large table (main) and a field [S_Numb] S_numb is 18
charecters.

From this table the user needs to extract a subset of 13000 record sets.
The requested record sets are from an outside source
Table (request) field [S_numb_clean] which is 13 charecters.

If I were preprocessing the data, I'd add a field to the main table and
add
data modified as such ....... Replace(Left([main]!S_numb,13),Chr(45),"")

My question, how would I create a query that will parse out the 18 char
field, and join it to the 13 char request field, so I can make a table of
the
requested data?

Can i directly query [s/n] for [s/n clean] in their current state, while
removing the excess charecters and length?

 

[Ken Snell [MVP]]

How about if you show us some sample data from the main and the request
tables?

What I'm understanding you want to do is to take the first 13 characters of
he 18-character field from the main table, replace any "-" characters in
that 13-character string with an empty string (which will mean that you will
have fewer than 13 characters left in the string if there is at least one
"-" character in the original 13-character string), and then join that
resulting shortened string to a 13-character string from the request table
(which, of course, will not match if the character string from the main
table was shortened by replacing a "-" character with an empty string). This
is not making sense to me because it seems that you might get lots of
mismatches and thus no match.

Sample data from main and request tables, and showing how the parsed strings
should be compared will allow us to give you an exact query SQL statement.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Hi Ken,

I entered it exactly as below, and it returns a NULL response. I'm going
to
presume that it works ... to a point. Now, to generate matching values,
how
do I remove the CHR(45) as in my example?

Replace(Left([main]!S_numb,13),Chr(45),"")

When I add the above in line 3, in place of the left(...,13) it returns a
data mismatch in critera error. both fields are text.

You can build a non-equi-join query in SQL View of a query:

SELECT Table1.*, Table2.*
FROM Table1 INNER JOIN Table2
ON Left(Table1.FieldName, 13) = Table2.FieldName;


--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Hello,
I need to create a query for a user. I can't pre-process things to a
temp
table

I have a very large table (main) and a field [S_Numb] S_numb is 18
charecters.

From this table the user needs to extract a subset of 13000 record
sets.
The requested record sets are from an outside source
Table (request) field [S_numb_clean] which is 13 charecters.

If I were preprocessing the data, I'd add a field to the main table and
add
data modified as such .......
Replace(Left([main]!S_numb,13),Chr(45),"")

My question, how would I create a query that will parse out the 18 char
field, and join it to the 13 char request field, so I can make a table
of
the
requested data?

Can i directly query [s/n] for [s/n clean] in their current state,
while
removing the excess charecters and length?

 

[Tony in Michigan]

Hi Ken
The main s/n is always in the format below. Only the non-punctuation values
will change.

Now the bit of SQL I posted will take data from the "Main" format, and make
the "Clean" format. which would have been the fast/dirty way I would have
done it.


Main
101-0141275-6_0001
101-0141329-5_0101
101-0141353-7_0901
300-0941223-6_0001

Request
10101412756
10101413295
10101413537
30009412236

How about if you show us some sample data from the main and the request
tables?

What I'm understanding you want to do is to take the first 13 characters of
he 18-character field from the main table, replace any "-" characters in
that 13-character string with an empty string (which will mean that you will
have fewer than 13 characters left in the string if there is at least one
"-" character in the original 13-character string), and then join that
resulting shortened string to a 13-character string from the request table
(which, of course, will not match if the character string from the main
table was shortened by replacing a "-" character with an empty string). This
is not making sense to me because it seems that you might get lots of
mismatches and thus no match.

Sample data from main and request tables, and showing how the parsed strings
should be compared will allow us to give you an exact query SQL statement.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Hi Ken,

I entered it exactly as below, and it returns a NULL response. I'm going
to
presume that it works ... to a point. Now, to generate matching values,
how
do I remove the CHR(45) as in my example?

Replace(Left([main]!S_numb,13),Chr(45),"")

When I add the above in line 3, in place of the left(...,13) it returns a
data mismatch in critera error. both fields are text.

You can build a non-equi-join query in SQL View of a query:

SELECT Table1.*, Table2.*
FROM Table1 INNER JOIN Table2
ON Left(Table1.FieldName, 13) = Table2.FieldName;


--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


message Hello,
I need to create a query for a user. I can't pre-process things to a
temp
table

I have a very large table (main) and a field [S_Numb] S_numb is 18
charecters.

From this table the user needs to extract a subset of 13000 record
sets.
The requested record sets are from an outside source
Table (request) field [S_numb_clean] which is 13 charecters.

If I were preprocessing the data, I'd add a field to the main table and
add
data modified as such .......
Replace(Left([main]!S_numb,13),Chr(45),"")

My question, how would I create a query that will parse out the 18 char
field, and join it to the 13 char request field, so I can make a table
of
the
requested data?

Can i directly query [s/n] for [s/n clean] in their current state,
while
removing the excess charecters and length?

 

[Ken Snell [MVP]]

OK. This should work:

SELECT Table1.*, Table2.*
FROM Table1 INNER JOIN Table2
ON Replace(Left(Table1.FieldName, 13), "-", "") = Table2.FieldName;

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Hi Ken
The main s/n is always in the format below. Only the non-punctuation
values
will change.

Now the bit of SQL I posted will take data from the "Main" format, and
make
the "Clean" format. which would have been the fast/dirty way I would have
done it.


Main
101-0141275-6_0001
101-0141329-5_0101
101-0141353-7_0901
300-0941223-6_0001

Request
10101412756
10101413295
10101413537
30009412236

How about if you show us some sample data from the main and the request
tables?

What I'm understanding you want to do is to take the first 13 characters
of
he 18-character field from the main table, replace any "-" characters in
that 13-character string with an empty string (which will mean that you
will
have fewer than 13 characters left in the string if there is at least one
"-" character in the original 13-character string), and then join that
resulting shortened string to a 13-character string from the request
table
(which, of course, will not match if the character string from the main
table was shortened by replacing a "-" character with an empty string).
This
is not making sense to me because it seems that you might get lots of
mismatches and thus no match.

Sample data from main and request tables, and showing how the parsed
strings
should be compared will allow us to give you an exact query SQL
statement.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Hi Ken,

I entered it exactly as below, and it returns a NULL response. I'm
going
to
presume that it works ... to a point. Now, to generate matching
values,
how
do I remove the CHR(45) as in my example?

Replace(Left([main]!S_numb,13),Chr(45),"")

When I add the above in line 3, in place of the left(...,13) it returns
a
data mismatch in critera error. both fields are text.

:

You can build a non-equi-join query in SQL View of a query:

SELECT Table1.*, Table2.*
FROM Table1 INNER JOIN Table2
ON Left(Table1.FieldName, 13) = Table2.FieldName;


--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


message Hello,
I need to create a query for a user. I can't pre-process things to
a
temp
table

I have a very large table (main) and a field [S_Numb] S_numb is 18
charecters.

From this table the user needs to extract a subset of 13000 record
sets.
The requested record sets are from an outside source
Table (request) field [S_numb_clean] which is 13 charecters.

If I were preprocessing the data, I'd add a field to the main table
and
add
data modified as such .......
Replace(Left([main]!S_numb,13),Chr(45),"")

My question, how would I create a query that will parse out the 18
char
field, and join it to the 13 char request field, so I can make a
table
of
the
requested data?

Can i directly query [s/n] for [s/n clean] in their current state,
while
removing the excess charecters and length?

 

[Tony in Michigan]

Hi Ken,
Thank you! Worked like a charm. But, only after I made a subtable from the
Main table, with only the S_numb field. I was still getting the mismatch
error.

OK. This should work:

SELECT Table1.*, Table2.*
FROM Table1 INNER JOIN Table2
ON Replace(Left(Table1.FieldName, 13), "-", "") = Table2.FieldName;

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Hi Ken
The main s/n is always in the format below. Only the non-punctuation
values
will change.

Now the bit of SQL I posted will take data from the "Main" format, and
make
the "Clean" format. which would have been the fast/dirty way I would have
done it.


Main
101-0141275-6_0001
101-0141329-5_0101
101-0141353-7_0901
300-0941223-6_0001

Request
10101412756
10101413295
10101413537
30009412236

How about if you show us some sample data from the main and the request
tables?

What I'm understanding you want to do is to take the first 13 characters
of
he 18-character field from the main table, replace any "-" characters in
that 13-character string with an empty string (which will mean that you
will
have fewer than 13 characters left in the string if there is at least one
"-" character in the original 13-character string), and then join that
resulting shortened string to a 13-character string from the request
table
(which, of course, will not match if the character string from the main
table was shortened by replacing a "-" character with an empty string).
This
is not making sense to me because it seems that you might get lots of
mismatches and thus no match.

Sample data from main and request tables, and showing how the parsed
strings
should be compared will allow us to give you an exact query SQL
statement.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/




message Hi Ken,

I entered it exactly as below, and it returns a NULL response. I'm
going
to
presume that it works ... to a point. Now, to generate matching
values,
how
do I remove the CHR(45) as in my example?

Replace(Left([main]!S_numb,13),Chr(45),"")

When I add the above in line 3, in place of the left(...,13) it returns
a
data mismatch in critera error. both fields are text.

:

You can build a non-equi-join query in SQL View of a query:

SELECT Table1.*, Table2.*
FROM Table1 INNER JOIN Table2
ON Left(Table1.FieldName, 13) = Table2.FieldName;


--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


message Hello,
I need to create a query for a user. I can't pre-process things to
a
temp
table

I have a very large table (main) and a field [S_Numb] S_numb is 18
charecters.

From this table the user needs to extract a subset of 13000 record
sets.
The requested record sets are from an outside source
Table (request) field [S_numb_clean] which is 13 charecters.

If I were preprocessing the data, I'd add a field to the main table
and
add
data modified as such .......
Replace(Left([main]!S_numb,13),Chr(45),"")

My question, how would I create a query that will parse out the 18
char
field, and join it to the 13 char request field, so I can make a
table
of
the
requested data?

Can i directly query [s/n] for [s/n clean] in their current state,
while
removing the excess charecters and length?