Name selection

[Bill Neilsen]

I have a table of employees that I would like to run a monthly report on.
But, I want the report to select different names each month progressively, so
the by the end of the year all employees have been covered without repeats.
And then the next year start again. Bear in mind that each year the list of
employees is different because of separations and new starters.
A method that immediaely pops into my mind is to align the Names
alphabetically to each month, but I have no idea how to do it.
Can anyone make a suggestion on a method.

 

[Hunter57]

Hi Bill,

If you have the employee Date of Birth you could run the report the month of
their birthday. Thay way everyone would be included only once. You can set
a parameter in your Report's RecordSource if it is a query.

Here is a sample Query:

SELECT FirstName, LastName, Month([DOB]) AS MonthNumber
FROM tblEmployees
WHERE (Month([DOB]))=[MonthNumber];

The Month Function returns the Month number which is 1 for Jan, 12 for Dec.,
etc.

Best Regards,

Patrick Wood
www.advancingsoftware.com
www.churchmanagesoftware.com

 

[Bill Neilsen]

Hi Hunter,

Is it possible for you to show me how to do it it Design View rather than
SQL View.

Thanks mate.

 

[Hunter57]

Hi Bill,


Field: | FirstName | LastName | MonthNumber: Month([DOB]) |

Table: | tblEmployees | tblEmployees |
|
| | |
|
| | |
|
Criteria: | | | [MonthNumber]
|

Replace the field and table names with your own. If your Date of Birth
field is named differently just replace DOB with your field name. You will
need to keep the ([ ]) around that field name.

Monthnumber can be changed to whatever term you prefer. When the query runs
or the report opens you will be prompted to enter Monthnumber. You can
change [Monthnumber] in the criteria row to any word or phrase like [Enter
the number of the Month]. Then the word or phrase you incluce in the brackets
in the Criteria row is what you will see when the dialog box pops up.

Also, you the word in the "Field:" row can be different from the word or
phrase in the "Criteria:" row. Like this:

| MonthBorn: Month([DOB]) |
| |
| |
| |
|[Enter Month Number] |

Best Regards,
Pat Wood
www.advancingsoftware.com
www.churchmanagesoftware.com

 

[Bill Neilsen]

Thanks Pat,

It works a treat, I took it one step further (in the interest of being
easier for the users) and referred the Month Number criterior to a text box
on the form where the report is run from.

Once again
Thanks mate.

Hi Bill,


Field: | FirstName | LastName | MonthNumber: Month([DOB]) |

Table: | tblEmployees | tblEmployees |
|
| | |
|
| | |
|
Criteria: | | | [MonthNumber]
|

Replace the field and table names with your own. If your Date of Birth
field is named differently just replace DOB with your field name. You will
need to keep the ([ ]) around that field name.

Monthnumber can be changed to whatever term you prefer. When the query runs
or the report opens you will be prompted to enter Monthnumber. You can
change [Monthnumber] in the criteria row to any word or phrase like [Enter
the number of the Month]. Then the word or phrase you incluce in the brackets
in the Criteria row is what you will see when the dialog box pops up.

Also, you the word in the "Field:" row can be different from the word or
phrase in the "Criteria:" row. Like this:

| MonthBorn: Month([DOB]) |
| |
| |
| |
|[Enter Month Number] |

Best Regards,
Pat Wood
www.advancingsoftware.com
www.churchmanagesoftware.com

Hi Hunter,

Is it possible for you to show me how to do it it Design View rather than
SQL View.

Thanks mate.

 

[Hunter57]

Hi Bill,

You are welcome. Glad I could help. I was thinking that you now have to
make some arrangement for those new employees whose birthday is earlier in
the year than their first day of employment. You could handle that in a
number of different ways.

Best Regards,
Pat Wood
www.advancingsoftware.com
www.churchmanagesoftware.com

Thanks Pat,

It works a treat, I took it one step further (in the interest of being
easier for the users) and referred the Month Number criterior to a text box
on the form where the report is run from.

Once again
Thanks mate.

Hi Bill,


Field: | FirstName | LastName | MonthNumber: Month([DOB]) |

Table: | tblEmployees | tblEmployees |
|
| | |
|
| | |
|
Criteria: | | | [MonthNumber]
|

Replace the field and table names with your own. If your Date of Birth
field is named differently just replace DOB with your field name. You will
need to keep the ([ ]) around that field name.

Monthnumber can be changed to whatever term you prefer. When the query runs
or the report opens you will be prompted to enter Monthnumber. You can
change [Monthnumber] in the criteria row to any word or phrase like [Enter
the number of the Month]. Then the word or phrase you incluce in the brackets
in the Criteria row is what you will see when the dialog box pops up.

Also, you the word in the "Field:" row can be different from the word or
phrase in the "Criteria:" row. Like this:

| MonthBorn: Month([DOB]) |
| |
| |
| |
|[Enter Month Number] |

Best Regards,
Pat Wood
www.advancingsoftware.com
www.churchmanagesoftware.com

Hi Hunter,

Is it possible for you to show me how to do it it Design View rather than
SQL View.

Thanks mate.

:

