Urgent help need for a salse commission.

T

Tim

Hi everyone,

I need a help on my query. Here is what I want to do:

If the (salesIDs are A or C) and the sum of Amount is
greater than 50, then the sales person get $1.5 from the
invoice otherwise get $1.

I have a query like this but it doesn't work. Could
anyone tell me how to fix it?

SELECT Sales.Invoice, IIf([SalesID]=("A" Or "C") And Sum
([Amount])>5000,1.5,1) AS Com, Sales.date
FROM Sales
GROUP BY Sales.Invoice, Sales.date
HAVING (((Sales.date) Between #8/1/2003# And #8/31/2003#));

Sales Table:

Invoice SalesID Amount date
1 a 100 8/15/03
2 a 10 8/1/03
3 b 15 8/30/03
4 c 51 7/31/03

Thanks in advaince.

Tim.
 
D

Duane Hookom

For starters...
IIf([SalesID]=("A" Or "C") should be IIf([SalesID]="A" Or [SalesID]="C")
I think your HAVING should be a WHERE.
Are you sure you want to group by Sales.Date?
 
T

Tim

Duane,

I tried what you said, but it didn't work. Could you help
me again?

I am looking for the output like this:

Invoice SalesID Com Date
1 a 1.5 8/15/03
2 a 1.5 8/1/03
3 b 1 8/30/03

Thanks a lot.

Tim.
-----Original Message-----
For starters...
IIf([SalesID]=("A" Or "C") should be IIf([SalesID]="A" Or [SalesID]="C")
I think your HAVING should be a WHERE.
Are you sure you want to group by Sales.Date?

--
Duane Hookom
MS Access MVP


Tim said:
Hi everyone,

I need a help on my query. Here is what I want to do:

If the (salesIDs are A or C) and the sum of Amount is
greater than 50, then the sales person get $1.5 from the
invoice otherwise get $1.

I have a query like this but it doesn't work. Could
anyone tell me how to fix it?

SELECT Sales.Invoice, IIf([SalesID]=("A" Or "C") And Sum
([Amount])>50,1.5,1) AS Com, Sales.date
FROM Sales
GROUP BY Sales.Invoice, Sales.date
HAVING (((Sales.date) Between #8/1/2003# And #8/31/2003#));

Sales Table:

Invoice SalesID Amount date
1 a 100 8/15/03
2 a 10 8/1/03
3 b 15 8/30/03
4 c 51 7/31/03

Thanks in advaince.

Tim.
Click to expand...


.
Click to expand...
 
D

Duane Hookom

What happened to SalesID c? How does Sum(Amount) fit in? Do you consider
only the sum of the current record or are you summing all the amounts for
the month?

--
Duane Hookom
MS Access MVP


Tim said:
Duane,

I tried what you said, but it didn't work. Could you help
me again?

I am looking for the output like this:

Invoice SalesID Com Date
1 a 1.5 8/15/03
2 a 1.5 8/1/03
3 b 1 8/30/03

Thanks a lot.

Tim.
-----Original Message-----
For starters...
IIf([SalesID]=("A" Or "C") should be IIf([SalesID]="A" Or [SalesID]="C")
I think your HAVING should be a WHERE.
Are you sure you want to group by Sales.Date?

--
Duane Hookom
MS Access MVP


Tim said:
Hi everyone,

I need a help on my query. Here is what I want to do:

If the (salesIDs are A or C) and the sum of Amount is
greater than 50, then the sales person get $1.5 from the
invoice otherwise get $1.

I have a query like this but it doesn't work. Could
anyone tell me how to fix it?

SELECT Sales.Invoice, IIf([SalesID]=("A" Or "C") And Sum
([Amount])>50,1.5,1) AS Com, Sales.date
FROM Sales
GROUP BY Sales.Invoice, Sales.date
HAVING (((Sales.date) Between #8/1/2003# And #8/31/2003#));

Sales Table:

Invoice SalesID Amount date
1 a 100 8/15/03
2 a 10 8/1/03
3 b 15 8/30/03
4 c 51 7/31/03

Thanks in advaince.

Tim.
Click to expand...


.
Click to expand...
Click to expand...
 
T

Tim

Duane,

C will not show on the output because I set the date range
between 8/1/03 to 8/31/03.

I tried so many different way to get the out put I posted,
but in vain. Do you have any idea?

Thanks a lot.

Tim.

-----Original Message-----
What happened to SalesID c? How does Sum(Amount) fit in? Do you consider
only the sum of the current record or are you summing all the amounts for
the month?

--
Duane Hookom
MS Access MVP


Tim said:
Duane,

I tried what you said, but it didn't work. Could you help
me again?

I am looking for the output like this:

Invoice SalesID Com Date
1 a 1.5 8/15/03
2 a 1.5 8/1/03
3 b 1 8/30/03

Thanks a lot.

Tim.
-----Original Message-----
For starters...
IIf([SalesID]=("A" Or "C") should be IIf([SalesID]="A" Or [SalesID]="C")
I think your HAVING should be a WHERE.
Are you sure you want to group by Sales.Date?

--
Duane Hookom
MS Access MVP


Hi everyone,

I need a help on my query. Here is what I want to do:

If the (salesIDs are A or C) and the sum of Amount is
greater than 50, then the sales person get $1.5 from the
invoice otherwise get $1.

I have a query like this but it doesn't work. Could
anyone tell me how to fix it?

SELECT Sales.Invoice, IIf([SalesID]=("A" Or "C") And Sum
([Amount])>50,1.5,1) AS Com, Sales.date
FROM Sales
GROUP BY Sales.Invoice, Sales.date
HAVING (((Sales.date) Between #8/1/2003# And #8/31/2003#));

Sales Table:

Invoice SalesID Amount date
1 a 100 8/15/03
2 a 10 8/1/03
3 b 15 8/30/03
4 c 51 7/31/03

Thanks in advaince.

Tim.



.
Click to expand...
Click to expand...


.
Click to expand...
 
D

Duane Hookom

Try:
SELECT Sales.Invoice, IIf(([SalesID]="A" Or [SalesID]="C") And
[Amount]>50,1.5,1) AS Com, Sales.date
FROM Sales
WHERE Sales.date Between #8/1/2003# And #8/31/2003#
GROUP BY Sales.Invoice, Sales.date;


--
Duane Hookom
MS Access MVP


Tim said:
Duane,

C will not show on the output because I set the date range
between 8/1/03 to 8/31/03.

I tried so many different way to get the out put I posted,
but in vain. Do you have any idea?

Thanks a lot.

Tim.

-----Original Message-----
What happened to SalesID c? How does Sum(Amount) fit in? Do you consider
only the sum of the current record or are you summing all the amounts for
the month?

--
Duane Hookom
MS Access MVP


Tim said:
Duane,

I tried what you said, but it didn't work. Could you help
me again?

I am looking for the output like this:

Invoice SalesID Com Date
1 a 1.5 8/15/03
2 a 1.5 8/1/03
3 b 1 8/30/03

Thanks a lot.

Tim.

-----Original Message-----
For starters...
IIf([SalesID]=("A" Or "C") should be IIf([SalesID]="A"
Or [SalesID]="C")
I think your HAVING should be a WHERE.
Are you sure you want to group by Sales.Date?

--
Duane Hookom
MS Access MVP


Hi everyone,

I need a help on my query. Here is what I want to do:

If the (salesIDs are A or C) and the sum of Amount is
greater than 50, then the sales person get $1.5 from the
invoice otherwise get $1.

I have a query like this but it doesn't work. Could
anyone tell me how to fix it?

SELECT Sales.Invoice, IIf([SalesID]=("A" Or "C") And Sum
([Amount])>50,1.5,1) AS Com, Sales.date
FROM Sales
GROUP BY Sales.Invoice, Sales.date
HAVING (((Sales.date) Between #8/1/2003# And
#8/31/2003#));

Sales Table:

Invoice SalesID Amount date
1 a 100 8/15/03
2 a 10 8/1/03
3 b 15 8/30/03
4 c 51 7/31/03

Thanks in advaince.

Tim.



.
Click to expand...


.
Click to expand...
Click to expand...
 
T

Tim

Duane,

The query didn't include one of the condition: Sum[Amount]
50,1.5,1). In other word, if sum of A is smaller than
Click to expand...
50, then A's com will be 1 for all invoices.

I tried to put this condition in the query, then I got
error message. Do you have any idea?

SELECT Sales.Invoice, IIf(([SalesID]="A" Or [SalesID]="C")
And Sum[Amount]>50,1.5,1) AS Com, Sales.date FROM Sales
WHERE Sales.date Between #8/1/2003# And #8/31/2003#
GROUP BY Sales.Invoice, Sales.date;


Thanks.

Tim.



-----Original Message-----
Try:
SELECT Sales.Invoice, IIf(([SalesID]="A" Or [SalesID] ="C") And
[Amount]>50,1.5,1) AS Com, Sales.date
FROM Sales
WHERE Sales.date Between #8/1/2003# And #8/31/2003#
GROUP BY Sales.Invoice, Sales.date;


--
Duane Hookom
MS Access MVP


Tim said:
Duane,

C will not show on the output because I set the date range
between 8/1/03 to 8/31/03.

I tried so many different way to get the out put I posted,
but in vain. Do you have any idea?

Thanks a lot.

Tim.

-----Original Message-----
What happened to SalesID c? How does Sum(Amount) fit
Click to expand...
in?
Do you consider
only the sum of the current record or are you summing
Click to expand...
all
the amounts for
the month?

--
Duane Hookom
MS Access MVP


Duane,

I tried what you said, but it didn't work. Could you help
me again?

I am looking for the output like this:

Invoice SalesID Com Date
1 a 1.5 8/15/03
2 a 1.5 8/1/03
3 b 1 8/30/03

Thanks a lot.

Tim.

-----Original Message-----
For starters...
IIf([SalesID]=("A" Or "C") should be IIf([SalesID] ="A"
Or [SalesID]="C")
I think your HAVING should be a WHERE.
Are you sure you want to group by Sales.Date?

--
Duane Hookom
MS Access MVP


Hi everyone,

I need a help on my query. Here is what I want to do:

If the (salesIDs are A or C) and the sum of Amount is
greater than 50, then the sales person get $1.5
Click to expand...
from
the
invoice otherwise get $1.

I have a query like this but it doesn't work. Could
anyone tell me how to fix it?

SELECT Sales.Invoice, IIf([SalesID]=("A" Or "C")
Click to expand...
And
Sum
([Amount])>50,1.5,1) AS Com, Sales.date
FROM Sales
GROUP BY Sales.Invoice, Sales.date
HAVING (((Sales.date) Between #8/1/2003# And
#8/31/2003#));

Sales Table:

Invoice SalesID Amount date
1 a 100 8/15/03
2 a 10 8/1/03
3 b 15 8/30/03
4 c 51 7/31/03

Thanks in advaince.

Tim.



.



.
Click to expand...
Click to expand...


.
Click to expand...
 
D

Duane Hookom

You may need to do this in two queries. One to filter and summarize your
data and the other to apply the "Com" value.

--
Duane Hookom
MS Access MVP


Tim said:
Duane,

The query didn't include one of the condition: Sum[Amount]
50,1.5,1). In other word, if sum of A is smaller than
Click to expand...
50, then A's com will be 1 for all invoices.

I tried to put this condition in the query, then I got
error message. Do you have any idea?

SELECT Sales.Invoice, IIf(([SalesID]="A" Or [SalesID]="C")
And Sum[Amount]>50,1.5,1) AS Com, Sales.date FROM Sales
WHERE Sales.date Between #8/1/2003# And #8/31/2003#
GROUP BY Sales.Invoice, Sales.date;


Thanks.

Tim.



-----Original Message-----
Try:
SELECT Sales.Invoice, IIf(([SalesID]="A" Or [SalesID] ="C") And
[Amount]>50,1.5,1) AS Com, Sales.date
FROM Sales
WHERE Sales.date Between #8/1/2003# And #8/31/2003#
GROUP BY Sales.Invoice, Sales.date;


--
Duane Hookom
MS Access MVP


Tim said:
Duane,

C will not show on the output because I set the date range
between 8/1/03 to 8/31/03.

I tried so many different way to get the out put I posted,
but in vain. Do you have any idea?

Thanks a lot.

Tim.


-----Original Message-----
What happened to SalesID c? How does Sum(Amount) fit in?
Do you consider
only the sum of the current record or are you summing all
the amounts for
the month?

--
Duane Hookom
MS Access MVP


Duane,

I tried what you said, but it didn't work. Could you
help
me again?

I am looking for the output like this:

Invoice SalesID Com Date
1 a 1.5 8/15/03
2 a 1.5 8/1/03
3 b 1 8/30/03

Thanks a lot.

Tim.

-----Original Message-----
For starters...
IIf([SalesID]=("A" Or "C") should be IIf([SalesID] ="A"
Or [SalesID]="C")
I think your HAVING should be a WHERE.
Are you sure you want to group by Sales.Date?

--
Duane Hookom
MS Access MVP


Hi everyone,

I need a help on my query. Here is what I want to
do:

If the (salesIDs are A or C) and the sum of Amount is
greater than 50, then the sales person get $1.5 from
the
invoice otherwise get $1.

I have a query like this but it doesn't work. Could
anyone tell me how to fix it?

SELECT Sales.Invoice, IIf([SalesID]=("A" Or "C") And
Sum
([Amount])>50,1.5,1) AS Com, Sales.date
FROM Sales
GROUP BY Sales.Invoice, Sales.date
HAVING (((Sales.date) Between #8/1/2003# And
#8/31/2003#));

Sales Table:

Invoice SalesID Amount date
1 a 100 8/15/03
2 a 10 8/1/03
3 b 15 8/30/03
4 c 51 7/31/03

Thanks in advaince.

Tim.



.



.
Click to expand...


.
Click to expand...
Click to expand...
 
T

Tim

Duane,

Could you show me how to do it?

Thanks a lot.

Tim.
-----Original Message-----
You may need to do this in two queries. One to filter and summarize your
data and the other to apply the "Com" value.

--
Duane Hookom
MS Access MVP


Tim said:
Duane,

The query didn't include one of the condition: Sum [Amount]
50,1.5,1). In other word, if sum of A is smaller than
Click to expand...
50, then A's com will be 1 for all invoices.

I tried to put this condition in the query, then I got
error message. Do you have any idea?

SELECT Sales.Invoice, IIf(([SalesID]="A" Or [SalesID] ="C")
And Sum[Amount]>50,1.5,1) AS Com, Sales.date FROM Sales
WHERE Sales.date Between #8/1/2003# And #8/31/2003#
GROUP BY Sales.Invoice, Sales.date;


Thanks.

Tim.



-----Original Message-----
Try:
SELECT Sales.Invoice, IIf(([SalesID]="A" Or [SalesID] ="C") And
[Amount]>50,1.5,1) AS Com, Sales.date
FROM Sales
WHERE Sales.date Between #8/1/2003# And #8/31/2003#
GROUP BY Sales.Invoice, Sales.date;


--
Duane Hookom
MS Access MVP


Duane,

C will not show on the output because I set the date range
between 8/1/03 to 8/31/03.

I tried so many different way to get the out put I posted,
but in vain. Do you have any idea?

Thanks a lot.

Tim.


-----Original Message-----
What happened to SalesID c? How does Sum(Amount) fit in?
Do you consider
only the sum of the current record or are you
Click to expand...
summing
all
the amounts for
the month?

--
Duane Hookom
MS Access MVP


Duane,

I tried what you said, but it didn't work. Could you
help
me again?

I am looking for the output like this:

Invoice SalesID Com Date
1 a 1.5 8/15/03
2 a 1.5 8/1/03
3 b 1 8/30/03

Thanks a lot.

Tim.

-----Original Message-----
For starters...
IIf([SalesID]=("A" Or "C") should be IIf
Click to expand...
([SalesID]
="A"
Or [SalesID]="C")
I think your HAVING should be a WHERE.
Are you sure you want to group by Sales.Date?

--
Duane Hookom
MS Access MVP


Hi everyone,

I need a help on my query. Here is what I want to
do:

If the (salesIDs are A or C) and the sum of Amount is
greater than 50, then the sales person get $1.5 from
the
invoice otherwise get $1.

I have a query like this but it doesn't work. Could
anyone tell me how to fix it?

SELECT Sales.Invoice, IIf([SalesID]=("A"
Click to expand...
Or "C")
And
Sum
([Amount])>50,1.5,1) AS Com, Sales.date
FROM Sales
GROUP BY Sales.Invoice, Sales.date
HAVING (((Sales.date) Between #8/1/2003# And
#8/31/2003#));

Sales Table:

Invoice SalesID Amount date
1 a 100 8/15/03
2 a 10 8/1/03
3 b 15 8/30/03
4 c 51 7/31/03

Thanks in advaince.

Tim.



.



.



.
Click to expand...
Click to expand...


.
Click to expand...
 
D

Duane Hookom

First Query "qtotSales":
SELECT Sales.Invoice, SalesID, Sum(Amount) as SumOfAmt, Sales.Date
Where Sales.date Between #8/1/2003# And #8/31/2003#
FROM Sales
GROUP BY Sales.Invoice, Sales.ID, Sales.date;

Next query:
Select Invoice, SalesID, IIf( (SalesID="A" or SalesID="C") And
SumOfAmt>50,1.5,1.0) as Com, Date
FROM qtotSales;


--
Duane Hookom
MS Access MVP


Tim said:
Duane,

Could you show me how to do it?

Thanks a lot.

Tim.
-----Original Message-----
You may need to do this in two queries. One to filter and summarize your
data and the other to apply the "Com" value.

--
Duane Hookom
MS Access MVP


Tim said:
Duane,

The query didn't include one of the condition: Sum [Amount]
50,1.5,1). In other word, if sum of A is smaller than
50, then A's com will be 1 for all invoices.

I tried to put this condition in the query, then I got
error message. Do you have any idea?

SELECT Sales.Invoice, IIf(([SalesID]="A" Or [SalesID] ="C")
And Sum[Amount]>50,1.5,1) AS Com, Sales.date FROM Sales
WHERE Sales.date Between #8/1/2003# And #8/31/2003#
GROUP BY Sales.Invoice, Sales.date;


Thanks.

Tim.




-----Original Message-----
Try:
SELECT Sales.Invoice, IIf(([SalesID]="A" Or [SalesID]
="C") And
[Amount]>50,1.5,1) AS Com, Sales.date
FROM Sales
WHERE Sales.date Between #8/1/2003# And #8/31/2003#
GROUP BY Sales.Invoice, Sales.date;


--
Duane Hookom
MS Access MVP


Duane,

C will not show on the output because I set the date
range
between 8/1/03 to 8/31/03.

I tried so many different way to get the out put I
posted,
but in vain. Do you have any idea?

Thanks a lot.

Tim.


-----Original Message-----
What happened to SalesID c? How does Sum(Amount) fit
in?
Do you consider
only the sum of the current record or are you summing
all
the amounts for
the month?

--
Duane Hookom
MS Access MVP


Duane,

I tried what you said, but it didn't work. Could you
help
me again?

I am looking for the output like this:

Invoice SalesID Com Date
1 a 1.5 8/15/03
2 a 1.5 8/1/03
3 b 1 8/30/03

Thanks a lot.

Tim.

-----Original Message-----
For starters...
IIf([SalesID]=("A" Or "C") should be IIf ([SalesID]
="A"
Or [SalesID]="C")
I think your HAVING should be a WHERE.
Are you sure you want to group by Sales.Date?

--
Duane Hookom
MS Access MVP


Hi everyone,

I need a help on my query. Here is what I want to
do:

If the (salesIDs are A or C) and the sum of
Amount is
greater than 50, then the sales person get $1.5
from
the
invoice otherwise get $1.

I have a query like this but it doesn't work.
Could
anyone tell me how to fix it?

SELECT Sales.Invoice, IIf([SalesID]=("A" Or "C")
And
Sum
([Amount])>50,1.5,1) AS Com, Sales.date
FROM Sales
GROUP BY Sales.Invoice, Sales.date
HAVING (((Sales.date) Between #8/1/2003# And
#8/31/2003#));

Sales Table:

Invoice SalesID Amount date
1 a 100 8/15/03
2 a 10 8/1/03
3 b 15 8/30/03
4 c 51 7/31/03

Thanks in advaince.

Tim.



.



.



.
Click to expand...


.
Click to expand...
Click to expand...
 
T

Tim

Duane,

Thanks for your help.

Tim.
-----Original Message-----
First Query "qtotSales":
SELECT Sales.Invoice, SalesID, Sum(Amount) as SumOfAmt, Sales.Date
Where Sales.date Between #8/1/2003# And #8/31/2003#
FROM Sales
GROUP BY Sales.Invoice, Sales.ID, Sales.date;

Next query:
Select Invoice, SalesID, IIf( (SalesID="A" or SalesID="C") And
SumOfAmt>50,1.5,1.0) as Com, Date
FROM qtotSales;


--
Duane Hookom
MS Access MVP


Tim said:
Duane,

Could you show me how to do it?

Thanks a lot.

Tim.
-----Original Message-----
You may need to do this in two queries. One to filter
Click to expand...
and
summarize your
data and the other to apply the "Com" value.

--
Duane Hookom
MS Access MVP


Duane,

The query didn't include one of the condition: Sum [Amount]
50,1.5,1). In other word, if sum of A is smaller than
50, then A's com will be 1 for all invoices.

I tried to put this condition in the query, then I got
error message. Do you have any idea?

SELECT Sales.Invoice, IIf(([SalesID]="A" Or [SalesID] ="C")
And Sum[Amount]>50,1.5,1) AS Com, Sales.date FROM Sales
WHERE Sales.date Between #8/1/2003# And #8/31/2003#
GROUP BY Sales.Invoice, Sales.date;


Thanks.

Tim.




-----Original Message-----
Try:
SELECT Sales.Invoice, IIf(([SalesID]="A" Or [SalesID]
="C") And
[Amount]>50,1.5,1) AS Com, Sales.date
FROM Sales
WHERE Sales.date Between #8/1/2003# And #8/31/2003#
GROUP BY Sales.Invoice, Sales.date;


--
Duane Hookom
MS Access MVP


Duane,

C will not show on the output because I set the date
range
between 8/1/03 to 8/31/03.

I tried so many different way to get the out put I
posted,
but in vain. Do you have any idea?

Thanks a lot.

Tim.


-----Original Message-----
What happened to SalesID c? How does Sum(Amount) fit
in?
Do you consider
only the sum of the current record or are you summing
all
the amounts for
the month?

--
Duane Hookom
MS Access MVP


Duane,

I tried what you said, but it didn't work.
Click to expand...
Could
you
help
me again?

I am looking for the output like this:

Invoice SalesID Com Date
1 a 1.5 8/15/03
2 a 1.5 8/1/03
3 b 1 8/30/03

Thanks a lot.

Tim.

-----Original Message-----
For starters...
IIf([SalesID]=("A" Or "C") should be IIf ([SalesID]
="A"
Or [SalesID]="C")
I think your HAVING should be a WHERE.
Are you sure you want to group by Sales.Date?

--
Duane Hookom
MS Access MVP


Hi everyone,

I need a help on my query. Here is what I want to
do:

If the (salesIDs are A or C) and the sum of
Amount is
greater than 50, then the sales person get $1.5
from
the
invoice otherwise get $1.

I have a query like this but it doesn't work.
Could
anyone tell me how to fix it?

SELECT Sales.Invoice, IIf([SalesID]=("A" Or "C")
And
Sum
([Amount])>50,1.5,1) AS Com, Sales.date
FROM Sales
GROUP BY Sales.Invoice, Sales.date
HAVING (((Sales.date) Between #8/1/2003# And
#8/31/2003#));

Sales Table:

Invoice SalesID Amount date
1 a 100 8/15/03
2 a 10 8/1/03
3 b 15 8/30/03
4 c 51 7/31/03

Thanks in advaince.

Tim.



.



.



.



.
Click to expand...
Click to expand...


.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need help on an query 2
Need help with a complex data set 12
Group query - Access XP 1
work out how many trucks came in between the hours of 9am and 3pm 1
Query Help 0
Emulating older pivot table "engine" in Excel 2007? Need help... 0
Urgent! Subquery help needed! 8
Total Summed Count of Numeric Repeat (Paired /Double Instance) Criteria 2

Top