Best approach? (1 Viewer)

Hi all,

I am creating a database to replace an Excel workbook I created a short while back.
It tracks commission payments, and at present the % is set.
However I was thinking that would likely change, and have been informed today that it will.
Therefore I need to keep track of the % during a particular period.
I realise that I would need at least
ID - PK
SubmitterID - FK
Rate - %
EffectiveDate -

but unsure on how I would then process this table via queries, or would I need VBA to find what %ge I would need to apply to a trade which is after or before an effective date for the above.

Data will be exported to Excel probably for ease of use by the recipients.
At present each trade shows it's respective commission and whether paid or not.

TIA

Dmax() function should get you the right % value.
will give you the ID. Add that to a Dlookup() for the rate;

I would use your rate table and run an update query to apply the pct based on Eff Dates.

Isskint said:

Dmax() function should get you the right % value.

Code:Copy to clipboard

Dmax("[ID]","[Table]","[EffectiveDate]=<" & [testdate])

will give you the ID. Add that to a Dlookup() for the rate;

Code:Copy to clipboard

Dlookup("[Rate]","[Table]","[ID]=" & Dmax("[ID]","[Table]","[EffectiveDate]=<" & [testdate]) )

Click to expand...

Thank you Isskint

Ranman256 said:

I would use your rate table and run an update query to apply the pct based on Eff Dates.

Click to expand...

Ranman256,

Are you saying have a field in the Rate table for the percentage?

I have that now, but if the rate changed and I then ran a query or report to report on previous values, it would reflect the current rate and not that at the time of payment.? In excel the values are repeated for each row, and I could change the formula at the correct time, but with the database I am not storing the values and would calculate them each time for query/report etc.

I *thought* that was the correct approach as I have seen it mentioned many times in these forums?

This is a learning experience for me as well as a constructive project.

Gasman said:

In excel the values are repeated for each row

Click to expand...

If this rate is subject to change, then it may make sense to save it with every object that requires it. For instance, prices are subject to change, so when you make an order or invoice, you look up the current price and then you save that price with the order, and this does not break normalization! Similarly, if you have other time sensitive variables that are required for an object to make sense, it may be the simplest and most reliable approach to save those values as they were at the time the object was created, even if the current value, or even a history of values, are stored elsewhere in your system.

For instance, even if you keep a price history in a Prices table, you should still ALSO store the price of each product in the OrderDetail table.

Hope this helps,

MarkK said:

If this rate is subject to change, then it may make sense to save it with every object that requires it. For instance, prices are subject to change, so when you make an order or invoice, you look up the current price and then you save that price with the order, and this does not break normalization! Similarly, if you have other time sensitive variables that are required for an object to make sense, it may be the simplest and most reliable approach to save those values as they were at the time the object was created, even if the current value, or even a history of values, are stored elsewhere in your system.

For instance, even if you keep a price history in a Prices table, you should still ALSO store the price of each product in the OrderDetail table.

Hope this helps,

Click to expand...

It does thank you.
That approach is certainly easier to implement (for me at least) and would involve less processing when producing reports.

Just to be pedantic... when rates can change dynamically to the point that each sale can have a different rate, then you have two choices for normalization.

One is for cases where a particular rate applies to all sales by a particular sales rep for a particular period. That would be a lookup table case for SalesRepID and SaleDate to pick up the rate.

The other is for cases where any number of special offers apply, such as "Buy item X and pay no interest for 60 months" (or such as that) where sales conditions at the point of the sale determine your rates. There, normalization suggests that the rate applied to the sale is a property associated with the sale itself, not the salesman or date. The rule about where to put things says to put them with the thing on which they depend. Case (1) above goes with a sales-rep/date table. Case (2) goes with the sale itself.

Which is why MarkK's advice is spot-on for this case.