I have 3 data sets that are joined through a few relationships. It includes ~ 240 documents that are tied to ~260 commitments that are tied to ~150 systems. The data is in normalized tables in an Access 2007 database.
I create a query [qComSys] that gives me 3 fields (columns) [Doc ID], [Commitment], and [System]. This query returns ~1700 unique records (rows) following the 1 to many to many relationship between the 3 fields. I need to create a summary query (or report) that tells me for this [Doc ID] I have these [Commitment] that have these [Systems] (created field as plural) where the systems are a "," delimited memo field (some cases exceed the text field limits). I will also be creating a summary of this [System] has these [Commitments] in a similar fashion.
I create the "," delimited [Systems] using another query [qComSysJoin] that runs a function on the [qComSys] to create the memo field. The problem is this method yields 1700 records when I only want 260 (~1440 duplicates). Because of the memo field, I cannot use the “DISTINCT” SQL query or it truncates to the 255 characters. Everything I researched says to use 2 queries, but I cannot get it to work.
I can create a DISTINCT query and return the 260 [Commitment]/[Doc ID] items, but I cannot get the second query to only add a single instance of the summary [Systems] field without truncating. Help please.
I am also open to other methodologies such as report, etc, just trying to not create another actual table that stores the memo field.
Thanks,
-Dax