Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    CBB57 is offline Novice
    Windows 10 Access 2007
    Join Date
    Feb 2021
    Posts
    4

    Database design, relationship, and forms - not sure where my problem is

    I am definitely a Novice and desperately need help. I have a Construction Project database I am working on. The problem I am having is this: I have a Customer Table, and a Table for each - Budget, Profit, and Cost. Cost has no records in it yet because I have another table called Cost Detail. So under Cost I have 60 items such as Admin Cost, Building Permit, etc. Each has a unique number (i.e. 1= Admin, 2=Building permit). In the Cost Detail I have the CodeID which relates to the unique # in the Cost table. So I might go into that table and say I spent $50. yesterday at 84 Lumber, $100 today at Lowe's. I want to be able to have this come under each customer. So for Customer A, I want to be able to see that I have $150 total under Admin Cost and broken out I can see that $50 was to 84 Lumber and $100 at Lowe's. Any help is greatly appreciated. Thank you so much!

    Attached Files Attached Files

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You are a self-admitted novice. My suggestion is that you back away from the physical database at the moment.
    In plain English, write a description of your business starting at the 30,000 ft level and gradually add some details.
    Key to a successful database is to have well designed tables and relationships that support your business requirements.

    I recommend this tutorial from RogersAccessLibrary for learning about database design. If you work through the tutorial(~1 hr.) you will experience a process that can be used with any database.

    Do not be too quick to jump into physical database. It is much easier to test a data model and adjust as necessary than a physical database. A well tested data model with data and scenarios will lead you to a blueprint for your database.

    The Database Planning and Design link in my signature has many articles that may be useful to you.
    Good luck.
    Last edited by orange; 02-13-2021 at 12:46 PM. Reason: spelling

  3. #3
    CBB57 is offline Novice
    Windows 10 Access 2007
    Join Date
    Feb 2021
    Posts
    4
    Thank you Orange. Actually I have done all you say. I previously found your advice in search on database design, read it and also worked on the exercises you mentioned. I will look at them again of course. I also redid my database design based on what I learned when I studied that articles mentioned. But because I can't seem to get it to come out right, I feel like I may have an error that someone more proficient would recognize quickly and that is why I posted. Again, thanks.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    OK.

    What exactly does this mean?
    I can't seem to get it to come out right

    Do you have a list of requirements for your database? Anything in support of your current design would be helpful for readers.

    Can you walk us through a day in the life of your business or something similar that shows us how Jobs and Customers etc relate to one another?
    What does spreadsheet info involve/fit?

    How about definitions of each of your "things"? Budget, Cost, Profit.
    table_name
    JOIN5
    QBIMPORT5
    Spreadsheet Info
    tblBudget/EstimatesT
    tblCostDetailT
    tblCostT
    tblCustomerInfoT
    tblDraws
    tblJobInfoT
    tblJoint
    tblProfitT
    tblSSCodes
    tblVendorList


    Isn't profit a calculation of some sort based on Sale - Cost?

    I recommend you do NOT use any field or object names with embedded spaces or special characters. Only the underscore "_" is accepted by Access without special consideration.

  5. #5
    CBB57 is offline Novice
    Windows 10 Access 2007
    Join Date
    Feb 2021
    Posts
    4
    The construction company is a small business that does new construction or remodeling for a customer. Currently, the construction manager uses Google spreadsheets to enter an estimate that turns into a budget, an estimated profit (which will be a calculation in Access), and then what the job actually cost. Did he come in on or under budget. Then we use Quickbooks for accounting which records the actual cost of each job. I am attempting to bring these 2 together so that we have less manual entry/work and have good numbers that everyone involved is looking at. I hope this addresses your requirement question. We will do a weekly import from Quickbooks to Excel to Access which will bring in the data pertaining to actual expenditures or deposits from customer.

    A day in the life of the business - Example: we have a new construction job with a Google spreadsheet that says the budget for the job is $450,000, with $50,000 budgeted for framing labor. The framing is completed and 2 weeks later a bill flows thru the Quickbooks accounting software and checkbook of course that indicates framing labor is $40,000. The construction manager would like to see if he has his job on budget so thru Access, he will be able to look at a report and see that he is actually under budget on this item. We will also be able to show deposits a customer has made to the company during the construction process. Also, thru Access he will be able to see his estimate, provide a customer with an estimate, etc.

    The tables that are being used are as follows:
    tblBudget/EstimatesT - I will change to the appropriate naming conventions, thk u; this is a table with the budgeted amount for each item on a specific job; not all items (1-60) will be used on every job.
    tblCostDetailT - table to hold cost as entered by construction manager as he keeps track before united with Quickbooks info
    tblCostTtblCustomerInfoT - Customer table, name, address, etc
    tblJobInfo - table for type job, status and percentage to charge for profit
    T
    tblJointtbl - a table to join from the Customer info to all the Spreadsheet tables (i.e. Budget, Profit ,Cost )
    ProfitT - table to calculate each individual items with a anticipated profit
    tblSSCodes - identifies 60 items used to budget, estimate, ultimately show profit and cost
    tblVendorList - identifies vendors for drop down menu

    The profit calculation will be the budgeted amount (tblBudget/Estimates) times a certain percentage (tblJobInfo) that construction manager enters.

    "I can't seem to get it to come out right" - this means that as I already stated, I want to be able to have a Customer, who has a Budget, Profit and Cost numbers and keep track of the possible 20 different costs that could apply to one Budget item (60 total). I have understanding on the Budget and Profit part - its the Cost part that is causing me angst.

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I have no working knowledge of Quickbooks.I have responded to a few posts over the years. I did a quick search in the forum and found over 160 posts with a reference to Quickbooks. I only mention this to say that there are others who use both Access and Quickbooks in some way.
    From my own experience, I know that synchronizing data on different platforms/software can be quite challenging.
    Google sheets, Quickbooks, Excel and Access seems quite a mix. I recall some people interfacing with Quickbooks from Access via ODBC. I don't know if that is of any value to you.

    Can you give a very explicit sample of data to highlight your Budget vs Profit issue? Perhaps readers can help with the underlying logic and/or suggest some concepts/options.

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    There might be some guidance here, items 273,4,5
    http://www.databaseanswers.org/data_...all_models.htm
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I agree with Micron that Barry Williams' site has many great data models and those he suggested may help with your set up.
    However, the fact that different processes are in different "environments" may make maintaining data sync an issue.

  9. #9
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You asked
    Database design, relationship, and forms - not sure where my problem is
    I would have to say it is the table designs & relationships.
    Look at this image
    Click image for larger version. 

