If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
design access
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 |
#2
|
|||
|
|||
design access
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 -- |
#3
|
|||
|
|||
design access
?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" wrote in message ... 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 |
#4
|
|||
|
|||
design access
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 -- . |
#5
|
|||
|
|||
design access
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 -- |
Thread Tools | |
Display Modes | |
|
|