Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    abztaffyboy is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    7

    Beginner looking for help

    Afternoon all

    I'm looking for help to create a data base from scratch based on an excel spreadsheet that we have to manage PO's for our mechanic shop

    I would love to have the idea of clicking on a button to then open up a tab where we fill in the information and then when we hit enter the database is updated on like a master sheet.

    Can anybody help me with this? Its my first attempt at Access so id like some advice



    PO Staff member Location Date requested Department Supplier Service Requested Truck / Trailer Cost Completed Date

  2. #2
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Can you elaborate on what exactly each of those fields (columns) contains. I'm guessing you'll want about 8 tables at the moment. How do you figure [Cost]? Does one purchase order only ever have one service requested?

  3. #3
    abztaffyboy is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    7
    Ok so in the PO field will be a number generated by the team member, staff member is a persons name, Location will be one of possibly 6 cities, Department will be an option of where the person opening up the PO works in the company, Supplier is who is being issued the PO, Service requested will be what type of service we want to have provided to us such as work on vehicle or parts etc, truck or trailer is the unique number / licence plate number, Cost if known would be in US Dollar with 2 decimal places, Completed date would be when the service has been finalized eg received parts or the work has been completed.

  4. #4
    Peter M is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2017
    Posts
    67
    Is your excel worksheet working for the most part? Is your main goal a nicer interface for entering information?
    If yes, then as a beginner starting point I would create a new Access database and import the Excel table into a new table (e.g. tbl_PurchaseOrders)

    Will you ever have more than one row (record) with the same PO Number? If not, use the PO as your Primary Key in the table design.

    From there, you could you the Form Wizard to make an "Edit Form" and an "Add Form."


    (Once that main table is smoothed out, you can then build additional features like limiting the city options, names of staff members, etc.)

  5. #5
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Will it always be just one service per PO?

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I suggest that you describe to readers in a few lines of simple, plain English (no jargon and no database) about a "day at work".
    As kd said ---sounds like ~8 tables, and that could be, but we need to know more abut the business and processes in clear terms to better understand your set up and needs.

    Here is a free, generic data model from Barry Williams that may be helpful for context.
    Good luck.

  7. #7
    abztaffyboy is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    7
    Ok so what my day currently revolves around speaking to suppliers, clients providing reference numbers (po’s) so we can reference back to it when we get an invoice from them. The main issue is that we have a possibility of nearly 20 people all trying to book services using this method.
    Tracing what has been ordered is a huge problem for us

  8. #8
    abztaffyboy is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    7
    Yes for the most part however so many people trying to access it is a nightmare.

  9. #9
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    To help you get started and to understand database development, work through these tutorials - should only take a couple of hours to work through them.
    Roger's Access Library > Tutorials


    You could post a copy of the spreadsheet (change any sensitive info first). It would help us understand the data you are working with.

  10. #10
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Quote Originally Posted by abztaffyboy View Post
    Ok so what my day currently revolves around speaking to suppliers, clients providing reference numbers (po’s) so we can reference back to it when we get an invoice from them. The main issue is that we have a possibility of nearly 20 people all trying to book services using this method.
    Tracing what has been ordered is a huge problem for us
    OK. So you have Clients and Suppliers and Invoices and supply some sort of Service(s). But what is your Business and the general business processes.

    Eg:
    We are a vehicle repair center who does repairs and maintenance services for clients.
    We have X licensed auto mechanics, a Parts area and deal with Q Parts Suppliers.
    Our Client appointment scheduling is as follows.....
    As for Parts ordering and Inventory we do the following.....
    Our biggest issue at this time is... We are losing clients because of poor scheduling / can't keep proper appointments....
    My position in this organization is....


  11. #11
    abztaffyboy is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    7
    Quote Originally Posted by orange View Post
    OK. So you have Clients and Suppliers and Invoices and supply some sort of Service(s). But what is your Business and the general business processes.

    Eg:
    We are a vehicle repair center who does repairs and maintenance services for clients.
    We have X licensed auto mechanics, a Parts area and deal with Q Parts Suppliers.
    Our Client appointment scheduling is as follows.....
    As for Parts ordering and Inventory we do the following.....
    Our biggest issue at this time is... We are losing clients because of poor scheduling / can't keep proper appointments....
    My position in this organization is....

    The company is a local trucking company here in Houston who is very much in the intermodal container trucking.
    Ok so i manage a mechanic shop with 8 mechanics working on upto 150 vehicles at any given time
    we use various suppliers to receive dedicated parts for each truck
    we also use suppliers for off site work on trucks that we cant get back to our office
    The biggest issue we have is that people currently don't log the details correctly and we sometimes get invoices for either services / parts that we didn't know were ordered. then we spend time chasing down who ordered it etc

  12. #12
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Here's my take on a model of turning your spreadsheet into a very simple database. (I'm an amateur, this very well could be a bad design.)

    Click image for larger version. 

Name:	DataModel2.png 
Views:	34 
Size:	47.2 KB 
ID:	35784

    Or if a purchase order has many services:
    Click image for larger version. 

Name:	DataModel.png 
Views:	33 
Size:	47.8 KB 
ID:	35785

  13. #13
    abztaffyboy is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    7
    i love the 2nd one. its exactly what i need to see.
    Now all i need to figure out is how to make this look in access. behind each subject heading id love what you have placed. im really not sure what the FK or PK at the end of things are so if you could let me know that would be appreciated

  14. #14
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    PK and FK suffixes are a convention that mean Primary Key and Foreign Key respectively. A primary key is a unique id number used to identify one record (row) in a table. A foreign key a reference to another table's primary key. It's a fundamental concept you'll want to understand for working with access. Most of the time you'll see db's use the suffix ID for both primary and foreign key pairs in access. I've only recently started using PK and FK instead of just ID, it's a bit more descriptive as to the purpose of the field.

    Keep in mind that the PK/FK/ID is usually just a number that shouldn't have any meaning to the user, only to the computer. The user doesn't even need to ever see them.

    Googled this up:
    http://www.differencebetween.net/tec...d-foreign-key/

  15. #15
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Here is a link to various articles re Database Planning and Design.
    I recommend you spend 30-60 minutes working through 1 or 2 of the tutorials from RogersAccessLibrary mentioned in the link. You will learn concepts that can be used with any database. You learn by doing the tutorials.

    It is also critical to clearly identify your needs. For that, the tutorial video(s) from BA-Experts (also mentioned in the link) will be helpful.

    You need to work from 30,000 ft level to some details, and you know the environment better than anyone. Don't be too quick to jump into physical database. Easier to solve an issue/problem when testing a model on paper with some test data than to "fix" a poorly designed physical data structure and related procedures.
    Good luck.

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

Similar Threads

  1. Help for a beginner
    By Beecharmer in forum Access
    Replies: 1
    Last Post: 03-04-2014, 09:44 AM
  2. 2 Beginner questions
    By Willump in forum Access
    Replies: 9
    Last Post: 01-23-2013, 04:32 PM
  3. Beginner needing help!
    By AussieAsh in forum Access
    Replies: 2
    Last Post: 01-23-2013, 11:20 AM
  4. Expression help for a beginner
    By Badvgood in forum Queries
    Replies: 5
    Last Post: 12-01-2011, 05:31 PM
  5. Help a DB beginner
    By adquinn in forum Access
    Replies: 0
    Last Post: 02-08-2011, 08:25 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