Tips for dealing with Recent/Latest/Current Prices, Rates etc. (1 Viewer)

[raziel3]

Hello all,
If you happened to come across my posts, most of my questions are related to getting the latest price or rate during data entry. I've gotten lots of help on the forums dealing with it but I think I may have a better solution.

A typical pricing/rate table looks like this:

+---------+-----------+----------+--------+
| PRICEID |  ITEMID   | EFFDATE  | PRICE  |
+---------+-----------+----------+--------+
|     001 | PRODUCT_A | 1/1/2022 | $5.00  |
|     002 | PRODUCT_B | 2/1/2022 | $10.00 |
|     003 | PRODUCT_A | 5/1/2022 | $6.00  |
+---------+-----------+----------+--------+

Some like to include ENDDATEs but I like using a query to deal with that and not put the ENDDATES at table level, thanks to @arnelgp for showing me how to to do that:

+---------+-----------+-----------+------------+-------+
| PRICEID |  ITEMID   | STARTDATE |  ENDDATE   | PRICE |
+---------+-----------+-----------+------------+-------+
|     001 | PRODUCT_A | 1/1/2022  | 4/30/2022  | $5.00 |
|     002 | PRODUCT_B | 2/1/2022  | 12/31/9999 | $5.00 |
|     001 | PRODUCT_A | 5/1/2022  | 12/31/9999 | $5.00 |
+---------+-----------+-----------+------------+-------+

Records Table:

+--------+-----------+-----------+-----+
| SALEID |  ITEMID   | SALEDATE  | QTY |
+--------+-----------+-----------+-----+
|    001 | PRODUCT_A | 1/15/2022 |   3 |
|    002 | PRODUCT_B | 1/16/2022 |   9 |
|    003 | PRODUCT_B | 1/17/2022 |   4 |
|    004 | PRODUCT_A | 5/2/2022  |   2 |
+--------+-----------+-----------+-----+

There are many ways to get the current price, they may include a Dlookup, Select Top 1, Making a Latest Price Query and JOIN it, Subquery etc. They are all good but when your records table starts to increase they all have efficiency problems, especially if you are generating a Report or using a Continuous Form for Data Entry.

My solution (and I may get some heat for this) is to add a column/field to the Records table to fill in the Current PriceID at the time of Data Entry. I have found that only after associating each Record to the PRICEID many of my downstream queries and reports were so easier to generate.

You only need to run one of the Functions once after an Event in your Data Entry Form that populates the PRICEID field in your table. This saves you having to create 3 to 5 queries to get the results you want. (Additionally, you can also run and UPDATE Query to populate the PRICEID Field)

Records Table New:

+---------+-----------+-----------+-----+---------+
| SALESID |  ITEMID   |  SALEATE  | QTY | PRICEID |
+---------+-----------+-----------+-----+---------+
|     001 | PRODUCT_A | 1/15/2022 |   3 |     001 |
|     002 | PRODUCT_B | 1/16/2022 |   9 |     002 |
|     003 | PRODUCT_A | 1/17/2022 |   4 |     001 |
|     004 | PRODUCT_A | 5/2/2022  |   2 |     003 |
+---------+-----------+-----------+-----+---------+

I know many of the seasoned db Developers will say that populating a field in a table that can otherwise be gotten from a running a query is poor practice but as I said before, after a while the queries tend to become slow and volatile causing Access to freeze and crash (that's just my experience, it may not be your case).

Anyways, would appreciate any feedback and criticisms.

 

[Minty]

If you have large datasets, what I would refer to as "helper" fields can make a big difference, particularly if the only way to obtain the join for the data is a calculation.

There is a saying "Normalise everything until it hurts, then wind back a bit", that I think could apply in your example.

I suspect if you were dealing with a SQL Server (or similar) backend you could build this as a view with calculated joins and it remains editable. The speed would be acceptable as the server would be doing the hard work.

 

[plog]

In your Records Table New, doesn't the PRICEID field relate to an item? If so, you do not need the ITEMID in there as well.

Further, by having leading zeroes on your ID fields it implies they are text. I'd use numbers for those fields.

 

[CJ_London]

I know many of the seasoned db Developers will say that populating a field in a table that can otherwise be gotten from a running a query is poor practice

my view is 'it depends'

Anything with legal implications such as an invoice I would include the data again, either as a link as you demonstrated or the actual value (particularly if the user has the right to modify the value) - same would go for values related to sales tax etc. Why? because 5/6 years down the line you may need to reprint that invoice, and you may end up with a different result.

Anything else, unlikely. There are faster ways than subqueries - aliased tables combined with non standard joins for example (subject to good table and index design). The only time a subquery will be slow is when you want to list all prices on a certain date. Looking up a price now and then should not have any significant performance hit. You have to consider the implications of data changes - unlikely in this example being a sales invoice but if you have to change the sales date that means you have to recalculate the price. Or perhaps someone realises they haven't entered a revised price - you then need to recalculate all invoices.

I don't include an end date unless there can be one rather than the value just being superceded. If a product ceases to be sold, I would normally expect the end date to be in the product record, not the price record for example.

Examples when an end date would be relevant could be a hotel room booking or an employee in a role. This can then be used to identify gaps where a room is unoccupied or a role is vacant. And in the case of a role also identify when two or more employees are in the same role at the same time (holiday cover for example)

 

[raziel3]

@Minty you are right on both accounts. A helper field really comes in handy and I try to integrate them in my tables whenever I can. Also an SQL server does makes things 100 times better when it comes to querying.

@plog You will still need the ITEMID because if you are doing Data Entry you enter by Item not by Price. So once the user selects the Item, the Dlookup will get the current PRICEID based on the Record Date and the ITEMID and populate the SALES.PRICEID field.

 

[raziel3]

You have to consider the implications of data changes - unlikely in this example being a sales invoice but if you have to change the sales date that means you have to recalculate the price. Or perhaps someone realises they haven't entered a revised price - you then need to recalculate all invoices.

The Accounting Control for that would be an "Edit Invoice" form and it would follow the rules of the initial Data Entry.

 

[CJ_London]

not arguing about your specific situation, just clarifying that storing 'repeated' data has its complications which need to be handled

 

[raziel3]

not arguing about your specific situation, just clarifying that storing 'repeated' data has its complications which need to be handled

Yes, Agreed. But ever since I started assigning the PRICEID to each sales record, my db is speeding. I pulled a 6 month report in seconds.