iff function in ms access

[Guest]

I am writing database for counting credits.
Need to use an iff function in a query

The result of the function should be:
If sum of credits >50, then 50, otherwise the result should be a sum of
credits
Help, Thanks

 

[Guest]

In the Query create another field

NewFieldName: IIf([sum of credits] >50, 50 , [sum of credits])

 

[Guest]

Got it working but every time I execute the query it displays a box asking to
enter parameter value for [sum of credits]. If I leave it blank the query is
executed fine. How to execute the Query without asking me for the parameter
every time?
Thanks,
JPol

In the Query create another field

NewFieldName: IIf([sum of credits] >50, 50 , [sum of credits])

--
Good Luck
BS"D

I am writing database for counting credits.
Need to use an iff function in a query

The result of the function should be:
If sum of credits >50, then 50, otherwise the result should be a sum of
credits
Help, Thanks

 

[Guest]

In your first post you mantioned something about
sum of credits >50

So in the example I provided you with , I assumed that the field name is
[sum of credits], if this field doesn't exist it will rompt you to enter this
as parameter.
Change this name to the name of the field in the table, that you want to
create this IIf on.

--
Good Luck
BS"D

Got it working but every time I execute the query it displays a box asking to
enter parameter value for [sum of credits]. If I leave it blank the query is
executed fine. How to execute the Query without asking me for the parameter
every time?
Thanks,
JPol

In the Query create another field

NewFieldName: IIf([sum of credits] >50, 50 , [sum of credits])

--
Good Luck
BS"D

I am writing database for counting credits.
Need to use an iff function in a query

The result of the function should be:
If sum of credits >50, then 50, otherwise the result should be a sum of
credits
Help, Thanks

 

[Guest]

This is how it looks:
In Query there is a field name: [numberOfCredits], since there could be many
credits for the particular date, I group this field by :[date] and Sum
[numberOfCredits]. When I run this Query the field [SumNumberOfCredits] is
created by Query. But [SumNumberOfCredits] may not be more than 50, because
MAX credits per day may only be 50. Therefore I created another field in
Query DailyNumberOfCredits: to do the following - If sum number of credits is

50, then 50, otherwise show the true sum of number of credits.

DailyNumberOfCredits:iif([SumNumberOfCredits]>50,50, [SumNumberOfCredits])

I then am getting annoyed with the POP-UP.
I am new to Access but I have a thick book and willing to learn.
JPol

In your first post you mantioned something about
sum of credits >50

So in the example I provided you with , I assumed that the field name is
[sum of credits], if this field doesn't exist it will rompt you to enter this
as parameter.
Change this name to the name of the field in the table, that you want to
create this IIf on.

--
Good Luck
BS"D

Got it working but every time I execute the query it displays a box asking to
enter parameter value for [sum of credits]. If I leave it blank the query is
executed fine. How to execute the Query without asking me for the parameter
every time?
Thanks,
JPol

In the Query create another field

NewFieldName: IIf([sum of credits] >50, 50 , [sum of credits])

--
Good Luck
BS"D


:

I am writing database for counting credits.
Need to use an iff function in a query

The result of the function should be:
If sum of credits >50, then 50, otherwise the result should be a sum of
credits
Help, Thanks

 

[Guest]

In that case, create a query that return the sum for each date (as you did
already)

Then create a query based on the query above, and then use th iif on the new
sum field name created in the query above.
If you need help, please post the SQL of the above query

--
Good Luck
BS"D

This is how it looks:
In Query there is a field name: [numberOfCredits], since there could be many
credits for the particular date, I group this field by :[date] and Sum
[numberOfCredits]. When I run this Query the field [SumNumberOfCredits] is
created by Query. But [SumNumberOfCredits] may not be more than 50, because
MAX credits per day may only be 50. Therefore I created another field in
Query DailyNumberOfCredits: to do the following - If sum number of credits is

50, then 50, otherwise show the true sum of number of credits.

DailyNumberOfCredits:iif([SumNumberOfCredits]>50,50, [SumNumberOfCredits])

I then am getting annoyed with the POP-UP.
I am new to Access but I have a thick book and willing to learn.
JPol

In your first post you mantioned something about
sum of credits >50

So in the example I provided you with , I assumed that the field name is
[sum of credits], if this field doesn't exist it will rompt you to enter this
as parameter.
Change this name to the name of the field in the table, that you want to
create this IIf on.

--
Good Luck
BS"D

Got it working but every time I execute the query it displays a box asking to
enter parameter value for [sum of credits]. If I leave it blank the query is
executed fine. How to execute the Query without asking me for the parameter
every time?
Thanks,
JPol
:

