Link to home
Start Free TrialLog in
Avatar of PIER117

asked on

multiple column values per row

I have two table with some similar fields.  The layouts are like this:

table 1
name, licenseid, ssn

table 2
id, name, licenseid, category1

I would like to combine the two tables and have each row layout as the following:

name, licenseid, ssn, category1, category2, category3

Table 2 has multiple entries for each person.  So in theory there could be dozens of 'category' fields for each person.

Avatar of peter57r
Flag of United Kingdom of Great Britain and Northern Ireland image

Totally bad idea.

Your current design is better, although I can't tell whether any single field from table1 provides a unique id which could be used in table2 (maybe ssn?).  If ssn was unique on table1 then you would have table 2 as
Recordid, ssn, category
and link the two tables using the ssn.
Avatar of PIER117


This is for a report.  Not for a permanent table.  I just need to be able to export the information in that format.

1. My first observation is that it appears that you have duplicate data in these two tables (name, licenseid)
Can I ask why?
Are these tables properly related in the database window?

2. Can you post some sample data from both of these tables.
Also post an example of exactly what you want the Report to display, based on the sample data.

Bascially, I agree with Pete.

Avatar of PIER117

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
How do you match the two tables?
On name ?
On licenseid?
are both these fields needed to match records?
Avatar of PIER117


LicenseID.  I'll post the script that was used later this evening.  I created a sub routine on a form in access that was able to combine the fields.  The process was a little long but in the end it got the job done.