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?
9 1771
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
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?
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
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.
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));
-
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].
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);
-
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?
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Wescotte |
last post by:
Here is a small sample program I wrote in PHP (running off Apache
1.3.31 w/ PHP 5.0.1) to help illustrates problem I'm having. The data
base is using DB2 V5R3M0. The client is WinXP machine using...
|
by: Brian Oster |
last post by:
After applying security patch MS03-031 (Sql server ver 8.00.818) a
query that used to execute in under 2 seconds, now takes over 8
Minutes to complete. Any ideas on what the heck might be going...
|
by: Jeff Blee |
last post by:
I hope someone can help me get this graph outputing in proper order.
After help from Tom, I got a graph to display output from the previous
12 months and include the average of that output all in...
|
by: Adam Louis |
last post by:
I would like help resolving this problem. I'm a novice who's been
hired to query a hospital database and extract useful information,
available to me only in a dynamically generated, downloadable...
|
by: Martin Lacoste |
last post by:
Ok, before I headbutt the computer... don't know why when I add
criteria in a query, I get an 'invalid procedure call'. I also don't
know why after searching the help in access, the various access...
|
by: Apple |
last post by:
1. I want to create an autonumber, my requirement is : 2005/0001
(Year/autonumber), which year & autonumber no. both can auto run.
2. I had create a query by making relation to a table & query,...
|
by: Andy_Khosravi |
last post by:
My problem:
I'm having trouble with a query taking much too long to run; a query
without any criteria evaluating only 650 records takes over 300 seconds
to run (over the network. On local drive...
|
by: Konrad Hammerer |
last post by:
Hi!
I have the following problem:
I have a query (a) using another query (b) to get the amount of records
of this other query (b), means:
select count(MNR) as Number from...
|
by: Stan |
last post by:
I am using MS Office Access 2003 (11.5614). My basic question is can
I run a query of a query datasheet. I want to use more that one
criteria and can not get that query to work. I thought I...
|
by: existential.philosophy |
last post by:
This is a new problem for me: I have some queries that open very
slowly in design view.
My benchmark query takes about 20 minutes to open in design view. That
same query takes about 20 minutes...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |