Grouping Question

[McArthurGDM]

Hi,



I'm still relatively new to MS Access so I'd prefer to stay away from coding if at all possible.

Anyways, my problem concerns how I can "consolidate" or "group" my primary key information. First off, I have the vlookup function built into my table (with respect to what I'm querying) for certain fields. I created my own custom primary key and when I run my query, I get a variety of combinations of my data. In other words, my tables are no longer in vlookup mode but run through all the respective possible combinations. The problem is, I have multiple records for the same primary key often. I want to "group" or "consolidate" all my records under one particular primary key at a time.

I realize that the grouping function (that Sum symbol) is probably used for this, though I have only been able to manipulate numeric data with this - not text.

Hope I was clear enough,
Thanks.

[June7]

Do you mean DLookup? Why are you using that?

I don't really understand what you mean by 'no longer in vlookup mode' nor 'run through all the respective possible combinations'.

Aggregate (Totals) GROUP BY query is intended for summarizing numeric data but can be used to return Max or Min values of text fields.

I suspect what you want requires VBA. Does this describe what you want: http://allenbrowne.com/func-concat.html

Could better advise if you posted example of raw data and desired result.

[McArthurGDM]

Sorry for being confusing with my wording. I believe that the link that you sent will answer my question. "Concatenation" is the word that I was looking for - not 'group'. Thanks for the help.

Does Access have something built into it to perform said function?

[McArthurGDM]

Actually, here, this might explain it better.


I'm sorry, I used the 'Look Up & Relationship' function in my tables when defining particular fields (notice the .Value naming).

I have the following fields (all from the same table) that I am running through my query:


Ref#ID (primary key)
Update
Source
Announced
Effective
IssuingAgencies.Value
Entities
DivisionsImpacted.Value
Stakeholders.Value
Content.Value

As you can see from the picture below, I show my query and my output. You can see that the Ref#ID gets repeated over and over again with combinations of data inputted from my Lookup & Relationship function. I do want to concatenate the information into only 1 respective record for 1 unique Ref#ID.

Perhaps you can help me set this up? I tried using the link you sent and I'm not sure how to implement it.

Thanks.

[McArthurGDM]

Hopefully this explains it better.

[June7]

Oh crud! Multi-value fields. I never use multi-value fields. Are you saving ID values or descriptive text in the multi-value fields?

If you don't reference the .Value property of the multi-value fields, don't you get the desired 'grouping'?

I also never set lookups in tables. http://access.mvps.org/access/lookupfields.htm

[McArthurGDM]

My lookup values are text-based. It works well for what I need it for. There are too many possible combinations for each Ref#ID to do it manually. As you can see, when I run the query, it 'normalizes' the data and breaks it down to its respective combinatoric possibilities. My problem is 'bringing it back' together when I run the code.

[June7]

Still a bit confused. You want to expand the multi-value fields so you can apply filter criteria to the multi-value field data then you want to collapse the selected records? Allen Browne's code accommodates multi-value field but I have never tested with multi-value fields.

Is the Ref#ID unique in MaintT (why does query show MainT_1)? Calling the function in query might be like:

SELECT Ref#ID, ConcatRelated("Content", "QueryName", "Ref#ID='" & [Ref#ID] & "'") AS ConcatContent FROM MainT;

Call the function for each field you want to concatenate.

If you are saving the text, this is inefficient data storage because text values I see are quite long. If using up the Access 2GB file size limit is not a concern, then probably okay.

Advise no spaces or special characters/punctuation (underscore is exception) in naming convention. Instead of # should use Num or No or Number.

[orange]

Further to the comments from June7, this part sets up a red flag to me
--My lookup values are text-based. It works well for what I need it for.

There are some people ( I know of 1) who use multivalued fields, but most database developers will tell you to avoid it. Use traditional design constructs. I think you are doing yourself a disservice with the quote above.

Here's a suggestion:

Write a 5-6 line description in plain English of your "business" to identify WHAT your proposed database is intended to support. Identify the Things that will become Tables and the attributes that will be fields in the Tables. Work through this tutorial that will help you with the overall process and Normalization. You'll save yourself time and have a solid design in the long run. Then use the concepts from the tutorial to do your own database. Also, set up some test data to vet or test your model. If you run into any anomalies, determine what is causing the anomaly --bad data, bad model??? reconcile the issue and test the model again-- repeat until your model supports your business requirements.

Depends on whether you want to learn database/Access or just get something out the door ASAP.

Good luck with whatever you decide.

[McArthurGDM]

I agree with what you are saying and I understand the setbacks by using multi-valued fields. I can not forsee ever upgrading to SQL server, so conversion to that would not cause an issue. I see myself potentially using SharePoint, but that's not going to cause an issue.

The issue with my records are that they are, in fact, multi-valued.

So for one particular primary key, I will have multiple values under a variety of fields. Constructing the raw data for this is a nightmare as far as I can tell.

Perhaps there is a different approach that I could take - but I just can't see it.

[McArthurGDM]

I don't know why I can't get it to work. The code compiles and there is no issue with that - so that's nice. I assume it's the calling of the function that I'm messing up somehow.

My query is "SearchQ". I am inputting values into a form (SearchF) and then calling on those values in a query (as you can see from the picture above) using LIKE criteria. It is worth noting that the fields that are created when I run my query are called (similarly) MainT.Content.Value, etc...

I'm not sure how to input this using these factors. Would you do it through SQL view or a Control Source? What steps do you take either way?

Thanks! I'm so close to figuring out a problem a few days and headaches old and it's much appreciated.

[June7]

What does 'can't get it to work' mean - error message, wrong results, nothing happens?
If you want to provide db for analysis and testing, follow instructions at bottom of my post.