hi,

On 09.03.2010 15:48, Revned wrote:
This makes not really sense. How do you know which payment matches which
order?
through field name RefNo, it would match the payments.
Thus it make sense??
please can you help me, I already try to remove the CustomerName to avoid
redundancy.
Okay, I see. This will still result in a problematic case:

I have a table tblOrders
CustomerName DateOrder RefNo Amount Status
primary key: RefNo
Field type: Status - check box

I have a table tblPayments
CustName Datepaid PaidRef AmntPaid
primary key: PaidRef
So your relation is

tblOrders(RefNo) - tblPayments(PaidRef)

If PaidRef is really your primary key, this means you can only have on
payment per order.

Normally you would use a table structure like this:

Order:
ID, AutoNumber, Primary Key, Not Null
idCustomer, Number(Long), Not Null
DateOrder, Date/Time, Not Null,
Amount, Currency, Not Null
RefNo, Text(255)

Payment:
ID, AutoNumber, Primary Key, Not Null
idOrder, Number(Long), Not Null
DatePaid, Date/Time, Not Null
Amount, Currency, Not Null

with a relationship with relational integrity from

Order(ID) 1 - n Payment(idOrder)

Then you would use a query like:

SELECT
O.*,
(O.Amount =
(SELECT SUM(P.Amount)
FROM Payment P WHERE P.idOrder = O.ID)) AS IsPaid
FROM [Order] O

Or as an UPDATE query:

UPDATE [Order] O
SET O.Status = True
WHERE O.Amount =
(SELECT SUM(P.Amount)
FROM Payment P WHERE P.idOrder = O.ID)


mfG
-- stefan --