Page 1 of 5 12345 LastLast
Results 1 to 15 of 67
  1. #1
    DBID10T is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    46

    Total DB Newbie stuck HELP please!


    Good Day Everyone,

    I am attempting to simplify my life by creating a database for my small business. I run a small Electrical Contracting Business and spend large amounts of time getting information sorted by hand to write up invoices for hours and materials on a project to bill my customers. I have spend the past several days researching and watching some tutorial videos. I feel pretty good about my very BASIC understanding of DB design, but feel that I am at the point where an intermediate or advanced understanding is whats needed.
    Click image for larger version. 

Name:	AccessDBRelationshipsView.jpg 
Views:	35 
Size:	62.1 KB 
ID:	39989


    Any Advice on any aspect would be greatly appreiciated. I am trying to generate an invoice form by project that lists all hours and products used on said project. I am also trying to figure out howto take the retail price associated with a ProductID and show the value from the products table automatically when the item is selected for an invoice.

    Thanks in advance for any help,

    DBID10T

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    I don't think you need CustomerID in Invoice table. Customer info can be retrieved through link to Project
    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.

  3. #3
    DBID10T is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    46
    Quote Originally Posted by June7 View Post
    I don't think you need CustomerID in Invoice table. Customer info can be retrieved through link to Project
    I thought that might be redundant, removed the link and CustomerID from invoice table. I also compacted the Dbase per your advice about compact and repair for Upload as mine was too big to upload, thanks much. I think i have a LOT to learn about Forms and Queries.

  4. #4
    DBID10T is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    46
    DB File Attached for anyone interested

    WellcDbDesign1.zip
    Last edited by DBID10T; 10-21-2019 at 01:41 PM. Reason: Operator Headspace and Timing

  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,816
    Zip the file, 2mb zip file allowed.
    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
    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
    Here's a revision to your layout -eliminating crossed lines. (based on your database)
    Click image for larger version. 

Name:	DBID10T.PNG 
Views:	30 
Size:	67.5 KB 
ID:	39991

    No Project name or desc?

    I recommend testing based on your model before getting too deep into physical Access.
    Mock up some data and test scenarios and make sure your tables and relationships enable you to get the outputs/interactions you need.
    Too many people think
    -Access is just Excel's big brother
    -Access will fill in the details

    Neither of these is true.
    Excel and Access are built on different object models.
    Access won't do anything you don't tell it.

    It's your business and your time -be cautious.
    Good luck.

  7. #7
    DBID10T is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    46
    Orange,

    My thanks to you for your responses.

    -No ProjectName or ProjectDescription in Project Table was an oversight on my part.

    "I recommend testing based on your model before getting too deep into physical Access. Mock up some data and test scenarios and make sure your tables and relationships enable you to get the outputs/interactions you need."
    -I have data that I can enter from past jobs for testing, as far as relationships and getting the outputs/interactions I need that's where I am going astray. I am struggling with mid level concepts like I understand why I would only want to track Retail Price of a Product in one place (i.e. Product Table.RetailPrice) I am getting lost in howto call the price to my invoice detail when a particular item is selected.

    I am not sure if I follow you about excel and access. But I think I follow you about the mistaken relationship between them. Am am fairly certain Access is the tool I need to be able to organize and catalog all my information about jobs, parts used per job, hours per job, customer contact information from the mess of paperwork that currently lives on the dash of my service van, and allow me some actual freetime. I spend hours right now sifting thru the pile to find parts receipts and hours scribbled on cardboard to write an invoice for a clients project.

    "Access won't do anything you don't tell it."
    I think this is the crux of my issue, I don't know how to say "Access what were the parts and hours used on this project."

    Thanks for your advice.
    DBID10T

  8. #8
    DBID10T is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    46
    WELLCDesign1.01.zip

    Database with Oranges suggested revisions
    Thanks again mate

  9. #9
    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
    See this article first.

    Then, there are several articles here that may help. I recommend you work through 1 or 2 of the tutorials from RogerAccessLibrary mentioned in this last link. You will experience a process that you can use with your set up. Each tutorial will take 30-45 minutes, but you will learn to design your database.

  10. #10
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I did a couple of slight mods to the WELLCDesign1.01 dB. (NOTE that there should be only ONE decimal in the filename.)

    I removed the spaces in the table names.
    I added the prefix "tbl" to the table names
    I removed the default zero value in the FE fields.
    I added suffixes for the PK/FK fields. (easier for me to keep track of PK/FK fields - YMMV)
    Added "Cust" or "Vend" prefix to the City, State, Zip fields in tables "tblCustomerTable" and "tblVendorTable"

    Click image for larger version. 

Name:	Presentation1.png 
Views:	29 
Size:	166.1 KB 
ID:	39994
    Attached Files Attached Files

  11. #11
    DBID10T is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    46
    Steve,

    Thanks very much for this, I have been doing the suggested reading by Orange, about catering and widget databases, I understand now what I lack, this will be a great building block with your revisions but i still need to focus on howto make forms and queries that interact with the database.

    Thanks again much appreciated
    DBID10T

  12. #12
    DBID10T is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    46
    I started looking thru the Data Models at database answers this one in particular has my attention as it seems to be closest to what I am attempting to make. http://www.databaseanswers.org/data_...jobs/index.htm I have spent several hours looking over the exercises and am starting over with this template as a guide....

  13. #13
    DBID10T is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    46
    I think I have the beginnings of a database, I am still lost on how you would go about generating an actual readable invoice from the data stored in the Database.

  14. #14
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Build a report object based on a query that joins tables. Or maybe a report/subreport arrangement will be needed.
    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.

  15. #15
    DBID10T is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    46
    Well this is what I have now, I am not sure if this is progress or regression. June7 yea i think thats my biggets problem, i could have the best database on earth I still don't know how to talk to it and get the answers i need from it. Sadly Tutorials about queries and forms all seem to be the same if all i needed to do was query 1 page to find record with the highest this or that match a certain that I would be ok, but how to pulll the project hours and parts used into a nice looking form to use to bill the customer.... that's where i get lost. My brain is mush been at this all day, need sleep.

    Thanks for the help everyone, ill check in tomorrow,

    DBID10T















    Click image for larger version. 

Name:	DBtry3.jpg 
Views:	25 
Size:	121.3 KB 
ID:	39999


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

Similar Threads

  1. Help please, total newbie in the deep dish
    By Peanutdust in forum Reports
    Replies: 2
    Last Post: 09-14-2017, 05:35 AM
  2. Newbie getting stuck with summing queries
    By snorky in forum Access
    Replies: 2
    Last Post: 01-19-2012, 03:42 PM
  3. stuck on expression for total query records
    By pacer31 in forum Queries
    Replies: 3
    Last Post: 07-04-2011, 11:30 AM
  4. Newbie Here & Stuck
    By FOZILD in forum Access
    Replies: 5
    Last Post: 09-24-2009, 08:26 AM
  5. :?: newbie...grand total?
    By PnerraD in forum Access
    Replies: 1
    Last Post: 08-24-2006, 06:28 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