Available prices (1 Viewer)

Mihail · Dec 7, 2013

Hello !

As you know, the prices are, unfortunately, dynamic "objects".

So, I have a table for products:
tblProducts
ID_Product - Autonumber (PK)
ProductName

and a table for prices
tblProductsPrices
ID_ProductPrice - Autonumber (PK)
ID_Product - Number (FK on tblProducts
Price - Double
PriceDate - Date/Time

The problem:
I have a certain date.
I need to know the available prices at that date for all products.

Thank you !

PS_1
I have a solution; but, for each product, I apply an external function that use two aggregate functions: DMax and Dlookup

For my small tables, this isn't a problem but I like to know if it is a better way.

PS_2
Note, please, that my skills are quite nulls regarding the SQL.
So, try to design for me something that I can see in Queries Design View.

WayneRyan · Dec 7, 2013

Mihail,

You'll need a couple of queries. This is still WAY faster than using the Domain functions. Assume you have the date on some form:

Code:

Query1:
Select ID_Product, PriceDate
From   tblProductsPrices
Where  PriceDate < Forms!SomeForm!TheDate

Query2:
Select ID_Product, Max(PriceDate) As ThePrice
From   Query1
Group By ID_Product

Finally:
Select A.ID_Product, B.ThePrice
From   tblProducts As A Inner Join Query2 As B On
          a.ID_Product = b.ID_Product

Wayne

Geotch · Dec 7, 2013

If Wayne Ryan's post doesn't help, I can upload a sample.

Mihail · Dec 7, 2013

@Wayne
Your Query3 has as the result the dates, not the prices.
Q: Is there any way to obtain A and B without directly manipulate the SQL ?

@Geo
Thank you for the offer.
Anyone, anytime, is welcome in my threads.
So, I like to see your solution.

@Anyone
I'll not implement a solution based on SQL strings because I'll not be able to adapt , to debug or to maintain my DB.
That don't mean that I don't like to see a solution like this. Maybe I can learn something.

Thank you all !

WayneRyan · Dec 7, 2013

Mihail,

Sorry, I forgot a part:

Code:

Query1:
Select ID_Product, PriceDate
From   tblProductsPrices
Where  PriceDate < Forms!SomeForm!TheDate

Query2:
Select ID_Product, Max(PriceDate) As TheDate
From   Query1
Group By ID_Product

Finally:
Select A.ID_Product, B.TheDate, C.Price
From   tblProducts As A Inner Join Query2 As B On
          a.ID_Product = b.ID_Product Inner Join tblProductPrices as C On
              B.ID_Product = C.ID_Product And
              B.TheDate = C.PriceDate

You can paste these into the Access Query Editor. They're not meant to
be run as VBA.

Wayne

spikepl · Dec 7, 2013

Remember to put index on the price dates

Mihail · Dec 8, 2013

@Wayne
Sorry. Still not work. And, as I said, I am not able to debug

JHB · Dec 8, 2013

Try if it satisfied your requirement:
Input at date in your form, and the run Query2.

Mihail · Dec 8, 2013

JHB said:
Try if it satisfied your requirement:
Input at date in your form, and the run Query2.

That was, JHB.
One more thank you because, now, I have a template for things like this one.

JHB · Dec 9, 2013

Yor're welcome, luck with it!

Mihail said:
PS_2
Note, please, that my skills are quite nulls regarding the SQL.

How can that be?

Mihail · Dec 9, 2013

JHB said:
How can that be?

Unfortunately, is true.
All I can do is to design very simple "Where" clauses in order to filter a form, a report or a domain function.

Available prices (1 Viewer)

Mihail

Registered User.

Attachments

WayneRyan

AWF VIP

Geotch

Registered User.

Mihail

Registered User.

WayneRyan

AWF VIP

spikepl

Eledittingent Beliped

Mihail

Registered User.

Attachments

JHB

Have been here a while

Attachments

Mihail

Registered User.

JHB

Have been here a while

Mihail

Registered User.

Similar threads

Users who are viewing this thread