In the Query create another field

NewFieldName: IIf([sum of credits] >50, 50 , [sum of credits])

--
Good Luck
BS"D


:

I am writing database for counting credits.
Need to use an iff function in a query

The result of the function should be:
If sum of credits >50, then 50, otherwise the result should be a sum of
credits
Help, Thanks

 

[Guest]

Here is the SQL:
SELECT [Srtudents Table].First_Name, [Srtudents Table].Last_Name, [Demerits
Table].Demerit_Date, Sum([Demerits Table].Demerit_Quantity) AS
SumOfDemerit_Quantity,
IIf([sumOfDemerit_Quantity]>50,50,[sumOfDemerit_Quantity]) AS demeritsByDay
FROM [Srtudents Table] RIGHT JOIN [Demerits Table] ON [Srtudents
Table].Student_Nr = [Demerits Table].Student_Nr
GROUP BY [Srtudents Table].First_Name, [Srtudents Table].Last_Name,
[Demerits Table].Demerit_Date,
IIf([sumOfDemerit_Quantity]>50,50,[sumOfDemerit_Quantity])
ORDER BY [Srtudents Table].Last_Name;

This the actual Query for counting demerits. I get the pop-up.

In that case, create a query that return the sum for each date (as you did
already)

Then create a query based on the query above, and then use th iif on the new
sum field name created in the query above.
If you need help, please post the SQL of the above query

--
Good Luck
BS"D

This is how it looks:
In Query there is a field name: [numberOfCredits], since there could be many
credits for the particular date, I group this field by :[date] and Sum
[numberOfCredits]. When I run this Query the field [SumNumberOfCredits] is
created by Query. But [SumNumberOfCredits] may not be more than 50, because
MAX credits per day may only be 50. Therefore I created another field in
Query DailyNumberOfCredits: to do the following - If sum number of credits is

50, then 50, otherwise show the true sum of number of credits.

DailyNumberOfCredits:iif([SumNumberOfCredits]>50,50, [SumNumberOfCredits])

I then am getting annoyed with the POP-UP.
I am new to Access but I have a thick book and willing to learn.
JPol

In your first post you mantioned something about
sum of credits >50

So in the example I provided you with , I assumed that the field name is
[sum of credits], if this field doesn't exist it will rompt you to enter this
as parameter.
Change this name to the name of the field in the table, that you want to
create this IIf on.

--
Good Luck
BS"D


:

Got it working but every time I execute the query it displays a box asking to
enter parameter value for [sum of credits]. If I leave it blank the query is
executed fine. How to execute the Query without asking me for the parameter
every time?
Thanks,
JPol
:

In the Query create another field

NewFieldName: IIf([sum of credits] >50, 50 , [sum of credits])

--
Good Luck
BS"D


:

I am writing database for counting credits.
Need to use an iff function in a query

The result of the function should be:
If sum of credits >50, then 50, otherwise the result should be a sum of
credits
Help, Thanks

 

[Guest]

Query No 1 - name it GetSumPerDemerit:

SELECT [Srtudents Table].First_Name, [Srtudents Table].Last_Name, [Demerits
Table].Demerit_Date, Sum([Demerits Table].Demerit_Quantity) AS
SumOfDemerit_Quantity
FROM [Srtudents Table] RIGHT JOIN [Demerits Table] ON [Srtudents
Table].Student_Nr = [Demerits Table].Student_Nr
GROUP BY [Srtudents Table].First_Name, [Srtudents Table].Last_Name,
[Demerits Table].Demerit_Date
=============================================
Query No 2

SELECT GetSumPerDemerit.* ,
IIf([sumOfDemerit_Quantity]>50,50,[sumOfDemerit_Quantity]) AS demeritsByDay
FROM GetSumPerDemerit ORDER BY Last_Name

--
Good Luck
BS"D

Here is the SQL:
SELECT [Srtudents Table].First_Name, [Srtudents Table].Last_Name, [Demerits
Table].Demerit_Date, Sum([Demerits Table].Demerit_Quantity) AS
SumOfDemerit_Quantity,
IIf([sumOfDemerit_Quantity]>50,50,[sumOfDemerit_Quantity]) AS demeritsByDay
FROM [Srtudents Table] RIGHT JOIN [Demerits Table] ON [Srtudents
Table].Student_Nr = [Demerits Table].Student_Nr
GROUP BY [Srtudents Table].First_Name, [Srtudents Table].Last_Name,
[Demerits Table].Demerit_Date,
IIf([sumOfDemerit_Quantity]>50,50,[sumOfDemerit_Quantity])
ORDER BY [Srtudents Table].Last_Name;

