Inventory

[Brian]

I have a process that tracks customer invoices through production and
eventual delivery. One of my customers is "showroom" where I place those
items that are manufactured for display in our showroom. Occasionally a
customer will purchase a showroom model.
How can I link the original showroom production information with a new
customer sale?

tblCustomer
--------------
customerID
Name (showroom)
address .... blah blah

tblInvoice
invoice#
saleDate
Production info .......

Both showroom and the new customer will have unique ID's yet share the same
product. I don't particularily want to duplicate the production information
since that may influence the production numbers.

Is it better to keep the showroom models in a seperate table and then
indicate a new invboice number when sold? Won't this violate some
normalization rules?

??????????????

Thanks

 

[JK]

Brian,

There is more than one way of skinning the bear

a) Presumably you have an Inventory file, if so, keep it there with a flag
that it is a showroom item (add a field to inventory table numShowRoom or
such like). In fact it is part of inventory

b) If you have facility for returned goods (from customers), return the
item(s) from customer "showroom" and resell it

This is just from the top of my head, there may be other ways

Regards/JK

 

[Brian]

Problem is every product's manufacturing information is tracked through an
invoice number (since products are not normally made until it is ordered, so
there is no inventory kept on hand other than the display models). Currently
showroom models are handled as orders from the customer "showroom" and given
a unique invoice number. It may be months but if a customer purchases that
showroom model I want to be able to transfer ownership of that "invoice" to
the new customer without duplicating the assiciated production data, costs,
etc. Another problem is that several forms/reports are linked by their
customer/invoice relationship (1:many). I need to be able to display the
current show room models under the customer "showroom", and display the
showroom models sold to other customers under that customer's name and not
under showroom anymore (yet be able to pull up a report showing the showroom
models sold)

<Thinking aloud>

Perhaps if I made a seperate table (identical to the table containing the
invoice information) and keep all showroom models there, with additional
fields indicating date sold, new customer, and new invoice number - that
would resolve the transfer of ownership tracking I need, and then add this
new invoice field to the queries with some statement ..... Is there a way to
create an alias for a field say SRInvoice# = NewInvoice#?

 

[JK]

Thinking .....

I have had what looks like a similar problem in the hospitality industry and
that is when a guest changes room. Checking-out of the old room and
checking-in to the new room was not an option because check-out will
finalise accounts and check-in will require him/her to pay
deposits/prepayments which are already paid . Although technically the
program can handle this but it is very tedious to the user and creates a
messy and confusing (2 or more) final statements when the guest finally
check out for real.

The way I solved that problem is by creating a special routine for those
occasions that does just that (check-out/check-in) but in a slight different
way:

Creates a new booking by (add a new record to the booking table), copy into
it all the all the data from the old booking except the room number and
price, if changed, then replace "many" side of linked tables (eg. money etc)
with the new booking key, re-flag both rooms as vacant/occupied (and do
other thing which are not relevant here) . All the user had to provide is
new room number and date.

Translating that into your problem, how about:

Create a new customer invoice record, copy all the costs and other details
needed from the showroom customer to the real customer record, re-line other
records if applicable and then delete or flagging the old showroom customer
details for those item to avoid duplication of sales/cost.

Dose this make sense?

Regards