help me design and connect Senior Citizens Discount Table?

[markpastoril]

I want to have a table that will discount the customers water consumed when they are registered as a Senior Citizen how Do I design the table which I can connect to Customers Table and to its Transactions table that automatically discounts?

this is my table field and relationship connection.



note i have to change also the way of payments of each customer/s.

[ranman256]

the DiscountRate on the tCustomer table , gets added to the tTrans table for each transaction, in a query: [AmtDue]-(AmtDue*DiscountRate)
tCustomer table could also have [PayVia] , for how the customer pays via cash,card,acct deduct, etc.

normally youd make a 'shopping cart' like all the internet uses.
tCustomers, tTransactions, tOrderDetails (for all items bought)

[markpastoril]

but i want to keep the information of customers with senior citizens registration e.g. senior citizens id,date of registration, and the municipality where she/he was registered and also I would like it to have an expiration date which they are require to update they're senior discount registration, the expiration date is one year after the date of registration and if not updated it will automatically changes to unregistered status and the should stop.

[Minty]

That's weird, surely once you are a senior citizen you always are?
Unless you are Benjamin Button ?

You need to store you transactions as per Ranmans suggestions, at the moment you will have a nightmare making this work as you get further into it...

[ArviLaanemets]

but i want to keep the information of customers with senior citizens registration e.g. senior citizens id,date of registration, and the municipality where she/he was registered and also I would like it to have an expiration date which they are require to update they're senior discount registration, the expiration date is one year after the date of registration and if not updated it will automatically changes to unregistered status and the should stop.

Then you need table:
tblSeniorCitizenCustomers: SCCID, CustomerID, MunicipalityID, [RegDate0], RegDate, ExpireDate
Where RegDate is updated every year, and ExpireDate is automatically updated whenever RegDate is updated (usually calculated fields aren't kept in tables, but I think here is a good reason to make an exception).

A saved query (e.g. qCurrentSeniorCustomers) returns a list of customers registered as Senior Citizens at current date

SELECT * FROM tblSeniorCitizensCustomers WHERE ExpireDate Is Not Null AND ExpireDate > Date()

You can use LEFT JOIN to link the saved query with table Transactions, and to get info is the customer Senior Citizen (SCCID Is Not Null) or not (SCCID Is Null).

[markpastoril]

That's weird, surely once you are a senior citizen you always are?
Unless you are Benjamin Button ?

You need to store you transactions as per Ranmans suggestions, at the moment you will have a nightmare making this work as you get further into it...

We need to update the senior citizen registered customer,coz we need to make sure/confirm if the customer is still active/deceased.

[markpastoril]

I've forgot to tell you that the senior citizens discount will deduct only if the (previous reading-present reading)consumption of the customer is equal or less than 30 cubic.