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 = Jan12FredFlintstone
Rec2 = Feb01WilmaRubble

Table 2
Rec1 = Jan12BarneyRubble
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.comwrote:

Sure that works. Here is an example using the Northwind sample
database:
SELECT Customers.CustomerID, Employees.EmployeeID,
Customers.ContactName, Employees.FirstName, Employees.LastName
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 = Jan12FredFlintstone
Rec2 = Feb01WilmaRubble

Table 2
Rec1 = Jan12BarneyRubble
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.comwrote:

Sure that works. Here is an example using the Northwind sample
database:
SELECT Customers.CustomerID, Employees.EmployeeID,
Customers.ContactName, Employees.FirstName, Employees.LastName
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 = Jan12FredFlintstone
Rec2 = Feb01WilmaRubble

Table 2
Rec1 = Jan12BarneyRubble
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.comwrote:

Sure that works. Here is an example using the Northwind sample
database:
SELECT Customers.CustomerID, Employees.EmployeeID,
Customers.ContactName, Employees.FirstName, Employees.LastName
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 = Jan12FredFlintstone
>Rec2 = Feb01WilmaRubble
>
>Table 2
>Rec1 = Jan12BarneyRubble
>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.
[e-mail] 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.CustomerID, Employees.EmployeeID,
Customers.ContactName, Employees.FirstName, Employees.LastName
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.CustomerID, Employees.EmployeeID,
Customers.ContactName, Employees.FirstName, Employees.LastName
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********@hotmail.com wrote in
news:11**********************@i3g2000cwc.googlegro ups.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/

[pietlinden]

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,
could you post an example? Joining on something like
INNER JOIN ON LEFT(tableA.MyField,4) = LEFT(tableB.OtherField,4) ?

[Rick Brandt]

pi********@hotmail.com wrote:

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,
could you post an example? Joining on something like
INNER JOIN ON LEFT(tableA.MyField,4) = LEFT(tableB.OtherField,4) ?

SELECT tableA.MyField, B.OtherField
FROM tableA INNER JOIN tableB
ON LEFT(tableA.MyField,4) = LEFT(tableB.OtherField,4)

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[David W. Fenton]

pi********@hotmail.com wrote in
news:11**********************@i42g2000cwa.googlegr oups.com:

>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.

could you post an example? Joining on something like
INNER JOIN ON LEFT(tableA.MyField,4) = LEFT(tableB.OtherField,4) ?

Post an example of what? The SQL for this was posted in other
replies, to which *you* replied.

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