By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,827 Members | 2,211 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,827 IT Pros & Developers. It's quick & easy.

Sub Query Problem

P: 4
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?
Feb 14 '08 #1
Share this Question
Share on Google+
9 Replies


FishVal
Expert 2.5K+
P: 2,653
Hi, MWalker.

You may join the tables in the following manner:
Expand|Select|Wrap|Line Numbers
  1. SELECT StockCodeCost.*, StockCodeSales.* FROM StockCodeCost INNER JOIN StockCodeSales ON StockCodeCost.DateSold = DMax("[Date]", "StockCodeSales", "[Date]=#" & StockCodeSales.DateSold & "#");
  2.  
Regards,
Fish
Feb 14 '08 #2

P: 4
Hi, MWalker.

You may join the tables in the following manner:
Expand|Select|Wrap|Line Numbers
  1. SELECT StockCodeCost.*, StockCodeSales.* FROM StockCodeCost INNER JOIN StockCodeSales ON StockCodeCost.DateSold = DMax("[Date]", "StockCodeSales", "[Date]=#" & StockCodeSales.DateSold & "#");
  2.  
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')

Expand|Select|Wrap|Line Numbers
  1. SELECT TOP 1 StockCodeCost.StockCode, StockCodeCost.Cost, StockCodeCost.Date
  2. FROM StockCodeCost
  3. WHERE (((StockCodeCost.Date)<=[MyDate]))
  4. 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?
Feb 14 '08 #3

FishVal
Expert 2.5K+
P: 2,653
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).

Expand|Select|Wrap|Line Numbers
  1. SELECT StockCodeCost.*, StockCodeSales.* FROM StockCodeCost INNER JOIN StockCodeSales ON StockCodeCost.Date = DMax("[Date]", "StockCodeCost", "[Date]<=#" & StockCodeSales.DateSold & "# AND [StockCode]=" & StockCodeSales.StockCode);
  2.  
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
Feb 14 '08 #4

P: 4
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).

Expand|Select|Wrap|Line Numbers
  1. SELECT StockCodeCost.*, StockCodeSales.* FROM StockCodeCost INNER JOIN StockCodeSales ON StockCodeCost.Date = DMax("[Date]", "StockCodeCost", "[Date]<=#" & StockCodeSales.DateSold & "# AND [StockCode]=" & StockCodeSales.StockCode);
  2.  
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.
Feb 14 '08 #5

FishVal
Expert 2.5K+
P: 2,653
Well. I've made some tests. The following query seems to work though looks ugly.

Expand|Select|Wrap|Line Numbers
  1. SELECT StockCodeSales.*, StockCodeCost.*
  2. FROM StockCodeSales INNER JOIN StockCodeCost ON ([StockCodeSales].StockCode=[StockCodeCost].StockCode) AND ([StockCodeCost].[Date]=DMax("[Date]","StockCodeCost","[Date]<=#" & [StockCodeSales].[DateSold] & "# AND [StockCode]=" & [StockCodeSales].StockCode));
  3.  
Feb 14 '08 #6

FishVal
Expert 2.5K+
P: 2,653
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].
Feb 14 '08 #7

FishVal
Expert 2.5K+
P: 2,653
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].
Expand|Select|Wrap|Line Numbers
  1. SELECT StockCodeSales.keyStockCodeSaleID, Max(StockCodeCost.Date) AS dteLatestChange
  2. FROM StockCodeSales INNER JOIN StockCodeCost ON (StockCodeSales.DateSold>=StockCodeCost.Date) AND (StockCodeSales.StockCode=StockCodeCost.StockCode)
  3. GROUP BY StockCodeSales.keyStockCodeSaleID;
  4.  
The next query joins [StockCodeSales] and [StockCodeCost] with the aid of [qryPriceLatestChange].
Expand|Select|Wrap|Line Numbers
  1. SELECT StockCodeSales.*, StockCodeCost.Cost
  2. FROM (StockCodeSales INNER JOIN qryPriceLatestChange ON StockCodeSales.keyStockCodeSaleID=qryPriceLatestChange.keyStockCodeSaleID) INNER JOIN StockCodeCost
  3. ON (StockCodeCost.Date=qryPriceLatestChange.dteLatestChange) AND (StockCodeSales.StockCode=StockCodeCost.StockCode);
  4.  
Feb 15 '08 #8

P: 4
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?
Feb 24 '08 #9

FishVal
Expert 2.5K+
P: 2,653
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.
Attached Files
File Type: zip MWalker.zip (23.5 KB, 70 views)
Feb 24 '08 #10

Post your reply

Sign in to post your reply or Sign up for a free account.