473,883 Members | 1,663 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

7 New Member
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.
May 17 '12 #1
17 14913
nowthatsaplan
7 New Member
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.
May 17 '12 #2
zmbd
5,501 Recognized Expert Moderator Expert
How comfortable are you with VBA code?
May 17 '12 #3
Rabbit
12,516 Recognized Expert Moderator MVP
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.
May 17 '12 #4
nowthatsaplan
7 New Member
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.
May 17 '12 #5
nowthatsaplan
7 New Member
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.
May 17 '12 #6
zmbd
5,501 Recognized Expert Moderator Expert
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
May 17 '12 #7
Rabbit
12,516 Recognized Expert Moderator MVP
@now, Did you try the solution in my post?
May 17 '12 #8
NeoPa
32,584 Recognized Expert Moderator MVP
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).
May 18 '12 #9
nowthatsaplan
7 New Member
@Rabbit
I am not really sure how to do the first part here about creating a pseudo ranking. It doesn't sound too difficult...jus t 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.
May 18 '12 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

3
2350
by: Fred | last post by:
I have a query that returns multiple identical records, however it should only return one. Indeed there is only one record for the OrderActionTypecode of 'P' yet there are two orderactions so it returnd both. Oddly the second query returns only the desired single record but wihtout all the additional fields I need. Does the Inner join somehow mess with the query? Thanks
1
2259
by: A P | last post by:
How can I go about updating multiple records or deleting multiple records from a DB at a time?
5
3355
by: Ross A. Finlayson | last post by:
Hi, I'm scratching together an Access database. The development box is Office 95, the deployment box Office 2003. So anyways I am griping about forms and global variables. Say for example I'm adding a customer. The Customer fields are mostly foreign keys that refer to primary keys in other tables, left join instead of junction tables at this point. So, when I want to add a customer record, I also need to add records to the other...
4
9942
by: Terren | last post by:
Is there a way to create one field from multiple records using sql. For example Table 1 John 18 Peter 18 David 18 Now I want an sql query that when executed will return a field that looks like this
22
18825
by: RayPower | last post by:
I'm having problem with using DAO recordset to append record into a table and subsequent code to update other tables in a transaction. The MDB is Access 2000 with the latest service pack of JET 4. The system is client/server, multiusers based. The MDBs are using record locking. Here is part of the code: Dim wkSpace As Workspace, db As Database Dim rstTrans As DAO.Recordset Set wkSpace = DBEngine.Workspaces(0)
5
14001
by: Kaur | last post by:
Hi, I have been successful copying a vba code from one of your posts on how to copy and paste a record by declaring the desired fields that needs to be copied in form's declaration and creating two button "copy" and "paste". Works like magic. My problem is how can I copy multiple records and paste them at the same time. My data entry form has main form that has a Questionlist box of Questions. The second list box on the same form displays...
1
4793
by: NumberCruncher | last post by:
Hi All, I am struggling with setting up my first system of tables, forms,and reports, and could use your help! I am setting up a database to keep track of the production of a produced item. The item is a panel, with a specific texture. There are standard panels that are then produced in a limited number of specific textures. The number of panels/project varies, so I keep track of the actual number of panels per project with the count:...
1
1943
by: davidevan | last post by:
I'm trying to update a single field in a table according to the an age category as a condition. The meat of the code that executes looks like this (I created a record set, and loop through it until there are no records): Code: Do While Not rstRecSet.EOF
4
9823
4Him
by: 4Him | last post by:
First off, let me say this is a great site! I've just started working with Access and much of my success is from what I've read here! Background: I have a form, driven off a single table. Goal: To have multiple fields copied and pasted into multiple records with a single button click. Work Done: In the form I have multiple fields, but for my question let's just refer to FIELD 1 and FIELD 2. I would like to copy the value of FIELD 2 and...
1
6291
by: tjm0713 | last post by:
Not sure this can be done but here is my current situation. I have a table containing millions of records. Each record has 28 fields. One of the fields I am trying to return the single record for is Res_code. There can be multiple records with the same Res_code. Some of the matching Res_code records can have different values in any of the 28 fields and some may have exactly the same value in each field. Example: Res_Id ...
0
9935
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9791
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10742
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10844
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
1
7970
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5990
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4609
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4215
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3231
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.