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?
Query with all prices per period
-
- NewLounger
- Posts: 10
- Joined: 30 Jun 2015, 11:46
- Location: The Hague
-
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Query with all prices per period
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?
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?
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 14
- Joined: 27 Sep 2022, 07:22
- Location: Schiedam, NL
Re: Query with all prices per period
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.
Groeten,
Peter
Peter
-
- NewLounger
- Posts: 10
- Joined: 30 Jun 2015, 11:46
- Location: The Hague
Re: Query with all prices per period
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.
Grtz, Ton
-
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Query with all prices per period
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
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
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 10
- Joined: 30 Jun 2015, 11:46
- Location: The Hague
Re: Query with all prices per period
Yes, I did compact the database.
Created a dropbox for it.Here the link:
https://www.dropbox.com/s/uq76tvuygo5iy ... accdb?dl=0
Created a dropbox for it.Here the link:
https://www.dropbox.com/s/uq76tvuygo5iy ... accdb?dl=0
Grtz, Ton
-
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Query with all prices per period
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"));
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"));
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Query with all prices per period
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:
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 10
- Joined: 30 Jun 2015, 11:46
- Location: The Hague
Re: Query with all prices per period
Yes Hans, That is exactly what I need.HansV wrote: ↑13 Feb 2023, 16:11Thank 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"));
Thank you very much.
Grtz, Ton
-
- NewLounger
- Posts: 10
- Joined: 30 Jun 2015, 11:46
- Location: The Hague
Re: Query with all prices per period
Okay, I will do that.
Strange that you managed to get it much smaller then I do.
Thanks again.
Grtz, Ton