I have a table of employees that I would like to run a monthly report on.
But, I want the report to select different names each month progressively, so
the by the end of the year all employees have been covered without repeats.
And then the next year start again. Bear in mind that each year the list of
employees is different because of separations and new starters.
A method that immediaely pops into my mind is to align the Names
alphabetically to each month, but I have no idea how to do it.
Can anyone make a suggestion on a method.

 

[Bill Neilsen]

Very interesting point, and I love learning new ideas. Can I ask you to
suggest how we could handle that situation please mate?

Hi Bill,

You are welcome. Glad I could help. I was thinking that you now have to
make some arrangement for those new employees whose birthday is earlier in
the year than their first day of employment. You could handle that in a
number of different ways.

Best Regards,
Pat Wood
www.advancingsoftware.com
www.churchmanagesoftware.com

Thanks Pat,

It works a treat, I took it one step further (in the interest of being
easier for the users) and referred the Month Number criterior to a text box
on the form where the report is run from.

Once again
Thanks mate.

Hi Bill,


Field: | FirstName | LastName | MonthNumber: Month([DOB]) |

Table: | tblEmployees | tblEmployees |
|
| | |
|
| | |
|
Criteria: | | | [MonthNumber]
|

Replace the field and table names with your own. If your Date of Birth
field is named differently just replace DOB with your field name. You will
need to keep the ([ ]) around that field name.

Monthnumber can be changed to whatever term you prefer. When the query runs
or the report opens you will be prompted to enter Monthnumber. You can
change [Monthnumber] in the criteria row to any word or phrase like [Enter
the number of the Month]. Then the word or phrase you incluce in the brackets
in the Criteria row is what you will see when the dialog box pops up.

Also, you the word in the "Field:" row can be different from the word or
phrase in the "Criteria:" row. Like this:

| MonthBorn: Month([DOB]) |
| |
| |
| |
|[Enter Month Number] |

Best Regards,
Pat Wood
www.advancingsoftware.com
www.churchmanagesoftware.com




:

Hi Hunter,

Is it possible for you to show me how to do it it Design View rather than
SQL View.

Thanks mate.

:

I have a table of employees that I would like to run a monthly report on.
But, I want the report to select different names each month progressively, so
the by the end of the year all employees have been covered without repeats.
And then the next year start again. Bear in mind that each year the list of
employees is different because of separations and new starters.
A method that immediaely pops into my mind is to align the Names
alphabetically to each month, but I have no idea how to do it.
Can anyone make a suggestion on a method.

 

[Hunter57]

Hi Bill,

I am so sorry I did not answer you, I did not see an alert for this
thread so I did not know you had posted again.

I would make it easy on myself and just do it for anyone that had been
missed in December. That way eveyone would be covered for the year.

You can get the list of those who were hired after their birthday that
year with a query like this:

SELECT FirstName, LastName, DatePart("y",[DOB]) AS DateofBirth,
DatePart("y",[DateHired]) AS HireDate
FROM tblEmployees
WHERE DatePart("y",[DOB]) < DatePart("y",[DateHired]) AND
DateHired>DateSerial(Year(Date()),1,1);

Best Regards,
Pat Wood
www.advancingsoftware.com
www.churchmanagesoftware.com

Very interesting point, and I love learning new ideas. Can I ask you to
suggest how we could handle that situation please mate?

HiBill,

You are welcome.  Glad I could help. I was thinking that you now have to
make some arrangement for those new employees whose birthday is earlier in
the year than their first day of employment.  You could handle that ina
number of different ways.

Best Regards,
Pat Wood
www.advancingsoftware.com
www.churchmanagesoftware.com

Thanks Pat,
It works a treat, I took it one step further (in the interest of being
easier for the users) and referred the Month Number criterior to a text box
on the form where the report is run from.
Once again
Thanks mate.
:
HiBill,
Field:     |  FirstName    |    LastName   | MonthNumber: Month([DOB]) |    
Table:    | tblEmployees | tblEmployees |                                    
       |
             |                    |                     |                 
                        |
             |                    |                     |                 
                        |
Criteria: |                     |                     | [MonthNumber]        
            |      
Replace the field and table names with your own.  If your Date of Birth
field is named differently just replace DOB with your field name. You will
need to keep the ([ ]) around that field name.  
Monthnumber can be changed to whatever term you prefer.  When the query runs
or the report opens you will be prompted to enter Monthnumber.  You can
change [Monthnumber] in the criteria row to any word or phrase like [Enter
the number of the Month]. Then the word or phrase you incluce in thebrackets
in the Criteria row is what you will see when the dialog box pops up..
Also, you the word in the "Field:" row can be different from the word or
phrase in the "Criteria:" row.  Like this:
| MonthBorn: Month([DOB]) |
|                                       |
|                                       |
|                                       |
|[Enter Month Number]       |
Best Regards,
Pat Wood
www.advancingsoftware.com
www.churchmanagesoftware.com
:
Hi Hunter,
Is it possible for you to show me how to do it it Design View rather than
SQL View.
Thanks mate.
:
I have a table of employees that I would like to run a monthly report on.
But, I want the report to select different names each month progressively, so
the by the end of the year all employees have been covered without repeats.
And then the next year start again. Bear in mind that each year the list of
employees is different because of separations and new starters.
A method that immediaely pops into my mind is to align the Names
alphabetically to each month, but I have no idea how to do it.
Can anyone make a suggestion on a method.- Hide quoted text -

- Show quoted text -