473,883 Members | 1,725 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
17 14913
nowthatsaplan
7 New Member
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.
May 18 '12 #11
Rabbit
12,516 Recognized Expert Moderator MVP
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT t1.groupField, 
  2.    t1.valueField, 
  3.    COUNT(*) AS Rank
  4. FROM someTable AS t1
  5.    INNER JOIN someTable AS t2
  6.    ON t1.groupField = t2.groupField
  7.       AND t1.valueField  <= t2.valueField
  8. GROUP BY t1.groupField, 
  9.    t1.valueField
May 18 '12 #12
nowthatsaplan
7 New Member
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!
May 18 '12 #13
zmbd
5,501 Recognized Expert Moderator Expert
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.
May 18 '12 #14
Rabbit
12,516 Recognized Expert Moderator MVP
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.
May 18 '12 #15
zmbd
5,501 Recognized Expert Moderator Expert
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
May 19 '12 #16
zmbd
5,501 Recognized Expert Moderator Expert
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
May 20 '12 #17
zmbd
5,501 Recognized Expert Moderator Expert
@nowthatsaplan
hey... how'd this work out for you?
-z
May 27 '12 #18

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
11137
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
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...
0
10410
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7122
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
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.