Comparing Combined Columns

[jason.teen]

Hi,

I have these two tables:

codes_together
----------------------------------------------
Prod_Name | CODE
----------------------------------------------
Cats BOB CAT
Wabbbits BUNNY & BUGS
HomeLine ELN + CURPWC + PAK03

and....

codes_separate
---------------------------------------------------------------------
ProductName | CODE_1 | CODE_2 | CODE_3
--------------------------------------------------------------------
Home Line ELN CURPWC PAK03
Cat BOB CAT null
Rabbit BUGS BUNNY null
I know that the Product Names can be sometimes off by a bit, so I am
trying to compare on the codes ONLY as I know they are accurate and
then tack on any of the Product names on the side and display only the
lines that MATCH together

Few things to take into consideration are:
* The codes_together.CODE can have any combination of the 3 codes. ie.
BUNNY can come before BUGS
* The codes_together.CODE will have either " " (empty space), "&" or
"+" between
So far, my method I have tried is to append all the code_1, code_2,
code_3 in 'codes_separate' together and then tried to compare it with
the codes_together.CODE column. (Not sure if this is the best method,
but the only one I know how... please suggest if you have a better way)
this is a start of the SQL command I have tried but gets syntax errors:

Select codes_together.Prod_name as [Product Name], codes_together.CODE
as [PCMS Codes]
from codes_together
INNER JOIN
(SELECT codes_split.ProductName, codes_split.CODE_1 & " " &
codes_split.CODE_2 & " " & codes_split.CODE_3
FROM codes_split)
ON codes_together.PCMS_CODE = codes.split.PCMScode;

[HS Hartkamp]

this is a start of the SQL command I have tried but gets syntax errors:

Select codes_together.Prod_name as [Product Name], codes_together.CODE
as [PCMS Codes]
from codes_together
INNER JOIN
(SELECT codes_split.ProductName, codes_split.CODE_1 & " " &
codes_split.CODE_2 & " " & codes_split.CODE_3
FROM codes_split)
ON codes_together.PCMS_CODE = codes.split.PCMScode;

Shouldn't the last period ( . ) be an underscore ( _ ) ?
and the bit where you join the three separate codes should include an AS
statement ( ...& codes_split.Code_3 AS PCMScode ).

Easier though is to first create a query that joins the three key-fields,
and save that query. Next, make your final query based on the table and the
query.

Furthermore, your naming conventions look a bit confusing to me. For
example, I cannot see the difference between Codes_separate and Codes_split.
A fine start would be to use prefixes such as tbl for table ans qry for
query. Allison Balter has a nice list with usable prefixes (google for 'tbl
qry prefix'). Makes your programming a lot easier. Especially when you need
to change something after a few months when you've forgotten what you did
earlier (I'm speaking from personal experience here:).
By the way, if you really want to learn the lot about Access, read this book
from Allison Balter start to finish. It covers all things from basic
relational database design to the final distribution of client-server
applications, and written for anyone between 'dummy' and 'pro-programmer',
and also works very fine as a reference guide when you need a specific bit
of information.

Bas Hartkamp.
<ja********@gmail.com> schreef in bericht
news:11**********************@p79g2000cwp.googlegr oups.com... Hi,

I have these two tables:

codes_together
----------------------------------------------
Prod_Name | CODE
----------------------------------------------
Cats BOB CAT
Wabbbits BUNNY & BUGS
HomeLine ELN + CURPWC + PAK03

and....

codes_separate
---------------------------------------------------------------------
ProductName | CODE_1 | CODE_2 | CODE_3
--------------------------------------------------------------------
Home Line ELN CURPWC PAK03
Cat BOB CAT null
Rabbit BUGS BUNNY null
I know that the Product Names can be sometimes off by a bit, so I am
trying to compare on the codes ONLY as I know they are accurate and
then tack on any of the Product names on the side and display only the
lines that MATCH together

Few things to take into consideration are:
* The codes_together.CODE can have any combination of the 3 codes. ie.
BUNNY can come before BUGS
* The codes_together.CODE will have either " " (empty space), "&" or
"+" between
So far, my method I have tried is to append all the code_1, code_2,
code_3 in 'codes_separate' together and then tried to compare it with
the codes_together.CODE column. (Not sure if this is the best method,
but the only one I know how... please suggest if you have a better way)
this is a start of the SQL command I have tried but gets syntax errors:

Select codes_together.Prod_name as [Product Name], codes_together.CODE
as [PCMS Codes]
from codes_together
INNER JOIN
(SELECT codes_split.ProductName, codes_split.CODE_1 & " " &
codes_split.CODE_2 & " " & codes_split.CODE_3
FROM codes_split)
ON codes_together.PCMS_CODE = codes.split.PCMScode;