Advanced Query Question

[tom]

I have a query with a field which strips numerics and
sometimes results with two numbers put together.

So for example I have two queries that I want to join.

Query 1 Query 2
123456 123
456

To look like this

123456 ---- 123
|
---- 456

So I would have two records in the final query

123456 123
123456 456


How do I do this?

 

[Dirk Goldgar]

I have a query with a field which strips numerics and
sometimes results with two numbers put together.

So for example I have two queries that I want to join.

Query 1 Query 2
123456 123
456

To look like this

123456 ---- 123
|
---- 456

So I would have two records in the final query

123456 123
123456 456


How do I do this?

You didn't give enough information. I garther the idea is to join two
result sets by matching on part of that field in Query1, but what part or
parts are to be used? Are we to match on the first 3 characters, the next
three characters, and so on? Is there a maximum number of "parts", and
hence of characters?

 

[tom]

I am not understanding your post.

Query 1 has numbers combined together, in this example
123 and 456 are combined or joined(a non sql sense of the word joined) to
make 123456. I want a query that will match 123 with 123456
and will match 456 with 123456.

I want 456 to join with *456* to put the idea in a
different form.

 

[Dirk Goldgar]

I am not understanding your post.

Query 1 has numbers combined together, in this example
123 and 456 are combined or joined(a non sql sense of the word joined) to
make 123456. I want a query that will match 123 with 123456
and will match 456 with 123456.

I want 456 to join with *456* to put the idea in a
different form.

My point is that you only gave one example. In that example, you want to
take 123456 -- a six-digit number or six-character text value -- and split
it into 123 and 456. In this case, at least, you are comparing the records
from Query2 against (a) the first 3 digits of that field, and (b) the next 3
digits of that field. So record 123 matches the the first 3 digits of
123456, and record 456 matches the next 3 digits of 123456.

A query could easily be written to do exactly that. But with only one
example, I have no way of knowing if that description of the process is a
complete description of what you want. For example, will the query also
have to join "12" with "123456"? Will it have to join "3456" with "123456"?
Will it have to join "123", "456", and "789" with "123456789"? It's
certainly not clear to me.

 

[tom]

I think the most general case would work.

In practice, the case I presented would most likely be
the only case along with the exact match case, so any solution to any
problem you gave would work for me as long as 123 also matches with 123.

 

[Dirk Goldgar]

I think the most general case would work.

In practice, the case I presented would most likely be
the only case along with the exact match case, so any solution to any
problem you gave would work for me as long as 123 also matches with 123.

Here's an example of a query that joins the two queries if Query1's Field1
*contains* Query2's Field1:

SELECT Query2.*, Query1.*
FROM Query1 INNER JOIN Query2
ON Query1.Field1 Like '*' & Query2.Field1 & '*'

Here's an example that joins them only if Query2.Field1 is either the start
of Query1.Field1 or the end of Query1.Field1:

SELECT Query2.*, Query1.*
FROM Query1 INNER JOIN Query2
ON (Query1.Field1 Like '*' & Query2.Field1)
Or (Query1.Field1 Like Query2.Field1 & '*');

You'll have to adapt them to your query and field names, of course. Note
that these won't display in Query Design View, due to the imprecise join
expression. You'll have to enter and edit them in SQL View.