Concatenating data into one record

[Minimorgie]

Hi,

I am trying to insert data from many tables into a single table for reporting in Access and I have a situation where I have a scheme (this is a property development scheme) that may have more than one 'site', in other words many records with the same scheme but different site. I need to get the scheme identifier into my reporting table just once along with all the site references in one field (in other words I need to concatenate them into one record) but I don't know the SQL for this and there will be differing numbers of sites for each scheme.

Can anyone help (I hope my explaination makes sense!).

Many thanks

Minimorgie

 

[Catadmin]

So, is this property development table/database the only one that uses the Site column?

If so, then just use a view to join all your tables (don't use union), then use a DTS job to copy the view into the table every night (or how often you need it copied).

Or, you can create your table with the site column, do all your inserts via the DTS Import/Export wizard, and where none of the other tables has a site, it'll be null. But for this one instance, it won't be.

To make it easy, though your Transaction logs & backups might be a pain, schedule a job that Truncates your single table right before a job that Inserts into the single table from all your other tables.

Check out DTS Import / Export Wizard, Truncate Table and just DTS in Books Online for more details.

If I misunderstood what you're trying to do, please post again.



Catadmin - MCDBA, MCSA
"Just because I'm paranoid doesn't mean the universe *isn't* out to get me!"