UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> A Real Concatenation Problem, Access 2016    
 
   
perrymans
post Feb 11 2019, 10:13 PM
Post#1



Posts: 260
Joined: 12-November 03
From: Virginia Beach, VA


Coming back to Access now after years of just doing Index(Match) in Excel and I am seriously rusty. VBA doing well, but conceptualizing queries is not going well.

I need to combine a bunch of datasets (I've already imported the various spreadsheets into a normalized table) andout one concatenated export where I need to identify the answers which are not the most common. Sound confusing? Ask the 14 queries I just did to accomplish this and thought; I think I need help!


Dataset:
Clothes...Color....Owner
================
Shirt......Red......Bill
Shirt......Red......Sue
Shirt......Green...Ted
Shirt......Red......Dan
Shoes....Black....Bill
Shoes....Brown...Sue
Shoes....Black....Ted
Shoes....White...Bill


Desired Result:
Clothes...Color
=====================
Shirt.......Red, but Ted has Green
Shoes.....Black, but Sue has Brown, but Bill has White


So you can see the most common shirt color was Red, so I identified that and only added the detail that Ted has a Green shirt.

Same with the shoes, but there were two values that *weren't* the most common, so I listed those.


It seems like it should be so easy to do, yet I am Grouping, Counting, Maxing, Grouping, Counting, Maxing an absolutely ridiculous approach here.


Any help would be appreciated! There is more than on "color" column and I don't want to be making 14 queries to handle each one!


Thanks. Sean.

Go to the top of the page
 
MadPiet
post Feb 11 2019, 10:37 PM
Post#2



Posts: 2,828
Joined: 27-February 09



This is the core summary query you want, I think:

SELECT ClothingType, Color, COUNT(*) AS Freq
FROM #Cloze
GROUP BY ClothingType, Color;

then the rest is a bit of a hassle, because you'd need to resort to VBA to effectively lump together the "but <person> has <color>" strings. Can't remember who wrote it, but there's a function here somewhere that does that kind of concatenation. Pretty sure you have to use VBA for it.
This post has been edited by MadPiet: Feb 11 2019, 10:56 PM
Go to the top of the page
 
June7
post Feb 11 2019, 11:30 PM
Post#3



Posts: 300
Joined: 25-January 16



Might be thinking of Allen Browne's ConcatRelated function http://allenbrowne.com/func-concat.html

Example dataset shows only one color column so what do you mean by there is more than one?







This post has been edited by June7: Feb 11 2019, 11:31 PM

--------------------
To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.
Go to the top of the page
 
MadPiet
post Feb 11 2019, 11:44 PM
Post#4



Posts: 2,828
Joined: 27-February 09



From what I understood (and I could be wrong), but there's the most common (Style, Color) combination, and then there are the others. and the "others" get concatenated.
Go to the top of the page
 
perrymans
post Feb 12 2019, 09:22 AM
Post#5



Posts: 260
Joined: 12-November 03
From: Virginia Beach, VA


@MadPiet that is definitely much easier!

Agree on the Allen Brown concat function, been using that one for all kinds of stuff for years.


Is there a way to provide a criteria for "Not Max"? Because there could be more than one item which is not the most common, i need to pass all of the "Not Max" values to the Concat code.


Using an updated Dataset example for @June7:

Clothes...Color....Size....Owner
===================
Shirt......Red......M........Bill
Shirt......Red......L.........Sue
Shirt......Green...M........Ted
Shirt......Red......M........Dan
Shoes....Black....10.......Bill
Shoes....Brown...10.......Sue
Shoes....Black....11.......Ted
Shoes....Black....10.......John


Result from @MadPiet's help:

Clothes...Color....Size....Freq
===================
Shirt......Red......M........2 <--meaning two owners have Red Medium shirts (Bill and Dan)
Shirt......Red......L.........1
Shirt......Green...M........1
Shoes....Black....10.......3 <--meaning three owners have black size 10 shoes (Bill, Sue, John)
Shoes....Black....11.......1


So then I'd feed these lines to Allen's code:

Clothes...Color....Size....Freq
===================
Shirt......Red......L........1
Shirt......Green...M.......1
Shoes....Black....11......1




Making this feed possible is why I'm hoping there is a "Not Max" method. Otherwise I have to use a query to pull out all the Max values, then an unmatched query to return the ones that were "Not Max".


Thanks again! Sean.

Go to the top of the page
 
June7
post Feb 12 2019, 02:38 PM
Post#6



Posts: 300
Joined: 25-January 16



So you want to group by color and size? You should have included example output for this data. Look at your revised sample source data again. Sue does not have black shoes size 10. They are brown.

Afraid this is too complicated for me to tackle. Good luck.



--------------------
To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.
Go to the top of the page
 
perrymans
post Feb 12 2019, 03:33 PM
Post#7



Posts: 260
Joined: 12-November 03
From: Virginia Beach, VA


Yeah, that "Brown" for Sue was a typo I missed.



I can figure out the concat part, do you know of a way to return "Not Max" value (meaning every other value besides the Max)?


Thanks. Sean.
Go to the top of the page
 
June7
post Feb 12 2019, 04:22 PM
Post#8



Posts: 300
Joined: 25-January 16



Perhaps with a VBA custom function or DMax() expression. Whatever the approach, expect slow performance with a very large dataset.





--------------------
To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.
Go to the top of the page
 
projecttoday
post Feb 12 2019, 08:32 PM
Post#9


UtterAccess VIP
Posts: 10,605
Joined: 10-February 04
From: South Charleston, WV


What if there's a tie for first place?

--------------------
Robert Crouser
Go to the top of the page
 
MadPiet
post Feb 12 2019, 09:29 PM
Post#10



Posts: 2,828
Joined: 27-February 09



I think this is going to be a serious pain.

This will return all groupings with fewer than 2 members:

CODE
SELECT ClothingItem
    , Color
    , [Size]
    , COUNT(*) AS Qty
FROM dbo.Threadz
GROUP BY ClothingItem
    , Color
    , [Size]
HAVING COUNT(*)>1;


You could get the values with a single member by changing the HAVING clause to HAVING COUNT(*)=1 but then you'd have to concatenate those somehow. I think in SQL Server, you can use STUFF(), but Access doesn't have that. You might have to write a VBA function to do it. <Shudder>
Go to the top of the page
 
perrymans
post Feb 17 2019, 02:54 PM
Post#11



Posts: 260
Joined: 12-November 03
From: Virginia Beach, VA


Sorry for the delay in responding, was on travel to rainy San Diego.

MadPiet, This was a huge help since I was now able to get it done using only 5 queries vs 15.

Thanks! Sean
Go to the top of the page
 
tina t
post Feb 17 2019, 04:17 PM
Post#12



Posts: 5,746
Joined: 11-November 10
From: SoCal, USA


QUOTE
...was on travel to rainy San Diego.

well, you sure picked your travel time, hon, lol! this is the rainiest winter SoCal has had in years. just a few years ago, the Inland Empire got rain twice - that i know of - between January and April. ;) tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    22nd February 2019 - 09:28 AM