conditional SUM

[hishamaboualfa]

Hello Guys

please have alook at the attached, I need to summarize all the expenses into two fields, Total ESM & Total Supplier, appreciate ur help.

 

[plog]

You would use a DSum(http://www.techonthenet.com/access/functions/domain/dsum.php), make sure you use the correct criteria in the 3rd argument.

 

[arnelgp]

are those combobox from different fields? if so your controls source:

=iif([fieldCombo1="Debit ESM",[D/O],0)+IIF([fieldCombo2]="Debit ESM",[Freight],0)+IIF([fieldCombo3]="Debit ESM",[Bayan],0), ....

i hope you figure it out.

 

[plog]

Arn raises a good point, but not with enough criticism.

If those are different fields of the same table--they shouldn't be. You shouldnt have a field in your Expense table named 'Frieght', you shouldn't have a field in your Expense table named 'Bayan', you shouldn't have...etc.

You shouldn't store values in table nor field names. 'Frieght', 'Bayan' etc. should be values you can choose for an ExpenseType field, not names of fields themselves.

 

[hishamaboualfa]

perfectly worked, specially i added the same in the table to help in the annual reports

 

[hishamaboualfa]

are those combobox from different fields? if so your controls source:

=iif([fieldCombo1="Debit ESM",[D/O],0)+IIF([fieldCombo2]="Debit ESM",[Freight],0)+IIF([fieldCombo3]="Debit ESM",[Bayan],0), ....

i hope you figure it out.

is there is any way to contact you for another support

 

[arnelgp]

you can use the private message here in this forum.

 

[hishamaboualfa]

thanks alot, well, i have Query and a report based on this query, but since i included values from another table for each record, it duplicate the counting, so the question is how i can put expression to allow the query to count the unique value of this field only.

 

[arnelgp]

can you show me the query string.

 

[hishamaboualfa]

here u go, attached

 

[Minty]

I shudder when I see tables called 2016 and invoices-16...

This is very badly stored data, and while Arnelgp is fixing your immediate issues he's not helping you sort out your data into a long term usable format.

As Plog stated you need to store your data correctly, then you won't be jumping through hoops to get your results easily and correctly.

 

[hishamaboualfa]

just to be more specified, please have alook at the attached record, what i want is at quarter basis i can make report showing for each supplier how many shipments and how many invoices per inventory per mode of shipment and the expenses, this records shows two inventories for one shipment, so can the report shows mixed inventories, like SOP+STO, till the moment i believe this is not possible due to the nature of our business, but since i am new in access i thought experts can help

 

[arnelgp]

on the field inventory ref, you can just show the First in your query.

 

[hishamaboualfa]

I shudder when I see tables called 2016 and invoices-16...

This is very badly stored data, and while Arnelgp is fixing your immediate issues he's not helping you sort out your data into a long term usable format.

As Plog stated you need to store your data correctly, then you won't be jumping through hoops to get your results easily and correctly.

sort of u r right, but we have load of records per year, do u mean to store all the years in one table?

 

[Minty]

Normally yes - what happens if you need to report over 2 years or 18 months...
How many records are you talking about 1000 , 10000, 100000 ?
Access deals well up to the 100000's record level, especially if the data is indexed & stored efficiently.