Recent content by JITA

I have 2 tables: Table1 is filled daily with information about clients from a legacy system. Every day we determine which client is "new" in that table with a query that is comparing today's records with yesterday's records. The outcome is used in a form showing "new" records in the table. This...

Found it [bigsmile] (thanks to http://dba.stackexchange.com/) This query produces the results I need SELECT s.Saledate, s.SaleQ, s.ProdID, p.Price, p.PriceDate FROM tblSales AS s LEFT JOIN tblPrices AS p ON p.ProdID = s.ProdID AND p.Datum...

I think you are on the right track. Only q is not defined. Running the query it ask for q.price and q.pricedate to enter a value. I suspect that q.price and q.pricedate should be p.price and p.pricedate but then I get the message "the specified field p.price could refer to more then one table in...

I see my dates are mixed up (european/american) I hope that is not too confusing

Sorry I did not want to confuse you. And I did not know about PRE tag either, I will try again tblSales tblSales.SaleDate tblSales.ProdID tblSales.SaleQ 04-02-2015 1 530 05-02-2015 1 370 06-02-2015 1...

I am back again Some data tblSales Dat SaleQ ProdID 02-04-2015 530 1 02-05-2015 370 1 02-06-2015 950 1 02-09-2015 130 1 02-10-2015 70 1 02-11-2015 60 1 02-12-2015 90 1 02-13-2015 860 1 02-16-2015 810 1 02-17-2015 710 1 02-18-2015 1010 1 02-19-2015 1000 1 02-20-2015 80 1 02-23-2015 910 1...

Again fast response. I Appreciate your help, but this time I still don't get the expected result. The query shows all sales after the last date the price has changed, but these sales are compared with all price records I have. I have a price table with 3 prices 02-01-2015 25 02-10-2015 23...

SELECT tblSales.SaleQ, tblPrices.Price, tblPrices.PriceDate, tblSales.SaleDate FROM tblSales LEFT JOIN tblPrices ON tblSales.ProdID = tblPrices.ProdID WHERE ( SELECT MAX(tblPrices.PriceDate) FROM tblPrices WHERE tblOpgewekteEnergie.Prod = tblEnergiePrijzen.Prod ) <=...

Whow that was a quick response [bigsmile] Unfortunately this suggestion does not change the outcome. The result of my query is that I get all records from tblPrices and all records from tblSales. I still have no clue

@SkipVought I have tried your suggestion, but yet succeeded. SELECT tblSales.SaleQ, tblPrices.Price, tblPrices.PriceDate, tblSales.SaleDate FROM tblSales LEFT JOIN tblPrices ON tblSales.ProdID = tblPrices.ProdID WHERE ( SELECT MAX(tblPrices.PriceDate) FROM tblPrices ) <= tblSales.SaleDate GROUP...

SkipVought I am afraid I don't understand your comment. At the moment I have no interest in inventory, just combine sales with prices. And my example does give data. Please explain what you mean.

Hi, I am trying to figure out how to match a sale date to a price date. I have three tables: tblProd ID Desc tblPrices PriceDate Price ProdID tblSales SaleDate SaleQ ProdID Prices do not change frequently, so the tblPrice is not updated regularly. What I would like to achieve is to find a...

Sorry wrong forum I will post this on an other form

Hi, I am trying to figure out how to match a sale date to a price date. I have three tables: tblProd ID Desc tblPrices PriceDate Price ProdID tblSales SaleDate SaleQ ProdID Prices do not change frequently, so the tblPrice is not updated regularly. What I would like to...

Hi Mazework. I am familiar with the BETWEEN statement. The reason for trying to create a function is that my "calculations" are more complicated then just BETWEEN. I will provide some examples. Item 1 with starting date of 2-1-2010 and end date of 1-31-2011 Item 2 with starting date of 7-1-2010...