Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Sama8525 is offline Novice
    Windows 10 Office 365
    Join Date
    Oct 2020
    Posts
    11

    Need some help with table references, specifically on forms.

    Hey all,
    I used to be somewhat proficient with access but that was 10+ years ago and i seem to have forgotten most of it. I need some help on how to set one up. I have several tables:

    1. Companies
    2. Company departments
    3. Marketing sponsor companies


    4. Sponsorships

    A company (1) can have multiple departments (2), each department is only part of one company. So i did a many to one with a lookup field in (2).

    The sponsors (3) have multiple partnerships with multiple companies (1), i created the sponsorships table (4) to track each partnership... connecting (1) and (4) with lookup fields.

    My problem is, each sponsorship (4) also involves either none of, some of, or all of a companies departments (2). How do i set that up in (4)? I dont want to directly reference (2) because i dont want to have a department connected to a company its not a part of.

    More specifically, id like to create a form for (4) where all data can be entered about a sponsorship, the sponsoring company (3) can be entered or selected from a dropdown, same with the company (1), and then somehow multiple select all the applicable departments for that sponsorship OF THE DEPARTMENTS of the already selected company. Is that possible? How do i set all this up?

    Thanks

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I would ditch the lookup field (if you mean a field in a table). Perhaps tblDepts and tblCompanies and a junction tblCompDepts to relate the two, assuming that this isn't about just one company. Same for tblSponsors and junction to relate sponsors and companies. That setup ought to solve your problem except (for me) numbering sponsors as both 3 and 4 doesn't help to clarify things.
    Or is it sponsors relate to departments?? Then your junction tblSponsorDept could group companies with departments but if you don't include the sponsor ID in this table, you need another table. That would probably be simpler but strictly speaking, not completely normalized. If you are rusty, then perhaps you should review database normalization and try applying it to your process. Then plan this out on paper before creating any db objects. Post back if you want feedback on the schema you arrive at. Last but not least, if you don't completely understand the nature of table lookup fields, I cannot help but wonder what your naming convention is, or what is the chance that you'd use table level calculated fields (another no-no).
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by Micron View Post
    I cannot help but wonder what your naming convention is, .....
    I would also add: Do not use spaces in object names.

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    then somehow multiple select all the applicable departments for that sponsorship OF THE DEPARTMENTS of the already selected company.
    sounds like you are talking about cascading combos

    A tip for the future - provide some example data and the outcome required from that example data, rather than trying to describe it - your descriptions may make sense to you but they often don't to others - and regret it doesn't to me.

  5. #5
    Sama8525 is offline Novice
    Windows 10 Office 365
    Join Date
    Oct 2020
    Posts
    11
    Thanks for the help, i can post some more specific examples tomorrow but a couple clarifications: those arent actual table names, just example descriptions of them, no calculated fields at table level (although some look up ones as mentioned, if i ditch those i just do them at form level with dropdowns?).

    My issue is sponsorships to departments...through companies.

    So each department can only belong to one company

    Each company can have multiple departments

    Each sponsor can have multiple sponsorships with multiple companies, and each company can have multiple sponsorships with multiple sponsors.

    Where i run into issues is each sponsorship can have multiple (or no) departments OF THE COMPANY thats part of that sponsorship. So not the whole department list, just the ones that are connected to that company.

    example data:

    --Companies--
    Id: c1
    Name: company 1

    Id: c2
    Name: company 2


    --Departments--
    Id: d1
    Name: accounting 1
    Company: 1

    Id: d2
    Name: finance 1
    Company: 1

    Id: d3
    Name: accounting 2
    Company: 2


    ----sponsors----
    Id: S1
    Name: Sponsor 1

    Id: S2
    Name: Sponsor 2


    ---sponsorships---

    Id: SP1
    Name: sponsorship 1
    Company: 1
    Sponsor: 1

    Id: SP2
    Name: Sponsorship 2
    Company: 1
    Sponsor: 1

    Id: SP3
    Name: Sponsorship 3
    Company: 1
    Sponsor: 2


    So what i need to get, is a form of the sponsorship table that allows me to enter the information above and connect the departments table with a multiple select with only the departments of the selected company. For example if i was entering SP2 it would let me select any or all of the departments for company 1 (but not say d3 which is for company 2) and connect them to the sponsorship somehow on the table level?

    Is that a cascading combo?

    Thanks

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    So each department can only belong to one company
    That implies that CompanyB can have a sales dept but no other company can.
    Each sponsor can have multiple sponsorships with multiple companies, and each company can have multiple sponsorships with multiple sponsors.
    To me they are the same thing, just stated in reverse.
    Where i run into issues is each sponsorship can have multiple (or no) departments OF THE COMPANY thats part of that sponsorship.
    That implies the sponsorship relates to the company, which I guess was my original understanding. If that's correct I don't see why departments come into this at all but it's not my process. I guess you're saying a sponsorship can be at the company or department level. While perhaps not 100% normalized, the sponsorship/company level (table) could have a department field that is null if no dept is involved. That might be an example of the part of the normalization mantra that says "de-normalize until it works".

    As soon as I got to your second table (Departments) I can see that you do not fully understand db normalization. Company does not belong in tblDepartments. Each "thing" is an entity. Each entity usually gets its own table. To relate one entity to another in a one to many relationship you use a simply relational join and have the ID of the one repeated in the other. To relate one entity to another in a many to many relationship you use a junction table. You should not have a one to one relationship between tables.

    As I said in my first post, you need to review db normalization, otherwise anything we tell you about that might just be confusing. In the meantime I'll look at your posted "schema" and try to grasp the process a little bit better.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    Sama8525 is offline Novice
    Windows 10 Office 365
    Join Date
    Oct 2020
    Posts
    11
    Company to department is a one to many relationship. Each company can have multiple departments, but each department can only be part of one company. The departments are not generic, theyre specific, so for example it wouldn't be "all these companies have a sales team" the sales team would be a specific sales team that belongs to one company, and another company might have a separate sales team specific to them.

    The sponsorship table is a junction table between companies and sponsors, but is also the main table i need. For each junction i also need to know which of the companies departments are involved.

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I can understand why you'd have sponsor1 and sponsor2 seeing as how they must be different people or entities but I don't understand the rationale behind Sales1 and Sales2. I guess it's not about the values of an entity called "department" which would mean that you have one "sales" value. If companyA and companyB have a sales department why would you want to call one SalesB and another SalesA (using my own terminology to try to explain how I've interpreted your statements)? There would be no need to make that distinction. I have to wonder if you got this idea from thinking like one does with a spreadsheet design.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  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
    Consider:
    Click image for larger version. 

