criteria

[Chi]

Hi,

The field named AGE stored the number from 1 to 100. I would like to have
criteria that can pull out the age from 0 to 80, 80-90 and 90 to 100.

Any advices are much appreciated.

Thanks
Chi

 

[KARL DEWEY]

What you are asking is pulling all records 0-100.
Do you want the data in three groups?
Do you want a count of what is in the three groups?

SELECT SUM(IIF([AGE] BETWEEN 0 AND 80, 1, 0)) AS [0-80], SUM(IIF([AGE]
BETWEEN 81 AND 90, 1, 0)) AS [81-90], SUM(IIF([AGE] BETWEEN 91 AND 100, 1,
0)) AS [91-100]
FROM YourTable;

 

[Chi]

Hi Karl,

Yes, I want the data in three groups and count of what is in the three groups.

Would you please show me where I add "SELECT SUM(IIF([AGE] BETWEEN 0 AND 80,
1, 0)) AS [0-80], SUM(IIF([AGE]

BETWEEN 81 AND 90, 1, 0)) AS [81-90], SUM(IIF([AGE] BETWEEN 91 AND 100, 1,
0)) AS [91-100] "?

Thank you so much!!
Chi

What you are asking is pulling all records 0-100.
Do you want the data in three groups?
Do you want a count of what is in the three groups?

SELECT SUM(IIF([AGE] BETWEEN 0 AND 80, 1, 0)) AS [0-80], SUM(IIF([AGE]
BETWEEN 81 AND 90, 1, 0)) AS [81-90], SUM(IIF([AGE] BETWEEN 91 AND 100, 1,
0)) AS [91-100]
FROM YourTable;

--
KARL DEWEY
Build a little - Test a little

Hi,

The field named AGE stored the number from 1 to 100. I would like to have
criteria that can pull out the age from 0 to 80, 80-90 and 90 to 100.

Any advices are much appreciated.

Thanks
Chi

 

[Chi]

Hi Karl,

I added "your answers" to SQL. It works!!!. It gives me the total numbers of
the three groups. However, I would like the patient's lastname and firstname
will go with it.

Thank you for all your help!


Thanks
Chi

What you are asking is pulling all records 0-100.
Do you want the data in three groups?
Do you want a count of what is in the three groups?

SELECT SUM(IIF([AGE] BETWEEN 0 AND 80, 1, 0)) AS [0-80], SUM(IIF([AGE]
BETWEEN 81 AND 90, 1, 0)) AS [81-90], SUM(IIF([AGE] BETWEEN 91 AND 100, 1,
0)) AS [91-100]
FROM YourTable;

--
KARL DEWEY
Build a little - Test a little

Hi,

The field named AGE stored the number from 1 to 100. I would like to have
criteria that can pull out the age from 0 to 80, 80-90 and 90 to 100.

Any advices are much appreciated.

Thanks
Chi

 

[KARL DEWEY]

I would like the patient's lastname and firstname will go with it.
The names will have no relevance to the age groups.
It would look like this --
First Last 0-80 81-90 91-100
Bill Jones 44 23 5
Joe Johns 44 23 5
Tom Brown 44 23 5
Sam Smith 44 23 5
Will Street 44 23 5

--
KARL DEWEY
Build a little - Test a little

Hi Karl,

I added "your answers" to SQL. It works!!!. It gives me the total numbers of
the three groups. However, I would like the patient's lastname and firstname
will go with it.

Thank you for all your help!


Thanks
Chi

What you are asking is pulling all records 0-100.
Do you want the data in three groups?
Do you want a count of what is in the three groups?

SELECT SUM(IIF([AGE] BETWEEN 0 AND 80, 1, 0)) AS [0-80], SUM(IIF([AGE]
BETWEEN 81 AND 90, 1, 0)) AS [81-90], SUM(IIF([AGE] BETWEEN 91 AND 100, 1,
0)) AS [91-100]
FROM YourTable;

--
KARL DEWEY
Build a little - Test a little

Hi,

The field named AGE stored the number from 1 to 100. I would like to have
criteria that can pull out the age from 0 to 80, 80-90 and 90 to 100.

Any advices are much appreciated.

Thanks
Chi

 

[Chi]

Hi Karl,
I have tried to add the patient's last name and firstname to the query. I
always got the error message "You tried to execute a query that does not
include the specified expression ' Lastname' as part of an aggregate
function".

---------------------------
I have tried to create the report to group by AGE. The formula that group by
80 is “=Int (([AGE)])/80)*80 & " - " & Int((([AGE])/80)+1)*80â€
Here is what I got with the formula above
0-80 FirstName LastName
80-160
160-240

Would you please help me to custom to formula so that the result will group
by 0-80, 80-90 and 90-100?
Thank you so much
Chi

The names will have no relevance to the age groups.
It would look like this --
First Last 0-80 81-90 91-100
Bill Jones 44 23 5
Joe Johns 44 23 5
Tom Brown 44 23 5
Sam Smith 44 23 5
Will Street 44 23 5

--
KARL DEWEY
Build a little - Test a little

Hi Karl,

I added "your answers" to SQL. It works!!!. It gives me the total numbers of
the three groups. However, I would like the patient's lastname and firstname
will go with it.

Thank you for all your help!


Thanks
Chi

What you are asking is pulling all records 0-100.
Do you want the data in three groups?
Do you want a count of what is in the three groups?

