Results 1 to 8 of 8
  1. #1
    Kraner is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Posts
    2

    Personal Finance Database

    Over the last few years I've become fairly proficient in Excel, but I recently got Access because most of what I was doing seemed more fitting for a database vice spreadsheet. I've figured out my Personal Property database, but I'm struggling putting together one for Personal Finance. Largely in part of my Excel knowledge and just playing around with Access with no guidance.

    Unable to attach the small file for some reason, so in a few words...
    There are four columns (sub category, item info, value, qualifier) with 25 fields under each (lookup with two columns from another table, short text, currency, short text respectively).
    I would like it this way so I can enter the data itemized like it appears on an actual receipt and so there is more data to manipulate later (i.e. use the qualifier to create a report with the details that I need to add to the Property database), and I see it as more efficient to just type than think about "okay, look down the list, so these two are this, now add them, cool. damn, missed one, redo..., and repeat a bunch of times". In excel it was as simple as using the sumif formula, but don't quite see anything like that in Access.

    I know it would work if I manually calculated the totals for the different categories and then entered them into their own field. But, I'm thinking there has to be a way to do it.

    TIA

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Database and spreadsheet are two different animals. You're thinking is aligned with spreadsheet.
    Where as spreadsheets tend to to be wide and shallow, databases are narrow and deep.
    There is a Database Planning and Design link in my signature that will take you to several articles related to database.
    If you spend some time with 1 or 2 of the tutorials from RogersAccessLibrary -mentioned in the link -you will get a quick experience with database concepts by working through it/them.

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by Kraner View Post
    Unable to attach the small file for some reason
    Typically if you do a compact/repair and then zip the file, it will be small enough to attach.

    Welcome to the site by the way!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    Kraner is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Posts
    2
    @orange - I will certainly check those out! I've always enjoyed just figuring stuff out, but I think your links will be very helpful
    @pbaldy - Thanks! I think it's there. Basically I would like it to combine all the same subcategories together (two Fast Food in the example). I'm assuming it needs to be it's own field. If so, it'd be nice to have an easy way to keep them updated if subcategories change over time.
    Attached Files Attached Files

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Advise not to use spaces nor punctuation/special characters in naming convention for objects (fields, tables, queries, forms, reports, controls). Nor reserved words as names for anything (Month, Date, etc)

    Data structure is definitely not normalized. Should be more like:

    tblReceipts
    RecID
    RecDate
    PayType

    tblReceiptDetails
    DetID
    RecID_FK
    SubCategory
    Amount

    If you want to allow multiple pay types for each receipt, normalization would mean another related dependent table but if you want to limit to 2 types, could get away with 2 type fields in tblReceipts.

    It is a balancing act between normalization and ease of data entry/output. "Normalize until hurts, denormalize until it works." Your denormalized purchase details does not work.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    You need to use Cascading Combo Box's to deal with your Categories & SubCategories.

    You should be able to use 1 Combobox to select a Category and the 2nd Combobox will display only those SubCategories associated with the Category selected.

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    OP is a long way from being ready to implement something as challenging as cascading comboboxes. Keep in mind that cascading combobox with lookup alias doesn't work nice on continuous or datasheet form.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi Kraner

    Whenever we see a table listing fields as shown in your "Form Data" table is screams not-normalized.

    What do the fields with names such as follows represent"-

    Value1
    Value2
    SC1
    SC2
    V3 TO V20
    SC(3) TO SC(25)
    Etc....
    Attached Thumbnails Attached Thumbnails Form Data.PNG  

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 9
    Last Post: 08-26-2018, 05:12 AM
  2. MS Access Personal Finance Budget Template
    By Kaloyanides in forum Access
    Replies: 0
    Last Post: 01-28-2015, 08:09 AM
  3. Personal Property - Asset or Inventory Database?
    By PoliticalOperative in forum Database Design
    Replies: 1
    Last Post: 05-31-2012, 03:03 PM
  4. insurance database for personal use
    By rmch77 in forum Forms
    Replies: 1
    Last Post: 06-29-2011, 01:23 AM
  5. Fulltime Access Developer with Finance experience
    By tchandler in forum General Chat
    Replies: 1
    Last Post: 12-20-2010, 04:03 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums