Re: Best Practice

From: LMB (RomulanQueen_at_10Forward.SSTNG)
Date: 11/21/04 

Date: Sun, 21 Nov 2004 08:06:33 -0500

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
  "Larry Daugherty" <Larry.NoSpam.Daugherty@verizon.net> wrote in message news:e1ZGnW8zEHA.1860@TK2MSFTNGP15.phx.gbl...
  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-
  --

  "JulieD" <JulieD@hctsReMoVeThIs.net.au> wrote in message
  news:uZr3YV4zEHA.3120@TK2MSFTNGP12.phx.gbl...
  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

    "LMB" <RomulanQueen@10Forward.SSTNG> wrote in message
  news:ecGdqO4zEHA.4028@TK2MSFTNGP15.phx.gbl...
    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

Quantcast