Producing a daily report

[KC]

I want to run a report based on an appointment table on a daily basis. I know
how to get the look of the form I want to create, but I want to ba able to
enter the date each morning and print the report. Any help is appreciated.

 

[Cheese_whiz]

Hi KC,

If you want a report to print records from one table based on a single date
('today'), you don't need a form for that. What you need is a report. Then
you can open the report with a command button and pass the criteria of
today's date to the report in the openReport method of the DoCmd object in
vba.

The vba step is simple. What I would suggest is you use the report wizard
and see if you can get a report that looks like you want it to look. Though
I question the normalization of your tables if you can get all the data you
need from one table, if you think the appointments table has all the data
your report needs, then use that as the record source in the report wizard.
Once you get something close, you can open the report in design view and try
to modify it to your needs. At that point you will probably be ready for
some more specific questions, which are easier to answer.

Hope that helps,
CW

 

[KC]

Cheese_whiz,
Let me go into a bit more detail and I bet you can help me out. I've got 3
tables that the "appointment" report I want to run will pull information
from. The appointment table has a providerID from a related table and a
patient ID from another related table. I want to pull a Name for each
provider ID for a daily report listing appointments for each provider. What I
envision is opening a report, whereas a prompt asks for ProviderID, which
produces the report for the provider I specify. Thanks for the help

 

[Lars Brownie]

I'd make a query that retrieves the information you need. In the particular
date field you can use the date() function so that you don't have to enter
it manually. Then you can base your report on this query.

In stead of asking the user for an ID number, you could open the report from
a form where the particular ProviderID is in the current form's record. The
following code opens "YourReportName" and only shows the information for the
form's current ProviderID field's value:

DoCmd.OpenReport "YourReportName", acViewPreview, , "[ProviderID]=" &
Me!ProviderID

Lars

 

[Cheese_whiz]

Hi KC,

Lars gave you a perfectly good answer to how to proceed. Like in most
cases, it's one way to proceed (and it's the way I had in mind as well after
your first post). There are other ways. If you aren't interested in
considering some comments that expand more broadly on the topic, stop reading
here. It may be a waste of your time anyway...

One thing I would consider is, instead of putting the date() function as a
criteria directly in the query, I might just add it to the end of the
openReport code Lars provided. That way, if you ever need to use the query
for something else, you won't have to deal with the date function being a
part of it. Not that it's a huge deal....you could always copy the query and
remove the date function from the new copy, but I hate doing stuff like
that....

Of course, it won't matter AT ALL if you never use the query for anything
else.

Another thing that might be helpful to consider is what other report needs
you might have in the future. For example, you may decide that you want to
be able to run the report for dates other than today. Maybe you want to look
back to see what you were doing two weeks ago, or maybe you want to see what
you have scheduled for Friday to decide if you can schedule a golf 'meeting'


Of course, you may have other reports as well which you want users to be
able to limit the data returned by a report.

So, knowing what you want to do long-term might dictate how you proceed now.
If, for example, you decide you DO want to be able to adjust the date to
other dates (not just today), then an unbound form with a control that allows
you to pick a date would allow that. You could still pull the provider from
the other form (unless you decided you wanted to be able to change the
provider, as well), or you could have a provider control on the new form and
make that user controlled like your new date field. It's a trade-off, of
course. If you use a separate form, then even if you open that form from
some other form that has the provider's name in it and default the date on
the new form to today, your users will STILL have to click ANOTHER button
(one on the new form) to run the report. Of course, for that additional
click you get the additional options of changing the date AND the provider.

If you think about it, you can take that a long way. I have 'report
consoles' for some of my applications that allow users to pick virtually
EVERYTHING that my data can currently provide. They can limit reports to
their current status (open, closed, for example). They can choose the report
they want to run on the fly. Based on that report choice, a bunch of
controls on the form are enabled/disabled to allow them to make choices that
are relevant to the specific report being run which further limits the data
returned to more closely match exactly what they want.

That all takes a bit more work, but it's just an idea of the kind of things
that are possible. So it comes back to making a judgment about what your
current needs are and what you think you might need for the future (and how
much work you can put into it!).

Sorry for the long post, but I just thought I would throw a few things out
for consideration.

CW

