How to Query unrelated table to do a calculation?

[arnodys]

Hi all, I have a problem in returning values from 2 different tables because they are not related. Let me explain:

I'm trying to do "Payment Due" query by substracting the amount in the "Cost" table with the amount in the "Payment" table ([CostAmount]-[PaymentAmount]). However, since no payment has been made, the table contains no related record.

At first I thought the problem lies in null values the table return hence I tried to use NZ function to convert null to zeros. Then I realised that no values has been returned from the table due to no related record available.

Can anyone help me?

Cheers

 

[jsanders]

You have to have a common table, and its primary key must be a foriegn key in the other two tables.

 

[arnodys]

That's normally what I did in the rest of my tables and queries. But in this case would be difficult, since it is impossible to have any record in the payment table if the customer hasn't pay.

I need to be able to show the user how much the customer need to pay, including if they haven't pay at all.

 

[arnodys]

My apology... I haven't been very clear in my explanation. I forgot to mentioned that I do have a common "ID" field which relate the two tables. When payment records exist in payment table, my query are ABLE to return the desired calculation. But my problem start when there is no payment has been made.

 

[KeithG]

Do you have an orders table?

 

[arnodys]

Yes. But I called it "BOOKING" table, because this database is built for a travel agency. Hence the main table is "BOOKING" which uses "BOOKINGID" as primary key, which then I use as foreign key in the other 2 tables (PAYMENT, and COST).

 

[ThreeCrow]

Give the attached a look see, it may help