Invoicing/Inventory Db

[Question Boy]

Hello all,

I am building a 'simple' invoicing db. I need to be able to create
quotes/estimates as well as actual purchase invoices. Should they all be in
the same table and simply differentiated by 'Type' or should they be in
separate table? Be give an explanation so I might learn a thing or 2.

Secondly, we have a weird need. When we have a purchase, let say a client
orders 50 boxes or item 1111, we would give them 5 more for free. I need to
track the free one give away for nothing as well as the one purchases. how
can this be done. Is it simply a question of entering 2 entries into the
invoice 50 @ xxx$ and 5 @ 0$ or is there another better approach.

Thank you,

QB

 

[Jeff Boyce]

You know your data (and situation) much better than we do.

If you need/want exactly the same data elements for both your "quotes" and
"purchases", you don't need separate table(s) ... however! until we know
more about how YOU are treating these, and the data, it's hard to offer
specific suggestions for a well-normalized data structure.

If you want to make your application's users do extra work, make them add a
second purchase item to reflect the "free" ones you add to an order. Or,
since you are already using an application, why not tell the application how
to determine the number of "free" items to be added and let the application
create the additional "order detail" row?

Good luck!


Regards



Jeff Boyce

Microsoft Office/Access MVP

 

[Question Boy]

Jeff,

with regards to the invoice vs. quotes, they contain the exact same info.
Actually, I need to be able to convert a quote into an invoice upon approval.
So in this case, I assumed, and thus the question, that a single table with
a 'Type' field would be best, but wanted other professional's opinion as this
is my first database of this type.

Thank you for your help!.

QB

 

[Jeff Boyce]

Given that the data is identical, I'd use the additional "Type" field to
"convert".

JOPO - just one person's opinion!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Fred]

Dear QB,

I think that you will find that your "simple" db is not simple because your
entities (invoices) are one time transactions, and will be automatically
modifying numerial inventory values in your inventory table. You will need
to make the distinction between entities which are one-time events (invoices,
shipment) and entities which are not (quotations, orders)