multi table query doesnt work!

[DowningDevelopments]

Hello all,

I am building a database for a residential compnay who rent out accomadation
to students, what they want is to store the financial information of each
student.

The problem that ive had to overcome is that the client needs to store what
bills and payments the student is paying for and how they are paying for it
on one form.
To do this i have seperate tabels for, telephone, interenet, parking and
other payments. (as each having different properties) which all link to a
payment table throught he paymentNo foreign key, which in turn links to the
student table (the students table holds the deposit) through the StudentRef
Foreign key in the Payment table.
It also has separate tables for the various payment types (as each has
different properties) 'Cash' , 'credit card', 'Cheque' & 'standing Order'.
These also link to the payment table through the paymentNo.

telephone bills
Internet connectionBill
ParkingBill Payoption
StudentRooms
OtherPaymentsBill ---> Payments ----> Students <---- Rents
StandingOrder
Cash
Credit Card
Cheque

Or at least it should, my problem is that im only able to have the payments
table attach to any one of these payment types or bills at anyone time eg.

payoption
StudentRooms

parkingbill
---------> Payments ----> Students <--- Rents

The SQL for this query is only complex as it has to show many fields from
the 6 tables, but i can list it if it helps.

In all tables linking to the payment table from the left, Ive matched up the
PK-FK
types

The payment table is very simple and has the following columns:

ReceiptNo (PK) (autonumber)
Date (date)
Student (string) --------------> StudentRef (PK in students table)
TotalCharges (currency)
TotalCollected (currency)

why is it that i cant link more than one table to the payments table and how
can i fix this?

with much thanks

Amit

 

[DowningDevelopments]

Sorry for the awkward rendering of that question, what i wanted to show was
that table Payoption, studentsRooms and Rent are tied into the Students table
and all the bills and payment methods are tied to the payments table.

 

[Ted Allen]

Hi Amit,

I couldn't really follow all of what you are trying to do nor exactly what
the problem is that you are encountering, but I will offer the following:

Often it is better/easier to base a form on a very simple data source. In
your case, it sounds like maybe just the students table and maybe any data
from other tables that relates 1:1 to the students table.

Then, for data relating 1:many with the main form record, you can display
various other data relating to the main form using subforms, each with their
own data source. This allows much more flexibility in what you can display
and how you display it.

Regarding your problem about not being able to link more than one table to
one of your tables ( I think it was the payments table), it's not clear to me
what you mean. You can always link more than one table to a given table, but
there are some cases where you must insert a table more than once into the
query to represent another instance of that table.

For instance, say you have a project table storing an EmployeeID twice, once
to represent the Project Manager and once for the Design Engineer. These
both link to the EmployeeID field in the Employees table.

But, you cannot just insert the Projects table and the Employees table, and
then link each of those fields to the EmployeeID. This would mean to Access
that the two fields in the Projects table would have to equal one another (it
would give you the cases where the Project Manager and the Design Engineer
were the same).

Instead, you would insert the Projects table, then insert the Employees
table, give it an alias such as ProjectManagers, then insert a second
instance of the Employees table and alias it to say DesignEngineers. You
would then link the ProjectManagerID field from the Projects table to the
EmployeeID in the Employees table (aliased as ProjectManagers), and link the
DesignEngineerID from the Projects table to the EmployeeID field in the
Employees table that is aliased as DesignEngineers. This tells Access that
both fields relate to the EmployeeID field in the Employees table, but that
they do not relate to one another. Incidentally, it is optional to alias the
two instances of the Employees table, Access will automatically alias the
second instance for you when you insert it into the query design grid.

I'm not sure if this is related to the problem that you were having, but I
thought I would offer it just in case.

-Ted Allen

 

[DowningDevelopments]

Thanks Ted,

I thought it was hard to follow what i was saying, i spent almost an hour
trying to figure out what the exact problem was and what should be included
in the Posted Question.

While waiting for an answer i started to experiment with the Subform idea as
ive used this many times before, the only difference here was that i would
need to be put in more than one cheque record per receipt.

As silly as it sounds students are allowed to pay their rent with 3 post
dated cheques + any other payments they want, hence on the one form ill need
to be able to take these details.
My solution was to use a continous form, seemed natural enough which imports
in the ReceiptNo from the receipt Form (payment has been renamed by my
client).

However though it takes the payment and stores it in the Cheque table when i
reopen the receipts form at that record it shows a completely blank subform
which is useless when you want to check it later.

Am looking at the linking between the mainform/subform, any clues?

Once again, sorry for the confused post this morning, i hope this one makes
more sense.

 

[Ted Allen]

If you look at the new record in the table, is all of the data entered
correctly? Including the studentID or whatever field you are using to link
between form/subform? If not, you may want to check to make sure that you
have defined the relationships between the tables in the relationships
window, including referential integrity.

If the data in the new record is correct, all that I can think of is either
the join between the form/subform is not correct, or the subform data entry
property is set to true to only allow entry of new records but not display
existing.

If none of that helps, you may want to post back with a little more info on
how you are joining between form/subform, and also the data source for each
form.

-Ted Allen