0

Here is the situation in an overly simplified version of what I need to do. I’m also still learning Access coming from other databases, so please be patient with me. Note: I have multiple version of Access I can use, so if the solution is in a newer version of access, just let me know and I’ll use that one. I try to code in Access 2007 since we MAY have a user or two still using it. But if I need to more to a later version, that is fine. On to the question…

There are 3 tables, a [PERSON] table, [CODE] table, [TRANSLATE] table. Each PERSON record is related to one (or more) CODE table records. There are 5 fields in there that all have alphanumeric codes in them. The codes are translated to ‘plain English’ in the TRANSLATE table.

So “PERSON A” may have the codes “A1”, “A2” (has this code twice), “B1” and “C1” in the CODES table.

Those codes are A1=”RUN”, A2=”JUMP”, B1=”SWIM”, and C1=”PLAY” in the TRANSLATE table.

Now, I want to have a form that shows these translations, in a single field, but I want to remove any duplicate translations. The other issue is that each person may have only values in code1 and code 3 and NULL for code2, code4, and code5.

So I need to figure out how to remove the duplicate codes (if they exist), translate them into the ‘plain English’ values and present them without any gaps.

Thanks for any input.

Andy G
  • 19,232
  • 5
  • 47
  • 69
Inchman
  • 25
  • 4
  • Possible duplicate of [Combine values from related rows into a single concatenated string value](https://stackoverflow.com/questions/13278590/combine-values-from-related-rows-into-a-single-concatenated-string-value) – Andre Jan 11 '18 at 14:54
  • Start with the above, then use DISTINCT or GROUP BY in the base query. – Andre Jan 11 '18 at 14:55
  • Please ask about one problem at a time. Are you having trouble using your lookup table, removing your duplicates or is it something else entirely? – Erik A Jan 11 '18 at 16:21
  • I am really sorry for any confusion. It's not that I am having problems with either, it's I don't know where to start to get the look ups and remove duplicates and place them in a single field. So a row in the CODE table may have 5 codes for a single PERSON or it may have 2 codes. Any of them can be duplicates. I just wanted to be able to show all the translated CODES for a PERSON in a report for that person. Thanks again for putting up with me. I have learned a ton of Access already by building this database and I love to expand my skills. – Inchman Jan 12 '18 at 14:48

0 Answers0