This the actual Query for counting demerits. I get the pop-up.

In that case, create a query that return the sum for each date (as you did
already)

Then create a query based on the query above, and then use th iif on the new
sum field name created in the query above.
If you need help, please post the SQL of the above query

--
Good Luck
BS"D

This is how it looks:
In Query there is a field name: [numberOfCredits], since there could be many
credits for the particular date, I group this field by :[date] and Sum
[numberOfCredits]. When I run this Query the field [SumNumberOfCredits] is
created by Query. But [SumNumberOfCredits] may not be more than 50, because
MAX credits per day may only be 50. Therefore I created another field in
Query DailyNumberOfCredits: to do the following - If sum number of credits is
50, then 50, otherwise show the true sum of number of credits.

DailyNumberOfCredits:iif([SumNumberOfCredits]>50,50, [SumNumberOfCredits])

I then am getting annoyed with the POP-UP.
I am new to Access but I have a thick book and willing to learn.
JPol


:

In your first post you mantioned something about
sum of credits >50

So in the example I provided you with , I assumed that the field name is
[sum of credits], if this field doesn't exist it will rompt you to enter this
as parameter.
Change this name to the name of the field in the table, that you want to
create this IIf on.

--
Good Luck
BS"D


:

Got it working but every time I execute the query it displays a box asking to
enter parameter value for [sum of credits]. If I leave it blank the query is
executed fine. How to execute the Query without asking me for the parameter
every time?
Thanks,
JPol
:

In the Query create another field

NewFieldName: IIf([sum of credits] >50, 50 , [sum of credits])

--
Good Luck
BS"D


:

I am writing database for counting credits.
Need to use an iff function in a query

The result of the function should be:
If sum of credits >50, then 50, otherwise the result should be a sum of
credits
Help, Thanks

 

[Guest]

I am getting a syntax error in query 1. I copied and pasted the SQL statement
JPol

Query No 1 - name it GetSumPerDemerit:

SELECT [Srtudents Table].First_Name, [Srtudents Table].Last_Name, [Demerits
Table].Demerit_Date, Sum([Demerits Table].Demerit_Quantity) AS
SumOfDemerit_Quantity
FROM [Srtudents Table] RIGHT JOIN [Demerits Table] ON [Srtudents
Table].Student_Nr = [Demerits Table].Student_Nr
GROUP BY [Srtudents Table].First_Name, [Srtudents Table].Last_Name,
[Demerits Table].Demerit_Date
=============================================
Query No 2

SELECT GetSumPerDemerit.* ,
IIf([sumOfDemerit_Quantity]>50,50,[sumOfDemerit_Quantity]) AS demeritsByDay
FROM GetSumPerDemerit ORDER BY Last_Name

--
Good Luck
BS"D

Here is the SQL:
SELECT [Srtudents Table].First_Name, [Srtudents Table].Last_Name, [Demerits
Table].Demerit_Date, Sum([Demerits Table].Demerit_Quantity) AS
SumOfDemerit_Quantity,
IIf([sumOfDemerit_Quantity]>50,50,[sumOfDemerit_Quantity]) AS demeritsByDay
FROM [Srtudents Table] RIGHT JOIN [Demerits Table] ON [Srtudents
Table].Student_Nr = [Demerits Table].Student_Nr
GROUP BY [Srtudents Table].First_Name, [Srtudents Table].Last_Name,
[Demerits Table].Demerit_Date,
IIf([sumOfDemerit_Quantity]>50,50,[sumOfDemerit_Quantity])
ORDER BY [Srtudents Table].Last_Name;

This the actual Query for counting demerits. I get the pop-up.

In that case, create a query that return the sum for each date (as you did
already)

Then create a query based on the query above, and then use th iif on the new
sum field name created in the query above.
If you need help, please post the SQL of the above query

--
Good Luck
BS"D


:

This is how it looks:
In Query there is a field name: [numberOfCredits], since there could be many
credits for the particular date, I group this field by :[date] and Sum
[numberOfCredits]. When I run this Query the field [SumNumberOfCredits] is
created by Query. But [SumNumberOfCredits] may not be more than 50, because
MAX credits per day may only be 50. Therefore I created another field in
Query DailyNumberOfCredits: to do the following - If sum number of credits is
50, then 50, otherwise show the true sum of number of credits.

DailyNumberOfCredits:iif([SumNumberOfCredits]>50,50, [SumNumberOfCredits])

I then am getting annoyed with the POP-UP.
I am new to Access but I have a thick book and willing to learn.
JPol


:

In your first post you mantioned something about
sum of credits >50

