Results 1 to 8 of 8
  1. #1
    ndbennett is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2017
    Posts
    18

    Newbie Question - Can A Query Pull A Value from a Table Based On A Linked Value

    I have a Cost database that has tables which define Cost Centre, Cost Type and Cost Sub-type.



    Cost Type can be assigned to any Cost Centre, depending upon the actual cost, but each Cost Sub-types is assigned to only 1 Cost Type. So I have a Cost Type table and a Cost Sub-type table, with the latter linked to the former, and the actual hierarchy relationship defined per individual Sub-type.

    I have another table where the Actual Costs are recorded, linked to the Cost Centre, Cost Type and Cost Sub-type.

    From all the above, I have a query that I am trying to use to export the data to Excel, but I find that if the Cost Type is not matched correctly to the Cost Sub-type in the Actual Costs table, that line item does not appear in the query. I have made a mapping reference document for my staff, but their attention to detail is still not great so I need to go through each line in the Actual Costs table to make sure that the correct Cost Type is selected for the Sub-type.

    Is there a way that I can get the query to look at the selected Cost Sub-type and, from the relationship I have already defined on the Cost Sub-type Table, select the correct Cost Type and populate that into the query? Thanks.

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I sort of understand what you are saying, but if you can provide some examples it would help to suggest a solution. The reason I'm struggling is because your input forms should not allow a wrong selection in the first place

  3. #3
    ndbennett is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2017
    Posts
    18
    Quote Originally Posted by Ajax View Post
    I sort of understand what you are saying, but if you can provide some examples it would help to suggest a solution. The reason I'm struggling is because your input forms should not allow a wrong selection in the first place
    Thanks for your reply. I have tried to capture what I am saying in these screenshots (the Cost Centre is not tied to anything so I never included that):

    1) Cost Type is defined in this table:
    Click image for larger version. 

Name:	CostType.JPG 
Views:	13 
Size:	60.2 KB 
ID:	29708


    2) I then created a Cost SubType table and added a combi box to assign each SubType to one specific Cost Type:

    Click image for larger version. 

Name:	CostSubType.JPG 
Views:	13 
Size:	124.2 KB 
ID:	29709

    3) I then created a Cost Table to capture the actual costs, which I update via a form:

    Click image for larger version. 

Name:	CostTable.JPG 
Views:	13 
Size:	85.6 KB 
ID:	29710

    4) This is where I hoped that the relationship I defined in step 2 would automatically appear in the Cost Table - but the values in the Cost Type column of the Cost Table need to be manually selected from the Drop Down else it stays blank - and if the wrong one is selected, it doesn't get picked up by the Query:

    Click image for larger version. 

Name:	CostQuery2.PNG 
Views:	13 
Size:	49.7 KB 
ID:	29711

    5) In case this helps, here is the overall relationship picture:

    Click image for larger version. 

Name:	Relationship.JPG 
Views:	13 
Size:	73.5 KB 
ID:	29712

    Again, thanks for your time - I appreciate it.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Don't need to save CostType in CostTable. The CostType can be retrieved in queries that join CostType to CostSubType which joins to CostTable.

    The same concept applies to ProfitCentre
    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
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Don't need to save CostType in CostTable. The CostType can be retrieved in queries that join CostType to CostSubType which joins to CostTable.

    The same concept applies to ProfitCentre.

    Do not want to create queries like your CostQuery linking CostType table to two other tables.
    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.

  6. #6
    ndbennett is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2017
    Posts
    18
    That's it! I deleted the unnecessary links to the Cost table as you suggested, created a new query and it is performing exactly as I wanted it to.
    Thank you and Ajax very much!

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Also suggest in your tables, rename the ID fields to something more meaningful like CostcentreID, IncomeID etc. You are also using a reserved word as a field name - Date in this case (which is a standard function equivalent to the excel Today function). See this link about reserved words https://support.office.com/en-us/art...7-DA237C63EABE

    Because of your naming convention, it also looks like you may be using lookups in tables. I am be wrong, but if not, take a look at this thread about the problems you will encounter when using lookups in tables http://access.mvps.org/access/lookupfields.htm.

  8. #8
    ndbennett is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2017
    Posts
    18
    Quote Originally Posted by Ajax View Post
    Also suggest in your tables, rename the ID fields to something more meaningful like CostcentreID, IncomeID etc. You are also using a reserved word as a field name - Date in this case (which is a standard function equivalent to the excel Today function). See this link about reserved words https://support.office.com/en-us/art...7-DA237C63EABE

    Because of your naming convention, it also looks like you may be using lookups in tables. I am be wrong, but if not, take a look at this thread about the problems you will encounter when using lookups in tables http://access.mvps.org/access/lookupfields.htm.
    Apologies for the ridiculous delay in acknowledging your input. Thanks for the tip - I was vaguely aware of the issue but had ignored it until now. I will take action accordingly. Thanks again.

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

Similar Threads

  1. Replies: 2
    Last Post: 07-01-2016, 09:27 AM
  2. Delete Query based on records in a linked Excel table
    By gaker10 in forum Database Design
    Replies: 11
    Last Post: 09-30-2014, 10:06 AM
  3. Linked table Query based form
    By sesproul in forum Forms
    Replies: 1
    Last Post: 01-21-2010, 08:46 PM
  4. Newbie has a Query Question
    By Bruce in forum Queries
    Replies: 2
    Last Post: 11-20-2009, 10:38 AM
  5. Replies: 2
    Last Post: 10-27-2009, 07:09 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