Urgent help need for a salse commission.

  • Thread starter Thread starter Tim
  • Start date Start date
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.
 
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,

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...
 
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...
 
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...
 
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...
 
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...
 
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...
 
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...
 
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...
 
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...
 
Back
Top