Using data from the previous year

(Page updated 2010-09-27)

[ Main | Access | Tips ]

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

Instead, because of the parameter, I had to use the following

[ 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