joining multiple records

[Mark F]

I have a big problem and hoping someone can help me out.

I've created a database with two linked tables - one showing individuals
(urn / name / postcode / dob / etc), the other table shows transactions (urn
/ date / value / descr / etc) - which have a separate urn as well as
contianing the individuals urn.

I've linked the two tables in access so I can see all the transactions an
individual has made - but need to run some analysis and wanted to put it into
a pivot table etc on excel - so i I need to create a new table which takes
the core info on the individual and takes the details of each transaction and
appends it to the record so i get something like this

URN / surname / dob / sex / trans urns#1 / value / date / trans urn
#2.......etc

the problem i've got is some people have upto 90 transactional records so
will need all info in one row - so the table will need a lot of columns.

 

[bhicks11 via AccessMonster.com]

Maybe you could reconsider your design. The point of a relational database
is that is much more efficient to have the transactions in a separate related
table and not duplicate data. There should be no reason to put all the
transactions in one record.

Bonnie
http://www.dataplus-svc.com

 

[John Spencer]

Actually if you have someone with 90 transactional records you would not be
able to build one record containing all the data. Access is limited to 255
columns and with 90 columns * 3 plus the 4 other fields you posted in the
example you would need 274 fields (columns).

Also, EXCEL (before Excel 2007) has a limit of 255 columns. So you will need
a different analysis tool or a different way of viewing/analyzing the data.

I don't see any simple way of solving your problem as stated. You could
export the data to a text file using some VBA to concatenate the data for each
individual URN into one row.

The big problem is what software you will use to to do the analysis. Must
analysis packages will accept/import a text file.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Ken Sheridan]

What is the nature of the analyses you wish to undertake? It may well be
possible to do them in queries in Access.

Ken Sheridan
Stafford, England