Apologies that my first post is a request for help, a bit cheeky I know!

I'm working on a table in Access, here's an extract:

http://img27.imageshack.us/img27/3961/tableu.jpg (http://img27.imageshack.us/my.php?image=tableu.jpg)

I want to eliminate duplicates in the Module_Code field, and merge the data in the UserText1 field, so I end up with something like this:

http://img24.imageshack.us/img24/8211/table2u.jpg (http://img24.imageshack.us/my.php?image=table2u.jpg)

Any suggestions on a query or series of queries that could do this would be much appreciated.

Thanks in anticipation.

Welcome to the forum. :)

I am just a bit curious as to why you want to denormalise your data as this will make it harder to work with.

Thanks for the welcome :)

I need to export the Module_Code data to a proprietary database. Module_Codes must be distinct there, but we want to preserve the data in the UserText1 field for reference only.

You use the Select distinct command to eliminate duplicate fields on a subquery to pull the data and try that http://www.techonthenet.com/access/functions/index.php
to sort out the second query to merge cells.
Your query should be
Select Modulecode, UserText where (select distinct module code from Talblename)
from table name;
try that and see what you get.

try that http://www.techonthenet.com/access/functions/index.php to sort out the second query to merge cells.


Thanks for your suggestion. I went to the link, but wasn't sure which particular function you're referring to, was it Concat with & (http://www.techonthenet.com/access/functions/string/concat.php)? I could use that, but it would be too manual. What I need is something that says "For equal entries on the Module_Code field, merge the entries in the UserText1 field."

Yup I was getting a bit lazy there, typing in the exact function. The & or concat with a subquery should do the trick. You got the skeleton put the body on it.
About the manual bit the where function does that for you. so you say

SELECT [Modulecode]& [usertext1] AS [Anyname]
FROM Tablename
where
Modulecode like (select distinct module code from Tablename);
I hope that helps.
IF that doesnt work read this

http://www.databasejournal.com/features/mssql/article.php/3464481/Using-a-Subquery-in-a-T-SQL-Statement.htm

Thanks - I'll check that out when I'm back in the office on Monday! :)

In the meantime I've been trying to do it using a report, based on article Q210163 at Microsoft Support.

I figured out how to do this now. You need to define a function "Concatenate", which can then be used in a query. The code is here:

http://tek-tips.com/faqs.cfm?fid=4233

good work you can do the same thing in different ways. its the way you think.