SELECT SUM(IIF([AGE] BETWEEN 0 AND 80, 1, 0)) AS [0-80], SUM(IIF([AGE]
BETWEEN 81 AND 90, 1, 0)) AS [81-90], SUM(IIF([AGE] BETWEEN 91 AND 100, 1,
0)) AS [91-100]
FROM YourTable;

--
KARL DEWEY
Build a little - Test a little


:

Hi,

The field named AGE stored the number from 1 to 100. I would like to have
criteria that can pull out the age from 0 to 80, 80-90 and 90 to 100.

Any advices are much appreciated.

Thanks
Chi

 

[KARL DEWEY]

Try this --
SELECT FirstName, LastName, IIF([AGE] BETWEEN 0 AND 80, 1, IIF([AGE]
BETWEEN 81 AND 90, 2, 3)) AS Sort
FROM YourTable;

Use the Sorting and Grouping in the report and select Sort field.

--
KARL DEWEY
Build a little - Test a little

Hi Karl,
I have tried to add the patient's last name and firstname to the query. I
always got the error message "You tried to execute a query that does not
include the specified expression ' Lastname' as part of an aggregate
function".

---------------------------
I have tried to create the report to group by AGE. The formula that group by
80 is “=Int (([AGE)])/80)*80 & " - " & Int((([AGE])/80)+1)*80â€
Here is what I got with the formula above
0-80 FirstName LastName
80-160
160-240

Would you please help me to custom to formula so that the result will group
by 0-80, 80-90 and 90-100?
Thank you so much
Chi

I would like the patient's lastname and firstname will go with it.

The names will have no relevance to the age groups.
It would look like this --
First Last 0-80 81-90 91-100
Bill Jones 44 23 5
Joe Johns 44 23 5
Tom Brown 44 23 5
Sam Smith 44 23 5
Will Street 44 23 5

--
KARL DEWEY
Build a little - Test a little

Hi Karl,

I added "your answers" to SQL. It works!!!. It gives me the total numbers of
the three groups. However, I would like the patient's lastname and firstname
will go with it.

Thank you for all your help!


Thanks
Chi


:

What you are asking is pulling all records 0-100.
Do you want the data in three groups?
Do you want a count of what is in the three groups?

SELECT SUM(IIF([AGE] BETWEEN 0 AND 80, 1, 0)) AS [0-80], SUM(IIF([AGE]
BETWEEN 81 AND 90, 1, 0)) AS [81-90], SUM(IIF([AGE] BETWEEN 91 AND 100, 1,
0)) AS [91-100]
FROM YourTable;

--
KARL DEWEY
Build a little - Test a little


:

Hi,

The field named AGE stored the number from 1 to 100. I would like to have
criteria that can pull out the age from 0 to 80, 80-90 and 90 to 100.

Any advices are much appreciated.

Thanks
Chi

 

[Chi]

Hi Karl,

It is perfect!!! I really appreciate for you help.

The only thing that I changed from your answer is instead of using number
1,2 and 3 for return, I used "0-80", "80 to 90" and "90 and up". Therefore,
the report is grouped by "0-80", "80 to 90" and "90 and up".

Again, thank you very much for all your help and patience.
Chi

Try this --
SELECT FirstName, LastName, IIF([AGE] BETWEEN 0 AND 80, 1, IIF([AGE]
BETWEEN 81 AND 90, 2, 3)) AS Sort
FROM YourTable;

Use the Sorting and Grouping in the report and select Sort field.

--
KARL DEWEY
Build a little - Test a little

Hi Karl,
I have tried to add the patient's last name and firstname to the query. I
always got the error message "You tried to execute a query that does not
include the specified expression ' Lastname' as part of an aggregate
function".

---------------------------
I have tried to create the report to group by AGE. The formula that group by
80 is “=Int (([AGE)])/80)*80 & " - " & Int((([AGE])/80)+1)*80â€
Here is what I got with the formula above
0-80 FirstName LastName
80-160
160-240

Would you please help me to custom to formula so that the result will group
by 0-80, 80-90 and 90-100?
Thank you so much
Chi

I would like the patient's lastname and firstname will go with it.
The names will have no relevance to the age groups.
It would look like this --
First Last 0-80 81-90 91-100
Bill Jones 44 23 5
Joe Johns 44 23 5
Tom Brown 44 23 5
Sam Smith 44 23 5
Will Street 44 23 5

--
KARL DEWEY
Build a little - Test a little


:

Hi Karl,

I added "your answers" to SQL. It works!!!. It gives me the total numbers of
the three groups. However, I would like the patient's lastname and firstname
will go with it.

Thank you for all your help!


Thanks
Chi


:

What you are asking is pulling all records 0-100.
Do you want the data in three groups?
Do you want a count of what is in the three groups?

SELECT SUM(IIF([AGE] BETWEEN 0 AND 80, 1, 0)) AS [0-80], SUM(IIF([AGE]
BETWEEN 81 AND 90, 1, 0)) AS [81-90], SUM(IIF([AGE] BETWEEN 91 AND 100, 1,
0)) AS [91-100]
FROM YourTable;

--
KARL DEWEY
Build a little - Test a little


:

Hi,

The field named AGE stored the number from 1 to 100. I would like to have
criteria that can pull out the age from 0 to 80, 80-90 and 90 to 100.

Any advices are much appreciated.

Thanks
Chi