Export to Excel Without Duplic - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Export to Excel Without Duplic

Shia G.

2 years ago

Hello everyone,

I have a table in Access called GroupsT where I store all the information about customers and the groups they belong to. Each customer can belong to multiple group types, and users can add new group types as needed. Here's an example of how my data is structured: Look at Example1 of how my data is structured:

Now, I want to export this information to Excel. Instead of duplicating customer names for each group type, I would like to present it in a single row per customer with a column for each group type they belong to. Example2 is how I want the output to look:

My questions are:

Is it possible to achieve this kind of output using an SQL query in Access?
If so, what would the SQL query look like?
Thank you for your help!

	Shia G. OP @Reply 2 years ago

	Ludwig Willems @Reply 2 years ago
to follow :)

	Shia G. OP @Reply 2 years ago
waiting thanks

	Richard Rost @Reply 2 years ago
Yes, this is definitely possible, but you're going to need something like a recordset to loop through all the possible groups for each customer. Then you can create a query based on that, or you could use something like my DLookupPlus function, which is in the Code Vault.

	Shia G. OP @Reply 2 years ago
Thanks Richard can you give me a brief example how the loop will look?

	Richard Rost @Reply 2 years ago
It's all explained on that page I linked you too. I cover creating the function and the recordset loop in detail in Access Developer 17. If you just want to use my function, you'll just call it in a query.

	John Davy @Reply 2 years ago
Hi Shia If you have been watching Richard's videos you may have noticed that you probably should have one table for Customers and another table for Groups. John

	Richard Rost @Reply 2 years ago
Looks like he does.

	John Campbell @Reply 2 years ago
Why don't you just do a cross tab query?

	Shia G. OP @Reply 2 years ago
I have two tables, one for customers and one for the groups they belong to. I know how to use a DLookupPlus to get all the groups info into one field, but my question is how can I split each group into a separate column (as shown in image)? Also, keep in mind that the user can add any group to the list, so I need to have a dynamic crosstab SQL solution (if this makes sense).

	Richard Rost @Reply 2 years ago
Yeah, that's what you would use a Crosstab for. What am I missing here? Sorry. Not enough coffee yet.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View