Tech Support Guy banner
Status
Not open for further replies.

Access Database Advice

1K views 6 replies 2 participants last post by  draceplace 
#1 ·
I am working on a class assignment. Basically we are a group programming a business application:

We are making a personal finance application, and I am working on the database in Access. I have a table for credentials which has the username password and ID. The next part is where I need advice and perhaps reference if you can. I need to have an expenses table of some degree. I was thinking about making various tables with a date column. I would have many of these (the date column is for an object in the actual application), there will be many expense tables(obviously) I was wondering how would I structure that? Would I make a table of just foreign keys and one of the foreign keys link back with the userID? How would you actually program that in VB? Would I reference the actual table of the expense(say savings) or would I reference the table of just foreign keys? Advise, tips?

Thanks.
 
#2 ·
This site is not a good place for homework help. Its homework not get somebody else to do it assignment.

That said here's a starter suggestion:
It depends on what your wanting to track. If its basically a 'checkbook', I would start simple with 3 basic tables. Transactions, Transaction_Detail and Master_Data.
Transactions' ( date and transaction ID) and link it to Transaction_detail table by TransactionID, A Master_Data table that would contain data that doesn't change(Like Name Address, Phone, Account number and such.) associated with transaction_Detail with Master_Data key.

Don't get crazy with a bunch of tables. You can query for debits or credits from the Trans_Detail table. Try to work the whole thing from queries, don't set the form on top of a table. Get your relationships squared away early in the process.

If its 'personal finances' there will be no need to have 'multi users' logging in or sharing the table. Access will allow you to password the whole database. Assuming user table is just part of the 'exercise'? Just validate the user ID to system user ID (on table) then prompt for password when opening the main form. That can be done later as it may make developing a pain. Not sure what the difference is between username and ID is?

VB won't be needed until you start putting buttons on a forms to drive transactions and reports.
 
#3 ·
I am unfortunately required to have user ID password and things of that nature lol. The basic premise of the application is paraphrased to, "A duel family household each has their own expenses, and incomes, design an application to help each individual person track their finances without access to another person(s).

Which is where we were assigned to must include logins and that kind of thing. Here is what I came up with:
We have 3 tables so far, there will probably be 2 more for the income tables.
tblCredentials
tblExpenses
tblExpenseType

tblCredentials
-> CredentialsID (AutoNum)
->tblCredentialsUsername (ShortText)
->tblCredentialsPassword (ShortText)

tblExpenses
->ExpenseID (autoNum)
->UserID (Number)
->ExpenseTypeID (Number)
->DateOfExpense (Date/Time)
->AmountOfExpense (Currency)

tblExpenseType
->ExpenseID (autoNum)
->ExpenseType (shortText)

Relationships
tblCredentialsID -> 1toMany (tblExpenses[userID])
tblExpenseType ->1toMany (tblExpenses[ExpenseTypeID])
 
#4 ·
Your off to a decent start. Not seeing the transaction table. I would turn tblExpenses into tbleTrans table. If you don't keep all debit/credit transactions on the same table your will later pay. With multi user requirement your going to want ad Username or CredentialID to all transactions so you can provide the 'personal view'. You have UserID in tblExpense but not its not a field in credentials, that needs cleaned up.
 
#5 ·
Not quite the direction I am going onto. Maybe I am misunderstanding you or vise versa lol.

Basically the application you click on it, the application pops up with a sign up or sign in button.
User clicks sign up enters a username and password clicks continue.
User goes back to the screen to log in, enters that information.
User goes to a home page. From this page a user adds money to their account (not real money obviously lol)
They now can add an expense.
User adds an expense.
User is shown that they have x left over on a graph or something(not that far yet).

I have a credentials table income table income type table(categories for the income table) expense table expense type table(categories for expenses).
The UID has everything lead back to it. Or at least should lol.

How do I go about actually (do you have a resource or something, I'm not looking for actual answers obviously. I am a visual learner so I do better if I can actually see someone doing it with the way they think...you know?) How do I go about actually making sure the data entered for person A isn't going to show up for person B. How do I actually make sure that Person A finances isn't being calculated with person B. Do I have to do anything special, or does the relationship in the DB actually take care of that? There doesn't seem to be much anything online that is just basic A B C D kind of thing for this. Does that make sense or did I fry my brain and am over thinking lol.
 
#6 ·
The queries or sql strings in the background should reference the current user in the criteria. I'll try and remember and look for an example. Busy collecting a paycheck at the moment. You will want to include and admin login that allows admin to see it all.
 
Status
Not open for further replies.
You have insufficient privileges to reply here.
Top