Joining fields in queries using partial values

[veaux]

Question about joins in queries. I have 2 tables with a field called
"ID".

Table 1
Rec1 = Jan12FredFlints tone
Rec2 = Feb01WilmaRubbl e

Table 2
Rec1 = Jan12BarneyRubb le
Rec2 = Mar03SamSlate

I'm trying to write a query that says show me all values where
Left([table 1].[id],5) = Left([table2].[id],5). I don't know how to
enter this into a query window. Do I need to join the tables somehow?
I tried just adding both tables without joining and entering the
parameters like I have above, but system froze. I have 30,000 records
in each table if that matters.

I don't want to create a new field that just has the left 5 of the ID
field if it's not needed.

Thanks,
D

[pietlinden]

This design is serious bad news. You can't create joins on partial
matches. You either have a match or you don't. Split the values in a
query, then try joining the resulting queries.

[Tom van Stiphout]

On 6 Sep 2006 12:24:49 -0700, "ve***@aol. com" <ve***@aol.comw rote:

Sure that works. Here is an example using the Northwind sample
database:
SELECT Customers.Custo merID, Employees.Emplo yeeID,
Customers.Conta ctName, Employees.First Name, Employees.LastN ame
FROM Customers, Employees
WHERE ((Left$([ContactName],5)=Left$([FirstName],5)));

I agree with PietLinden: you have a seriously problematic db design,
and you will have more problems down the road if you don't redesign.

-Tom.

>Question about joins in queries. I have 2 tables with a field called
"ID".

Table 1
Rec1 = Jan12FredFlints tone
Rec2 = Feb01WilmaRubbl e

Table 2
Rec1 = Jan12BarneyRubb le
Rec2 = Mar03SamSlate

I'm trying to write a query that says show me all values where
Left([table 1].[id],5) = Left([table2].[id],5). I don't know how to
enter this into a query window. Do I need to join the tables somehow?
I tried just adding both tables without joining and entering the
parameters like I have above, but system froze. I have 30,000 records
in each table if that matters.

I don't want to create a new field that just has the left 5 of the ID
field if it's not needed.

Thanks,
D

[veaux]

Great, looks like that worked. Thanks Tom! Guess I wrote it
incorrectly. Thanks for mention on DB design. They actually are split
out and a combo of 4 fields. I won't bore anyone with why they are
combined here but this should solve my current issue nonetheless!

Tom van Stiphout wrote:

On 6 Sep 2006 12:24:49 -0700, "ve***@aol. com" <ve***@aol.comw rote:

Sure that works. Here is an example using the Northwind sample
database:
SELECT Customers.Custo merID, Employees.Emplo yeeID,
Customers.Conta ctName, Employees.First Name, Employees.LastN ame
FROM Customers, Employees
WHERE ((Left$([ContactName],5)=Left$([FirstName],5)));

I agree with PietLinden: you have a seriously problematic db design,
and you will have more problems down the road if you don't redesign.

-Tom.

Question about joins in queries. I have 2 tables with a field called
"ID".

Table 1
Rec1 = Jan12FredFlints tone
Rec2 = Feb01WilmaRubbl e

Table 2
Rec1 = Jan12BarneyRubb le
Rec2 = Mar03SamSlate

I'm trying to write a query that says show me all values where
Left([table 1].[id],5) = Left([table2].[id],5). I don't know how to
enter this into a query window. Do I need to join the tables somehow?
I tried just adding both tables without joining and entering the
parameters like I have above, but system froze. I have 30,000 records
in each table if that matters.

I don't want to create a new field that just has the left 5 of the ID
field if it's not needed.

Thanks,
D

[veaux]

Spoke too soon. I wrote my question wrong I think. I'm trying to show
all the values that are in one table but NOT in the other.

So the Left(table1) = Left(table2) does work if I was trying to show
the equal values but if I substitute <for = that doesn't work. So in
my original example, from Table 1 I'd expect to only see Rec2 because
the left 5 of that record is not in Table 2.

ve***@aol.com wrote:

Great, looks like that worked. Thanks Tom! Guess I wrote it
incorrectly. Thanks for mention on DB design. They actually are split
out and a combo of 4 fields. I won't bore anyone with why they are
combined here but this should solve my current issue nonetheless!

Tom van Stiphout wrote:

On 6 Sep 2006 12:24:49 -0700, "ve***@aol. com" <ve***@aol.comw rote:

Sure that works. Here is an example using the Northwind sample
database:
SELECT Customers.Custo merID, Employees.Emplo yeeID,
Customers.Conta ctName, Employees.First Name, Employees.LastN ame
FROM Customers, Employees
WHERE ((Left$([ContactName],5)=Left$([FirstName],5)));

I agree with PietLinden: you have a seriously problematic db design,
and you will have more problems down the road if you don't redesign.

-Tom.

>Question about joins in queries. I have 2 tables with a field called
>"ID".
>
>Table 1
>Rec1 = Jan12FredFlints tone
>Rec2 = Feb01WilmaRubbl e
>
>Table 2
>Rec1 = Jan12BarneyRubb le
>Rec2 = Mar03SamSlate
>
>I'm trying to write a query that says show me all values where
>Left([table 1].[id],5) = Left([table2].[id],5). I don't know how to
>enter this into a query window. Do I need to join the tables somehow?
>I tried just adding both tables without joining and entering the
>parameters like I have above, but system froze. I have 30,000 records
>in each table if that matters.
>
>I don't want to create a new field that just has the left 5 of the ID
>field if it's not needed.
>
>Thanks,
>D

[pietlinden]

ve***@aol.com wrote:

Spoke too soon. I wrote my question wrong I think. I'm trying to show
all the values that are in one table but NOT in the other.

create an outer join - use the Find Unmatched query wizard.

[veaux]

Hate to be a nuisance but I do have an idea how to do the unmatched,
but not with a partial field. Can't use "Is Null" because I only want
to match partial.
pietlin...@hotm ail.com wrote:

ve***@aol.com wrote:

Spoke too soon. I wrote my question wrong I think. I'm trying to show
all the values that are in one table but NOT in the other.

create an outer join - use the Find Unmatched query wizard.

[pietlinden]

You would have to base in on the query Tom gave you, or something like
it. (I bet normalization is sounding pretty good right about now...)

SELECT Customers.Custo merID, Employees.Emplo yeeID,
Customers.Conta ctName, Employees.First Name, Employees.LastN ame
FROM Customers, Employees
WHERE ((Left$([ContactName],5)=Left$([FirstName],5)));
Then you'd base your Find Unmatched query on a query like the above

[pietlinden]

You would have to base in on the query Tom gave you, or something like
it. (I bet normalization is sounding pretty good right about now...)

SELECT Customers.Custo merID, Employees.Emplo yeeID,
Customers.Conta ctName, Employees.First Name, Employees.LastN ame
FROM Customers, Employees
WHERE ((Left$([ContactName],5)=Left$([FirstName],5)));
Then you'd base your Find Unmatched query on a query like the above

[David W. Fenton]

pi********@hotm ail.com wrote in
news:11******** **************@ i3g2000cwc.goog legroups.com:

This design is serious bad news. You can't create joins on
partial matches. You either have a match or you don't. Split the
values in a query, then try joining the resulting queries.

Of course you can do non-equi joins. You just can't do them in the
QBE.

But it's non-optimal, as the join can't use indexes. The examples
are clearly cases where the parts of the single value should be
stored in multiple fields.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/