Table Design

[Gettingthere]

I have to create a database for transactions of school books. The Customer
often pays by different combination of media, eg: Pay portion by cash and pay
portion by chq. There are 4 medias, CASH, Cheque, Credit Card, Gov
Assistance.
Do i create a Non- Normalize table and include column for each or do I have
a drop down list selecting the type of payment. The second otion presents a
problem where the data entry operator may need to enter INV details two or
more times depending on how many different types of payment they use, not
posible due to restraint on time. Each type of payment needs to separated in
a list later to show all transactions of cash only, cc only etc... anything
with 2 or more types of payments need to be separated and listed on a
combination report.

What is best practice? and How?
Thanks in advance

Roger

 

[Kevin]

I would use a normailzed table and let them choose the transaction type from
a drop down list. I assume INV details is "Invoice"? If so, have two tables
1) tbl_Invoice and tbl_tansaction and tie the two together using a
invoice_ID. In the transaction form, have the user choose the invoice number
for each transaction. Selecting only this invoice number will mean the user
only has to enter invoice detail one time, then refer to it to enter each
transaction.

Your report would be by invoice and could seperate each transaction and the
status of that transaction etc.

I hope that helps!

Kevin

 

[Lynn Trapp]

Create a normalized table that is a child of your payment table. It would be
the Payment details table and include the payment type that you could use a
combo box to select from a list of possible payment types.

 

[Gettingthere]

Thanks this helps alot. But if I may 1 more question.

Would you then use in the form, the transaction form as a sub form of DATA
ENTRY form, where primary form is invoice?

Thanks
Roger

 

[Kevin]

You could do it either way. Enter your invoice first, then in a different
form enter the transaction data or have a sub form tied to the transaction
table that is a child of the invoice data entry form. It depends on how you
expect the process to work. Will the invoice be created at a different time
then the transactions occuring? If so have two different forms, if you would
be creating both at the same time, use the form/subform design.

What your trying to do here is model the process that is occuring or that
you (or your customer) want to occur.

Hope that helps!

Kevin

 

[Gettingthere]

My i explain quickly, this database is created on the fly. Acustomer walks
in and receives an invoice preprinted by head office. the customer receives
the product and the invoice is then entered into this database, which is
purely for reconciliation. The data entry operator enters Inv number, amount
paid and how. The complication comes when we have to print Batch listing of
25 transactions for each payment type, and there is a combination batch wher
an invoice is paid by two or more methods (which can be frequent). Batch
numbers have to be unique between batches, and invoices cannot be carried
over to the next day, eg the last batch cannot include other days
transactions.

I have somewhat succefully created a database using 4 tables, one for each
transaction type and the data entry person select which form/table to enter
the data. This creates a very messy database trying to get the
reconciliation, but it makes creating batches easy. In the end I thought to
myself there must be a BETTER way of doing this!!!!

Any advise would be fatastic

Thanks

Roger

 

[Kevin]

I would have several tables. The first would be tbl_invoice, the second would
be tbl_transaction. You may also want one that is tbl_trans_type for the
transaction types. If you expect the transaction types would always be the
same and would NEVER change or add one then just build the transaction types
into the dropdown list on the form. I will take a stab at the design of the
tables here,, but you know what data you need to track so modify as necessary.

tbl_invoice:
field Name field Type Comment
invoice_ID AutoNumber - Long Int Primary Key

invoiceDate Date - General Date General
Date gives you more

flexability. You can display this

however you need by fixing the

display format in forms and Reports

CustomerFName Text FirstName
CustomerLName Text Last Name

Item_ID Long Int From
tbl_Item, another table

ItemValue Currency from
tbl_item. I would put enter

value here to ensure the actual

value as sold is captured. This way

if you change your prices you can

still refer to the item value as sold.

There are other ways to do this,

but it would add to the complexity

with no real value, and possibly a

performance hit.

item_qty INT
Item quantity sold

invoice_Closed Boolean A flag
to indicate iof the invoice

has been paid in full

discount_val Number - Double If you use
a percentage for the

discount, keep this as double. If

you use a dollar value, change it to

currency.

tbl_transaction:
field Name field Type Comment
transaction_ID Autonumber - Long Int Primary Key
invoice_ID Number - Long Int Linked to
tbl_invoice
transaction_type text if
you use a table tbl_trans_Type

then this would be a long int and

would be the primary key in the

table tbl_trans_Type

account_num text this
would be a check number,

credit card number, whatever you

need to capture.

amt_paid currency

transaction_Date Date - General Date

tbl_item:
field Name field Type Comment
item_ID AutoNumber - Long Int Primary Key

item_Description Text

Item_Value Currency

For the dataentry form, have the main part of the form tied to tbl_Invoice.
Put a subForm on the main form tied to tbl_Transaction.

If you need to track inventory, add a table tbl_inventory. When you sell an
item, subtract it from inventory. It would be designed as follows:

field Name field Type Comment
inv_ID AutoNumber - Long Int Primary Key

item_ID Number - Long Int From
tbl_Item

qty_onhand Number - Long Int

qty_order_Lim Number - Long Int low quantity
order limit

Hope that helps!

 

[Gettingthere]

Thanks for that will try and get back to you

Regards

Roger