1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Access Database Advice

Discussion in 'Software Development' started by wwe9112, Oct 10, 2015.

Thread Status:
Not open for further replies.
Advertisement
  1. wwe9112

    wwe9112 Thread Starter

    Joined:
    May 4, 2008
    Messages:
    85
    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. draceplace

    draceplace

    Joined:
    Jun 8, 2001
    Messages:
    2,583
    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. wwe9112

    wwe9112 Thread Starter

    Joined:
    May 4, 2008
    Messages:
    85
    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:
     
  4. draceplace

    draceplace

    Joined:
    Jun 8, 2001
    Messages:
    2,583
    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. wwe9112

    wwe9112 Thread Starter

    Joined:
    May 4, 2008
    Messages:
    85
    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. draceplace

    draceplace

    Joined:
    Jun 8, 2001
    Messages:
    2,583
    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.
     
  7. draceplace

    draceplace

    Joined:
    Jun 8, 2001
    Messages:
    2,583
    I guess a user Type is needed in that scenierio
     
  8. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1157624

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice