Better Way?

[rgf593]

Hello, first timer here.

Surely there is a more elegant way to achieve what i've done here? In excel i would have just sumifs the "Used" column on condition of "array" and "pool" fields. Now i have to name the 4 pool field options in the union query (sata, 10k, 15k, efd).

Should i do something in VB?

SELECT ARRAY, "SATA" AS Pool, Sum(Used) AS Used
FROM [EDG Thin]
WHERE ((([EDG Thin].[Pool]) Like '*SATA*'))


GROUP BY [EDG Thin].ARRAY, "SATA";

UNION

SELECT ARRAY, "10K" AS Pool, Sum(Used) AS Used
FROM [EDG Thin]
WHERE ((([EDG Thin].[Pool]) Like '*10K*'))
GROUP BY [EDG Thin].ARRAY, "10K";

UNION

...repeated for "15K" and "EFD"

Thanks!

[RayMilhon]

Not sure I understand: in the POOL field of "EDG THIN" ARE THERE MULTIPLE WAYS OF PUTTING IN "15k" If not then

SELECT ARRAY, Pool, Sum(Used) AS Used
FROM [EDG Thin]
GROUP BY [EDG Thin].ARRAY, pool;

Would work.

If there are you might want to create a VB Function Like

Public Function getpool(ByRef strVal As String) As String
     If instr(strval,"15K") <> 0 Then
           getpool = "15K"
    Else If instr(strval,"10K")<>0 Then
          getpool = "10K"
    Else If instr(strval,"EFD") <> 0 Then
          getpool = "EFD"
   Else If Instr(strval,"SATA") <> 0 Then
         getpool = "SATA"
  End if
        
End Function

Then your query would be
SELECT ARRAY, getPool([pool], Sum(Used) AS Used
FROM [EDG Thin]
GROUP BY [EDG Thin].ARRAY, getpool([pool]);

Bear in mind that this second query would take a bit longer as it will have to call the getpool function twice for each record.

[June7]

Why UNION?

SELECT ARRAY, Pool, Sum(Used) As SumUsed
FROM [EDG Thin]
GROUP BY ARRAY, Pool;

Or build a report using Grouping & Sorting with aggregate calcs. This will allow display of detail records and summary data.