Querry not matching records

[sgp667]

So I have two(relevant) tables one that lists products bought and another that lists inventory level in storage. I wrote a query that calculates average rate that we use inventory at, and another that states most recent inventory level. Now to make that information useful I need to combine it together.


I need to estimate today's inventory level I am rather certain that formula for that should be:

(Most recent inventory level) - (days since inventory was measured) * ( average usage rate) = Estimates inventory level


The problem is that when I try to include records from both queries( one that states most recent counted inventory, and one that calculates depletion rate) the final query instead of matching items applies each record form the queries to each record from the other query, I'll illustrate:

My notation "Level" - recent Inventory level, "usage" - average usage, A B C - different items

What I want

Level of A Usage of A
Level of B Usage of B
Level of C Usage of C

What I get

Level of A Usage of A
Level of A Usage of B
Level of A Usage of C
Level of B Usage of A
Level of B Usage of B
Level of B Usage of C
Level of C Usage of A
Level of C Usage of B
Level of C Usage of C

I will upload the access file late I just need more time before I reduce size of it to fit upload requirements.

Thanks!

[June7]

Show the SQL statement of the query. Sounds like it does not have a JOIN clause.

[sgp667]

To be honest with you I know very little about SQL most of the time I let Access compile it for me, that includes this case:
SELECT [Most Recent Inventory Update].Item, [Most Recent Inventory Update].Count, [Depletion Rate].Usage, [Most Recent Inventory Update]!Count-DateDiff("d",[Most Recent Inventory Update]!["Date"],Date())*[Depletion Rate]!Usage AS [Estimated Inventory]
FROM [Most Recent Inventory Update], [Depletion Rate];

Indeed the JOIN clause is missing. However I am not that sure how to incorporate JOIN into this my guess would be that I have to state that [Most Recent Inventory Update].Item is the same as [Depletion Rate].Item

[June7]

Select the linking field in one table and drag to the linking field in other table. Click on the resulting line to edit the join type.

Access will create the query join only if Relationships are established.

[sgp667]

Ok so I joined the fields together like you suggested but new problem came up.
The fields imported from Joined table show up as empty.

Here is the SQL I used, I have tried this with different fields of "right"(joined) table but all show up as blank.

SELECT [Most Recent Inventory Update].Item, Max([Most Recent Inventory Update].Date) AS Checked, [Depletion Rate].Usage
FROM [Most Recent Inventory Update] LEFT JOIN [Depletion Rate] ON [Most Recent Inventory Update].Item = [Depletion Rate].Item
GROUP BY [Most Recent Inventory Update].Item, [Depletion Rate].Usage;

[June7]

Need to know more about data structure. Post sample data or provide database. Follow instructions at bottom of my post.