Results 1 to 4 of 4
  1. #1
    StatsAnalyst is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2016
    Posts
    2

    Effective Budget Design

    I'm fairly new to access and I am try to learn by working on some personal projects. I am currently working on a family budget. While working on this I ran across an issue with getting information into the tables for a form. I think my issue is related to how I designed the tables. Here is my design:

    Category
    id (pk)
    Name

    SubCategory
    id (pk)
    CategoryID (fk)
    Show (Boolean)
    Name

    Budget
    Id(pk)
    SubCategoryID(fk)
    Date
    Amount

    Transactions
    Id(pk)
    SubCategoryid(fk)
    store(fk)
    date
    amount
    notes

    Some subcategories will not appear every month (like Vacation, or Diapers), so the Show field in subCategories table is there to define if I want it to currently be displayed.

    When I am setting my budget for the month, I would like my form to look like is this:

    Category SUM(Budget_Amount)
    subcategory Budget_Amount
    subcategory Budget_Amount
    subcategory Budget_Amount

    Category SUM(Budget_Amount)
    subcategory Budget_Amount
    subcategory Budget_Amount

    Category SUM(Budget_Amount)
    subcategory Budget_Amount
    subcategory Budget_Amount

    Is the design I am looking at the best one for the implementation? Is the only way to get this design to work with VBA?

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Welcome to the forum..

    So, I have to say that I would revise your field names. "Date" and "Name" are reserved words in Access and shouldn't be used in object names. "ID" is a poor name for a field especially if every table has the same PK field name.

    Here is how I would name the tables:
    Code:
    tblCategory
    ----------
    CatID_PK (Autonumber)
    CategoryName (Text)
    
    tblSubCategory
    ----------
    SubCatID_PK (Autonumber)
    CatID_FK (Long)
    Show (Boolean)
    SubCatName (Text)
    
    tblBudget
    ----------
    BudgetID_PK (Autonumber)
    SubCatID_FK (Long)
    BudgeDate (DateTime)
    BudgeAmount (Double)
    
    tblTransactions
    ----------
    TransID_PK (Autonumber)
    SubCatID_FK (Long)
    Store_FK (Long)
    TransDate (DateTime)
    TransAmount (Double)
    TransNotes (Text)


    Is the design I am looking at the best one for the implementation?
    "Best" design is relative. The best design for me might not be the best design for you.

    Is the only way to get this design to work with VBA?
    You might be able to use just queries, but that depends on what you want to do with the budget and what you want to see.

    I am using an Excel spreadsheet I made years ago. It has months horizontally and categories down the left side. I can see what I planned to spend (budgeted) and what I spent (actual) for the month. (there are total rows)

    I can collapse rows so it doesn't display details. But I don't have the detail that you seem to want. I buy things from the store and it gets entered under "Groceries", not broken down to hamburger, Pepsi, paper towels, etc. A lot of things get entered under "Misc".


    But I find this an interesting idea.

  3. #3
    StatsAnalyst is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2016
    Posts
    2
    Thanks for the reply. The set-up is not so much I bought hamburgers, but that I bought from McDonalds or Wal-mart. I can use that data to see where I'm spending my money and also audit my bank statements each month to check for fraud.

    With using queries, I ran into an issue. The query returned a dataset structured as follows:

    SubCategory_Name Amount
    SubCategory (Blank)
    SubCategory (Blank)
    SubCategory (Blank)
    etc.

    When I tried to make a form based off of this, only the first result showed. All the other were considered additional records that I would have to go to next to see, instead of all of them on one page. Is this a data structure issue, or is there a better way to write the query?

    Query Used
    SELECT *
    FROM SubCategory LEFT JOIN (SELECT * FROM Budget WHERE Budget.Budget_Date = [Input Date]) AS b ON B.Budget_SubCategoryID = SubCategory.SubCategory_Id
    WHERE SubCategory.SubCategory_Show = True;

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    When I tried to make a form based off of this, only the first result showed.
    It sounds like the form was in "Single Form" view instead of "Continuous Form" view?


    In regards to the query, I don't know. I haven't built a dB for testing and I don't have what you are using. Again, the dB I build will be different that what you build - you are going for more detail than I thought. Does the query return the expected results when you execute it?

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

Similar Threads

  1. Selecting field based on effective date
    By Luffk73 in forum Access
    Replies: 1
    Last Post: 03-30-2015, 12:40 PM
  2. Replies: 7
    Last Post: 01-11-2015, 09:45 PM
  3. Calculating a Bonus based on "effective date"
    By mpreston14 in forum Queries
    Replies: 2
    Last Post: 07-09-2013, 06:49 AM
  4. Filter not effective
    By GraeagleBill in forum Programming
    Replies: 5
    Last Post: 04-08-2013, 02:18 PM
  5. Correct tables for an effective database
    By Sillub in forum Database Design
    Replies: 6
    Last Post: 08-04-2012, 09:21 PM

Tags for this Thread

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