Count, but some as Halves

[theevilsam]

Hey.



So this is for my dissertation and it is coming allong pretty well. almost finished it now xD

Anyway, I'm making a pub Epos system in access and its all OK, except now I have reached the stock control.

To get the query (Stock = Stock - Sales) I need to do a count query, which is easy enough, though the problem with pubs is they often serve half pints...

Is there any way to get the count SQL function to count certain ProductID's as 0.5?

This is a part of the table, and Product ID 2,4,6,8 and 10 are all relating to half pints.




Thanks

Sam

[June7]

Create a field with an expression that will return 1 or .5 then Sum that field.

Served: IIf(ProductID < 11 And ProductID Mod 2 = 0, 0.5, 1)

[theevilsam]

sorry, I don't understand, you mean in the table have another field like StockVolume and have that as 1 or 0.5 and sum that instead of count it? that would work

Is that what you meant?

Sam

[June7]

You could have a field where you input a value .5 or 1. However, what I suggested was a calculation in a query. Since you have Access 2010 you could have this as a Calculated field in table. Otherwise, do it in a query. The calculation option is fine as long as the half pint productID's never change.

[theevilsam]

I have tried that expression but it wont work. I get the error code "cannot be used in a calculated column...

The Product ID's never change.

Any Idea why it wont let the expression work? I think It may be the MOD bit for some reason...

I tried doing it in a round about way aswell with

IIf(ProductID = 2 or 4 or 6 or 8 or 10, 0.5, 1) but it returned all values as 0.5

Thanks

Sam

[June7]

I just tested the Mod in a calculated field and in a query. True, does not work in table but does work in query.

This will work in table:
IIf(ProductID=2 Or ProductID=4 Or ProductID=6 Or ProductID=8 Or ProductID=10, 0.5, 1)