Queries - can I update and/or delete in queries?

S

Sandy

Hope this explains it....I have a table called Class Listing (classid, name,
etc ), another called registrations (actual registrations, class id, date
reg. , etc.). I have classid linked between the tables and my queries run
just fine....I need to be able to change the info in the registration table
(either change or delete) on a case by case basis...right now I have the info
pulling up from a query which I cannot make any changes to. What is the best
way to handle this? (i.e...parents register their children for a class, then
a few days pass and they call and want to switch to a different
class...sometimes the costs are the same, sometimes not). Would I be better
off to "cancel" the registration and "reregister" instead of attempting to
change???
 
C

Chris2

Sandy said:
Hope this explains it....I have a table called Class Listing (classid, name,
etc ), another called registrations (actual registrations, class id, date
reg. , etc.). I have classid linked between the tables and my queries run
just fine....I need to be able to change the info in the registration table
(either change or delete) on a case by case basis...right now I have the info
pulling up from a query which I cannot make any changes to. What is the best
way to handle this? (i.e...parents register their children for a class, then
a few days pass and they call and want to switch to a different
class...sometimes the costs are the same, sometimes not). Would I be better
off to "cancel" the registration and "reregister" instead of attempting to
change???

Sandy,

The way to handle this is determined by the business requirments.

How does the business handle class registrations and cancellations?


Sincerely,

Chris O.
 
S

Sandy

Right now they don't have any set policy (everyone does it differently).
Normally if someone registers for a class and then later changes their mind
they either, reassign to another class, refund their money, give them a
credit for a future class, etc....
 
C

Chris2

Sandy said:
Right now they don't have any set policy (everyone does it differently).
Normally if someone registers for a class and then later changes their mind
they either, reassign to another class, refund their money, give them a
credit for a future class, etc....

"Chris2" wrote:

Sandy,

So, effectively, the business rules are:

Anyone may register a student for a class.

Registration for a class requires payment for the class.

Anyone may cancel a registration for a student for a class.

Cancellation of a class requires a refund of the payment for the class.

A refund may be applied toward the registration of another class(es).

Any funds remaining must still be refunded.

Any funds still required must still be paid.


Since this involves money, IMO, you will need to maintain a transactional history of
payments and refunds.


Students
StudentID -- Primary Key

Classes
ClassID -- Primary Key
ClassName
ClassCost
ClassStartDate
ClassEndDate

Registrations
ClassID ------\
StudentID ----- 3-column Primary Key
RegDate --/
Cancelled

(I would normally spell RegDate as RegistrationDate, but am saving horizontal space for
this example.)

Payments
ClassID ------\
StudentID ----- 3-column Foreign Key to Registrations
RegDate --/
PaymentDate --/ 4-column Primary Key (with above 3)
PaymentAmount

(RegDate and PaymentDate both assume times will be entered with the dates.)

Payments are appended as positive PaymentAmount values at the date and time of the
payment.

Refunds are appended as negative PaymentAmount values at the date and time of the refund.

Rows in Payments are **never** **ever** changed for any reason, no matter what. If a
correction for an error is required, a new row representing the correction is entered.

Whenever you want to know the status of payments for registrations, you SUM the
PaymentAmount of rows that are Registrations.Cancelled = False and compare that to
Classes.ClassCost to be sure the class has been paid.

Whenever you want to know the status of refunds for cancelled registrations, you SUM the
PaymentAmount of rows that are Registrations.Cancelled = True, and for amounts greater
than 0, a refund remains due.

Version of the above queries would exist for individual students or registrations (and
possibly even classes, if one wanted to see all incomplete payments for a particular
class).

Whenever a registration is made, if full payment is not tendered, the system would
generate a report showing the amount still due and that would be turned over the
individual conducting the registration, or it would be mailed to them, etc.

Obviously, students will not be attending class if any payment remains due.

A separate two-part transaction would be programmed for entering a refund for one
registration, and then entering a payment equal to the refund amount for another
registration (used when a student is switching classes and done to keep users from having
to run full refunds, hand money back over, and then run full payments and take money right
back). Over and under payment for the new class continues to be handled as for all other
classes.

At the end of any month where over payments remained on file, notices (and checks, if you
are setup for it) could be mailed.

The same goes over under payments (except it would be notices only).


Sincerely,

Chris O.
 
C

Chris2

Students
StudentID -- Primary Key

Classes
ClassID -- Primary Key
ClassName
ClassCost
ClassStartDate
ClassEndDate

Registrations
ClassID ------\
StudentID ----- 3-column Primary Key
RegDate --/
Cancelled

(I would normally spell RegDate as RegistrationDate, but am saving horizontal space for
this example.)

Payments
ClassID ------\
StudentID ----- 3-column Foreign Key to Registrations
RegDate --/
PaymentDate --/ 4-column Primary Key (with above 3)
PaymentAmount

I was mixing data model with database implementation above, sorry about that. It should
be:

Students
StudentID -- Primary Key

Classes
ClassID -- Primary Key
ClassName
ClassCost
ClassStartDate
ClassEndDate

Registrations
RegistrationID -- Primary Key
ClassID -----------\
StudentID ---------- 3-column Unique Index
RegistrationDate --/
Cancelled

Payments
PaymentID -- Primary Key
RegistrationID -- Foreign Key to Registrations
PaymentDate ----/ 2-column Unique Index (with RegistrationID)
PaymentAmount


Sincerely,

Chris O.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top