Office 97 newbie

[gil]

I have a student database in Corel Approach that I have duplicated in
Access, as more people seem to be using the MS program. My version is
Office 97 - Access.

Eventually, I will be able to manipulate Access as well as I can
manipulate Approach, but for now I need a quick fix. There is a
significant difference in the terminology and procedures used between
the two programs. Access help is comprehensive but very confusing.

In a large student database (+1200), I have occasion to print a list of
the students who have a birthday during the current month. In Approach,
I had created a formatted birthday 'view'. For any particular month, I
would create a 'found set' and print it in the birthday view. Worked
well for two years.

I am trying to do a similar filter in Access 97. It seems that I have to
create a separate query for each month of the year, and then create a
separate report for each query with the correct page format copied from
report to report.

There must be a simpler method.

I have been using the date format yyyy-mm-dd in a DOB field, and
creating the 'found set' using a filter by selection on -mm-.

Please point me in the correct direction. Treat me as a newbie, short
non-techie, english words.

Thanks


--
Gil.

If you get the facts first,
You may not need to reconstruct your hard drive later!

Please reply to the group.

-----

 

[Cheryl Fischer]

Gil,

It is very easy to do what you want in Access using a single report which
uses a Parameter Query as its record source. Here is how:

1. Open a new query in Design View and select the table you are currently
using for your birthday queries as its source.

2. Drag all of the fields you want to display in the report into columns in
the design grid.

3. Now, in the next available column in the design grid, type the following
in the first row of that column:

Month([DOB])

4. In that same column, insert the following in the Criteria: row

[Enter the desired month as a number:]

5. You can test the query now by clicking the icon with the exclamation
point. You will see a prompt pop up asking you to enter the desired month
as a number. If you enter 4, all of the birth dates you have in your table
which occur in April will be returned by your query.

6. Return to design view of the query and save it to a name not already
used, say BirthdayQuery. Close the query.

7. Now open your Birthday Report in design view and open the Properties
page of the report. Click on the tab labeled 'Data'. In the Record Source
property of your report, enter the name of the query you just created and
saved. You can preview your report now by clicking the View icon. When
you do this, the Parameter in the query will request the month and will
return to the report only those months satisfying the number you input.


Give this a try and let us know how it goes.

 

[gil]

Cheryl,

By following your instructions, I was able to produce the exact format I
was looking for without a lot of hassle. Now I can take my time, read
the newsgroups and learn more about the program, without feeling rushed.

Thank you very much.

Gil


At approximately 2004-01-31 23:50, Cheryl Fischer typed these characters:

Gil,

It is very easy to do what you want in Access using a single report which
uses a Parameter Query as its record source. Here is how:

1. Open a new query in Design View and select the table you are currently
using for your birthday queries as its source.

2. Drag all of the fields you want to display in the report into columns in
the design grid.

3. Now, in the next available column in the design grid, type the following
in the first row of that column:

Month([DOB])

4. In that same column, insert the following in the Criteria: row

[Enter the desired month as a number:]

5. You can test the query now by clicking the icon with the exclamation
point. You will see a prompt pop up asking you to enter the desired month
as a number. If you enter 4, all of the birth dates you have in your table
which occur in April will be returned by your query.

6. Return to design view of the query and save it to a name not already
used, say BirthdayQuery. Close the query.

7. Now open your Birthday Report in design view and open the Properties
page of the report. Click on the tab labeled 'Data'. In the Record Source
property of your report, enter the name of the query you just created and
saved. You can preview your report now by clicking the View icon. When
you do this, the Parameter in the query will request the month and will
return to the report only those months satisfying the number you input.


Give this a try and let us know how it goes.


--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX

I have a student database in Corel Approach that I have duplicated in
Access, as more people seem to be using the MS program. My version is
Office 97 - Access.

Eventually, I will be able to manipulate Access as well as I can
manipulate Approach, but for now I need a quick fix. There is a
significant difference in the terminology and procedures used between
the two programs. Access help is comprehensive but very confusing.

In a large student database (+1200), I have occasion to print a list of
the students who have a birthday during the current month. In Approach,
I had created a formatted birthday 'view'. For any particular month, I
would create a 'found set' and print it in the birthday view. Worked
well for two years.

I am trying to do a similar filter in Access 97. It seems that I have to
create a separate query for each month of the year, and then create a
separate report for each query with the correct page format copied from
report to report.

There must be a simpler method.

I have been using the date format yyyy-mm-dd in a DOB field, and
creating the 'found set' using a filter by selection on -mm-.

Please point me in the correct direction. Treat me as a newbie, short
non-techie, english words.

Thanks

--
Gil.

If you get the facts first,
You may not need to reconstruct your hard drive later!

Please reply to the group.

-----

 

[Cheryl Fischer]

You're welcome and good luck with your project.

--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX

Cheryl,

By following your instructions, I was able to produce the exact format I
was looking for without a lot of hassle. Now I can take my time, read
the newsgroups and learn more about the program, without feeling rushed.

Thank you very much.

Gil


At approximately 2004-01-31 23:50, Cheryl Fischer typed these characters:

Gil,

It is very easy to do what you want in Access using a single report which
uses a Parameter Query as its record source. Here is how:

1. Open a new query in Design View and select the table you are currently
using for your birthday queries as its source.

2. Drag all of the fields you want to display in the report into columns in
the design grid.

3. Now, in the next available column in the design grid, type the following
in the first row of that column:

Month([DOB])

4. In that same column, insert the following in the Criteria: row

[Enter the desired month as a number:]

5. You can test the query now by clicking the icon with the exclamation
point. You will see a prompt pop up asking you to enter the desired month
as a number. If you enter 4, all of the birth dates you have in your table
which occur in April will be returned by your query.

6. Return to design view of the query and save it to a name not already
used, say BirthdayQuery. Close the query.

7. Now open your Birthday Report in design view and open the Properties
page of the report. Click on the tab labeled 'Data'. In the Record Source
property of your report, enter the name of the query you just created and
saved. You can preview your report now by clicking the View icon. When
you do this, the Parameter in the query will request the month and will
return to the report only those months satisfying the number you input.


Give this a try and let us know how it goes.


--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX

I have a student database in Corel Approach that I have duplicated in
Access, as more people seem to be using the MS program. My version is
Office 97 - Access.

Eventually, I will be able to manipulate Access as well as I can
manipulate Approach, but for now I need a quick fix. There is a
significant difference in the terminology and procedures used between
the two programs. Access help is comprehensive but very confusing.

In a large student database (+1200), I have occasion to print a list of
the students who have a birthday during the current month. In Approach,
I had created a formatted birthday 'view'. For any particular month, I
would create a 'found set' and print it in the birthday view. Worked
well for two years.

I am trying to do a similar filter in Access 97. It seems that I have to
create a separate query for each month of the year, and then create a
separate report for each query with the correct page format copied from
report to report.

There must be a simpler method.

I have been using the date format yyyy-mm-dd in a DOB field, and
creating the 'found set' using a filter by selection on -mm-.

Please point me in the correct direction. Treat me as a newbie, short
non-techie, english words.

Thanks

--
Gil.

If you get the facts first,
You may not need to reconstruct your hard drive later!

Please reply to the group.