Record Consolidation (1 Viewer)

[HeelNGville]

Can someone offer guidance here? I have a table that stores liquidated inventory records. I want to design a make table query to dump selected records into a new table. Some of the more important fields in the table are as follows:



Vendor Location Units Type
9998 A004B106 2 Home Decor
A278 A004B106 5 Home Improvement
AA16 A004B106 1 Cleaning
AH55 A004B106 3 Personal Care
X910 A004B106 2 Personal Care
N004 A004B106 2 Rugs
A278 A004B106 7 Home Improvement

Ideally, I want the query to only retrieve 1 record for each location. For the listing above, I would like the data to be displayed as such:


Vendor Location Units Type
Mixed A004B106 22 Home Improvement

Basically, If the vendor count a 1 location is >1 I want to change the vendor to "Mixed", sum all units assigned to the location and populate the "Type" field with the description containing the greatest unit count. If I use the "First" or "Last" function in the "Type" field, the returned result is based on the alphabet positioning, so that option would not work for me. Any guidance would be greatly appreciated!

Regards,

Rick

 

[ByteMyzer]

Try this Union Query (change Table1 to your actual Table Name, and any field names as appropriate):

SELECT 'Mixed' AS Vendor, T1.Location, Sum(T1.Units) AS Units, (SELECT TOP 1 T2.Type FROM Table1 AS T2 WHERE T2.Location=T1.Location ORDER BY Units DESC AS Type
FROM Table1 AS T1
WHERE T1.Location IN (SELECT T3.Location FROM Table1 AS T3 GROUP BY T3.Location HAVING Count(T3.Vendor)>1
GROUP BY T1.Location;

UNION SELECT T4.*
FROM Table1 AS T4
WHERE T4.Location NOT IN (SELECT T5.Location FROM Table1 AS T5 GROUP BY T5.Location HAVING Count(T5.Vendor)>1;