Merging data into one record

G

Guest

I have 2 tables. The first contains personal data
regarding an individual. As you would expect I have one
record per person. In the second table, I have
information regarding this persons representative within
the company. Each individual could have more than one
representative in this table.

What I am trying to do is produce one record that
combines all of the individuals personal information with
that of their reps (multiple). This record would be used
for an application outside of Access.

What is the best/easiest way to do this?

Thanks
Bill
 
L

Lynn Trapp

One way is to use multiple subqueries in the main select statement. Since
you didn't supply the column names for your tables, I'm going to supply some
dummy names. I have not tested this but it should get you stated..

Select A.Person_ID,
A.Person_Name,
A.Person_Address,
(Select Rep_Name
From tblRepresentatives As X
Where X.Person_ID = A.Person_ID),
(Select Rep_Address
From tblRepresentatives As X
Where X.Person_ID = A.Person_ID)
From tblPersons AS A;
 
J

Jeff C

You have Individual information (one) to representative information (many).
Build a query combining the two.
 
G

Guest

When I run a query trying to combined the two tables, I
end up with multiple records for each person (one record
for each record that exisits in the rep table). What I
want is for all of the individual rep records to be
appended onto the end of the single record. How do I do
this?
 
G

Guest

Lynn,
Thank you for your response. When you wrote "use multiple
subqueries in the main select statement", should that be
part of an Append Query to combine the information into 1
table? Assuming the following , what would the script be?

Table 1 = CustCallData
Table 2 = RepReports
Field common to both tables = Cust#
Fields to append to the end of Table 1 = RepName,
RepLastName, RepLocation

Thanks,
Bill
 
L

Lynn Trapp

I understood from your original posting that you needed this for an external
application, not for a native Access table.
 
A

averageguy

You want to de-normalize the normalized 1-M relationship. It takes
successive queries on the results of each query to do this.
 
T

Tom Wickerath

Hi Bill,

Try this sample:

How to Concatenate Data from the 'Many' Side of a Relationship
http://support.microsoft.com/?id=210163


Tom
__________________________________


I have 2 tables. The first contains personal data
regarding an individual. As you would expect I have one
record per person. In the second table, I have
information regarding this persons representative within
the company. Each individual could have more than one
representative in this table.

What I am trying to do is produce one record that
combines all of the individuals personal information with
that of their reps (multiple). This record would be used
for an application outside of Access.

What is the best/easiest way to do this?

Thanks
Bill
 
Top