(Page updated 2010-09-27)
|
The following is how I chose to setup one
Microsoft Access database where the client wanted to
have reports showing last years data as well as this years data. This was for a
sporting league database.
I chose to create a long integer field on the table with the year in the value.
I then put those values, ie 2007 and 2006 in two fields in a "Global Options"
table. The user will have to remember to change those next year. I also place
all the data on my Global Options table in a hidden form opened by either the
autoexec macro or the form that is opened by your first form.
Tip: Don't use a menu or other form viewable by the user for these "Global
Options" values as if the user accidentally scrolls up with the mouse you'll get
a new Global Options record created which could cause much confusion in queries
and elsewhere.
I wasted an hour trying to diagnose this problem remotely with a client before I
finally had them zip the Backend MDB file and email it to me. Once I figured out
the problem it took me another three or four months before I had the aha moment
that told me how the user managed to do this. Listen to the voice of experience
here. <smile>
Now in your various queries you can filter the records based on these two values
in the hidden form. Or you could throw in the Global Options table into the
queries. I'm not sure I like that option because it's a Cartesian join and thus
may be relatively inefficient.
I also created some very standard base queries called "Teams this Year" and
"Teams Last Year." T These included the main table with annual data as well as
all records from the tables which had foreign keys in this main table.
The following only applies if you have some very complex queries including cross
tab, make table or stacked queries. Stacked queries being where one query calls
another and so forth.
I was having troubles in one particular very ugly cross tab MakeTable query with
the afore mentioned hidden "Global Options" form when I had to execute the query
in code. So I created the query using a standard name parameter "Season". I also
had to explicitly define the "Season" parameter in the query. So the Make Table
code ended up being a few lines longer.
My usual logic for executing an action query in code is
Currentdb.Execute strSQL, dbFailonError |
Instead, because of the parameter, I had to use the following
Dim db As Database, qdf As QueryDef, prmSeason As Parameter Set db = CurrentDb() Set qdf = db.QueryDefs("Team Forecast Report - Make Table") qdf.Parameters("Season") = Forms!GlobalOptionsHidden!goCurrentSeason qdf.Execute dbFailOnError |
[ Access Tips | Access | Main ]
Auto FE Updater distribute new and updated Front End databases to your users with several mouse clicks.
Granite Fleet Manager - the best designed fleet maintenance tracking and management system available
Comments email Tony | Search | Contact | Tony's Blog | Privacy Policy | Table of Contents |
Website copyright © 1995-2013 Tony Toews |