So in the example I provided you with , I assumed that the field name is
[sum of credits], if this field doesn't exist it will rompt you to enter this
as parameter.
Change this name to the name of the field in the table, that you want to
create this IIf on.

--
Good Luck
BS"D


:

Got it working but every time I execute the query it displays a box asking to
enter parameter value for [sum of credits]. If I leave it blank the query is
executed fine. How to execute the Query without asking me for the parameter
every time?
Thanks,
JPol
:

In the Query create another field

NewFieldName: IIf([sum of credits] >50, 50 , [sum of credits])

--
Good Luck
BS"D


:

I am writing database for counting credits.
Need to use an iff function in a query

The result of the function should be:
If sum of credits >50, then 50, otherwise the result should be a sum of
credits
Help, Thanks

 

[Guest]

It could be that when you paste the SQL it cut one of the line.
All I did is, I took your SQL and removed the iif from the select and group
by section, and then removed the order by.
You try and remove it from your SQL, and then build query 2

--
Good Luck
BS"D

I am getting a syntax error in query 1. I copied and pasted the SQL statement
JPol

Query No 1 - name it GetSumPerDemerit:

SELECT [Srtudents Table].First_Name, [Srtudents Table].Last_Name, [Demerits
Table].Demerit_Date, Sum([Demerits Table].Demerit_Quantity) AS
SumOfDemerit_Quantity
FROM [Srtudents Table] RIGHT JOIN [Demerits Table] ON [Srtudents
Table].Student_Nr = [Demerits Table].Student_Nr
GROUP BY [Srtudents Table].First_Name, [Srtudents Table].Last_Name,
[Demerits Table].Demerit_Date
=============================================
Query No 2

SELECT GetSumPerDemerit.* ,
IIf([sumOfDemerit_Quantity]>50,50,[sumOfDemerit_Quantity]) AS demeritsByDay
FROM GetSumPerDemerit ORDER BY Last_Name

--
Good Luck
BS"D

Here is the SQL:
SELECT [Srtudents Table].First_Name, [Srtudents Table].Last_Name, [Demerits
Table].Demerit_Date, Sum([Demerits Table].Demerit_Quantity) AS
SumOfDemerit_Quantity,
IIf([sumOfDemerit_Quantity]>50,50,[sumOfDemerit_Quantity]) AS demeritsByDay
FROM [Srtudents Table] RIGHT JOIN [Demerits Table] ON [Srtudents
Table].Student_Nr = [Demerits Table].Student_Nr
GROUP BY [Srtudents Table].First_Name, [Srtudents Table].Last_Name,
[Demerits Table].Demerit_Date,
IIf([sumOfDemerit_Quantity]>50,50,[sumOfDemerit_Quantity])
ORDER BY [Srtudents Table].Last_Name;

This the actual Query for counting demerits. I get the pop-up.

:

In that case, create a query that return the sum for each date (as you did
already)

Then create a query based on the query above, and then use th iif on the new
sum field name created in the query above.
If you need help, please post the SQL of the above query

--
Good Luck
BS"D


:

This is how it looks:
In Query there is a field name: [numberOfCredits], since there could be many
credits for the particular date, I group this field by :[date] and Sum
[numberOfCredits]. When I run this Query the field [SumNumberOfCredits] is
created by Query. But [SumNumberOfCredits] may not be more than 50, because
MAX credits per day may only be 50. Therefore I created another field in
Query DailyNumberOfCredits: to do the following - If sum number of credits is
50, then 50, otherwise show the true sum of number of credits.

DailyNumberOfCredits:iif([SumNumberOfCredits]>50,50, [SumNumberOfCredits])

I then am getting annoyed with the POP-UP.
I am new to Access but I have a thick book and willing to learn.
JPol


:

In your first post you mantioned something about
sum of credits >50

So in the example I provided you with , I assumed that the field name is
[sum of credits], if this field doesn't exist it will rompt you to enter this
as parameter.
Change this name to the name of the field in the table, that you want to
create this IIf on.

--
Good Luck
BS"D


:

Got it working but every time I execute the query it displays a box asking to
enter parameter value for [sum of credits]. If I leave it blank the query is
executed fine. How to execute the Query without asking me for the parameter
every time?
Thanks,
JPol
:

In the Query create another field

NewFieldName: IIf([sum of credits] >50, 50 , [sum of credits])

--
Good Luck
BS"D


:

I am writing database for counting credits.
Need to use an iff function in a query

The result of the function should be:
If sum of credits >50, then 50, otherwise the result should be a sum of
credits
Help, Thanks

 

[Guest]

I found the problem. Don't need separate query. All I did is in query design
change the grouping from Group by to Expression, then I added another
calculated fied for complience %. This a new SQL:

