You are here:

Using MS Access/consolidate/make unique record?

Advertisement


Question
QUESTION: How do I get Access to only give me ONE  "AF" & "CF" record  in the "Type Received" and a a list of the different "Type Tested" ?  So, you would only have 2 record rows based on "Type Received" and then AF, AF2, CF2 in the ONE cell for "Type Tested"
Is this possible?
SampleName Type Received    Type Tested
ZX-10003   AF     AF
ZX-10003   AF     AF2
ZX-10003   AF     CF
ZX-10003   CF     AF

ANSWER: The function here:

http://access.mvps.org/access/modules/mdl0008.htm

Should do what you want. You group Sample Name and Type Recieved and then use the function to concatenate the Type Tested.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------

QUESTION: Hi!

Thank you for responding; however the link you provided with the answer is coming up "unavailable" :(

Would you mind trying to re-send it?

Thank you so much!

ANSWER: Hmm I had no problem with it. Lets try again:

<A href="http://access.mvps.org/access/modules/mdl0008.htm"</a>

If this doesn't work try copying and paste the URL into your browser.

http://access.mvps.org/access/modules/mdl0008.htm

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------

QUESTION: Hi:
Okay, I have tried to follow the code you provided, but I get an error message everytime!!  I am not clear how to use the code specific to my data.  Are you able to provide me an example of code using my ONE field that I want to use? PLEASE?
Thank you so much.

Answer
First, what is the exact error you are getting? Second, Did you place the code from the link in a global module?

Once you have done that you should be able to do a group By query grouping SampleName and Type received and then concatenating Type Tested, using the function.

Can you show me the SQL for your query?

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.

Experience

I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Organizations
Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.