I am using Access 2010. I have not created a database in over ten years and have completely forgotten most of the rules. Although I have found some refresher tips online, I'm still having a really hard time with this. I think I might be over thinking it.
Basically I own a timeshare and am trying to create a database to help me keep track of my points. I figured if I kept it simple with only two tables really needing data entry, the rest of the information I would want could come from reports.
Here are the four components I've been trying to design the database around.
1-Each year we receive points.
2-The points expire in two years.
3-You can use points for rooms and discounts (multiple uses).
4-Points that don't expire are rolled over into the next year.
1- For the points we receive I have created a table called New Points. I thought it would be easiest to treat each year points are received as a new record so I made the PK of this table Points Year.
2 - To handle the expiration I just entered a field for an expiration date on my New Points table. I figured this data will be pulled by a report (only because I know of no other way to make an expiration date work than running a report to list dates.
3 - For this I created a second table called Points Used.
4 - I didn't work on this part yet.
My problem was in trying to make a connection between the points we receive and the points we used. I tried a relationship beween the two tables and also tried creating forms with expressions in certain fields.
I want to be able to enter into the forms the new year's points and then what points have been used (even if it's multiple entries) leaving a "Remaining Points" field that's accurate. I got it to work but only for the two records that were displayed (Record 1 New Points & Record 1 Points Used)...once one of the records was changed the data disappeared and started over. (So if I stay on Record 1 New Points & change the subform to record 2, the formula doesn't work until new info is entered).
Please look at my attached attempt at the DB and HELP!!!! What am I doing wrong? What am I missing? I'm I over thinking things? I would greatly appreciate anyone's assistance!!
Sindho, thanks for responding but I'm not really quite sure what you mean. As I mentioned, I have done DBs in years. I think I understand what you mean but I'm not really sure how I would create the transaction table to or what you mean by only one item (the Point). Sorry...It's basically as though I've never created a database before. It makes me so sad.
1. You have one item to handle in your stock: the points from the timeshare you own.
2. As for any stock management system, there are movements (or transactions):
a) Add to stock (you receive new points that must be added to the amount of points you already have)
b) Remove from stock (you spend a given amount of points that must then be substracted from the quantity in stock).
Those movements can be stored in a Transaction table with the following information (for instance):
-Date of transaction
-Type of transaction
plus one or several additional pieces of information (reason of the transaction, etc.)
3. The only special case here is that the points you receive have a limited period of validity. You must then imagine a mechanism to regularly check which points are near their expiration date. It should not be very difficult to create a mechanism (one or a few queries or some VBA code that would loop into a RecordSet open on the Transaction table) to make a survey of the "stock" in a given time frame:
I don't know if every point is uniquely identified, which would mean that you have to spend this precise point within its period of validity, or if you simply receive x points every y period of time. Depending on this, the system could be more or less complex but the principle remains the same.
In the first case, you have a stock management system such as those used for perishable goods (food products for instance), while in the second, more simple system, you only track the amount of items in a specified time frame.
You can even imagine an archiving mechanism that would allow you to keep the transaction table rather short: all transactions older than a given number of months, or year, being extracted from the "living" Transaction table and stored elsewhere (another table, a file in csv, xml or any other format you see fit).
Ok, I think I see what you are saying. My flaw seems to be in the initial design of the database (as I said, overthinking it). It is a bit more complex because points only last for two years and if those points assigned for those two years are not used then they expire and are lost (unless we pay to extend them for two more years but I don't think we will).
I will start from square one and try redesigning it then try constructing it again. I appreciate your help!
If each point has a specific validity period, you must store it whith this period and also a unique ID. You must then implement a FIFO (First In First Out) mechanism, so that when you want to "expend" some points (i.e. remove them from the stock), the older ones (but still valid) are removed first. It should be an interesting application to build.