Reward Points Calculation

[imran]

Hi There,

I am starting to promo a Reward card for my company but dont know how to keep record, i'm looking for some help...

Reword Program Details

We have given membership cards to our clients as Reword Point card, which will enable our client to earn points for his travel or service they purchase from us

• Each point is equivalent to Rs. 1

Points Structure

Services	Points You Earn
Domestic Air Tickets	1 Point for one way & 2 points for return ticket
International Air Tickets	3 Points for one way & 7 Point for return ticket on Full cost airlines 1 Point for one way & 2 points for return ticket on Low cost airlines
Hotel Bookings	1 Points for each night on 1 to 3 Night stay 3 Points for each night on more than 3 Night Stay
Luxury Cab Booking	5 Points for each day on booking of Mercedes/Audi/BMW 2 Point for each day on booking of Honda City/Civic/Accent/Corolla
Tour Packages	Per Person Package Cost 5 Point for Rs.10,000/- to Rs.25,000/- 12 Point for Rs.25,000/- to Rs.50000/- 25 Point for Rs.50,000/- to Rs.99,999/- 50 Point for Rs.1 Lakh. To above

now there are may things to be noted:

once the service is sold equivelant point as per the chart above is credited in members card account, in case the service is cancelled the point is also deducted from account
secondly once the guest have sufficient points in his account he can deduct it for any service

kindly help me with this program

regards
M Imran

[Robeen]

What have you done so far?

[imran]

What have you done so far?

To be true with u NOTHING!!!

I'M JUST BLANCK AND DON'T KNOW FROM WHERE TO START...

[Robeen]

Are Yaar!!!!! Kuchh Bhi Nahin????

Ok . . . Here's what you have to do - I will try & help you as much as possible:

Do not use Lookup Fields in your tables.

1. First step you will have to have your data in one or more tables. In order to do this well and make your database useful to you in the long run - you should Google 'Database Normalization'. This will teach you the basis of creating your tables in the correct way. It will probably take you 15 to 20 minutes to read a short article on this - but you should take extra time to really understand the concept so that your database is solid.
2. In your case, you will need at least four tables:
a. Customer Information [you can name it something like 'Customers' - Fields: CustomerID, Name, Address1, Address2, State, PostalCode, email, phone . . .],
b. ServiceTypes [that contains the different Services and the points assigned to each - Fields: ServiceID, Description],

Eg:

ServiceID	Description
1	Flight Domestic
2	Flight International
3	Hotel
4	Tour

