Query with all prices per period

[tknijnenburg]

Hi I'm new here, so please forgive me, if my post is not that nice.
I try to design a query, but It doesn't give me the desired output.
The database has 3 tables tblArticlePrice, tblArticle, tblStockChange. tblArticle is in the middle and connected to both tables with ArticleID.
Prices can change throughout the year (record with old price has a field EndDate en the new record contains a field StartDate with the new price.
The stock changes do also have dates.

In my query I need the changes from the stock with the price that was in that period.
So for example, if my price is changed at 01-01-2023 and the stock changed two times on 29-12-2022 and on 13-01-2023 I need to see 2 rows with the prices at that time.

My query:
SELECT Artikelen.Artikelnaam, Voorraadmutaties.DatOnd, [Voorraadmutaties]![Voorrmut]*-1 AS [Aantal artikelen], tblArtikelPrijs.VerkPrijs, [Voorraadmutaties]![Voorrmut]*[tblArtikelPrijs]![VerkPrijs]*-1 AS Totaalprijs
FROM Filialen INNER JOIN ((Artikelen INNER JOIN tblArtikelPrijs ON Artikelen.ArtikelID = tblArtikelPrijs.ArtikelID) INNER JOIN Voorraadmutaties ON Artikelen.ArtikelID = Voorraadmutaties.ArtikelID) ON Filialen.FiliaalID = Voorraadmutaties.FiliaalID
WHERE (((Voorraadmutaties.DatOnd) Between [tblArtikelPrijs]![StartDat] And [tblArtikelPrijs]![EindDat]));

Could anybody help me?

[HansV]

A belated welcome to Eileen's Lounge!

Could you create a copy of the database, strip away everything that isn't relevant to the problem, as well as all sensitive information, then zip the copy and attach it to a reply?

[xps35]

It may go wrong because you set a condition for the stock change date. However, you want to find a certain price. Turn it around and set a condition for the price change.

[tknijnenburg]

A belated welcome to Eileen's Lounge!

Could you create a copy of the database, strip away everything that isn't relevant to the problem, as well as all sensitive information, then zip the copy and attach it to a reply?

I've stripped a lot.
Have 4 tables and 1 query left, and zipped the file.
(the biggest table has 150 records, the others less than 100)

Cannot upload the file because it's still 4022 KB.

[HansV]

Did you compact the database before zipping it?
If it'ds still too large, you can upload it to a cloud service such as Google Drive, OneDrive, Dropbox or similar, obtain a share link and post the link.
Or email it to me: hans dot vogelaar at gmail dot com

[tknijnenburg]

Yes, I did compact the database.
Created a dropbox for it.Here the link:
https://www.dropbox.com/s/uq76tvuygo5iy ... accdb?dl=0

[HansV]

Thank you! Does this do what you want? This is the query from your sample database, with added date condition:

SELECT Filialen.Filiaalnummer, Artikelen.Artikelnaam, Voorraadmutaties.DatOnd, [Voorraadmutaties]![Voorrmut]*-1 AS [Aantal artikelen], tblArtikelPrijs.VerkPrijs, [Voorraadmutaties]![Voorrmut]*[tblArtikelPrijs]![VerkPrijs]*-1 AS Totaalprijs, Artikelen.VoorrSoort
FROM Filialen INNER JOIN ((Artikelen INNER JOIN tblArtikelPrijs ON Artikelen.ArtikelID = tblArtikelPrijs.ArtikelID) INNER JOIN Voorraadmutaties ON Artikelen.ArtikelID = Voorraadmutaties.ArtikelID) ON Filialen.ID = Voorraadmutaties.FiliaalID
WHERE (((Filialen.Filiaalnummer)<>1) AND ((Voorraadmutaties.DatOnd) Between [StartDat] And Nz([EindDat],#12/31/9999#)) AND ((Artikelen.VoorrSoort)="ZWE"));

[HansV]

It might be a good idea to create a new database and import all objects from your database into it. This is the result of doing that with your sample database - size went from 4 MB to 532 kB, and only 35 kB zipped:

Database1.zip

[tknijnenburg]

Thank you! Does this do what you want? This is the query from your sample database, with added date condition:

SELECT Filialen.Filiaalnummer, Artikelen.Artikelnaam, Voorraadmutaties.DatOnd, [Voorraadmutaties]![Voorrmut]*-1 AS [Aantal artikelen], tblArtikelPrijs.VerkPrijs, [Voorraadmutaties]![Voorrmut]*[tblArtikelPrijs]![VerkPrijs]*-1 AS Totaalprijs, Artikelen.VoorrSoort
FROM Filialen INNER JOIN ((Artikelen INNER JOIN tblArtikelPrijs ON Artikelen.ArtikelID = tblArtikelPrijs.ArtikelID) INNER JOIN Voorraadmutaties ON Artikelen.ArtikelID = Voorraadmutaties.ArtikelID) ON Filialen.ID = Voorraadmutaties.FiliaalID
WHERE (((Filialen.Filiaalnummer)<>1) AND ((Voorraadmutaties.DatOnd) Between [StartDat] And Nz([EindDat],#12/31/9999#)) AND ((Artikelen.VoorrSoort)="ZWE"));

Yes Hans, That is exactly what I need.
Thank you very much.

[tknijnenburg]

It might be a good idea to create a new database and import all objects from your database into it. This is the result of doing that with your sample database - size went from 4 MB to 532 kB, and only 35 kB zipped:


Database1.zip

Okay, I will do that.
Strange that you managed to get it much smaller then I do.

Thanks again.