Thank-you Julie and Larry. I have to work my "real" job for about 16 hours today, so I plan to read both replies and try to absorb. I really appreciate the suggestions and welcome other ideas as well.
Linda
Hi Linda, JulieD,
First of all, there are very few absolutes where Best Practices in Access
are concerned. You don't even get universal agreement about the things one
absolutely should NOT do.
As Julie wrote, the curse/blessing of a good application is that users want
more and right away.
Rather than write that "I agree" here and "I disagree" there, I'll just
write some of my own heuristics and preferences in the general area of your
questions.
Firstly, as you've already realized, you can cover a lot of ground in a big
hurry with an ad hoc solution but you almost as quickly painting yourself
into a corner and have to do a lot of work to solve the problem the right
way. You are already addressing the issue. It's a lesson I had to learn a
few times before I decided to always start with a product specification and
a functional specification before I write any code.
I recommend that you go crazy with Report Launcher forms (As far as I know,
I made that up
). A Report Launcher is a form with Option groups, Text
Boxes, List Boxes, Comboboxes, and command buttons to supply the arguments
to the variables your report's queries will need.
Use a separate Report Launcher for each family of reports. Many
applications have only one family of reports. A family of reports would be
reports based on more or less the same data set that may vary in the number
of elements covered (all of the members of all teams; just the members of
selected teams, just selected members) for this day; this month, this year,
from this day of this year to that day of that year.
Where it makes sense, I include one or more cascading multiselect list
boxes with intelligence such that team members' names will only show in the
box to the right where that team has been selected in the box to the left.
Include buttons beside each box to Select All or Unselect all. These MSLBs
use callback functions and are the only things under discussion that require
paying close attention to tedious detail while tip-toeing over egg-shells.
Once you've ever worked out how to use one you can copy it over to your new
applications and modify it for use there. I use MSLB callbacks taken from
The Access 2.0 Developer's Handbook by Ken Getz et alia and modified
continually in re-use. By the way, having The Access [YourVersion]
Developer's Handbook in your library is a "Best Practice". Most people only
need the Desktop Edition of the two volume sets.
I always include an option group to Print or Preview to indicate what
should happen when the command buttons at the bottom of the launcher are
pressed.
You may want or require that the user type in a title/subtitle (this
report being generated at the request of ...............), etc. You will
probably want to include "Date printed ........" in the footer. If you are
printing "between dates" you'll probably want to include that information
near the top of the report...
For date ranges, I usually have worked out some default that makes sense
within the application but always make it possible for the user to double
click in the Starting Date and Ending Date text boxes to bring up a pop-up
calendar and navigate to the dates they want.
Command buttons near the bottom of the Launcher will List the report
category to print that will apply all of the relevant criteria on the
Launcher. The query on which the report is based will refer back to the
relevant text boxes, list boxes and comboboxes.
A word about queries: My Database|Queries window is always empty or
nearly so. That way I'm never sucked into using a query for more than one
form or report either intentionally or in error. Remember that space is
cheap but your time is valuable. My initial form or report design is
usually based on that form or report's main table. In the form/report's
data source, I create a query to do all the query kinds of things and then
save CHANGES but DO NOT SAVE IT TO THE QUERY WINDOW WITH A UNIQUE NAME.
That keeps the query within the form or report, invisible to the rest of the
world.
To the extent that it's possible to do so, your goal should be to
produce the user's desired outcome with the least effort practical. At the
end of your post your description of what you're doing is somewhat
ambiguous. In the last paragraph you write that you "have 3 types of
reports" followed by a statement that makes it sound like the same report
but for a different shift. You haven't let us know how you know what shift
is involved (in fact you asked about "best practice" and then segued into a
question about an incompletely described application. In my ignorance, I
would have to assume that there is a shift flag or indicator in each
employee's record. If not, there should be
You've asked us to indicate what will ALWAYS be the best way to do
something when you haven't assured us that the supervisor will ALWAYS want
to have exactly the same result
Rather than use an MSLB I'd put in 3
command buttons: 1 for each shift. Simply have them click each shift in
order to get them all. They will Preview or Print in the order clicked.
That means that they'll print in order but the last button clicked will be
the first report previewed. The OnClick event of the command buttons will
have Where clauses with the appropriate shift indicated.
60 queries and 70 reports sound like a lot for an application that "just
growed".
Of course, "Best Practices" start long before you get into the design of
queries and even forms and reports. That's why it's good to start by
determining what is the need to be met by this application and what are the
entities within its sphere of concern. That's the start of your product
specification. You then try to list and understand all of the entities in
play. Entities are classes of people, places, things, events, etc. Knowing
where to draw the boundary lines. Your application might not be concerned
with all of the employees in your company, only those in your department,
building, etc. You could have a table. tblEmployee, that could include
every employee in the organization from President to Machinist to Cook. You
might be concerned about buildings or about rooms. Once you've determined
the entities, you have to determine how they relate to each other. Once you
think you have a handle on that you might start considering what you want
for Outputs/Reports. Yet, you start by deciding what you want to get out of
it so you know what to put into it.
Next to consider are the tools you'll use. You've settled on Access for
one. More important than Access is you. You want to know that you know
what you're doing. You are already making great strides forward. I've
already recommended one Text/reference. There are others. You can do a lot
with self directed study and just doing it. Formal classes aren't required
(but if you are offered a chance to get some quality Access training, go for
it). I suggest that, if you don't already know what "third normal form"
means you read about relational database systems until you've grasped that
part. You'll never get out of the shallow end of the pool until you've
mastered at least the third normal form. If you already have mastered it
then you're well on your way.
If anything sounded harsh, it wasn't intended that way. Good luck with
it. Post back with questions.
HTH
--
-Larry-
--
Hi LMB
one of the dangers of actually finding out how useful an access database can
be
how about, one query, one report and a form so that the users can choose the
information they want the report to display. This works by linking the
queries' criteria to the form's combo box rather than the user having to
type the criteria in.
here's some notes on how to achieve this:
1. create the query that the report is to be based on
2. use the report wizard to build a report based on the query
3. create the "report options" form - ensuring that i name each control
something easy to find later (ie if its the start date that i want i name
the control startdate) - close & save form
4. return to the query, click in the criteria line of the field that i
need to reference the control on the form
5. click on the expression builder icon on the toolbar
6. go to forms / all forms / find my "report options" form, in the centre
section double click on the form control that i am using as the criteria
do this for all of the criteria that i'm setting
7. close & save query
8. return to the form & put a command button on it to open the report in
print preview mode - close & save form
9. open form, set criteria, click print button
let us know how you go.
Cheers
JulieD
Hi Guys,
I have a database that we have been using for 2 or so years. The original
intent for this database was only to keep track of one thing. After that
one thing was such a huge time saver, I have added bits and pieces and now
the thing is quite large. Now, I am currently re-making this database and
want to do it right from the start. My question is...Is it more
efficient/better for the database to make one query and have the user type
in the criteria or is ok to make 3 queries and then 3 more reports to save
the user from always having to type in criteria? Below is what I have now.
I have 3 types of reports. All have the same reports but only show
records for day shift employees, or night shift employees, or all employees.
I know I can just make one report and base it on one query and have the
supervisor to type in days to see day shift records or nights for night
shift but I figure they will have to do this 3 times since they run 3
reports per shift and it may annoy them. Can having a lot of queries an
reports cause problems? Right now my database has 60 queries and about 70
reports. Gee it sure did grow.
Access 2000
Thanks,
Linda
