How to do something similar to excel sumifs formula

[Draszor]

Hi All,

I am new to MS Access, trying to build my first Budget database that would calculate Net Sales for few companies.

The problem description: I have a query build with 2 linked (with referantial integrity) tables, consisting of the following dimensions:
Query name: TGT_VolumeandPrices
Customer, PurchasedProductType1, PurchasedProductType2, InternationalLocal, PurchasedProductCode, PPPrice, SalesVolume2013

I have as well few tables with kinds of discounts customer can get, like the one:
Table "VolumeDiscount", with the dimensions:

PurchasedProductType1,InternationalLocal, VolumeDiscount%

Now the problem:
I would like to add another dimension to my TGT_VolumeandPrices query, called VolumeDiscount%, containing the % discount for a given PPPType1 and IntLocal values combination in each record.

If it would be excel, then I would use sumifs formula, or in case of only one condition, vlookup... but in access I am struggling to have this very simple action done.

Thanks a lot for your help.

 

[vbaInet]

I don't understand what you're trying to do but the equivalent of SUMIFS is the DSum() function:

http://www.techonthenet.com/access/functions/domain/dsum.php

 

[Draszor]

hi,

I am trying the following:
Query name: TGT_VolumeandPrices
Customer, PurchasedProductType1, PurchasedProductType2, InternationalLocal, PurchasedProductCode, PPPrice, SalesVolume2013

Values of this query:
cocacola, AS,zx,I,333,20.33$,1000
pepsico,WE,ws,I,443,44.90$,500.55
Alpha,SD,ww,L,554,30.33$,444.99
Beta, SS,rr,L,559,20.00%,222
etc

Table "VolumeDiscount", with the dimensions:
PurchasedProductType1,InternationalLocal, VolumeDiscount%

Values of this table:
AS,I,4%
WE,I,5%
AS,L,2%
SD,I,9%,
SD,L,6%

THEN I WOULD LIKE TO ADD THE DIMENSION VolumeDiscount% to my query, so that I would have the following result in its values:

Values of query after change:
cocacola, AS,zx,I,333,20.33$,1000,4% as for AS and I there is 4% in disc table
pepsico,WE,ws,I,443,44.90$,500.55,5%
Alpha,SD,ww,L,554,30.33$,444.99,6%
Beta, SS,rr,L,559,20.00%,222,0% there is no such combination in dosc table, so the discount is zero (or no discount, but zero would be better for calculation)
etc

 

[vbaInet]

All you need to do is create a query, add the two tables, join them by them via PurchaseProductType1 and InternationalLocal, then drop the fields you want to see in the query.

Read more about queries here:

http://office.microsoft.com/en-us/a...part-iii-writing-the-queries-HA010247313.aspx

When you have that working, you will notice that the last record in your TGT table will not be visible. We can fix that at a later stage.

 

[Draszor]

Thanks vbaInet for your hint. This works of course. Let me describe my 2nd part of the problem...
Each discount has at least two conditions, for instance:
if customer is International and PPType = "AS" then discount = 4% etc

the tables storing dimensions used for those conditions (In this case dimension PurchasedProductType1,InternationalLocal) are separated, and linked to the main table with TGT volumes (with enforced referential integrity). This is, because the main table, with TGT volumes contains only the basic info (for instance customer code), all secondary info (like if the customer is Local or International) are stored in other - "descriptive" tables (Customer table in this case)..

When linking the InternationalLocal dimension from the discount table, to the InternationalLocal dimension of customers table (I want all records from cust table to be displayed, and only the ones that exist from discount table), and then doing the same kind of link for PurchasedProductType1 dimension, I get the "ambiguous outer joins" error...

I understand that the problem is with the order such a query is run (giving different results) but how then should I add the discount. This I ment when saying that in excel, when having such a situation, I would solve it easy with sumif or vlookup formula...

Thanks a lot for any comments

 

[vbaInet]

