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.

imageshack.us/photo/ my-images/688/2121212e.png
​

Thanks

Sam

 

[vbaInet]

You want a Sum() then. Use IIF() to set the value to 1 or 0.5, sum that field.

 

[TheEvilSam]

kk so I am creating a new feild in the table with an expression using IIF

I used IIf([ProductID]='2' Or '4' Or '6' Or '8' Or '10',0.5,1) but all the items were 0.5, what am I doing wrong?

Thanks

Sam

 

[vbaInet]

I would expect you would know this since you're doing this at college or uni

These are the two things I'm referring to:
1. '2' is a String but 2 is a Number. I would imagine that your ProductID field is of type Number.
2. Write this in the Immediate Window and see the result:

?2='2' Or '4' Or '6' Or '8' Or '10'

So the question is, what is the proper syntax for testing with two or more conditions? Is it
[Somefield]=2 Or 8

or is it

[Somefield]=2 Or [Somefield]=8
??

 

[TheEvilSam]

Oh god, thanks....

I feel dumb, I did actually use 2 instead of '2' first but i turned to '2' just to see if it would work...

Its actually a business and It degree and we have done little IT and just one module on access... but I enjoy it when I'm not being stupid.

Thanks for the help, your a life saver

 

[vbaInet]

Oh, cool. Well good luck with your degree.

And good luck with the rest of your project!

 

[TheEvilSam]

How would I go about making it sum products with ID 1 and 2 together, 3 and 4 together, 5 and 6 together, 7 and 8 together, and 9 and 10 together because each of these is the same product, just different sizes?

thanks

Sam

 

[vbaInet]

You do the same thing as you've done for the field to sum on, i.e. creating your own ProductID for each of those grouped products using IIF. But what will you call the ProductID for 1 and 2?

By the way, you might be better off doing this in a table. Two fields one for ProductID and the other for the value, i.e. 0.5 or 1. The Null ProductID will have a value of 1. Then link this to your main table via an Outter Join. Anyway, get it working before you think about this.