problem with query that has many tables with differnt relationship

[DowningDevelopments]

Hello,

Im making a database that stores the details of students for a housing
company and I have a form that is based on a query which brings together many
tables for easy data input called Payments.

The main table is called payment (PK PaymentNo) and is used to link the
different types of payment that a Student might have, eg telephone, rent,
parking etc. Each payment type has different information and are hence stored
in different tables. All have the 'PaymentNo' foreignKey.

Here's the problem Some payments are one off and can be listed by the
StudentRef whilst others (like telephone) are top up type and would be listed
by their paymentNo.

There is a one to many relationship between the payment and the various
payments except those that are one off which have a one off payment where the
relationship is one to one (through the students table).

The problem im having is that the form wants all the possible payments to be
filled in and i want it so that only the required payments need to be filled
in. to this extent ive made it so that only if a checkbox is clicked will the
paymentNo and studentRef be imported into the the telephonePayment record
(hence a new one is created) otherwise no record for that paymentis created
as no payment is being taken.

This is quite a complex problem but im sure that there is a solution to it.
How should i go about fixing this?

With thanks and regards

Amit

 

[Ted Allen]

Hi Amit,

It was a little hard to follow all of what you are trying to do, but from
what I read it sounds like it may be easier to do what you are trying to do
if you simplify the main form query somewhat and use subforms to display much
of the linked information.

Generally I would always use subforms to display information that is 1 to
many with the main form info, but I will also use them to display 1:1
information as well if it makes sense.

In your case, it sounds like you only want certain info to be available for
input under certain conditions. If those fields are part of a subform, it
would be easy to use VBA to disable the subform or hide it based on the
values of one of the fields on your form.

If you want more detailed help, you may want to post some examples of what
your data looks like, and also your query sql.

HTH, Ted Allen