Results 1 to 10 of 10
  1. #1
    krizzle is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    3

    New Access User Project Help

    I'm fairly new to Access and I'm trying to set up a database to track projects and materials I will be working with. My projects/materials consist of sheets of acrylic cut on a laser cutter. I currently have a table containing all of my materials, material type, cost, size of material in sqIN etc. Then another table containing project information such as customer info,date received and due, size of the material being used in sqIN. I have my two tables set up as well as a basic form based on the project table but am not sure how to link the project to the material being used. I have a dropdown in the project form that pulls the values from the materials table but I am unsure how to use those values to calculate my project price within the form. Any help is greatly appreciated.

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    7,125
    You are saying you only have 2 tables? If yes, then time to stop and fix that.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

  3. #3
    CarlettoFed is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2019
    Posts
    68
    Attach the file to see the structure you made.

  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
    14,905
    Krizzle,
    I suggest you work through 1 or 2 of the tutorials from RogersAccessLibrary identified in the Database Planning and Design link in my signature. Each tutorial will take about 45 minutes to complete, but you will learn and what you learn can be used with any database. The tutorials have a problem statement, some working guides and a solution. May be the best 45 minutes you'll ever spend on database.
    Good luck.

  5. #5
    krizzle is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    3
    Orange,
    I will definitely go through those tutorials as I want to further my skills.

    I have also attached the database for further help on this topic. Is using 2 tables the wrong way to go about this? My goal is to calculate the price of the material needed for each project by calculating the percentage from material needed per project.
    Attached Files Attached Files

  6. #6
    mike60smart is online now Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    326
    Hi Krizzle

    So 1 Customer has 1 or More Projects and Each Project is made up of a number of Materials.

    Does the above statement cover your process?

    If not, then please give a detailed explanation of your actual business process.

  7. #7
    krizzle is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    3
    Hey Mike60smart,

    That's exactly my process. I wanted to start with 1 customer to 1 project/material at a time and once I got familiar with access I can add on multiple materials per project, but yes that's my workflow. My goal is to just track customers and projects and have access do the math of what the final project material cost will be based on the chosen material from the dropdown.

  8. #8
    mike60smart is online now Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    326
    Hi Krizzle

    I would name your tables as follows:-

    tblCustomer
    -CustomeriD - Autonumber - PK
    -CustomerFirstname
    -CustomerLastName
    -CustomerPhone
    -CustomerEMail
    -(Plus any other Customer details you need)


    tblCustomerOrders
    -CustOrderID - Autonumber - PK
    -CustomerID - Number - FK (Linked to PK from tblCustomers)
    -OrderDate
    -DueDate
    -InvoiceNumber

    tblOrderDetails
    -OrderdetailID - Autonumber - PK
    -CustOrderID - Number - FK (Linked to PK from tblCustomerOrders)
    -MaterialQty
    -MaterialID - Number - FK (Linked to PK from tblMaterials)

    tblMaterials
    -MaterialID - Autonumber - PK
    -MaterialTypeID - Number - FK (Linked to PK from tbluMaterialTypes)
    -MaterialThickness
    -MaterialColorID - - Number - FK (Linked to PK from tblMaterialColors)
    -MaterialDimensions
    -MaterialCost

    tbluMaterialTypes
    -MaterialTypeID - Autonumber - PK
    -MaterialType

    tbluMaterialColors
    -MaterialColorID - Autonumber - PK
    -Material Color

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,905
    Start with a description of the business to ensure you have identified the scope of this project. You can start at very high level and gradually add details.
    You have Customers who can have 1 or many Projects. A Project can have 1 or many types of Materials and various amounts of each type of Material. Traditionally Projects can have estimated start and completion times, estimated resources and also have actual start and completion times. They also have milestones and status. But it isn't clear if you are doing forecasting and planning for specific projects and/or materials. Are you also maintaining materials management/inventory (ordering/receiving/warehousing/dispersal). Better to get an overall picture of your project and how the pieces fit within your business than to start with 1 customer, 1 project and attempt to expand to whatever is needed. That's the trial and error -design as you go approach - that is often unsuccessful.
    Do the analysis and design before you get into serious coding. Do it on paper; get a plan and blueprint before you build.
    Good luck with your project.

  10. #10
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    7,125
    I agree with Orange and this is one reason why: the suggested tables reflect one person's interpretation of the entire process (which may be just fine). However, I would point out that when a cost is going to be used in calculations you have to ensure that updating this cost won't affect old results. You might need a date field that can flag that cost as obsolete yet still be used for prior calculations, and a new record for the current price for new calculations. It all depends on the needs of the process. If that turned out to be true, then it might be said that you're still not taking a thorough planning approach as opposed to running with what seems good at the time.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

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

Similar Threads

  1. Replies: 2
    Last Post: 04-07-2014, 10:01 AM
  2. Replies: 13
    Last Post: 11-18-2013, 02:20 PM
  3. Replies: 1
    Last Post: 11-12-2013, 05:57 AM
  4. Multi User Project
    By ganeshvenkatram in forum Access
    Replies: 1
    Last Post: 06-20-2011, 09:57 AM
  5. Replies: 22
    Last Post: 01-25-2011, 11:19 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 - Senior Forums