RE: Table Design
From: Kevin (Kevin_at_discussions.microsoft.com)
Date: 11/19/04
- Next message: Brad_A: "Re: adding database to another"
- Previous message: Lynn Trapp: "Re: Replication error"
- In reply to: Gettingthere: "RE: Table Design"
- Next in thread: Gettingthere: "RE: Table Design"
- Reply: Gettingthere: "RE: Table Design"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 19 Nov 2004 05:55:01 -0800
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" wrote:
> 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" wrote:
>
> > 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" wrote:
> >
> > > 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" wrote:
> > >
> > > > 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
> > > >
> > > > "Gettingthere" wrote:
> > > >
> > > > > 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
- Next message: Brad_A: "Re: adding database to another"
- Previous message: Lynn Trapp: "Re: Replication error"
- In reply to: Gettingthere: "RE: Table Design"
- Next in thread: Gettingthere: "RE: Table Design"
- Reply: Gettingthere: "RE: Table Design"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
