Help Please (7 Viewers)

[SteveF]

Hi folks, thanks in advance for help.

I have this running now having installed Access 2007 so the first hurdle is cleared.

This was designed purely to clock in members and I want to modify it to run a loyalty scheme. Currently it scans a barcode which writes the entry time to a table in the back end. What I would like is for that scan to write a Loyalty Amount to the same table, is that a simple process?

I've not looked at Access for years and wasn't competent then, apologies for dumb questions.

 

[theDBguy]

What is the "scheme?" Do they get a point every time they log in? So, if they log in and then log out and then log in again, they get more points?

 

[SteveF]

What is the "scheme?" Do they get a point every time they log in? So, if they log in and then log out and then log in again, they get more points?

It will be a cash amount so say 30p or something. That would be each time the keytag is scanned so every time they buy a beer they would accumulate another 30p by scanning the tag. They then redeem the accumulated amount at the bar when there's enough.

 

[plog]

What I would like is for that scan to write a Loyalty Amount to the same table, is that a simple process?

Sort of easy, but not the right way to do things. You don't store data in more than one place in a database.

Can the Loyalty amount of a customer change? If so, do you want to record those changes or do not care what a customers loyalty amount was 3 weeks/months/years ago?

 

[XPS35]

I suggest you alter the relationship between the tables:

 

[SteveF]

Sort of easy, but not the right way to do things. You don't store data in more than one place in a database.

Can the Loyalty amount of a customer change? If so, do you want to record those changes or do not care what a customers loyalty amount was 3 weeks/months/years ago?

I was thinking along those lines yes, we would set the amount as we add new members. The changes wouldn't be important historically no.

Looking at my 'Clockings' table it records the date and time of the entry. If it recorded the loyalty amount there then - in my head - we should be able to query it out and redeem it as needed.

If it simplified the process I could live with a set loyalty amount though.

 

[SteveF]

I suggest you alter the relationship between the tables:
View attachment 121273

Could you say a bit more? Have you found an existing error or a solution to my problem?

 

[June7]

What is LoyaltyAmount field in Members table for? Different reward for different members?
This is no different from tracking inventory or leave balances. Review http://allenbrowne.com/AppInventory.html
Unless you want to wipe out rewards each day. Use or Lose?

I agree - relationship setup is wrong.

 

[plog]

The changes wouldn't be important historically no.

If that's truly the case, then storing loyalty amount in both places is actually fine. Did you write that VBA code that does the INSERT? Because the simplest way is modify that to get the LoyaltyAmount from the Members table and include it in there. Either with a Dlookup or by modifying the existing SELECT you are using.

 

[SteveF]

What is LoyaltyAmount field in Members table for? Different reward for different members?
This is no different from tracking inventory or leave balances. Review http://allenbrowne.com/AppInventory.html
Unless you want to wipe out rewards each day. Use or Lose?

I agree - relationship setup is wrong.

That's an interesting comparison. It was an inventory database that brought me to Access in the first place. It is similar, tracking balances etc..

I was hoping to vary the amount per member yes but I'm quite happy to use a set amount per transaction if that simplifies it.

We've no intention of wiping balances, we would carry the balance until the customer redeemed it so I will need to sort that aspect out as well.

My Access knowledge is limited and rusty. I had this written for me quite a while ago so I wouldn't understand why that relationship is wrong.

Thanks for input; appreciated

 

[Gasman]

I was thinking along those lines yes, we would set the amount as we add new members. The changes wouldn't be important historically no.

Looking at my 'Clockings' table it records the date and time of the entry. If it recorded the loyalty amount there then - in my head - we should be able to query it out and redeem it as needed.

If it simplified the process I could live with a set loyalty amount though.

So if I spend £30 and another member spends £10, we get the same loyalty amount?

 

[SteveF]

If that's truly the case, then storing loyalty amount in both places is actually fine. Did you write that VBA code that does the INSERT? Because the simplest way is modify that to get the LoyaltyAmount from the Members table and include it in there. Either with a Dlookup or by modifying the existing SELECT you are using.

I didn't write it no. It's a database that I had developed for my small business a few years ago.

It made sense to me to be able to vary the amount per member if we wanted to, and that form seemed the obvious place to do that.

 

[June7]

Your entities are Members and Clockings. The common identifier to link on is barcode, not reward value. Reward value for a member can change and many members can have same reward - assigned barcode should be unique per member and never change.

You need history if members are allowed to partially use rewards.

 

[SteveF]

So if I spend £30 and another member spends £10, we get the same loyalty amount?

No. If we serve 4 pints we do four scans so they will get the loyalty amount per drink not per transaction

 

[SteveF]

Your entities are Members and Clockings. The common identifier to link on is barcode, not reward value. Reward value for a member can change - their assigned barcode should never change.

You need history if members are allowed to partially use rewards.

Yes, that's why I was thinking that the loyalty amount would write to the 'clockings' table. If it varied over time it wouldn't matter because the amount is stored in the table and we could redeem it as and when needed

 

[June7]

Yes, that is justification for saving the reward with each clocking.

Doesn't matter if you call these "clockings" or "inventory", still transactions of in/out movement of something.

 

[XPS35]

Have you found an existing error or a solution to my problem?

It is an error. Clockings are related to a member and a member can have one or more clockings. So there is a one to many relationship between members and clocking. The field that connects them is barcode. All clockings with the same barecode belong to one member.
Connecting on amounts is wrong anyway. Multiple clockings can have the same amount. Which member it belongs to?

 

[CarlettoFed]

Do the beers:
- entitle you to the same loyalty amount?
- are they all the same price?
this information is used to determine if you're entitled to free drinks and how much.

 

[SteveF]

Do the beers:
- entitle you to the same loyalty amount?
- are they all the same price?
this information is used to determine if you're entitled to free drinks and how much.

Yes - once the loyalty amount is set it's that amount per drink

No - the prices vary but I'm not trying to do a percentage as loyalty.

I've never seen it done this way before but am convinced it will work. It's just a digital version of stamping a card to my mind, like we used to get at coffee shops etc. This way should save us some time at the bar and give us more control over the offer.

 

[CarlettoFed]

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.