将每个ID的多个记录转换为具有多个列的单个ID记录 [英] Convert multiple records per ID into single ID record with multiple columns

问题描述

早上好，


我看到其他帖子的问题与我的相似，但我还没有找到有效的解决方案。我怀疑可能没有解决方案，但我很难相信它。我提前为帖子的长度道歉，但我希望尽可能多地包含细节。我目前正在使用Access 2003和Excel 2003（将在6月升级到2010年）。


我有一个包含多个表的数据库，这些表被组合起来生成一个包含20个人的列表每周都会发一份调查问卷。由于我们在选择参与者方面的要求，它相当复杂，但我已经设法让大多数工作都能正常运行。这就是困境......


一张表包含人口统计信息（身份证，姓名，年龄，性别，地点等）。该表包含每人一条记录。另一张表包含一个人正在服用的药物清单。此表最多可包含每人50条记录。药物表包含多个列，但我有一个查询结合了一些列（品牌名称，通用名称，剂量）。所以我正在使用具有ID和药物（每人多个记录）的查询以及具有人口统计信息的表（每人一个记录）。我想要做的是组合单个记录表和多记录查询，以便药物水平列在与人口统计信息相同的行上。我需要上传到我们的其他系统以发送调查的最终列表必须在一行上显示人口统计信息和所有药物。请看下面的例子。


人口统计资料：

身份证号码 - 姓名 - 年龄 - 地下

453546 - 约翰 - 54 - M

649241 - Mary - 37 - F

649726 - Jason- 48 - M


药物：

身份证号码 - 药物

453546 - Tylenol

453546 - 布洛芬

453546 - 多种维生素

649241 - 布洛芬

649241 - Tylenol

649726 - 阿司匹林

649726 - 沙丁胺醇

649726 - 多种维生素


预期成果：

身份证号码 - 姓名-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 - 沙丁胺醇 - 多种维生素


我正在寻找一种分组和转置药物清单的方法，使它们看起来像这样：

ID No. -Med 1 -Med 2 -Med 3

453546 -Tylenol -Ibuprofen -Multivitamin

649241 -Ibuprofen -Tylenol

649726 -Aspirin -Albuterol -Multivitamin


然后很容易将一对一与人口统计表结合起来。交叉表查询不起作用，因为它要求我对某些内容进行求和或计数，而我只是尝试重新排列存在的数据;没有什么可以计算或总结的。转置模块不起作用，因为它还转换了ID号，这些号码没有任何帮助。我无法控制数据的来源，因为它来自另一个更大的数据库。我只能按原样下载报告并粘贴或导入到Access中。


我现在的流程是使用Access来运行查询，为我提供列表20人和相关的人口统计数据。另一个查询为同一个20人提供药物清单。我将两个查询的结果粘贴到已经设置了公式的Excel电子表格中，Excel将所有内容组合成一个包含20行的报表。必须针对多个位置重复此过程。作为具有某种技术能力的人，我不是真的有这样做的问题，但目标是dummify。这个过程让任何人都可以做到这一点，即使他们缺乏更先进的计算机技能。我真的想找到一种方法来使整个过程更加自动化。


有什么建议吗？


如果您需要更多信息或说明，请与我们联系。


谢谢，

Jennifer


PS我认为我的Access技能是中级的。我可以创建一个功能数据库，但有很多对我来说还是新的。我正在学习VBA和SQL，主要是通过搜索和复制/粘贴代码并将其调整为我需要的。

解决方案

我继续审查其他类似的帖子问题，没有看到解决方案。但是，我知道Access比我目前的知识更先进！ :)有没有办法使Access操作类似于Vlookup？例如，使用我的人口统计信息创建一个查询并添加潜在药物的列，然后在这些药物字段中有一个表达式，可以查找匹配的ID号和独特的药物？


身份证号码 - 姓名-Age -M / F -Med 1 -Med 2 -Med 3

453546 -John -54 -M - （表达式）搜索药物清单并找到基于匹配ID的药物）


我不确定我是否解释得很好，但我希望你能理解它。正如我之前所说的那样，我怀疑Access根本无法解决这个问题。

你对VBA代码有多满意？

这仅适用于报告吗？以这种方式存储它会破坏规范化的规则。


首先需要做的是在meds上创建一个伪排名。您可以通过将表连接到自身并使用计数来实现。


一旦获得排名，您就可以使用交叉表查询将其连接到顶部，如您所愿。 / BLOCKQUOTE>

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.

解决方案

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.

How comfortable are you with VBA code?

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.

这篇关于将每个ID的多个记录转换为具有多个列的单个ID记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！