SELECT [Srtudents Table].First_Name, [Srtudents Table].Last_Name, [Demerits
Table].Demerit_Date, Sum([Demerits Table].Demerit_Quantity) AS
SumOfDemerit_Quantity,
IIf([sumOfDemerit_Quantity]>50,50,[sumOfDemerit_Quantity]) AS DemeritsByDay,
100-[DemeritsByDay]*100/50 AS CompliencePercentDaily
FROM [Srtudents Table] RIGHT JOIN [Demerits Table] ON [Srtudents
Table].Student_Nr = [Demerits Table].Student_Nr
GROUP BY [Srtudents Table].First_Name, [Srtudents Table].Last_Name,
[Demerits Table].Demerit_Date
ORDER BY [Srtudents Table].Last_Name;

When I get stuck again I will post the question to you.

JPol

It could be that when you paste the SQL it cut one of the line.
All I did is, I took your SQL and removed the iif from the select and group
by section, and then removed the order by.
You try and remove it from your SQL, and then build query 2

--
Good Luck
BS"D

I am getting a syntax error in query 1. I copied and pasted the SQL statement
JPol

Query No 1 - name it GetSumPerDemerit:

SELECT [Srtudents Table].First_Name, [Srtudents Table].Last_Name, [Demerits
Table].Demerit_Date, Sum([Demerits Table].Demerit_Quantity) AS
SumOfDemerit_Quantity
FROM [Srtudents Table] RIGHT JOIN [Demerits Table] ON [Srtudents
Table].Student_Nr = [Demerits Table].Student_Nr
GROUP BY [Srtudents Table].First_Name, [Srtudents Table].Last_Name,
[Demerits Table].Demerit_Date
=============================================
Query No 2

SELECT GetSumPerDemerit.* ,
IIf([sumOfDemerit_Quantity]>50,50,[sumOfDemerit_Quantity]) AS demeritsByDay
FROM GetSumPerDemerit ORDER BY Last_Name

--
Good Luck
BS"D


:

Here is the SQL:
SELECT [Srtudents Table].First_Name, [Srtudents Table].Last_Name, [Demerits
Table].Demerit_Date, Sum([Demerits Table].Demerit_Quantity) AS
SumOfDemerit_Quantity,
IIf([sumOfDemerit_Quantity]>50,50,[sumOfDemerit_Quantity]) AS demeritsByDay
FROM [Srtudents Table] RIGHT JOIN [Demerits Table] ON [Srtudents
Table].Student_Nr = [Demerits Table].Student_Nr
GROUP BY [Srtudents Table].First_Name, [Srtudents Table].Last_Name,
[Demerits Table].Demerit_Date,
IIf([sumOfDemerit_Quantity]>50,50,[sumOfDemerit_Quantity])
ORDER BY [Srtudents Table].Last_Name;

This the actual Query for counting demerits. I get the pop-up.

:

In that case, create a query that return the sum for each date (as you did
already)

Then create a query based on the query above, and then use th iif on the new
sum field name created in the query above.
If you need help, please post the SQL of the above query

--
Good Luck
BS"D


:

This is how it looks:
In Query there is a field name: [numberOfCredits], since there could be many
credits for the particular date, I group this field by :[date] and Sum
[numberOfCredits]. When I run this Query the field [SumNumberOfCredits] is
created by Query. But [SumNumberOfCredits] may not be more than 50, because
MAX credits per day may only be 50. Therefore I created another field in
Query DailyNumberOfCredits: to do the following - If sum number of credits is
50, then 50, otherwise show the true sum of number of credits.

DailyNumberOfCredits:iif([SumNumberOfCredits]>50,50, [SumNumberOfCredits])

I then am getting annoyed with the POP-UP.
I am new to Access but I have a thick book and willing to learn.
JPol


:

In your first post you mantioned something about
sum of credits >50

So in the example I provided you with , I assumed that the field name is
[sum of credits], if this field doesn't exist it will rompt you to enter this
as parameter.
Change this name to the name of the field in the table, that you want to
create this IIf on.

--
Good Luck
BS"D


:

Got it working but every time I execute the query it displays a box asking to
enter parameter value for [sum of credits]. If I leave it blank the query is
executed fine. How to execute the Query without asking me for the parameter
every time?
Thanks,
JPol
:

In the Query create another field

NewFieldName: IIf([sum of credits] >50, 50 , [sum of credits])

--
Good Luck
BS"D


:

I am writing database for counting credits.
Need to use an iff function in a query

The result of the function should be:
If sum of credits >50, then 50, otherwise the result should be a sum of
credits
Help, Thanks