Sub Query Problem

[MWalker]

I have two tables in an Access database. Table "StockCodeCost" stores the StockCode, Cost, and Date (for when the cost of the StockCode changes). This table essentially keeps track of any price change for the StockCode.

Then I have another table "StockCodeSales". This table stores the StockCode, QTYSold, and DateSold.

I want to create a query that can show me the sum of the costs for each transaction (each sale). This requires looking up the cost of the stock code at the time of the sale.

I have a query that will return the cost of the stock code based on a date parameter that I enter. I have tried to use this as a sub query in another query to link my sales table together, but I am not having any luck.

How do I use the DateSold value from one table to be the parameter for my sub query?

[FishVal]

Hi, MWalker.

You may join the tables in the following manner:

SELECT StockCodeCost.*, StockCodeSales.* FROM StockCodeCost INNER JOIN StockCodeSales ON StockCodeCost.DateSold = DMax("[Date]", "StockCodeSales", "[Date]=#" & StockCodeSales.DateSold & "#");
 

Regards,
Fish

[MWalker]

Hi, MWalker.

You may join the tables in the following manner:

SELECT StockCodeCost.*, StockCodeSales.* FROM StockCodeCost INNER JOIN StockCodeSales ON StockCodeCost.DateSold = DMax("[Date]", "StockCodeSales", "[Date]=#" & StockCodeSales.DateSold & "#");
 

Regards,
Fish

Thanks for the feedback. I've never used the DMax function before. I don't necessarily want the maximum cost of the part at any point in time. What I want is to be able to say on this DateSold, the cost of the StockCode at that time was X amount of dollars. The problem I have is that dates don't match in the two tables. For example, the cost could have changed on Feb. 1st and then again on Feb. 10th, but we sold some on Feb. 3rd and Feb. 12th. The sale price of these two transactions would be different. I need to be able to determine that on Feb. 3rd, the price was the same as it was on Feb. 1st.

The code below is from the query that correctly finds the cost of the stock code based on a date that I enter (in this case it is 'MyDate')

SELECT TOP 1 StockCodeCost.StockCode, StockCodeCost.Cost, StockCodeCost.Date
FROM StockCodeCost
WHERE (((StockCodeCost.Date)<=[MyDate]))
ORDER BY StockCodeCost.Date DESC;

I think the solution is somehow using your code with the INNER JOIN along with this code shown here. Any suggestions?

[FishVal]

Ok. I'm not 100% sure it will work as is because I've not tested this particular code. I will explain its logic (I've changed the code because it was erroneous).

SELECT StockCodeCost.*, StockCodeSales.* FROM StockCodeCost INNER JOIN StockCodeSales ON StockCodeCost.Date = DMax("[Date]", "StockCodeCost", "[Date]<=#" & StockCodeSales.DateSold & "# AND [StockCode]=" & StockCodeSales.StockCode);
 

When database engine take a combination of records from the tables being joined it checks whether cost change date is the same as maximal from those in [StockCodeCost] which are less or equal to the sell date from [StockCodeSales] and [StockCode] is the same.

Sure it is not the optimal strategy. You may post a sanitized copy of your db with only these two tables containing some records sufficient to design a query. I will look for more efficient solution.

Regards,
Fish

[MWalker]

Ok. I'm not 100% sure it will work as is because I've not tested this particular code. I will explain its logic (I've changed the code because it was erroneous).

SELECT StockCodeCost.*, StockCodeSales.* FROM StockCodeCost INNER JOIN StockCodeSales ON StockCodeCost.Date = DMax("[Date]", "StockCodeCost", "[Date]<=#" & StockCodeSales.DateSold & "# AND [StockCode]=" & StockCodeSales.StockCode);
 

When database engine take a combination of records from the tables being joined it checks whether cost change date is the same as maximal from those in [StockCodeCost] which are less or equal to the sell date from [StockCodeSales] and [StockCode] is the same.

Sure it is not the optimal strategy. You may post a sanitized copy of your db with only these two tables containing some records sufficient to design a query. I will look for more efficient solution.

Regards,
Fish

Please excuse my ignorance, but I don't see how to attach files. The help file says

To attach a file to a new post, simply click the [Browse] button at the bottom of the post composition page, and locate the file that you want to attach from your local hard drive.

I don't see this browse button anywhere.

[FishVal]

Well. I've made some tests. The following query seems to work though looks ugly.

SELECT StockCodeSales.*, StockCodeCost.*
FROM StockCodeSales INNER JOIN StockCodeCost ON ([StockCodeSales].StockCode=[StockCodeCost].StockCode) AND ([StockCodeCost].[Date]=DMax("[Date]","StockCodeCost","[Date]<=#" & [StockCodeSales].[DateSold] & "# AND [StockCode]=" & [StockCodeSales].StockCode));
 

[FishVal]

Please excuse my ignorance, but I don't see how to attach files. The help file says

I don't see this browse button anywhere.

Having made a post, click [edit/delete], then click [Manage attachments].

[FishVal]

Take a look at pure SQL solution. Seems to work.

Query ([qryPriceLatestChange]) returning a date of latest price change for each cell. [keyStockCodeSaleID] is a primary key of [StockCodeSales].

SELECT StockCodeSales.keyStockCodeSaleID, Max(StockCodeCost.Date) AS dteLatestChange
FROM StockCodeSales INNER JOIN StockCodeCost ON (StockCodeSales.DateSold>=StockCodeCost.Date) AND (StockCodeSales.StockCode=StockCodeCost.StockCode)
GROUP BY StockCodeSales.keyStockCodeSaleID;
 

The next query joins [StockCodeSales] and [StockCodeCost] with the aid of [qryPriceLatestChange].

SELECT StockCodeSales.*, StockCodeCost.Cost
FROM (StockCodeSales INNER JOIN qryPriceLatestChange ON StockCodeSales.keyStockCodeSaleID=qryPriceLatestChange.keyStockCodeSaleID) INNER JOIN StockCodeCost
ON (StockCodeCost.Date=qryPriceLatestChange.dteLatestChange) AND (StockCodeSales.StockCode=StockCodeCost.StockCode);
 

[MWalker]

Hi FishVal,

I really appreciate all your help in this matter. But I am still not able to get this to work. I am trying to do this inside Access 2003 and it may not have the capabilities to process the SQL that you have created.

To compound matters, I can't find the Edit button that would allow me to attach a file. I've searched the help section, searched the forums, but I can't find anything. It's very frustrating. I am obviously new to this forum, but so far I'm not impressed with the help menu.

There is something in the help menu that says that the administrator can turn these functions on and off. Is it possible that since I am so new that these functions have not been turned on?

[FishVal]

Hi FishVal,

I really appreciate all your help in this matter. But I am still not able to get this to work. I am trying to do this inside Access 2003 and it may not have the capabilities to process the SQL that you have created.

Hi, MWalker.

Access query editor is not capable to represent all kinds of SQL joins in design view (you should work in SQL view). This however doesn't prevent an SQL expression (in all other ways quite valid) from being executed properly. A good reason to learn SQL. ;)

To compound matters, I can't find the Edit button that would allow me to attach a file. I've searched the help section, searched the forums, but I can't find anything. It's very frustrating. I am obviously new to this forum, but so far I'm not impressed with the help menu.

There is something in the help menu that says that the administrator can turn these functions on and off. Is it possible that since I am so new that these functions have not been turned on?

[Edit/Delete] button expected to be on the right/bottom corner of a post you've made. It will disappear after a certain period (1h or something like it).

I'm going to attach a sample with a queries mentioned in above posts.