Page 2 of 2 FirstFirst 12
Results 16 to 24 of 24
  1. #16
    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

    Michael,

    I don't know if you have seen the tutorials from RogersAccessLibrary or not, but I have found them very helpful. They deal specifically with business facts and a process to identify and test tables, attributes and relationships. I recommend you work through 1 or 2 of the Rogers tutorials mentioned in this Planning and Design link (also in my signature), then apply what you have learned/experienced with your own situation. Most problems with database can be traced to design/structure issues. So getting your design aligned with your business requirements and adhering to database concepts is key, and is best done before getting too deeply into physical database(Access, Oracle...). In my view you are not ready for forms or tabs etc; you are still asking questions re tables/attributes. Get a tested/vetted data model as a blueprint before jumping into Access with both feet.
    There are several other articles in that link that may be helpful to you.
    Good luck with your project.

  2. #17
    MichaelBilo is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Posts
    13
    Orange,
    Thank you. I have seen you post these links in response to others and have begun to view the tutorials. I have watch many hours of video on Access...challenge is I have a deadline and limited budget...going to have to do my best...this forum is very helpful.

  3. #18
    Join Date
    Apr 2017
    Posts
    1,673
    Btw, keep your terminology together. Your customer sends his Purchase Order to you, as he want to buy your product/service. In your database, you can't register it as Purchase Order - you don't purchase your product/service from customer, you sell it.

    Additionally, you have to buy materials from suppliers to fill this order. How do you name the order you send to supplier, when Purchase Order is used elsewhere - your bookkeeper may be annoyed

  4. #19
    MichaelBilo is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Posts
    13
    Appreciated...but not accurate....we are the sub-contractor...the customer does not send us his PO....we sign a contract with a General Contractor and we generate a PO for our work under that Contract...all suppliers MUST use that PO on all orders related to that job....we do not send an order to a supplier....they name the order with our PO....

  5. #20
    MichaelBilo is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Posts
    13
    Micron, I took your advice...
    Licenses
    Here are is the current listing of our Trades Licenses...some are required in the state of Rhode Island and others in Massachusetts. Thank you for your suggestion. I will now create a junction table between Employee and this table and add the license number....other suggestions??
    Click image for larger version. 

Name:	tblTradesType.png 
Views:	21 
Size:	55.6 KB 
ID:	40474

  6. #21
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Maybe. I'd have to review what I wrote last and your subsequent responses. Problem is, you have not said if the image of your relationships window is wrong or not, and I did ask already...

  7. #22
    MichaelBilo is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Posts
    13
    Image is not accurate. I have been working on my design all day...I will post a new design in the future....relationships are not correct....thanks for your responses.

  8. #23
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Just a comment - instead of (or in addition to ) posting an image, it is extremely helpful to post the dB. Having the dB answers a LOT of questions without having to ask and wait for an answer....
    When the design changes, just post the new design..

  9. #24
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    tblLicenses
    LicenseID
    LicenseType
    LicenseNumber
    EmployeeID (FK)
    Yes
    Materials...each material has a unique SKU from the Supplier...are you suggesting remove the SupplierID (FK) from my materials table and connect materials and suppliers with a junction table?
    Yes. Then if you get 3/4" x 10' pipe from 3 different suppliers, tblSuplr.Suplr has only 1 entry for ABC Steels, there is only 1 record for steel pipe for that size and length, but the junction table lists the same material PK 3x (value is the fk value from materials) with one record for each supplier id (again, PK values from supplier table).
    Any insights on Timecards
    That would depend on the process, which can be quite involved. F'rinstance, are there different shift rates? Trade rates? Rates within trades according to license (e.g journeyman vs 2nd year apprentice)? Overtime/holiday rates paid by customer? Work orders? Equipment rentals charged to customer? Contracted services (e.g. security) paid for by customer? Discount rates? Refunds? This is why I wonder if you should develop the project db and keep the current timekeeping system until the project side is working. You can always develop a billing/time sheet app and link between them via Project or PO number, but the decision is yours of course. What you don't want to do is develop a project app then hack into it to incorporate time sheet functions in the same app. If going for a complete system, be prepared to spend months getting it to work correctly - and start with pencil and paper for mapping everything out. It's a HUGE task for a beginner with many potential pitfalls. The more you can read about Access before beginning, the better your chances of pulling it off.

    A note on naming - SupplierID is OK as a PK but when it's a FK field, consider SupplierIDfk rather than have the same field name in both. You can look at a query sql and know right away that it's a pk or fk field. Also, watch spelling. Maybe OK if you use Despcription everywhere as you have above, but likely you won't.

    EDIT - I came here from the email and that puts you on a specific post. I looked and thought "I thought I answered this already. Oh well,...)
    Hopefully I'm consistent

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

Similar Threads

  1. Replies: 9
    Last Post: 06-23-2018, 10:25 PM
  2. Replies: 18
    Last Post: 12-04-2017, 05:25 PM
  3. Replies: 3
    Last Post: 01-13-2017, 03:52 PM
  4. Mechanical Completion and Commissioning
    By masoud_sedighy in forum Sample Databases
    Replies: 1
    Last Post: 06-12-2013, 07:20 AM
  5. Replies: 2
    Last Post: 01-28-2013, 04:42 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