Convert multiple records per ID into single ID record with multiple columns

[nowthatsaplan]

Good morning,

I have seen other posts with an issue similar to mine, but I have yet to find a solution that works. I suspect that there might not be a solution, but I have a hard time believing it. I apologize in advance for the length of the post, but I wanted to include as much detail as possible. I am currently using Access 2003 and Excel 2003 (will upgrade to 2010 in June).

I have a database with multiple tables that are combined to generate a list of 20 people who will be sent a survey each week. Because of the requirements we have in selecting participants, it's fairly complex but I've managed to get most everything working. Here's the dilemma...

One table contains demographic information (ID, name, age, gender, location, etc.). This table contains one record per person. Another table contains a list of medications a person is taking. This table can contain up to 50 records per person. The medication table contains multiple columns, but I have a query that combines some of the columns (brand name, generic name, dosage). So I'm working with a query that has ID and medication (multiple records per person) and a table that has demographics (one record per person). What I would like to do is to combine the single record table and multi-record query so the medications are listed horizontally on the same line as the demographics info. The final list that I need to upload into our other system to send out the survey has to show the demographic info and all medications on a single line. Please see the example below.

Demographics:
ID No. - Name - Age - M/F
453546 - John - 54 - M
649241 - Mary - 37 - F
649726 - Jason- 48 - M

Medications:
ID No. - Medication
453546 - Tylenol
453546 - Ibuprofen
453546 - Multivitamin
649241 - Ibuprofen
649241 - Tylenol
649726 - Aspirin
649726 - Albuterol
649726 - Multivitamin

Desired Result:
ID No. -Name -Age -M/F -Med 1 -Med 2 -Med 3
453546 -John -54 -M -Tylenol -Ibuprofen -Multivitamin
649241 -Mary -37 -F -Ibuprofen -Tylenol
649726 -Jason -48 -M -Aspirin -Albuterol -Multivitamin

I was looking for a way to group and transpose the medication lists so they look like this:
ID No. -Med 1 -Med 2 -Med 3
453546 -Tylenol -Ibuprofen -Multivitamin
649241 -Ibuprofen -Tylenol
649726 -Aspirin -Albuterol -Multivitamin

It would then be easy to combine one-for-one with the demographics table. A crosstab query does not work because it wants me to sum or count something, and I'm only trying to rearrange the data that exists; there is nothing to count or sum. A transpose module doesn't work because it also transposes the ID numbers which doesn't help any. I have no control over the way the data comes to me as it comes from another much larger database. I can only download the reports as they are and paste or import them into Access.

The process I have in place now is to use Access to run a query that provides me with my list of 20 people and associated demographics. Another query provides the medication lists for the same 20 people. I take the results of both queries, paste them into an Excel spreadsheet that already has formulas set up, and Excel combines everything into a single report of 20 rows for me. This process has to be repeated for multiple locations. As someone with some technical ability, I don't really have a problem doing it this way, but the goal is to "dummify" the process so that anyone can do it even if they lack more advanced computer skills. I would really like to find a way to make the entire process more automated.

Any suggestions?

Please let me know if you need additional information or clarification.

Thanks,
Jennifer

P.S. I would consider my Access skills to be intermediate. I can make a functional database but there is a lot that is still new to me. I am learning VBA and SQL, mostly by searching for and copy/pasting code and adjusting it to what I need.

[nowthatsaplan]

I have continued to review other posts with similar issues and don't see a solution. However, I know that Access is much more advanced than my current knowledge! :) Is there a way to have Access operate similar to a Vlookup? For example, create a query with my demographics info and add columns for the potential medications, then have an expression in these medication fields that would look for a matching ID number and unique medication?

ID No. -Name -Age -M/F -Med 1 -Med 2 -Med 3
453546 -John -54 -M -(expressions to search medication list and find medications based on matching ID)

I'm not sure if I'm explaining it very well, but I hope you can make sense of it. As I said before, I suspect that Access is simply not capable of this which kind of surprises me.

[zmbd]

How comfortable are you with VBA code?

[Rabbit]

This is for a report only right? To store it that way would break the rules of normalization.

What you need to do first is create a pseudo ranking on the meds. You can do that by joining the table to itself and using a count.

Once you have a ranking, you can use a crosstab query to get it across the top like you want.

