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.
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.
Microsoft Access
Last Comment
PIER117
ASKER
This is for a report. Not for a permanent table. I just need to be able to export the information in that format.
PIER117,
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.
JeffCoachman
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.
JeffCoachman
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
How do you match the two tables?
On name ?
On licenseid?
are both these fields needed to match records?
On name ?
On licenseid?
are both these fields needed to match records?
ASKER
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.
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.