Help Please (4 Viewers)

[SteveF]

The cost of each beer type is used to determine if I've accumulated enough to drink it.
For example:I have two types of beer:
- Beer 1 costs $10
- Beer 2 costs $15
let's say drinking one beer creates a $4 bonus, and so far I've drunk 3 beers, so I've accumulated a $12 bonus.
If I now want to drink Beer2, I either pay the difference of $3 or I have to settle for Beer1, which is free, and I'll have $2 left in the accumulated bonus.

In an ideal world yes. If you have an integrated EPOS system then setting a percentage of the sale and using that to form your loyalty bonus makes sense.

We're not in that world so we're going to set the amount ourselves as a cash amount and the customer redeems it as they choose. Some will be better off than others because some beers cost more at the bar yes, but then free beer is still free beer so I wouldn't expect too many complaints.

 

[Ken Sheridan]

The arithmetic is very simple. With your example the balance after buying a $15 beer would be:

CurrentBalance + AmountDue - Price, i.e. 12 + 3 - 15 = 0.

If they'd bought a $10 beer the balance would, using the same expression:

12 + 0 - 10 = 2.

So computing the balance after each purchase would a straightforward matter. The amount due is simply Price - Balance.

PS: Do they get a bonus if they only pay a small amount due, as in the first example above, or only if they pay the full price?

 

[SteveF]

The arithmetic is very simple. With your example the balance after buying a $15 beer would be:

CurrentBalance + AmountDue - Price, i.e. 12 + 3 - 15 = 0.

If they'd bought a $10 beer the balance would, using the same expression:

12 + 0 - 10 = 2.

So computing the balance after each purchase would a straightforward matter. The amount due is simply Price - Balance.

PS: Do they get a bonus if they only pay a small amount due, as in the first example above, or only if they pay the full price?

The arithmetic isn't really the problem.

 

[Ken Sheridan]

I don't see what the problem is I'm afraid. Surely you simply need to update the customer's bonus balance with simple arithmetical expressions like those I described when you scan their barcode. The only other factor is whether you credit a customer with a bonus amount when only a part of the full price is paid as a result of the current purchase being partially paid by their current bonus balance. If you do, as you are scanning each beer purchased separately, the bonus attracted by that purchase will be carried forward to the next purchase, and so on down the line until used for a purchase.

I see no point in computing running balances. All that matters is the current one per customer. If you do want to record running balances, however, the following query is an example of the most efficient way to do so by joining two instances of a table:

SELECT
    T1.CustomerID,
    T1.TransactionDate,
    T1.TransactionAmount,
    SUM(T2.TransactionAmount) AS Balance
FROM
    Transactions AS T1
    INNER JOIN Transactions AS T2 ON (T2.CustomerID = T1.CustomerID)
    AND (T2.TransactionDate <= T1.TransactionDate)
    AND (
        T2.TransactionID <= T1.TransactionID
        OR T2.TransactionDate <> T1.TransactionDate
    )
GROUP BY
    T1.CustomerID,
    T1.TransactionDate,
    T1.TransactionAmount,
    T1.TransactionID
ORDER BY
    T1.CustomerID,
    T1.TransactionDate DESC,
    T1.TransactionID DESC;

 

[SteveF]

Those calculations will come later and I agree with you that it's simple arithmetic.

What I'm trying to figure out at the moment is how to carry a 'loyalty amount' to the 'clockings' table with the scan of the barcode. Once I have that bit resolved I'll look at the rest of it.

It's worth saying again that my Access skills are limited and rusty. I don't use Access to make a living, more to solve problems like how to run a loyalty scheme in my bar with as little friction added to the service process as possible.

Thanks for input, by the way.

 

[Ken Sheridan]

As I understand it from your earlier posts, the loyalty amount is set for each customer when they open an account. It is an attribute of the Customers entity type, so you just have to look it up from the Customers or similar table, in which the Barcode column will be a candidate key. The customer's current balance will also be a column in that table, so when their barcode is scanned you just need to update the value of the LoyaltyBalance column by adding the value of the LoyaltyAmount column to it. I see no need to record the amount in the Clockings table.

All that's necessary when the barcode is scanned is to execute a couple of simple SQL statements, one an 'append' query to insert a row into the Clockings table, the other an 'update' query to update the value of the LoyaltyBalance column in the Customers table.

When a customer wants to redeem their loyalty balance, or a part of it it's another 'update' query to compute the new LoyaltyBalance value, using the simple arithmetical expressions I described earlier.

I don't see that you can get any simpler method than the above. I'm a strong advocate for the KISS principle!