Determine Price between 2 dates and then Calc Percent Change

[aetedford]

I am trying to find pricing of products (from BRANDS) between dates that I select and then get the Percent Change using four tables.


For example, if I choose dates 1/1/18 and 3/31/18, it will look for each product, get the most recent price on 1/1/18, then the most recent price on 3/31/18 and calculate % Change, if any.

PROD 1, latest price is on 3/12/14 at $10 (Would be the price for 1/1/18). Has a price change on 2/6/18 and is now $15 (3/31/18 price). Calculates the price change as 50%.
Doesn't matter how many price changes between 1/1/18 and 3/31/18. Just take the price on those 2 dates.
If there is no beginning price, it will not be included in the results

TABLE ONE (DefinedBrands) - a list of BRANDS I defined and uploaded. Each is unique

BRAND

Brand1

Brand2

Brand3

Brand4

TABLE TWO (AllBrands) - connected to TABLE ONE to get the BRAND ID

BrandID	BRAND
1	Brand1
2	Brand2
3	Brand3
4	Brand4

TABLE THREE (AllProducts) - contains PRODUCTS for each BRAND. Connected by TABLE TWO ID

ProdID	BrandID	ProductName
1	1	Prod1
2	1	Prod2
3	1	Prod3
4	1	Prod4
5	2	Prod5
6	2	Prod6
7	2	Prod7
8	2	Prod8
9	3	Prod9
10	3	Prod10
11	3	Prod11
12	3	Prod12
13	4	Prod13
14	4	Prod14
15	4	Prod15

TABLE 4 (ProdPricing) - Contains price and dates for products.

ProdID	DATE	PRICE
1	3/12/14	10.00
1	2/6/18	15.00
2	1/1/18	2.00
2	2/6/18	1.85
2	3/12/18	4.00
3	2/8/17	5.00
3	1/2/18	8.00
3	1/9/18	12.00
4	9/6/15	7.00

Please let me know if there is any other info needed. Thank you in advance.

[Bob Fitz]

So what do want to return for Prod 4 if you choose dates 1/6/15 and 3/31/18

[aetedford]

Great question. Because there is no starting price for the date chosen, it would not be included. Hope this helps.

[aetedford]

To better assist, here is a sample of what I would expect to return with the above tables:

START DATE 1/1/18
END DATE 3/31/18

BRAND	ProdID	Price on 1/1/18	Price on 3/31/18	% Change
1	1	10.00	15.00	50.0%
1	2	2.00	4.00	100.0%
1	3	5.00	12.00	140.0%
1	4	7.00	7.00	0.0%

***if there is no price value for 1/1/18, it would not be included in report

Please let me know if more information is needed. Appreciate any assistance.

Thank you.

[orange]

What exactly is your ultimate goal/usage of this info?
This seems to be some sort of summary statistics to be used elsewhere.
In many cases it would seem these statistics would be gathered from an operational/production system and used to focus some sales/marketing strategy(ies).

I don't understand the real difference between table1 and table2--perhaps you could explain.
Good luck.

[aetedford]

Hello - Table1 is a list of brands that I have imported in as a separate table. I then need to link those to the 2nd table to get the Brand ID.

My ultimate goal is to import a list of BRANDS and then get the Price Change for each product of those brands.

[ArviLaanemets]

Have a look an UDF ValidValue(), I posted in https://www.accessforums.net/showthr...ght=validvalue

It assumes you have a table where all price changes for every product are registered. Something like
tblProductPrices: ProductctPriceID, ProductID, PriceDate, Price.

The formulas to get latest price at given date in your case will be like (assumed ProductID is numeric)

ValidValue("tblProductPrices", "ProductID", [YourProductID], FALSE, "Price", "PriceDate", [YourDate])