Name:	Table1.png 
Views:	56 
Size:	89.9 KB 
ID:	44233
    Your design has 2 repeating fields:
    Draw1, Draw2, Draw3,....
    and
    Draw1Date, Draw2Date, Draw3Date,....

    What would happen if the customer wanted/needed more draws? You would have to edit the tables, queries, forms, reports and VBA code.
    Now look at my design. With this design, you can have an (almost) unlimited draws.

    ================================================== ================

    What about this table?
    Click image for larger version. 

Name:	Table2.png 
Views:	53 
Size:	120.5 KB 
ID:	44234
    In your design almost every FIELD is DATA and shouldn't be field names.
    What would you have to do to add another Budget/Cost item or 3?
    Again, you would have to edit the tables, queries, forms, reports and VBA code.
    You already have a table "tblSSCode", so you could use a combo box (bound to tblSSCode) on a form so you wouldn't have to type in the items and the spelling would be consistent.

    You can see I named my table "tblBudgetedActualCosts". You could add an additional field for the account type (Budget or Cost) to make it easier to calculate Profit... but I forgot to add this to the table design.



    BTW, most of your table designs are known as "Committing Spreadsheet", because the design mimics a spreadsheet design.
    It is probably one of the most common errors people commit in designing tables.

  10. #10
    CBB57 is offline Novice
    Windows 10 Access 2007
    Join Date
    Feb 2021
    Posts
    4
    Thank you so much for your input. We had lost power and just got it back so I am reading what you are saying and definitely see what you mean on the tblDraws table. I want to give it some thought on the other table you created. I know you are right, I want to think about it and make sure I understand but I just wanted to say thanks to everyone. Also, for Micron, I will study the information you have sent me in the link. Thanks.
    Last edited by CBB57; 02-14-2021 at 05:26 PM. Reason: left out information

  11. #11
    Amyfb is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2021
    Posts
    64
    [QUOTE=CBB57;470949]I am definitely a Novice and desperately need help.]



    I too am a novice, albeit with a bit of experience over the last few months.

    The best thing I did for myself recently was spend $200 of the company's money for a 1 year subscription to a website with tutorials for beginning and advanced Access how to. I watched, so far, eight hours of beginner videos and even the modules where I thought I knew it all, I learned something fresh.

    I started skipping around the advanced lessons but realized quickly that there are no shortcuts to a decent database design.

    My favorite reference book is Database Design for Mere Mortals and I need to do a reread of that as well as watch more videos!

    good luck to you!

  12. #12
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    @Amyfb

    You have jumped into the middle of an existing thread with a new subject. Often called thread hijacking.

    I agree with you that the Hernandez Book you mentioned is an often quoted reference.
    See the link in my signature Database Planning and Design for several articles, tutorials and references to help you with your database learning.

  13. #13
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You have jumped into the middle of an existing thread with a new subject. Often called thread hijacking.
    Only when the poster posts a problem? I see that as just a helpful comment on how a novice can get help. Even the first line of the first post was quoted.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  14. #14
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    And did you notice that the last post was 02-14-2021?

    But I commend you on all the studying/watching. (I am also still studying and learning )

  15. #15
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Dang! I had the wrong tab active! Moving this post.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Design / relationship issues with small database
    By Beachbum808 in forum Database Design
    Replies: 1
    Last Post: 05-05-2020, 04:19 AM
  2. Database design relationship problem
    By cysklement in forum Access
    Replies: 4
    Last Post: 12-04-2014, 08:47 AM
  3. Problem with table and relationship design
    By fekrinejat in forum Database Design
    Replies: 4
    Last Post: 03-19-2013, 12:11 PM
  4. Replies: 2
    Last Post: 11-19-2012, 10:54 AM
  5. Forms design problem
    By GeorgeD in forum Forms
    Replies: 2
    Last Post: 05-08-2008, 12:28 AM

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