I'd make a query that retrieves the information you need. In the particular
date field you can use the date() function so that you don't have to enter
it manually. Then you can base your report on this query.

In stead of asking the user for an ID number, you could open the report from
a form where the particular ProviderID is in the current form's record. The
following code opens "YourReportName" and only shows the information for the
form's current ProviderID field's value:

DoCmd.OpenReport "YourReportName", acViewPreview, , "[ProviderID]=" &
Me!ProviderID

Lars

Cheese_whiz,
Let me go into a bit more detail and I bet you can help me out. I've got 3
tables that the "appointment" report I want to run will pull information
from. The appointment table has a providerID from a related table and a
patient ID from another related table. I want to pull a Name for each
provider ID for a daily report listing appointments for each provider.
What I
envision is opening a report, whereas a prompt asks for ProviderID, which
produces the report for the provider I specify. Thanks for the help

 

[KC]

Thanks very much for the information guys. I still have a great deal to learn
about RDBMS. I just hope to know as much as people like you one day.
--
Kyle

Hi KC,

Lars gave you a perfectly good answer to how to proceed. Like in most
cases, it's one way to proceed (and it's the way I had in mind as well after
your first post). There are other ways. If you aren't interested in
considering some comments that expand more broadly on the topic, stop reading
here. It may be a waste of your time anyway...

One thing I would consider is, instead of putting the date() function as a
criteria directly in the query, I might just add it to the end of the
openReport code Lars provided. That way, if you ever need to use the query
for something else, you won't have to deal with the date function being a
part of it. Not that it's a huge deal....you could always copy the query and
remove the date function from the new copy, but I hate doing stuff like
that....

Of course, it won't matter AT ALL if you never use the query for anything
else.

Another thing that might be helpful to consider is what other report needs
you might have in the future. For example, you may decide that you want to
be able to run the report for dates other than today. Maybe you want to look
back to see what you were doing two weeks ago, or maybe you want to see what
you have scheduled for Friday to decide if you can schedule a golf 'meeting'


Of course, you may have other reports as well which you want users to be
able to limit the data returned by a report.

So, knowing what you want to do long-term might dictate how you proceed now.
If, for example, you decide you DO want to be able to adjust the date to
other dates (not just today), then an unbound form with a control that allows
you to pick a date would allow that. You could still pull the provider from
the other form (unless you decided you wanted to be able to change the
provider, as well), or you could have a provider control on the new form and
make that user controlled like your new date field. It's a trade-off, of
course. If you use a separate form, then even if you open that form from
some other form that has the provider's name in it and default the date on
the new form to today, your users will STILL have to click ANOTHER button
(one on the new form) to run the report. Of course, for that additional
click you get the additional options of changing the date AND the provider.

If you think about it, you can take that a long way. I have 'report
consoles' for some of my applications that allow users to pick virtually
EVERYTHING that my data can currently provide. They can limit reports to
their current status (open, closed, for example). They can choose the report
they want to run on the fly. Based on that report choice, a bunch of
controls on the form are enabled/disabled to allow them to make choices that
are relevant to the specific report being run which further limits the data
returned to more closely match exactly what they want.

That all takes a bit more work, but it's just an idea of the kind of things
that are possible. So it comes back to making a judgment about what your
current needs are and what you think you might need for the future (and how
much work you can put into it!).

Sorry for the long post, but I just thought I would throw a few things out
for consideration.

CW

I'd make a query that retrieves the information you need. In the particular
date field you can use the date() function so that you don't have to enter
it manually. Then you can base your report on this query.

In stead of asking the user for an ID number, you could open the report from
a form where the particular ProviderID is in the current form's record. The
following code opens "YourReportName" and only shows the information for the
form's current ProviderID field's value:

DoCmd.OpenReport "YourReportName", acViewPreview, , "[ProviderID]=" &
Me!ProviderID

Lars

Cheese_whiz,
Let me go into a bit more detail and I bet you can help me out. I've got 3
tables that the "appointment" report I want to run will pull information
from. The appointment table has a providerID from a related table and a
patient ID from another related table. I want to pull a Name for each
provider ID for a daily report listing appointments for each provider.
What I
envision is opening a report, whereas a prompt asks for ProviderID, which
produces the report for the provider I specify. Thanks for the help