T
timbits35 via AccessMonster.com
Hi,
I have a gym membership database. I will show the tables below. I have the
main sales form based on the sales table and from this I can open other forms
in particular the payments form. I have an invoice status as default "DUE." I
want to be able to change the status to "PAID" when the balance due reaches
$0.00. On the sales form I have a search box for selecting an athlete. The
status is displayed in the search box. The idea is that I select the invoice
marked as "DUE" and enter the payments against this invoice. Even better
would be that the search combo box only shows invoices with the status "DUE.
"I already have a query in place which has a calculated field for the balance
due. I don't know how to create the update query. And I don't know when is
the right time to run the query (upon exiting the database?, after any
payment is added to an invoice?) Please help.
tblsales
salesid
invnum
invstatus
tblsalesdetails
salesid
all the line item details
tblpayments
invnum
all the rest of the payment info
qrypayments due (this is based on 2 other queries - sumsales and sumpmts) and
has the calculated field : balance: [totsales]-NZ([totpmt])
Thank you,
Liane
I have a gym membership database. I will show the tables below. I have the
main sales form based on the sales table and from this I can open other forms
in particular the payments form. I have an invoice status as default "DUE." I
want to be able to change the status to "PAID" when the balance due reaches
$0.00. On the sales form I have a search box for selecting an athlete. The
status is displayed in the search box. The idea is that I select the invoice
marked as "DUE" and enter the payments against this invoice. Even better
would be that the search combo box only shows invoices with the status "DUE.
"I already have a query in place which has a calculated field for the balance
due. I don't know how to create the update query. And I don't know when is
the right time to run the query (upon exiting the database?, after any
payment is added to an invoice?) Please help.
tblsales
salesid
invnum
invstatus
tblsalesdetails
salesid
all the line item details
tblpayments
invnum
all the rest of the payment info
qrypayments due (this is based on 2 other queries - sumsales and sumpmts) and
has the calculated field : balance: [totsales]-NZ([totpmt])
Thank you,
Liane