Conditional calculation in query

[nmoakeh]

I need some help! I'm definitely not the most versed in Access so
forgive me if this seems simple. Basically, I need to output on a form
and report (assuming done via query) a calculation based on a
condition. What I have is a table that contains a StartDte and CompDte
with a query that calculates the difference in dates called [Days
Served]. I need to display a field that takes [Days Served] and
outputs [PaymentDue] with the following criteria:
If [Days Served] > 3, then [PaymentDue]=(([Days Served] - 3) * 25) + 27
but, if [Days Served <=3, then [PaymentDue]=([Days Served] * 9)

I'm not sure how to go about this... I've tried researching this and
trying different things but I haven't found something that gives me the
correct output.





Thanks in advance for your help!

 

[nmoakeh]

Wayne,

Thanks so much that worked perfectly!

I also have another question I just ran into my boss needs. He wants
to be able to have an a Secretary select X amount of records (randomly)
from one table into another, so that when it's selected it's moved from
the main table into a different room

for instance,

Table: Eligible Jurors (contains a pool of 3500 records)
secretary needs to select any number she wants any time, and have it
moved into a different table based on 3 different rooms (1, 2, or 3)
when she selects those records. So I have a table for each room. How
do i move them based on her selections?

Thanks again, and happy holidays!

PaymentDate: IIf([Days Served]>3,((([Days Served]-3)*25)+27),([Days Served]*9))




--
Buon Natale, Happy Chritmas.

Wayne
Manchester, England.
Scusate,ma il mio Inglese fa schiffo
Percio se non ci siamo capiti, mi mandate un
messagio e provero di spiegarmi meglio.

I need some help! I'm definitely not the most versed in Access so
forgive me if this seems simple. Basically, I need to output on a form
and report (assuming done via query) a calculation based on a
condition. What I have is a table that contains a StartDte and CompDte
with a query that calculates the difference in dates called [Days
Served]. I need to display a field that takes [Days Served] and
outputs [PaymentDue] with the following criteria:
If [Days Served] > 3, then [PaymentDue]=(([Days Served] - 3) * 25) + 27
but, if [Days Served <=3, then [PaymentDue]=([Days Served] * 9)

I'm not sure how to go about this... I've tried researching this and
trying different things but I haven't found something that gives me the
correct output.





Thanks in advance for your help!

 

[nmoakeh]

Thanks again Wayne... however it's not exactly what I need. I already
have a query to randomly select a set 5000 records from an original
dataset. What is needed is when that query is run, a prompt to pop-up
and ask the secretary "Please enter the number of people you would like
to select." rather than the static 5000. Current Code is as follows:

INSERT INTO tblRandom
SELECT TOP 5000 *
FROM JS_Temp3
ORDER BY Rnd(SelID);

(that's the first part - separate from the second part)

The second part is for another query (because it is run at a different
occassion) to prompt to update their Room Assignment based on their
current room assignment is a Null entry. The second prompt would read
"Please Select a Room to place them into." Rooms are predefined so it
would be nice for that prompt to be a drop-down list, where after she
chooses the room, it updates their information in that given table. I
don't have any query that has worked to show you

Hope that makes sense. I've been stuck on this so any help is greatly
appreciated! Thanks again for your help, and I hope you had a great
Holiday!

There are quite a few method to get random records but one of the simplest
ones I have seen can be found at

http://www.mvps.org/access/queries/qry0011.htm

By simple I mean the method is simple - not the original thought that went
in to making it by Joe Foster.

Good luck


--
Buon Natale, Happy Chritmas.

Wayne
Manchester, England.
Scusate,ma il mio Inglese fa schiffo
Percio se non ci siamo capiti, mi mandate un
messagio e provero di spiegarmi meglio.

Wayne,

Thanks so much that worked perfectly!

I also have another question I just ran into my boss needs. He wants
to be able to have an a Secretary select X amount of records (randomly)
from one table into another, so that when it's selected it's moved from
the main table into a different room

for instance,

Table: Eligible Jurors (contains a pool of 3500 records)
secretary needs to select any number she wants any time, and have it
moved into a different table based on 3 different rooms (1, 2, or 3)
when she selects those records. So I have a table for each room. How
do i move them based on her selections?

Thanks again, and happy holidays!

PaymentDate: IIf([Days Served]>3,((([Days Served]-3)*25)+27),([Days Served]*9))




--
Buon Natale, Happy Chritmas.

Wayne
Manchester, England.
Scusate,ma il mio Inglese fa schiffo
Percio se non ci siamo capiti, mi mandate un
messagio e provero di spiegarmi meglio.



:

I need some help! I'm definitely not the most versed in Access so
forgive me if this seems simple. Basically, I need to output on a form
and report (assuming done via query) a calculation based on a
condition. What I have is a table that contains a StartDte and CompDte
with a query that calculates the difference in dates called [Days
Served]. I need to display a field that takes [Days Served] and
outputs [PaymentDue] with the following criteria:
If [Days Served] > 3, then [PaymentDue]=(([Days Served] - 3) * 25) + 27
but, if [Days Served <=3, then [PaymentDue]=([Days Served] * 9)

I'm not sure how to go about this... I've tried researching this and
trying different things but I haven't found something that gives me the
correct output.





Thanks in advance for your help!