Prev: SQL Update question
Next: Calculated field question
From: klp via AccessMonster.com on 2 Jun 2010 09:32
I have on my customer master, a field for credit limit. The user can input
the credit limit for that particular customer. When the user then goes in to
the order screen, in the line item section, I would like for an message to
come up if they have reached their credit limit. So I know I would have to
compare the order quantity against the credit limit. Would I do this in a
query? Do I need to create a different type of query instead of a standard
one? So I would want to see:

Customer Credit Limit Qty Ordered Amt Avail
ACE Hardware 650 20
630
ACE Hardware 60
570

And have it keep it going for every order. Is this possible? What is the
easiest way to do this?

Thanks in advance
Kim P

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201006/1

From: KARL DEWEY on 2 Jun 2010 10:36
You need to have customer and their credit limit in one table and their
ordering and payments (transactions) in another.
You have to keep a running balance or otherwise you would not let them order
more even though they have paid the bills.
A query would sum their transactions and real-time reduce their credit limit
as of that date by the total outstanding balance.

--
Build a little, test a little.


"klp via AccessMonster.com" wrote:

> I have on my customer master, a field for credit limit. The user can input
> the credit limit for that particular customer. When the user then goes in to
> the order screen, in the line item section, I would like for an message to
> come up if they have reached their credit limit. So I know I would have to
> compare the order quantity against the credit limit. Would I do this in a
> query? Do I need to create a different type of query instead of a standard
> one? So I would want to see:
>
> Customer Credit Limit Qty Ordered Amt Avail
> ACE Hardware 650 20
> 630
> ACE Hardware 60
> 570
>
> And have it keep it going for every order. Is this possible? What is the
> easiest way to do this?
>
> Thanks in advance
> Kim P
>
> --
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201006/1
>
> .
>
 | 
Pages: 1
Prev: SQL Update question
Next: Calculated field question