design access

[Revned]

Hello everyone,

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

can anyone help me figure out how can I design my query and my form/subform
that when in my tblPayment field name AmntPaid equals to tblOrder field name
Amount, access will automaitcally set the field name Status to Yes.

please can anyone help me.

thanks in advance, i appreciate

 

[Stefan Hoffmann]

hi,

can anyone help me figure out how can I design my query and my form/subform
that when in my tblPayment field name AmntPaid equals to tblOrder field name
Amount, access will automaitcally set the field name Status to Yes.

This makes not really sense. How do you know which payment matches which
order?

btw, your tables are not well designed.

I have a table tblOrders
CustomerName DateOrder RefNo Amount Status
I have a table tblPayments
CustName Datepaid PaidRef AmntPaid

There should be a table Customer which should referenced in both tables
instead of storing the customer name reduuntantly.


mfG
--> stefan <--

 

[Jeff Boyce]

?The way you are tying a payment amount to an order is by the Amount?

Is there ever a situation when two orders have the same Amount?

Do you ever have a situation where someone pays less than (?!more than) the
full Order Amount?

I'll suggest that you take another look at the Northwind database that comes
with Access for a possible model of the data structure to use for an Order
application.

Good luck!

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Revned]

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.

thank Stefan Hoffman, I really appreciate your reply

 

[Stefan Hoffmann]

hi,

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 <--