Name:	SamaB1.png 
Views:	38 
Size:	32.2 KB 
ID:	43327

  10. #10
    Sama8525 is offline Novice
    Windows 10 Office 365
    Join Date
    Oct 2020
    Posts
    11
    Quote Originally Posted by Micron View Post
    I can understand why you'd have sponsor1 and sponsor2 seeing as how they must be different people or entities but I don't understand the rationale behind Sales1 and Sales2. I guess it's not about the values of an entity called "department" which would mean that you have one "sales" value. If companyA and companyB have a sales department why would you want to call one SalesB and another SalesA (using my own terminology to try to explain how I've interpreted your statements)? There would be no need to make that distinction. I have to wonder if you got this idea from thinking like one does with a spreadsheet design.
    Im trying to simplify it. In reality no 2 departments will be the same. But i still wanted 0- several of them associated to the parent company which is associated to the sponsorship to connect to the sponsor.

  11. #11
    Sama8525 is offline Novice
    Windows 10 Office 365
    Join Date
    Oct 2020
    Posts
    11
    Quote Originally Posted by ssanfu View Post
    Consider:
    Click image for larger version. 

Name:	SamaB1.png 
Views:	38 
Size:	32.2 KB 
ID:	43327
    Oh thats interesting. Not exactly what i need but i might be able to make that idea work.

    How would that function with forms?

  12. #12
    Sama8525 is offline Novice
    Windows 10 Office 365
    Join Date
    Oct 2020
    Posts
    11
    Ok doing some research, what it seems like i need to simplify this is much as possible, is as someone above mentioned...cascading combo boxes.

    Using the company table for the first combo box (in a form) and the departments page as the second dependant on the results of the first and with multi select somehow.

    Really stupid question: how do i do that?

  13. #13
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Check a simple structure in attached sample database.
    It's based on this schema:
    Click image for larger version. 

Name:	SponsorsSchema.JPG 
Views:	22 
Size:	16.3 KB 
ID:	43360
    The sponsorships refers analytically to the departments of each company.
    Open the form frmSP, add some sponsorships and then open the report rptSP.
    You have to fill the fields for the sponsor, the company and the date to enable the appending of departments.


    If you want to refers to the company generally, you have to chose the "Invoice-Invoice details" structure.
    For example:
    Click image for larger version. 

Name:	SponsorsSchema2.JPG 
Views:	22 
Size:	24.5 KB 
ID:	43361
    In this case, there is no need to select any departments for a sponsorship.

    I hope helps!

    Cheers,
    John
    Attached Files Attached Files

  14. #14
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Using the company table for the first combo box (in a form) and the departments page as the second dependant on the results of the first and with multi select somehow.

    Really stupid question: how do i do that?
    assuming your company combo is called cboCompany and your department combo is called cboDepartment

    the cboDepartment rowsource will be something like

    Select DepartmentPK, DepartmentName FROM tblDeaprtments WHERE CompanyFK=[cboCompany]

    and in the cboCompany afterupdate event you put

    cboDepartment.Requery

    depending on how your form works, you may also want to put in the form current event


    cboCompany_afterupdate

  15. #15
    Sama8525 is offline Novice
    Windows 10 Office 365
    Join Date
    Oct 2020
    Posts
    11
    Quote Originally Posted by accesstos View Post
    Check a simple structure in attached sample database.
    It's based on this schema:
    Click image for larger version. 

Name:	SponsorsSchema.JPG 
Views:	22 
Size:	16.3 KB 
ID:	43360
    The sponsorships refers analytically to the departments of each company.
    Open the form frmSP, add some sponsorships and then open the report rptSP.
    You have to fill the fields for the sponsor, the company and the date to enable the appending of departments.


    If you want to refers to the company generally, you have to chose the "Invoice-Invoice details" structure.
    For example:
    Click image for larger version. 

Name:	SponsorsSchema2.JPG 
Views:	22 
Size:	24.5 KB 
ID:	43361
    In this case, there is no need to select any departments for a sponsorship.

    I hope helps!

    Cheers,
    John

    this almost worked, but didn't, but seems to be sort of what I need. I'm about to post hopefully a better explanation with pictures.

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

Similar Threads

  1. Replies: 2
    Last Post: 09-13-2019, 06:00 PM
  2. Replies: 1
    Last Post: 09-10-2019, 08:42 AM
  3. how to code specifically for a cell in a table
    By campbell707 in forum Forms
    Replies: 3
    Last Post: 12-28-2015, 09:50 PM
  4. Composite key references same table
    By Bangsadrengur in forum Reports
    Replies: 0
    Last Post: 08-04-2011, 05:46 AM
  5. Replies: 1
    Last Post: 11-22-2010, 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