Query- sum combining diff. vars in a field

Crizznitch

Board Regular
Joined
Nov 18, 2004
Messages
120
I am trying to write a query that sums up a field "Premium" and is grouped by another field "Vendor".

Code:
SELECT      Vendor, Sum(Premium) AS SumPrem
FROM        tblSales
GROUP BY Vendor
My problem is that in my table of about 100 vendors, there is one vendor ABC that has been split into 'ABC' and 'XXABC', and I would like for the premium for both of those to be summed up as just 'ABC'. Is there a way that I can set this up in the query? I've been trying to use case, but I think I have my syntax wrong.
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Try using a calculated field instead of the Vendor.

In the query that feeds the report, create a field like...

VendorCalc:Iif([Vendor] Like "*ABC*","ABC",[Vendor])

In the report, use VendorCalc instead of Vendor. That will let you see all premiums for the combined vendors.

Denis
 
Upvote 0
Thanks Denis, I will definitely try that when I get to work tomorrow. Would that also work for multiple variables that you would want changed?
 
Upvote 0
Yep. You will need to use a nested Iif to catch all the cases, if you have more than one vendor split.

Denis
 
Upvote 0
rather than trying to code your way through a solution I think it's better to use the database to solve your problems for you. The simple solution to this problem is to just add a new column to your table.

<CENTER><TABLE cellSpacing=0 align=center cellpadding=2px width=50% ><TR ><TD CLASS="AccTBMain" border-left=1px solid colSpan= 4 ><TABLE width="100%" align=left VALIGN="TOP" ><TR ><TD CLASS="AccTBInner" align=Left ><font color="White">tblVendor : Table</font></TD><TD CLASS="AccTBInner" align=right >Access 2003</TD></TR></TABLE></TD></TR><TR ><TD CLASS="AccHDRLeftElem" >
</TD><TD CLASS="AccHDRMain" align=left >VendorID</TD><TD CLASS="AccHDRMain" align=left >VendorName</TD><TD CLASS="AccHDRMain" align=left >VendorParent</TD></TR><TR ><TD ID="currRec" CLASS="AccHDRLeftElem" >
</TD><TD CLASS="AccDataElem" >1</TD><TD CLASS="AccDataElem" >ABC</TD><TD CLASS="AccDataElem" >ABC</TD></TR><TR ><TD ID="currRec" CLASS="AccHDRLeftElem" >
</TD><TD CLASS="AccDataElem" >2</TD><TD CLASS="AccDataElem" >XXABC</TD><TD CLASS="AccDataElem" >ABC</TD></TR><TR ><TD ID="IE" CLASS="AccIEOnlyMain" colSpan= 4 ><TABLE width="100%" align=left VALIGN="TOP" ><TR ><TD CLASS="AccIEOnlyInnerRight" > Record: </TD><TD CLASS="AccButton" >
</TD><TD CLASS="AccIEOnlyData" > 1 </TD><TD CLASS="AccButton" >
</TD><TD CLASS="AccIEOnlyInnerLeft" > of 2</TD></TR></TABLE></TD></TR><TR ><TD CLASS="AccIEOnlyMain" colSpan= 4 ><TABLE cellSpacing=0 cellPadding=0 width="100%" align=left VALIGN="TOP" ><TR ><TD ID="Data840236843" CLASS="AccInfoBarInnerRight" >tblVendor</TD><TD ID="Info840236843" CLASS="AccInfoBarData" > Record Count: 2</TD></TR></TABLE></TD></TR><TR ><TD CLASS="tpkrow" colSpan = 4 ><CENTER><font size = 1>Access HTML Add-in provided by theprimarykey.com</font></CENTER></TD></TR></TABLE></CENTER>

By using the database you don't have to recode whenever your data changes, you just simply update your table.

hth,
Giacomo
 
Upvote 0
Thank you for your help guys! I finally got it working the way I wanted.

Giacomo, I wanted to go with your method before I gave myself this headache. Unfortunately, I am not the person who updates this table, and it would have caused other issues had I started changing things.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,184
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top