c. ServicePointAllocation [contains the points for various levels of a Service [Fields: ServiceID, Level, LevelDescription, Points]
Eg:

ServiceID	Level	LevelDescription	Points
1	1	Domestic One Way	1
1	2	Domestic Round Trip	2

d. ServicesUsed [containing CustomerID, ServiceID, ServiceLevel].

I have done this pretty quick - so I am not sure that I've considered everything - and there may be inconsistencies in my table layout suggestions.
This is why you must read up about Normalization and how to create tables correctly.

Once you have your Tables created - put a few test rows of data in each Table - like your real data.

Then - you will want to create a Form that you can use to add data to your Tables and to keep track of your Customers and the points they have accumulated.

Let me know when you have made some progress and we can proceed from there.

Also - there is a LOT of information on the internet & you can just type 'ms access 2010 tutorial' and you should find tutorials. Those are a very good place to start.

All the best!!

[imran]

Are Yaar!!!!! Kuchh Bhi Nahin????

Ok . . . Here's what you have to do - I will try & help you as much as possible:

Do not use Lookup Fields in your tables.

1. First step you will have to have your data in one or more tables. In order to do this well and make your database useful to you in the long run - you should Google 'Database Normalization'. This will teach you the basis of creating your tables in the correct way. It will probably take you 15 to 20 minutes to read a short article on this - but you should take extra time to really understand the concept so that your database is solid.
2. In your case, you will need at least four tables:
a. Customer Information [you can name it something like 'Customers' - Fields: CustomerID, Name, Address1, Address2, State, PostalCode, email, phone . . .],
b. ServiceTypes [that contains the different Services and the points assigned to each - Fields: ServiceID, Description],

Eg:

ServiceID	Description
1	Flight Domestic
2	Flight International
3	Hotel
4	Tour

c. ServicePointAllocation [contains the points for various levels of a Service [Fields: ServiceID, Level, LevelDescription, Points]
Eg:

ServiceID	Level	LevelDescription	Points
1	1	Domestic One Way	1
1	2	Domestic Round Trip	2

d. ServicesUsed [containing CustomerID, ServiceID, ServiceLevel].

I have done this pretty quick - so I am not sure that I've considered everything - and there may be inconsistencies in my table layout suggestions.
This is why you must read up about Normalization and how to create tables correctly.

Once you have your Tables created - put a few test rows of data in each Table - like your real data.

Then - you will want to create a Form that you can use to add data to your Tables and to keep track of your Customers and the points they have accumulated.

Let me know when you have made some progress and we can proceed from there.

Also - there is a LOT of information on the internet & you can just type 'ms access 2010 tutorial' and you should find tutorials. Those are a very good place to start.

All the best!!

Roobinoor


WOW!!!
Thanks man there couldn't be better reply for my query, I will definetly come back to you with all 4 tables, I'm prity familier with access its just that I was not having idea how will I start for this program and you have given me that... Thanks Again

Catch you soon

[imran]

Roobinoor,

I have gone through Normalisation tutorieals and definetly its useful but i cant say i have understood 100% but as much as i did i have ganareted all database with 4 tables as u instructed and also updated some data but i'm not sure how will i enter data in SarvicePointsAllocation for Hotels & Tour Packages where there are different lavels...

like in Hotels each Night will give 1 point till 3Nights that means 1N = 1P, 2N = 2P & 3N = 3P
and if the booking is for more than 3Nights each night will give 2P e.i. 1N=2P, 2N = 4P & 3N = 6P and so on

Same in Packages

Tour Packages

Per Person Package Cost 5 Point for Rs.10,000/- to Rs.25,000/- 12 Point for Rs.25,000/- to Rs.50000/- 25 Point for Rs.50,000/- to Rs.99,999/- 50 Point for Rs.1 Lakh. To above

now how should i enter this?
will we have to create one more table?

[Robeen]

Well . . . you can enter data directly into your Tables . . .

BUT

Typically, you create a Form for each table into which you need to add data.
So - you should have a Form to manage [Add, Modify, Delete] data in each of your Tables.

Access makes it easy to do this - so when you try & create a new Form - it will bring up a Form Wizard. You can use the wizard to create your Form - if you don't like the Form - you can always modify the form - or just delete it and start again [it will be goood practice! ].

The reason I recommended looking on line for MS Access Tutorials is that some of the tutorials give you very good ideas [using videos] about how to create very efficient applications.

For instance - when I create an application for a user - I usually have a 'Startup Form' that opens when the user opens the database.
On that Form are buttons that give the user options to do different things.
For instance - you could have separate buttons that allow you to open different Forms.

So - basically - create Forms to input data into your various Tables.
Then - create a 'Main Form' that has buttons that open the various Forms that you created earlier.
When you create a command button on a Form, a Command Button Wizard will open up and it will give you options to make the button do different things. You can used the Wizard to help you with the functionality of the buttons.

i'm not sure how will i enter data in SarvicePointsAllocation for Hotels & Tour Packages where there are different lavels...
like in Hotels each Night will give 1 point till 3Nights that means 1N = 1P, 2N = 2P & 3N = 3P
and if the booking is for more than 3Nights each night will give 2P e.i. 1N=2P, 2N = 4P & 3N = 6P and so on

. . . as far as the kind of 'logic/calculations' that you refer to here - that can be done in Queries.
For instance:
In your Table - you may record data like this:

Customer	ServiceType	ServiceLevel
Name1	Air Fare Round Trip	Low Cost
Name 2	Tour	75,000

Then - you create a Query using that Table as the data source.
In the query, you will put 'logic' that tells Acces something like:
If this row of data is a HotelBooking Then
If the Number of days is greater than 3 Then
Calculate NumberOfPoints by multiplying the NumberOfDays X 2.

Because my instructions are not really 'Organized' - that is why I am recommending that you look at on line tutorials.
Quite often - those tutorials are well organized and it is easier to learn the basics from them.

You have to teach yourself the basics of Access.

You can get most of the basic information in free tutorials on the internet.
In fact, the Microsoft web sites have a lot of very good tutorials.
For instance: http://office.microsoft.com/en-us/tr...006118141.aspx
. . . and there are many others.

This is something that you will have to do on your own.

You need to learn:
1. Normalization [because the first thing you'll probably always do is create Tables].
2. How to create Queries.
3. How to create Forms.
4. How to create Reports.
You've already read up on Normalization.
Now you need to Google how to create and use Forms, Queries & Reports.
Take the time to understand these concepts.

Then - since you understand your data best - plan how you will organize your data into Tables. This is possibly the most important part of your project -> making sure that you have all your data organized correctly - and that your Tables are set up to correctly store all the data you need to run your application [you can always go back & make changes - but it helps if you plan well to begin with].