I
ivan
Advice needed on applying payments received from customers. I have an
application which deals with sales to members of organizations (example:
students in
schools, members of clubs etc.). Typically, when we make a sale to a member
of the organization, it is almost always a one time sale only. The company
typically would never sell to that member again! So tables were designed
with that in
mind. The few rare times that a customer would have a second order, we
would just enter him/her again as a new customer and new order (and the
customer would pay multiple payments but always toward each separate order).
This was practical, since we almost never had multiple orders or re-orders,
and since the customer would pay for each order separately anyway.
Now however, due to new and different product offerings, we are selling not
just to the members but directly to the organization itself.
"XYZ" organization might place different orders several times during a
month.
They then might send one payment to cover 4 different invoices. I have
manually been breaking up the check applying $250 to this order, $125 to
that order, etc. I would like Access to apply the payment automatically,
but I am unsure as to the strategy to do this, especially since each order
is for a "new customer".
I am thinking that since I am now picking up multiple orders for the same
"organization", I need to revisit the issue of a "new customer" for each
order. The problem with that, is that each "organizational" order might be
a "new Customer" for billing purposes... e.g.- (Math dept at XYZ school;
Social studies dept at XYZ school).. very different customers.... and yet
the school or school board may send one check to cover multiple invoices.
I have many tables but there are four tables involved with payments:
Organization, Customers, Orders, and Payments with the following related
fields tying them together-(there are a lot more fields but these are the
ones that are relevant to each other):
Organization has: OrganizationName (used as a lookup for the Customer
table).
Customer has: CustomerID, CustomerName, OrganizationName
Orders has: OrderID and CustomerID
Payments has : PaymentID and OrderID
So, "Payments" is tied to "orders" through the OrderID field. I am
thinking that now that we are receiving multiple orders for some customers,
we should be entering customers only once and linking payments to the
"Customer" table instead of the "orders" table. Is this thinking correct or
is there a better way? And if it is correct, how do I change my table
structures without affecting all of my existing data. Or should we just
enter an organization as a customer only once and still keep payments tied
to "Orders"? Please keep on mind that we still have the "members of
organization one time" orders to deal with in the same tables.
Any thoughts appreciated.
application which deals with sales to members of organizations (example:
students in
schools, members of clubs etc.). Typically, when we make a sale to a member
of the organization, it is almost always a one time sale only. The company
typically would never sell to that member again! So tables were designed
with that in
mind. The few rare times that a customer would have a second order, we
would just enter him/her again as a new customer and new order (and the
customer would pay multiple payments but always toward each separate order).
This was practical, since we almost never had multiple orders or re-orders,
and since the customer would pay for each order separately anyway.
Now however, due to new and different product offerings, we are selling not
just to the members but directly to the organization itself.
"XYZ" organization might place different orders several times during a
month.
They then might send one payment to cover 4 different invoices. I have
manually been breaking up the check applying $250 to this order, $125 to
that order, etc. I would like Access to apply the payment automatically,
but I am unsure as to the strategy to do this, especially since each order
is for a "new customer".
I am thinking that since I am now picking up multiple orders for the same
"organization", I need to revisit the issue of a "new customer" for each
order. The problem with that, is that each "organizational" order might be
a "new Customer" for billing purposes... e.g.- (Math dept at XYZ school;
Social studies dept at XYZ school).. very different customers.... and yet
the school or school board may send one check to cover multiple invoices.
I have many tables but there are four tables involved with payments:
Organization, Customers, Orders, and Payments with the following related
fields tying them together-(there are a lot more fields but these are the
ones that are relevant to each other):
Organization has: OrganizationName (used as a lookup for the Customer
table).
Customer has: CustomerID, CustomerName, OrganizationName
Orders has: OrderID and CustomerID
Payments has : PaymentID and OrderID
So, "Payments" is tied to "orders" through the OrderID field. I am
thinking that now that we are receiving multiple orders for some customers,
we should be entering customers only once and linking payments to the
"Customer" table instead of the "orders" table. Is this thinking correct or
is there a better way? And if it is correct, how do I change my table
structures without affecting all of my existing data. Or should we just
enter an organization as a customer only once and still keep payments tied
to "Orders"? Please keep on mind that we still have the "members of
organization one time" orders to deal with in the same tables.
Any thoughts appreciated.