Combine Tables

[Sal]

Sorry for the vague subject, not sure how to properly sum up what I need to
do.

I have 2 tables

Tbl1 is single field:

CODE

2115-GOR
2115-JUN

it is always in that format with a "-" seperating a prefix and a suffix

Tbl2 has 2 fields:

CODE|ID

2115|S
2115|M
2115|L
2115|XL

What I need to do is take the value that comes before the "-" in TBL1 and
whenever it is found in Tbl2 my result is like this (in a 3rd table or in
one of the original 2, it doesn't really matter)

CODE|ID

2115-GOR|
2115-GOR|S
2115-GOR|M
2115-GOR|L
2115-GOR|XL
2115-JUN|
2115-JUN|S
2115-JUN|M
2115-JUN|L
2115-JUN|XL

So it would write a first record of the CODE from TBL1 (ID field stays
blank) and then a record of the CODE and matching ID value from TBL2.

I hope that made sense. The way I described it in words might not have but
hopefully the starting and end point examples did.

Thanks much.

[Sal]

That's a pipe character jammed in there as field seperator. I'll add some
spaces for easier reading:

Tbl1 is single field:

CODE

2115-GOR
2115-JUN

it is always in that format with a "-" seperating a prefix and a suffix

Tbl2 has 2 fields:

CODE | ID

2115 | S
2115 | M
2115 | L
2115 | XL

What I need to do is take the value that comes before the "-" in TBL1 and
whenever it is found in Tbl2 my result is like this (in a 3rd table or in
one of the original 2, it doesn't really matter)

CODE|ID

2115-GOR |
2115-GOR | S
2115-GOR | M
2115-GOR | L
2115-GOR | XL
2115-JUN |
2115-JUN | S
2115-JUN | M
2115-JUN | L
2115-JUN | XL

So it would write a first record of the CODE from TBL1 (ID field stays
blank) and then a record of the CODE and matching ID value from TBL2.

I hope that made sense. The way I described it in words might not have but
hopefully the starting and end point examples did.

Thanks much.

[Bob Quintal]

Sa**@nospam.com (Sal) wrote in
news:97*******************@216.196.97.136:

That's a pipe character jammed in there as field seperator.
I'll add some spaces for easier reading:

Tbl1 is single field:

CODE

2115-GOR
2115-JUN

it is always in that format with a "-" seperating a prefix and
a suffix

Tbl2 has 2 fields:

CODE | ID

2115 | S
2115 | M
2115 | L
2115 | XL

What I need to do is take the value that comes before the "-"
in TBL1 and whenever it is found in Tbl2 my result is like
this (in a 3rd table or in one of the original 2, it doesn't
really matter)

CODE|ID

2115-GOR |
2115-GOR | S
2115-GOR | M
2115-GOR | L
2115-GOR | XL
2115-JUN |
2115-JUN | S
2115-JUN | M
2115-JUN | L
2115-JUN | XL

So it would write a first record of the CODE from TBL1 (ID
field stays blank) and then a record of the CODE and matching
ID value from TBL2.

I hope that made sense. The way I described it in words might
not have but hopefully the starting and end point examples
did.

Thanks much.

start with a query that calculates just the part before the
dash, and includes the code.
If this is always 4 characters that can be as simple as
BaseNumber: LEFT([code],4)

the SQL would be
SELECT CODE, LEFT([code],4) as basenumber form tbl1;

If it's variable, you will need to replace the 4 with some code
that determines where, the dash is.
Basenumber: left([code],instr("-",[code]))

If you have the possibility that there is no dash at all, you
will have to filter out the no dash codes.

THEN use the query and tbl2 to create another query that joins
on query.basenumber and tbl2.code, and concatenates query.code
and tbl2.ID, using the & operator.

SQL:
SELECT query.code & tbl2.ID AS FullCode from Query INNER JOIN
tbl2 ON tbl2.CODE = query.basenumber;

Now create a union query to add in the tbl1.code

SQL:
SELECT FULLCODE from Query2 UNION SELECT CODE from tbl1;

Use this query as the source for a maketable query.

You need a third table as the number of records is greater that
from either table.

--
Bob Quintal

PA is y I've altered my email address.