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.