If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Table Design
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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. -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm "Gettingthere" wrote in message ... 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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
Thanks for that will try and get back to you
Regards Roger "Kevin" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Property of table in design view toolbar button? | Margaret Bartley | General Discussion | 0 | August 13th, 2004 10:25 PM |
Need help w/ table design | Tom | Database Design | 0 | August 12th, 2004 02:34 PM |
Complicated Databse w/many relationships | Søren | Database Design | 7 | July 13th, 2004 05:41 AM |
Table design question - advice needed | David | Database Design | 3 | June 8th, 2004 02:21 AM |
COMPARE THE TWO TABLES | Stefanie | General Discussion | 0 | June 4th, 2004 04:36 PM |