Create Excel Spreadsheet from Access with 1 to many relationship

[alex]

I've tried everything I can think of, so hopefully someone may have
some advice.

I have 2 tables in Access 2003, I'll call them Table1 and Table2.

Table1 has a variety of fields, with a key field, I'll call KEY.

Table2 has the KEY field, but allows duplicates (ie table1=1,
table2=many). Each record in Table2 also has a memo field.

The requirement is that I produce a report in Excel that contains all
of the data from both tables, matched up.

Now, if I do this as a straight query, using a left join, it doesn't
really work, because I get all of the rows for each KEY from Table1
replicated as many times as records there are with that KEY in table 2
(obviously).

What I really need to be able to do is take all of the records from
Table2 that have the same KEY and concatentate them into ONE field, so
I can then use the left join and get the result I need.

To illustrate (pretend the below are the actual values in the tables):

Table1:

KEY1, data1, data2, data3
KEY2, data1, data2, data3
etc

Table2:

KEY1, Memo1
KEY1, Memo2
KEY1, Memo3
etc

So if I did a left join query on the above, what I would get would be:

KEY1, data1, data2, data3, Memo1
KEY1, data1, data2, data3, Memo2
KEY1, data1, data2, data3, Memo3

But what I actually need is this:

KEY1, data1, data2, data3, Memo1 & Memo2 & Memo3

It seems like there must be some kind of simple query to make this
happen, but I'm at a loss. I tried doing it as a report with a sub-
report, which works just fine as a report, but doesn't work at all
when exporting to Excel. I get an "Overflow error" and the whole
thing stops, presumeably because of CR's or other funky characters
within the memo fields (or possibly just because it makes it too
wide... in the real database there are far more than 3 fields, but i
simplified it for ease of explanation.)

Is there a way I can write an access query, or a series of queries, or
VBCode to do what I am trying to do?

Thanks in advance.

 

[Michel Walsh]

You cannot handle a text field past the limit of 255 characters, in a query.
SO, using memo, and concatenation of memos is likely to exceed that limit.
You probably have to do it using VBA code, and if it is for a report, maybe
it can be done using the subroutine handling the print event.



Hoping it may help,
Vanderghast, Access MVP

 

[Jamie Collins]

if I did a left join query on the above, what I would get would be:

KEY1, data1, data2, data3, Memo1
KEY1, data1, data2, data3, Memo2
KEY1, data1, data2, data3, Memo3

But what I actually need is this:

KEY1, data1, data2, data3, Memo1 & Memo2 & Memo3

It seems like there must be some kind of simple query to make this
happen

Is there a way I can write an access query, or a series of queries, or
VBCode to do what I am trying to do?

In technical terms, you want to use the SQL language to violate First
Normal Form ;-)

Try using procedural code (e.g. VBA) instead.

Jamie.

--