Count number of times an entry for list B shows up in List A (cont

[BlueWolverine]

Hello,
MS Access 2003 on XP Pro.

I have two lists, A and B.
B contains a comprehensive, unique list of all possible CCC codes.
A contains records of work elements. One piece of data for each work
element is its CCC Code. Since each Work Element can be binned to several
CCC Codes, I have a problem.

I want to count each time a CCC from list B shows up in a record in A. If
the CCC for Work element "Check Brakes" is listed as "A07,B54,T12" then my
query return

A07 1
B54 1
T12 1

NOT

A07,B54,T12 1

Help please?

Also, the data came from another document, so it's a lot of work to manually
break the information out. I'm hoping there's a good way around this.

Thanks

 

[Ken Sheridan]

Try this:

SELECT A.[Work Element], B.[CCC Code]
FROM A INNER JOIN B
ON A.[CCC Code] LIKE "*" & B.[CCC Code] & "*";

From the look of your sample data this should work, but is not completely
bullet-proof in theory as if one CCC Code happened to be a substring of
another CCC code then there could be mismatches.

You could of course use the above as the basis for an 'append' query to fill
an AB table to model the many-to-many relationship between work elements and
CCC codes in the correct way:

INSERT INTO AB([Work Element], [CCC Code])
SELECT A.[Work Element], B.[CCC Code]
FROM A INNER JOIN B
ON A.[CCC Code] LIKE "*" & B.[CCC Code] & "*";

Your non-normalized A table would then be redundant.

Ken Sheridan
Stafford, England