Results 1 to 8 of 8
  1. #1
    RSGChris is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    4

    Form Design to Update One table with information from multiple tables

    Hello,



    I am new to this forum, and am hoping that you can point me in the right direction. I am an intermediate Access User, my real weakness is that I can update VB ok, but writing it is a struggle.

    I am looking for a design/structure for a form in a company CRM I am working on. There are two tables: tblTechs, and tblSites. The idea is that I could have a command button in a continuous form that lists sites from tblSites that need technicians assigned to complete them (The continous for is already working well). The command button opens a form, frmAssignSite, with that site's information from tblSite already filled in. The form would allow you to select a technician to assign to the site from tblTechs, and three fields would populate based on that selection: txtSCID, txtMobilePhone, and txtEmail and those fields would be editable if the information is missing ot incorrect (I was using a cascading combobox at one point). Then, the technician and the additional three pieces of information would be added to the source table; tblsite. Then, I run a report from tblSites that is emailed to the technician I selected to the email in txtEmail. The report and email macro are good to go, but if you have any improvements, let me know.

    I am am really looking for just the best structure, because every time I solve one problem, I seem to create another (table lookup fields, cascading comboboxes, subforms, queries, etc). Unless it is very VB heavy, I feel like I could do it if I knew how to structure it. No worries if it includes a third table, any combination of action queries, subqueries, etc. The only other consideration is that it will be used in a very small multi-user environment (3-4 people) on Runtime. Thank you in advance for any help you can give, and have a wonderful day.

  2. #2
    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
    My advice is to identify what you are trying to do in plain English, business terms --no database jargon.
    The forms, comboboxes, macro etc are possible/probable parts of how the what could be done

    Techs and Sites conjures up a Many to many relationship.

    I see 3 tables

    tblTech--->tblTechAssignedToSite<---tblSite

    but maybe I'm misreading your set up.

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Maybe you could post your dB with just a few records????

  4. #4
    RSGChris is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    4
    Hello,

    Sorry this took so long, my work laptop wireless card took a dive, and I have no ethernet port. The many-to-many might work, and in a nutshell, I would like technicians to be assigned to sites on a table that I can then use to run reports. As much as can be automatically filled in should be to protect data integrity and mitigate user error. I a modified version of the db, removing actual records and replacing with a few fake ones, and removing all unrelated tables/forms/queries is on my google drive. Here is the link: https://drive.google.com/file/d/0Bwz...ew?usp=sharing Hopefully this helps, thank you.

  5. #5
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Well, I spend some time looking at your dB..... there are things I think will cause you headaches later.

    EVERY module should have these two lones at the top:
    Code:
    Option Compare Database
    Option Explicit
    In your code you are using
    Code:
        dTotalSize = Form_frmProgressMeter.txtBack.Width
        Form_frmProgressMeter.bxBack.Width = 0
    to refer to a form. This is not the proper way. It is internally how Access refers to forms, but you cannot use it to refer to forms.
    The proper method is "Forms!frmProgressMeter"

    Don't use spaces in object names (fields, queries, forms or reports)

    I think you need work on your table structures. You have "tblTechs" PK field linked to "tblSites" PK field. This is a one-to-one relationship. I think it should be a one-to-many or a many-to-many relationship.

    one-to-many: One Tech can be assigned to many Sites and one Site has one Tech.
    many-to-many: One Tech can be assigned to many Sites and one Site can have many Techs.
    Which of the above two lines is true?

    I added a table for "Skills and Certifications" and a junction table. I removed "CCNA", etc from the Techs table... "CCNA" is data, not a field name.
    Why a table?? Like you have the table design, if you wanted to add another Skill/Cert, you would have to modify the Tech table, then the queries, then form(s), then reports(s). A lot of work just to add 1 Skill/Cert....
    With a table, you just add another record.. Plus you have the option of adding expiration dates.


    I realize I don't know anything about your requirements, but this is what I came up with (it is a start - still needs more work):
    Click image for larger version. 

Name:	NewStructure.jpg 
Views:	17 
Size:	70.6 KB 
ID:	26118

  6. #6
    RSGChris is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    4
    Wow, that is an amazing amount of work, I really appreciate it. Lead and Preferred are not per job, but can change. Lead means that they meet client requirements to be a lead tech on specific projects, and preferred tech is our way of noting that a tech went above and beyond in some way, the situation is explained in notes, and then we can recognize them and their contribution in the future. The Certs were the abbreviations provided to me for specific certifications, if they are not accurate, they can definitely be changed. My previous employment was in a completely different industry, so IT is something I am learning.

    I completely glossed over the table structure/relationship aspect. The tables were straight imports of excel documents we are provided by our clients, I should have thought about updating the structure/relationships first. Spent so long looking at trees, I missed the forest. This works for everything that I removed as well. I am a graphic\web designer and marketing person trying to learn Access at a small company as a jack of all trades. This means a great deal to me. I hope you have an amazing day.

  7. #7
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    The tables were straight imports of excel documents we are provided by our clients,
    Aha, it did look like you were "committing spreadsheet".


    Lead means that they meet client requirements to be a lead tech on specific projects, and preferred tech is our way of noting that a tech went above and beyond in some way,
    Since you said "specific projects", those two fields might be added to the junction table (TechsSites). Depends on your wishes to mark a tech as a permanent lead, regardless of the project.


    I am also a little confused about "Sites" and Projects". Are they the same thing?


    ---------------------------------------------

    Here is some reading if you can't get to sleep:
    How do I Create an Application in Microsoft Access?
    http://rogersaccessblog.blogspot.com...cation-in.html

    Normalization Part I (other parts are II, III, IV, and V)
    http://rogersaccessblog.blogspot.ca/...on-part-i.html

    Entity-Relationship Diagramming: Part I
    http://rogersaccessblog.blogspot.com...ng-part-i.html

    Microsoft Access Tables: Primary Key Tips and Techniques
    http://www.fmsinc.com/free/newtips/primarykey.asp

    Autonumbers--What they are NOT and What They Are
    http://www.utteraccess.com/wiki/index.php/Autonumbers

    I have an autonumber field as the PK field in 99.99% of my tables. And, as you can see, I use a suffix of "_PK" and "_FK" to identify PK/FK fields.

  8. #8
    RSGChris is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    4
    I will definitely read those articles. I appreciate it. Projects and Sites are confusing. Projects are akin to Rack and Stack project for X client. Sites are the locations that the Rack and Stack are being performed. Those suffixes probably make things much easier as well. Thank you again, and I'll let you know how it all goes.

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

Similar Threads

  1. Form Information from multiple tables.
    By Homegrownandy in forum Access
    Replies: 1
    Last Post: 08-27-2015, 05:20 AM
  2. Replies: 3
    Last Post: 01-09-2014, 07:45 PM
  3. Replies: 8
    Last Post: 10-23-2013, 04:37 PM
  4. Replies: 1
    Last Post: 12-18-2012, 02:50 PM
  5. Replies: 4
    Last Post: 09-17-2012, 10:36 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