Query for Reconciliation - Help/advice needed

[Chatholo]

Hi,

I am setting up a new database and need some assistance with my main query.

I have 3 tables:
tbl_LEI_Targets (define budget by productID - it is updated at each new LEI Version which means unique productID for each LEI_Version)
tbl_LEI_Version (this table maps LEI_version against Budget_version from the tbl_MAIN)
tbl_Main (budget broken down by Department/Business units/Markets...)

What I need to do:
Create a query that will help me reconcile the [tbl_LEI_Target] and the [tbl_Main] when 4 criterias are met: type/Year/Version/ProductID
tbl_LEI_Targets.[ProductID] = tbl_Main.[ProductID]
tbl_LEI_Targets.Year=tbl_Main.Year
tbl_LEI_Targets.Type=tbl_Main.Type

I can do the query with these 3 criterias but I am struggling to get a result to tie to "version". I have tried different relationships with no success.
tbl_LEI_Targets.LEI Version is mapped to the tbl_Main through the tbl_LEI_Version

my sql with 3 criterias is:

SELECT tbl_Main.ProductID, tbl_LEI_Targets.ProductID, tbl_LEI_Targets.[LEI Version], Sum(tbl_LEI_Targets.Value) AS SumOfValue, tbl_Main.Year, tbl_LEI_Targets.Year, tbl_LEI_Targets.Type, tbl_Main.Type, tbl_LEI_Targets.Year, tbl_Main.ProductID, Sum([tbl_LEI_Targets]![Value]-[tbl_Main]![Quantity]) AS Var, Sum(tbl_Main.Quantity) AS SumOfQuantity
FROM tbl_LEI_Targets LEFT JOIN tbl_Main ON tbl_LEI_Targets.ProductID = tbl_Main.ProductID
WHERE (((tbl_LEI_Targets.Year)=[tbl_Main].[Year]) AND ((tbl_LEI_Targets.Type)=[tbl_Main].[Type]))
GROUP BY tbl_Main.ProductID, tbl_LEI_Targets.ProductID, tbl_LEI_Targets.[LEI Version], tbl_Main.Year, tbl_LEI_Targets.Year, tbl_LEI_Targets.Type, tbl_Main.Type, tbl_LEI_Targets.Year, tbl_Main.ProductID;



I am attaching the DB.

I am pretty sure it's easy but I have now spent hours on it ... thank you for your help!


Chatholo

[orange]

For your benefit and for readers:
-describe what your database/business is about in plain English
-explain your terminology/jargon --What's a LEI (version)
-make a list of your business rules
-always create an ERD/model of the proposed database

We only know what you tell us. So to get focused help/advice, give us a thorough description of your issue/opportunity, and your experience expertise.
Good luck

[June7]

If tbl_LEI_Targets and tbl_Main are supposed to be associated through the link in junction table tbl_LEI_Version, why do both tables have fields for Year, Type, ProductID?

Also, the PK fields of tbl_Main and tbl_LEI_Targets are autonumber but the foreign keys in tbl_LEI_Version are text. Associated PK/FK fields must be same type. An autonumber PK must be related to a number (long) FK.

Data structure doesn't make sense.

[Chatholo]

Thank you both. I do need to make sure I have the right structure.

To Orange:
We have a budget allocated for our business. The Budget is allocated by ProductID through a document called LEI. The LEI is updated every quarter - Hence the reason why I need to track the versions. In parallel the budget allocated is broken down by Department/Business units/ Markets. Most of the time the breakdown is done before we get the official LEI. So the purpose of this request is to reconcile the tbl_Main (broken down budget) with the tbl_LEI_Targets according to the appropriate tbl_LEI_Version.

To June: Both tables have the year filed because they do not come from the same source. I then use the LEI_Version to compare the other two tables together By ProductID for every type by Year. ProductID are the same, tb_LEI_Targets has one unique by LEI_Version. Type is the type of cost or $ (can be heads or purchased services ...).
Thank you for highlighting the PK/FK association. I will see how I can fix this.

With ,I hope a better understanding of my objective, would you be able to recommend the best structure, since mine still doesn't seem to be right
Thank you

[June7]

Exactly what data do you need to reconcile? You want to know if the latest Budget_Version value for each LEI_Version in tbl_Main agrees with the latested Budget_Version value in tbl_LEI_Version and the latest LEI_Version in tbl_LEI_Targets is the latest LEI_Version in tbl_LEI_Version?

For example, the latest Budget_Version in tbl_LEI_Version is V3 but the latest in tbl_Main is V2. The latest LEI_Version in tbl_LEI_Version is V8 and the latest in tbl_LEI_Targets is V6.

How are tbl_LEI_Targets and tbl_Main supposed to be related? The only common fields are Year (and since Year is a reserved word should not be used as name) and Type. The connection through tbl_LEI_Version is only through the two version fields, there is no breakdown by year and type.