how to combine (sum) the dollar amounts per vendor

[eeps24]

I have a report that lists vendors and the accounts used to pay these vendors along with the total $ amount.



If you look at my screen shot you will see vendor "healthtrax international" for example. You will see there are multiple payments paid to this vendor from account 100101. We are a multi entity company, for example we have companies 101 and 120. How do I combine all those payments from account 100101 in to one total?

I want it to combine for both entities (company 101 and 120). So basically, I only want to have 1 line of account 100101 for this vendor. Ultimately I want to do this for for every vendor with every account.

To throw in a curve ball, we used those same accounts to pay other vendors, I want to keep each vendor separate. (as you can see above in my screenshot charles warner also uses 100101 but I DONT want to combine that total with healthtrax)



here is my code

SELECT dbo_APVENMAST.VENDOR_VNAME AS VENDOR_NAME, dbo_APVENMAST.VENDOR AS VENDOR_NUM, dbo_APVENMAST.VEN_CLASS, dbo_APDISTRIB.DIST_COMPANY, dbo_APDISTRIB.DIS_ACCT_UNIT AS ACCOUNTING_UNIT, dbo_GLNAMES.DESCRIPTION AS DEPT, dbo_APDISTRIB.ORIG_TRAN_AMT AS DOLLAR_AMT
FROM dbo_APVENMAST LEFT JOIN ((dbo_APDISTRIB LEFT JOIN dbo_GLMASTER ON (dbo_APDISTRIB.DIS_SUB_ACCT = dbo_GLMASTER.SUB_ACCOUNT) AND (dbo_APDISTRIB.DIS_ACCOUNT = dbo_GLMASTER.ACCOUNT) AND (dbo_APDISTRIB.DIS_ACCT_UNIT = dbo_GLMASTER.ACCT_UNIT) AND (dbo_APDISTRIB.DIST_COMPANY = dbo_GLMASTER.COMPANY)) LEFT JOIN dbo_GLNAMES ON (dbo_GLMASTER.ACCT_UNIT = dbo_GLNAMES.ACCT_UNIT) AND (dbo_GLMASTER.VAR_LEVELS = dbo_GLNAMES.VAR_LEVELS) AND (dbo_GLMASTER.COMPANY = dbo_GLNAMES.COMPANY)) ON dbo_APVENMAST.VENDOR = dbo_APDISTRIB.VENDOR
WHERE (((dbo_APVENMAST.VEN_CLASS)<>"EMP" And (dbo_APVENMAST.VEN_CLASS)<>"FRN" And (dbo_APVENMAST.VEN_CLASS)<>"ONE" And (dbo_APVENMAST.VEN_CLASS)<>"UTL") AND ((dbo_APVENMAST.CREATE_DATE)>=#4/1/2015# And (dbo_APVENMAST.CREATE_DATE)<=#12/1/2015#));

[ranman256]

remove DIST CO from the sum query. only sum on VENDOR & ACCTUNIT.

[eeps24]

If I remove DIST CO, it still shows up the same way... i.e. multipile lines for that account.

[CJ_London]

you need to group by and sum - at the moment you just have a select query

[eeps24]

ok, I was thinking that. But If I do a sum for the dollar amount. How do I tell it to keep the sums separate from other vendors? I dont want it to sum up every 100101 account for other vendors.

[June7]

GROUP BY Vendor_Number, Accounting_Unit

Consider building a report and use its Sorting & Grouping features with aggregate calcs in report and group footer sections. Report allows display of detail info as well as summary calcs.

[eeps24]

When I do Group by just for those 2 fields, access wont let me run my query. It wants me to do a group by for every field.
When I put group by for vendor_name, then it complains the next field about the same error. Is there a way I can do group by on just those two fields you mentioned or do they all have to have it?

[eeps24]

I was able to do the sum (however I had to have group by for every field). It sorted of worked, it group each sum by company. However I just want to have one line for 100101 and grand total. Not 3 lines as you see in mys creen shot. I am almost there

[eeps24]

i think i solved it...the reason it was not grouping was because the description is misspelled. Look at asset's name... After i changed it in our system to correct spelling then did my query it came out as one.

I want to thank you all for your help. great forum!

[June7]

Did you consider a report as suggested in post 6?

[eeps24]

Did you consider a report as suggested in post 6?

To answer your question, NO I did not try that "yet". I was going to ask you for more information about that. If What I wanted to do wouldn't work, then I was going to ask you for more direction regarding your idea. Not sure what you meant by report as I am already building one. I am assuming you mean to dump what I have so far in to say excel and do my calculations in there?

[June7]

No, I mean an Access report based on raw data and use its Sorting & Grouping features with aggregate calcs in textboxes in group and footer sections.

[eeps24]

I guess that would have to be my next course of action if I couldnt fix the issue. Thank you all for your help on this issue.