Need query to show 'denormalized' view of data (1 Viewer)

[RCurtin]

Hi all,
I have a table called Customers and one called Notes. The Customer has a CustomerID. This is the fk in the Notes table. The Notes fields include NoteDate and Note (which is a memo field).

I need a query which will display the data as follows:
CustomerID | NoteDate1 | Note1 | NoteDate2 | Note2 | NoteDate3 | Note3 etc.

I don't think a crosstab would work for this and have tried with pivot table but couldn't get it to work. I don't have any experience with pivots in any case.

Or would I need to do this in VBA? Or maybe I'm overcomplicating and there is an easier way to do it?

The reason I need it in a format like this is I want to export it to Excel and import it into a crm from there.

(The other option would be to export it to XML but the export to xml option doesn't seem to give the option to allow you to specify that each customer can have one to many notes associated with it)

 

[Rabbie]

I think you will need to use VBA to do this. Be warned that if a client has many notes you may generate extremely long rows in your excel file. In the worse case you could even reach the Excel limit.

 

[RCurtin]

Hi Rabbie,
Thanks very much for that. The number of notes won't be a problem in this case - the one with the most is 35 and on average it is less than 10.

Just on the xml option - am I right in saying its not really possible to get it to show many notes for each customer? This is what I get when I export it currently:

<M_Notes>
<NoteID>644</NoteID> 
<ContactID>732</ContactID> 
<Note>This is the first note</Note> 
<NoteDate>2009-03-18</NoteDate> 
<PostInfo>0</PostInfo> 
</M_Notes>
<M_Notes>
<NoteID>645</NoteID> 
<ContactID>732</ContactID> 
<Note>This is the second note</Note> 
<NoteDate>2010-03-18</NoteDate> 
<PostInfo>0</PostInfo> 
</M_Notes>

Wheras what I would like is something like this:

<M_Notes>
<NoteID>644</NoteID> 
<ContactID>732</ContactID> 
<CustomerNote>
	<Note>This is the first note</Note> 
	<NoteDate>2009-03-18</NoteDate>
<CustomerNote> 
<CustomerNote>
	<Note>This is the second note</Note> 
	<NoteDate>2010-03-18</NoteDate>
<CustomerNote> 
<PostInfo>0</PostInfo> 
</M_Notes>

This would be the preferable solution as its easier to import xml into the crm. If not I will start working on the VBA solution.