Full Version: Has to be a better way
UtterAccess Forums > Microsoft® Access > Access Queries
Greetings all-
I have almost completed my database sans a few functions that my old database had....
Oknow there is a better way to do this...so any ideas would be great.
THere is the deal:
First Function:
On my main form I have a combo box that has a persons rank. When the cbo get the focus, I set the value of a
non-visible textbox to the value of the combo box so it houses the persons current rank before I change the cbo. After the combo box updates a report is ran. On this report it has to show the Rank the person is getting promoted to and the Rank the person was previously. On the report I reference the main form for the previous rank.
Second Function:
On my main form I have a combo box that has a persons unit. When the cbo get the focus, I set the value of a
non-visible textbox to the value of the combo box so it houses the persons current unit before I change the cbo. After the combo box updates a report is ran. On this report it has to show the Currrent Unit, as well as the Previous Unit.... On the report I reference the main form for the previous unit.
There has to be a better way...
Any ideas would be great..this works...but I am now using lookup tables for my rank and units and the
old way is being stubborn.
By the way..I am using DLookup on my main form to reference the tables...just in case the info is helpful
Thanks all
Hi Daniel,
If you are wanting to keep track of these changes, perhaps it would be good to set up tables...
RankChangeID, autonumber
PID, long 'PersonID or whatever the ID is that refers to the person
RankID_old, long
RankID_new, long
DateChange, date
UnitChangeID, autonumber
PID, long
UnitID_old, long
UnitID_new, long
DateChange, date
this way, you can have an accurate history and don't have to rely on the system not crashing while the report is processed wink.gif
Then, you can print reports according to the date that the changes were done
setting a report filter
I think the problem here may be an inappropriate table design. First, a little database design background.

Neither Rank nor Unit are STATIC attributes of a person. They are both SEQUENTIAL attributes. Unlike birthdate, which is static (you are born on one and only one day and that can never change), rank and unit assignments change, or can change, over time. There are two key elements to understanding what a sequential attribute looks like: it can have different values, and there is usually a specific "Date of Change" associated with any change of values.

Depending on the purpose of your database, it may be acceptable to store sequential attributes in the same table as the table in which you store static attributes for a person: name, birthdate, etc. If you are NOT concerned about the history of rank, or the history of unit assignment, you can store these values in the same table, with or without the "date of change" for the values.

However, if you are concerned about storing a history of rank, unit assignment, etc. then you MUST have a separate history table to capture that information. A history table, obviously, needs several pieces of information, including the foreign key which identifies the person to which that attribute applies, the foreign key to the attribute table (rank or unit assignment) which identifies the value for that attribute, and the Date of Change.

In this case, where you are resorting to hidden fields on a form to TEMPORARILY store a history of Rank or Unit Assignment, it sounds like you DO need to capture history, and that your current database DOES NOT have these required history tables in addition to the two lookup tables that store the sequential attributes.

If you have a history table for rank and unit assignment, you can do away with those work around fields, and create queries which show last previous rank or assignment to provide values into the report. Plus, as an added bonus, you'll be able to see a complete history for each indivual.


Crystal beat me to the punch here. Use her suggested tables.


Edited by: GroverParkGeorge on Sun Jan 15 1:37:32 EST 2006.
agree..and yes I have to have record of all the chages..I was just, just...well I just didn't know before frown.gif
I have made the tables...per examples (a couple a small changes...but mostly the same)
In saying that I have a couple of concerns / questions.
On my main form I use a cbo for the user to input the rank and another one for the user to input the unit....
This is for when a new person gets there, and for changes as well.
How would I incorporate the change tables?
Would I just keep things the way I have them now for user input?
How would the Rank / Unit Change tables get updated?
Maybe I am a little lost here...but I am getting there (with your help of course)
Hope I am not being a pain frown.gif
We all gather here to ask and answer quuestions, so it's no problem at all.
ou'll need subforms for the rank and unit tables.
These are now separate tables, related one-to-many to the person table. Follow the subform wizard to add them.
Thanks frown.gif
Maybe it just isn't "clicking"..I dunno.
Ocreate the subforms for my main form...how does the whole old rank / new rank update to the table work..here is why I ask.
Let's say a person's current rank is SPC to promote a person in the database, the user uses the cbo and selects sergeant. How does the table know it is a "new record"? Will there be a record for evertime the cbo is changed?
I am just asking because she recommended RankID_Old Rank and RankID_New Rank..
To add to that..do I use my lookup table on each of those fields?
Quesiton after quesion...but I will get it
I appreciate your time
You will add new records in the subform the same as you would in the main form.

Actually, most of the time subforms are designed either as continuous form or datasheet because those formats allow you to see several,or all, of the history records at a time.

Obelieve you really only need one rankID field. Each time you add a new record, the last previous one becomes the "old rank".

The sample db I created for my book has a couple of examples of subforms like this. Look at the "Persons" form where there are subforms for phone and email. These are similar to history tables, except that they don't have date fields in them.

Edited by: GroverParkGeorge on Sun Jan 15 3:08:33 EST 2006.
thanks George....I would appreciate a sample..I learn quicker that way.
Done. See the edit above. It's not precisely the same, but close enough for you to see the idea, I think.
Ok..thanks George...i will toy around and see what I can do
Thanks for the advice..and I am sure you will be hearing from me again
Thanks again
You're welcome.
est of luck.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.