Group By with distinct values across records in one field (1 Viewer)

[B Kava]

I have an Access table, named Events (see attached screen shot) where I want to group 4 fields: EVENT_MONTH, EVENT_TYPE, LOCATION, and SOURCES. Summarizing just the first 3 fields is simple. But what I want to do with the SOURCES field is select distinct values across records (don’t know if I said that properly). I don’t want repeated sources in my SOURCES grouping. See Summary table for what I desire. So, using the LOCATION ‘Jupiter’ and EVENT_TYPE ‘Protests’ as an example, the summarized SOURCES field should not repeat ‘Jupiter Rising’ or ‘John Doe’. Similarly, LOCATION ‘Mars’ and EVENT_TYPE ‘Riots’ should not repeat ‘TikTok’. Hope this makes sense. Thank you.

 

[plog]

You have not normalized your data properly. That's the process of setting up your tables properly:

https://en.wikipedia.org/wiki/Database_normalization

The specific rule you broke is not having each distinct piece of data in its own field. [SOURCES] should not be a field in Events. The data in it needs to go into its own seperate table. Strutcured as such:

tblSources
SOURCE_ID, autonumber, primary key
EVENT_ID, number, foreign key back to Events
SOURCE_NAME, text, this is where all those values go

So, instead of 4 values crammed into Sources for EVENT_ID=8, you will have 4 records in ttblSources for it:

tblSources
SOURCE_ID, EVENT_ID, SOURCE_NAME
1, 8, Jupiter Rising
2, 8, Instagram
3, 8, Jane Doe
4, 8, John Doe

With that structure you will then be able to more easily achieve what you want. Specifically you will wan to use Allen Brownes ConcatRelated function:

Microsoft Access tips: Concatenate values from related records

Return a string containing the values of related records.

allenbrowne.com

Additionally, databases shouldn't have a "life", they should be able to accomodate data in perpetuity. Yours seems to only be able to work for one year. What happens to Events in 2023? Why no year value in there?

 

[arnelgp]

you can use a Query (see Query1), without the need of a "summary" table, to summarize your data.
see also Module1 for the function that does the concatenations.

 

[B Kava]

Thank you, plog and arnelgp, for your responses. Regarding normalization, this is how the data comes to me, so I have no control over the SOURCES field. I will give the concat function a whirl, after I wrap my simple brain around its seeming complexity.

 

[The_Doc_Man]

Doesn't matter how the data comes to you as long as you have the authority to DO something with it once you've got it.

You can break up that field because it uses semicolons. For parsing purposes, you can use a function called SPLIT to break apart the sources to make a "child table" (also called a dependent table).

VBA Split Function - Split String of Text into Array - Automate Excel

In this ArticleVBA Split FunctionUsing the Split Function with a Delimiter CharacterUsing a Limit Parameter in a Split FunctionUsing the Compare Parameter in a Split FunctionUsing Non-Printable Characters as the Delimiter CharacterUsing the Join Function to Reverse a SplitUsing the Split...

www.automateexcel.com

Split function (Visual Basic for Applications)

Office VBA reference topic

docs.microsoft.com

If you normalize your data, you will be amazed at what you can do with it later, and the Concatenate function mentioned by ArnelGP doesn't really care how many things you concatenate as long as you have room for the result.

If you build your list of sources, then a SELECT query using keyword "DISTINCT" will also be helpful here, as it would eliminate duplicates such as you said you wanted to do.

SQL: DISTINCT Clause

This SQL tutorial explains how to use the SQL DISTINCT clause with syntax and examples. The SQL DISTINCT clause is used to remove duplicates from the result set of a SELECT statement.

www.techonthenet.com