How to setup tables

[melaniecarr23]

I already setup a table that is NOT ideal, and would like to try redoing it, but am not sure what is best. Here's the details:

I have a table named AUTO_CC which has the following fields:

id
pid (patient id, lookup to client table)
start_date
end_date
card_num
exp_date
vcode
jan
feb
mar (etc for each month of the year)
jan_paid (etc for each month), checkbox
closed (for when a year's payments have been completed)

When I run queries, I have to run them for all autocc records not closed and not paid for a certain month with an amount over $0.

Is there a way to setup the table (or tables) so I can run one report and select the month?

Melanie

 

[Darrylles]

Hi Melanie,

Does this help?
[tt]
tblAuto_CC tblPayment
id <--| p_month (key)(01,02 ....10,11,12
pid | p_year (key)(2004,2005,2006...)
start_date |-->> auto_cc_id (key)
end_date amount (currency)
card_num paid (yes/no)
exp_date
vcode
[/tt]

Regards,

Darrylle


Never argue with an idiot, he'll bring you down to his level - then beat you with experience. darrylles@yahoo.co.uk

 

[melaniecarr23]

My question regarding this setup is:

Can I use 1 form to enter the credit card details (check the months selected, enter the amounts for each month) and have each payment selected be entered into the tblPayment table?

 

[Darrylles]

Hi Mel,

Yes (and no).
The tblPayments table would be in a sub-form within the main form (which would hold tblAuto_CC).
The tblPayments records would be kept automatically 'in sync' with the tblAuto_CC records via the child/parent link (property of a sub-form).

Sub-forms exist to allow related tables to be displayed / updated as if on one form. i.e. The user would only see one form.

I'm not sure about your knowledge of relational tables, but the above is the standard way to go (although table design may well be totally different dependant on the developer).

Advice:
Define the tables as above.
NB Remember to define THREE keys in tblPayment - this is crucial to keep the records unique.

Produce the frmPayments_sub form just as you would any other form - test that you can add/update records. (Temporarily make the tblPayments 'auto_cc_id' field Autonumber for this purpose. When it works ok set this field back to Long Int (it'll be automatically copied from the related AUTO_CC table when it's a sub-form)).

Produce your frmAuto_cc form as usual. Test that you can add/update these records also.

Drop a subform (from the toolbox) onto frmAuto_cc and when prompted, choose your frmPayments_sub form.
When prompted for child/parent links, choose tblAuto_CC.ID as the master and tblPayment.auto_cc_id child.

The above is of course simplified, but all steps are described. i.e. When entering Month and Year, you'll want to make this user friendly (maybe a drop-down list for each) - you can polish each form up later though.

ATB

Darrylle











Never argue with an idiot, he'll bring you down to his level - then beat you with experience. darrylles@yahoo.co.uk