Excluding items from a totalled amount

[AdamO]

Hello,

I am having problems writing a query.

In my table I have fields which look up products sold. These are named:

ServiceName1, ServiceName2…to…ServiceName10

I then have prices for each Service. These fields are:

SalesServName1PriceUsed, SalesServName2PriceUsed….to… SalesServName10PriceUsed

I can work out a Net Sales Amount as follows:

NetSaleAmt: [SalesServName1PriceUsed]+[SalesServName2PriceUsed]+[SalesServName3PriceUsed]+[SalesServName4PriceUsed]+[SalesServName5PriceUsed]+[SalesServName6PriceUsed]+[SalesServName7PriceUsed]+[SalesServName8PriceUsed]+[SalesServName9PriceUsed]+[SalesServName10PriceUsed]

However, I would like another expression which removes from NetSaleAmt four services which may or may not be listed in ServiceName1, ServiceName2…to…ServiceName10.

I do not want to enter criteria as I need all records shown. I am looking to write an expression which I am having problems with.

Any help would be very much appreciated.

Thank you

 

[dcx693]

You could use a series of four IIF functions in your query. Take the total from where you total the 10 prices and subtract out the results of the four IIf functions. The IIf functions will have something like IIf(service appears, price). That way, you'll subtract out the price only if the service appears. Now, the hard part is how to come up with the service appears part of that equation. How do you determine that in your database.

I will comment though, that your database is not optimally set up for these types of calculations. If you had these fields in your table: servicename and serviceprice and perhaps a transactionID or something like that, then you wouldn't need to deal with 10+4 fields.