Results 1 to 8 of 8
  1. #1
    TomAtNMR's Avatar
    TomAtNMR is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Jun 2021
    Location
    Florida
    Posts
    11

    Query to show equipment owned by multiple companies

    I have a database which tracks companies and the tooling they own.
    Each company pays to have their tooling maintained.
    There is a report ([rptSingle]) that shows the tooling owned by a company and the total cost to have that tooling maintained.
    Some companies may have subsidiary companies.


    When a company has a subsidiary, I am trying to build a report ([rptMultiple]) to show the main companies tooling as well as their subsidiaries tooling.
    This is where I am running into an issue. I cannot seem to get a query to show both the main company tooling and their subsidiaries tooling.
    The report [rptSingle] uses query [qryReportSing]
    The report [rptMultiple] uses query [qSubsidEquipment]
    Does anyone have a suggestion on how to show the data from both the main company and the subsidiary?
    I have attached the database as a zip file.Test.zip

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    8,980
    i would hope theres a [parentCoID] field on the tSusidary table to show the owner.
    then you can add another query to get the chlds from the parents.

  3. #3
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    1,316
    THis is the amended Single.

    You can work out how to replace the 1 key with whatever value you need?
    Code:
    SELECT tblCompanies.CompanyName, tblCompanies.ContactTitle, tblCompanies.ContactFirstName, tblCompanies.ContactLastName, tblEquipment.EquipModel, tblEquipment.SerialNumber, tblEquipment.ContractRate, tblEquipment.EquipOwner, tblTaxs.TaxCounty, tblTaxs.TaxRate, tblCompanies.CompanyAddress, tblCompanies.CompanyCity, tblCompanies.CompanyStateProvince, tblCompanies.CompanyZipPostalCode, tblCompanies.CompanyDiscount, tblCompanies.TaxExempt, tblCompanies.ContractStartDate, tblCompanies.ContractEndDate, tblTaxs.SurTaxRate, IIf([tblEquipment].[ContractRate]>5000,5000,[tblEquipment].[ContractRate]) AS cappedAmt, tblEquipment.EquipNote, tblCompanies.CompanyID_PK, tblCompanies.ParentCompanyID_FK, tblCompanies.CompanyName
    FROM tblTaxs INNER JOIN (tblCompanies INNER JOIN tblEquipment ON tblCompanies.CompanyID_PK = tblEquipment.EquipOwner) ON tblTaxs.TaxZipCode = tblCompanies.CompanyZipPostalCode
    WHERE (((tblCompanies.CompanyID_PK)=1)) OR (((tblCompanies.ParentCompanyID_FK)=1))
    ORDER BY tblCompanies.CompanyName, tblCompanies.ParentCompanyID_FK;
    I included the PK/FK just to check the output.

    HTH
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?search_query=debug+access+vba



  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,352
    Appears to be circular relationship with tblCompanies, tblContracts, tblEquipment https://www.codeproject.com/articles...atabase-design

    QryReportMulti is using circular linking and probably causing confusion. Also should not link tblTaxs to both table and query. Modify query links when building query because they will adopt links built in Relationships.

    Data in page header gives appearance all equipment associated with ESPN. Should probably use a Group Header. Set Grouping on parent and all equipment info in Detail. Showing all companies, including parents and subsidiaries, is simple until you want to filter for specific subsidiary.

    Missing "e" in tblTaxs ?

    Why hide fields in tblContracts?

    Report has label with: This proposal is valid for ninety (900) days.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    9,653
    Classic case of report / sub report (one company, many subsidiaries)?
    Can't look at file until 2morrow so just throwing that out there for now. Typically, a sub report is not displayed if there is no data (unless you want to see it anyway) so it looks one way if there is at least one subsidiary and still looks normal if there are none. Then one query for each report instead of trying to build a query that works for situations where there is no data.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    Iím telling everyone it's good to eat dried grapes. Itís all about raisin awareness.

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    9,653
    IMO the tables and relationships are not correct but I'm making assumptions on how things are supposed to work. If there is no need to know what equipment a company has unless there is/was a contract for it, then I'd say equipment relates to contracts, not companies. I'm thinking that would solve the issue of relating equipment to subsidiaries because you'd link it to contracts instead. Also thinking an outer join (all companies plus subs where companyPK = companyFK) and an equal join from companyPK to contract.companyFK should relate contracts to companies whether or not there is a sub. Then relate equipment to contracts.

    However, there is some (a lot?) of cleanup that would be required. To name but a few,
    - contract fields don't belong in company table
    - ditch the attachment field and link to files instead
    - you are limited to 1 contact per company. Might be better to have tblContacts. That would also eliminate what are repeating fields (sort of) wherein there are 4 phone number fields - or empty fields for where there is no (e.g.) fax. Plus if you find the need for others you must add table a field(s), a sure sign of incorrect design.
    - adopting the suggestion would mean that some fields should no longer be where they are/are not needed (e.g. equipment and location in contracts table).
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    Iím telling everyone it's good to eat dried grapes. Itís all about raisin awareness.

  7. #7
    TomAtNMR's Avatar
    TomAtNMR is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Jun 2021
    Location
    Florida
    Posts
    11
    Thanks to all who responded, this has helped.
    @Welchgasman - I was able to create a function, GetMyID() which gets the CompanyID_PK, and call that from your query
    Code:
    SELECT tblCompanies.CompanyName, tblCompanies.ContactTitle, tblCompanies.ContactFirstName, tblCompanies.ContactLastName, tblEquipment.EquipModel, tblEquipment.SerialNumber, tblEquipment.ContractRate, tblEquipment.EquipOwner, tblTaxs.TaxCounty, tblTaxs.TaxRate, tblCompanies.CompanyAddress, tblCompanies.CompanyCity, tblCompanies.CompanyStateProvince, tblCompanies.CompanyZipPostalCode, tblCompanies.CompanyDiscount, tblCompanies.TaxExempt, tblCompanies.ContractStartDate, tblCompanies.ContractEndDate, tblTaxs.SurTaxRate, IIf([tblEquipment].[ContractRate]>5000,5000,[tblEquipment].[ContractRate]) AS cappedAmt, tblEquipment.EquipNote, tblCompanies.CompanyID_PK, tblCompanies.ParentCompanyID_FK, tblCompanies.CompanyNameFROM tblTaxs INNER JOIN (tblCompanies INNER JOIN tblEquipment ON tblCompanies.CompanyID_PK = tblEquipment.EquipOwner) ON tblTaxs.TaxZipCode = tblCompanies.CompanyZipPostalCode
    WHERE (((tblCompanies.CompanyID_PK)=GetMyID())) OR (((tblCompanies.ParentCompanyID_FK)=GetMyID()));
    @Micron - There are lots of issues, all due to my access-abilities. I always struggle with any database. If you wanted to adjust the relationships and post it, I would enjoy trying to understand how it works better than the one I shared. Thanks for your reply.

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    9,653
    What I did here was
    - ensure there was an equipment record a one company and and another record for a company that is a sub
    - added fk fields for that
    - altered the relationships
    - created query1

    Query seems to work but there aren't enough records to really determine that. However, I did not do anything else that I mentioned.

    Test_micron.zip
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    Iím telling everyone it's good to eat dried grapes. Itís all about raisin awareness.

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

Similar Threads

  1. Replies: 3
    Last Post: 08-18-2017, 07:26 AM
  2. Replies: 14
    Last Post: 03-21-2017, 07:00 AM
  3. Multiple Companies Same Invoice
    By mikajake in forum Database Design
    Replies: 3
    Last Post: 02-28-2013, 01:39 AM
  4. Replies: 1
    Last Post: 01-05-2013, 10:46 PM
  5. show companies with null values
    By jamo in forum Programming
    Replies: 11
    Last Post: 11-06-2012, 08:11 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