I have a database which includes member records in one table and in a joined table, it also includes specialty codes. Each member can have between one and seven different specialties. When I run a simple query, I end up with multiple rows for the same person, because of having more than one specialty code.
I thought the best way to consolidate the data, was to make a pivot table so that I would list each person only once, but could create columns in which each of their specialty codes would show up (spec_code1, Spec_code2, spec_code3...etc).
The problem stems from the fact that I need to use this data in a mail merge in Word. No problem pulling the member information (name, address, etc) from the pivot table. However, when I attempt to include specialty codes I run into formatting issues.
How do I go about including a comma between each code but not following the last specialty code (keep in mind that some people will have one or two, while others may have as many as seven).
Additionally, when I drop each of the specialty code fields into my word document I end up with spaces where there is no code (for example: 100, 203, , 402,
I would be most appreciative if someone can give me a few pointers on these issues. Maybe there is a better way to pull and combine the specialty codes into one field so that I can use it in my mail merge document.
Thanks in advance,
Austin
I thought the best way to consolidate the data, was to make a pivot table so that I would list each person only once, but could create columns in which each of their specialty codes would show up (spec_code1, Spec_code2, spec_code3...etc).
The problem stems from the fact that I need to use this data in a mail merge in Word. No problem pulling the member information (name, address, etc) from the pivot table. However, when I attempt to include specialty codes I run into formatting issues.
How do I go about including a comma between each code but not following the last specialty code (keep in mind that some people will have one or two, while others may have as many as seven).
Additionally, when I drop each of the specialty code fields into my word document I end up with spaces where there is no code (for example: 100, 203, , 402,
I would be most appreciative if someone can give me a few pointers on these issues. Maybe there is a better way to pull and combine the specialty codes into one field so that I can use it in my mail merge document.
Thanks in advance,
Austin
