If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Count number of times an entry for list B shows up in List A (cont
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 -- BlueWolverine MSE - Mech. Eng. Go BLUE! |
#2
|
|||
|
|||
Count number of times an entry for list B shows up in List A (cont
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 "BlueWolverine" wrote: 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 -- BlueWolverine MSE - Mech. Eng. Go BLUE! |
Thread Tools | |
Display Modes | |
|
|