It depends on how you've joined them up. If you have two joins linking two tables, you must change the join type on both joins to the same direction for it to work.

Perhaps you can upload a cut-down version of your db so I can see the join that's failing. See the FAQs for how:

http://www.access-programmers.co.uk/forums/forumdisplay.php?f=23

 

[Draszor]

Hi,
I attach the database (changed the data, but all the dimensions and links stay as in my original one) and the error, when trying to add DedVDT containing Volume Discount.

The idea is, to have the % discounts in the same form for each record, as prices and volumes..

 

[vbaInet]

By just looking at the screenshot I can already see that you're trying to perform an impossible join. It's one or the other. Perhaps you could rethink your strategy.

 

[Draszor]

... HMM the issue is that customer can get a discount because of some features belonging to "being a customer" (For instance if he is within the purchase group, or international, then he gets higher one), but as well because of some features belonging to "products he takes" (for instance, if he takes certain type of products then he gets a higher discount.

Only the combination of those two issues (kept in separate tables, because their nature is different) does decide about the discount.

What would be the idea of different strategy? The one I can only imagine is to connect the Customer and Product table, but this is not logical...
Would you have an idea of something different?

Thanks a lot in advance for any hint

 

[vbaInet]

Well, what would be easier is for you to show me in Excel, the records you hope to achieve and I will look at your tables and devise the best route.

 

[Draszor]

Hi vbaInet,

Sorry, I was travelling a bit. I attach the excel with results I would like to get in AllData Query. The query columns I fight with in Access are shown in excel S:Y range - with proper excel formulas to the Ded Tables (kept in Ded_TABLES sheet).

I hope this is more clear now what I would like to get...

Thanks a lot for any help on it...

Best Regards
Draszor

 

[vbaInet]

Much better. All you're doing is adding summing a field based on certain criteria. So you need three queries, one each for VD, SOP and CoP_Sh.

You need to use a Totals Query to get your desired output. See the following link:

http://office.microsoft.com/en-us/access-help/sum-data-by-using-a-query-HA010096310.aspx

 

[Draszor]

Hi,
thanks a lot for the hint. I tried to apply the article hints to my DB, but was not successful with this. Would it be possible you add just one discount to my query with all data so that I could have a good base for adding other?
Thanks a lot in advance...

 

[vbaInet]

Much better if you showed me what you tried and we take it from there.

 

[Draszor]

OK,
So point by point:
1. You write I need each query for each discount - but what should be in this query? I was trying to add all the fields from tables that contain the fields present in a given discount table (I attach it).
2. constructing the queries in the way described above, I added it to AllData Query. Technically to is ok - meaning, no error, but sum of 2013_VOLUME in the AddData query is 226 009, but in the original TGT_VOLUME table it is 6 126 247, so I miss a lot of data in the query...

What am I doing wrong?

 

[vbaInet]

All I see is a spreadsheet with a highlighted column and no calculations so I'm not sure what you're expecting me to make of it?

Upload your db so I can see what queries you've made. And tell me exactly which ones are problems.

 

[Draszor]

Hi,

Hmm... I think I have attached it in the ACCESS.zip archive. At least I can see and open the database from attached zip

 

[Draszor]

Dear Colleagues,

Is there anyone able to help me with this simple problem? After some discussion, I still do not know how to do what I want, which is:
- to have in my AllData Query of attached ACCESS DB the additional columns showed in attached excel in columns S:Y (I show the logic I want to use when putting them to AllData Query).

I know this problem is simple, I am sorry for this but I am not experienced Access user wishing to use it more. I hope the idea of this forum is as well to increase MS Access usage... in my case this mean to help in solving this simple issue.

Thanks a lot for any answer that helps me to solve this issue finally.

//Draszor

 

[Draszor]

Hi,

Anyone able to give me some more help on this one?

I am fighting hard not to change with my project to excel (where my problem is solved by one simple formula "SUMIFS") but my deadline is close...

Thanks a lot for any help on this ... I was thinking very simple issue