[nowthatsaplan]

Rabbit - This is not for a report, just a query to go to Excel. The end result showing demographics and medications in 20 lines has to be in Excel so we can upload it to another database. I probably am trying to break the rules of normalization. It's frustrating because some steps work better in Excel while others work better in Access, and I haven't figured out how to automate processes that require both yet.

[nowthatsaplan]

zmbd - I am fairly new to vba code but have used quite a bit of it in various parts of the database so far. Typically when I want to do something new, I search online until I find example, copy/paste the vba code into my database, and edit it as needed. The hardest part of vba is knowing what it's even capable of and then knowing the right wording to get the desired outcome.

I am wondering if I would be better off keeping everything entirely in Excel, but I'm not sure that I can get Excel to do everything I need either. It seems like I have to use both Access and Excel, but as I said to Rabbit, I'm still figuring out how to automate between the two. Although the behind-the-scenes process might be quite complex, I am trying to make the user process as simple as possible so that anyone can do with just a few clicks of the mouse.

[zmbd]

It would be nice if the second database could connect to the one with the information you need... I do this between a MySQL database and MSAccess... no retyping and no transcription data entry errors.

I think that a VBA code could do this... and it can export the resulting table to an excel worksheet when done.

-z

[Rabbit]

@now, Did you try the solution in my post?

[NeoPa]

Application Automation may interest you as a side-issue.

A Cross-Tab is probably what you're looking for, as Rabbit suggested. It would help us all to know (generally as well as in this specific instance) that you've tried a suggestion and what happened when you did. That way we can all follow where you're at more easily, and none of the experts feel like their advice is not being taken seriously (which I'm confident is not the case, but it's easy to misinterpret situations when all you see is what other members post).

[nowthatsaplan]

@Rabbit
I am not really sure how to do the first part here about creating a pseudo ranking. It doesn't sound too difficult...just something I've never done before.

My understanding of a crosstab query is that the value fields have to show an aggregate function such as Sum or Count, not just a text value. Is that not accurate? Whenever I try to get it to show the actual text value (such as the medication name) in the value field, Access tells me I have to have an aggregate function there.

[nowthatsaplan]

Thanks, NeoPA, for the link. I will definitely check that out.

I have tried a crosstab query without success so far. I have not tried it with the pseudo ranking...I'm still trying to figure out how to do that.

I am also looking at the Excel side to figure out what else I can automate on the side to reduce the number of steps required. Realistically, I think that I probably need to transpose the data before it goes into Access or look at automating in Excel once the separate queries have been exported to Excel. I was trying to do everything in Access when I probably need to use both Access and Excel.

[Rabbit]

You can use any aggregate function. In the situation where I need to "aggregate" a text field, I use Max(). Max() will work on a text field.

As for a ranking query, it looks like this:

SELECT t1.groupField, 
   t1.valueField, 
   COUNT(*) AS Rank
FROM someTable AS t1
   INNER JOIN someTable AS t2
   ON t1.groupField = t2.groupField
      AND t1.valueField  <= t2.valueField
GROUP BY t1.groupField, 
   t1.valueField

[nowthatsaplan]

Thanks Rabbit. I will try that out. It might take a few days due to other projects, but I will post back with an update when I can. Thanks!

[zmbd]

Rabbit:
I haven't used a lot of crosstab queries... is there a "crosstab queries for dummies" out there along the lines you did for the AES/RC4/SHA thing? I did a search; however, not anything like really basic.

[Rabbit]

Do you mean the SQL syntax for a crosstab? In Access I just use the query designer and select what column I want to go across the top, what column to use for the rows, and what value I want in the cells.

In SQL Server, I find a simple example online and hack away at it until it comes out the way I want. I don't use cross tabs a lot either so I just look up the syntax each time.

[zmbd]

basically... yes a quick SQL wouldn't be bad.

As for the few I have tried in the past, I've done the same thing you do, use the wizard or find something "close enough" online and set the ax to it too :)

-z

[zmbd]

If the cross tab query doesn't work... using your posted example data, I have solution using vba that seems to work. It's a variation on a theme I was working on for a project for an equipment-id creation on a report.

-z

[zmbd]

@nowthatsaplan
hey... how'd this work out for you?
-z