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

hi,

On 09.03.2010 11:21, Revned wrote:
> 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 <--

?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" <(E-Mail Removed)> wrote in message
news:4D7B6931-DCF2-48B5-9662-(E-Mail Removed)...
> 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

> 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" wrote:

> hi,
>
> On 09.03.2010 11:21, Revned wrote:
> > 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 <--
> .
>

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