Originally Posted by
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.
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
Code:
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).