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 Help / Query
Ok i know this is probably more simple than my mind will allow at this point
so i'm asking for help. I have a client database that I am creating that I want to do a basic billing/payment component. There are approximately 5 different services each with their own cost. (For example...One-on-One Training - cost is $35.00 an hour.) So currently i have a client table established with the following fields: cClientID - Autonumber CFirstName CLastName *other basic demographis info* I want to have a subform (that looks like a datasheet) at the bottom of a client's form to show what is being charged and a payment and the current balance. but i want a button to click to add a new service and also a button to add a payment. i hope this is somewhat clear to what i am desiring...it is basically an billing/payment that i want to create invoices for and keep up with the accounting aspect. any help on the design of the tables would be appreciated OR if you know where i can find a good (simple in terminology) template to look at and learn from. All the templates i have found do not include the payment tables and examples. I am using Access 2002. Thanks!!! |
#2
|
|||
|
|||
Table Design Help / Query
Could it ever happen that you want to invoice a client for 2 things at once?
If so, you need tables like this: FeeType table, with one record for each type of fee, and a FeeTypeID primary key. Invoice table, with fields: - InvoiceID primary key - ClientID relates to the primary key of your Client table - InvoiceDate Date/Time - InvoiceHead Memo comments to show above details - InvoiceFoot Memo comments to show below details InvoiceDetail table, with fields: - InvoiceDetailID AutoNumber primary key - InvoiceID Number relates to Invoice.InvoiceID - FeeTypeID relates to FeeType.FeeTypeID - Fee Currency how much Now one invoice can contain multiple line items. You nees a main form for the invoice, and a subform for the line items. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "LTOSH" wrote in message ... Ok i know this is probably more simple than my mind will allow at this point so i'm asking for help. I have a client database that I am creating that I want to do a basic billing/payment component. There are approximately 5 different services each with their own cost. (For example...One-on-One Training - cost is $35.00 an hour.) So currently i have a client table established with the following fields: cClientID - Autonumber CFirstName CLastName *other basic demographis info* I want to have a subform (that looks like a datasheet) at the bottom of a client's form to show what is being charged and a payment and the current balance. but i want a button to click to add a new service and also a button to add a payment. i hope this is somewhat clear to what i am desiring...it is basically an billing/payment that i want to create invoices for and keep up with the accounting aspect. any help on the design of the tables would be appreciated OR if you know where i can find a good (simple in terminology) template to look at and learn from. All the templates i have found do not include the payment tables and examples. I am using Access 2002. Thanks!!! |
#3
|
|||
|
|||
Table Design Help / Query
This helps greatly...Yes it could be possible for an invoice to bill for 2
things at once...but in the billing i would need a field to indicate how many hours billing for/or months billed for. for example. one on one training is $35 hour....client is billed for 5 hours...i want the total billed on the invoice. which table do I put this field in. question 2....what about a payment table....when they send in the payment i want this to be inputed in the database...can you please give me an idea how that table would be set up based on the two tables you have described. question 3....now that i have these tables set up i want to put a "subform" in the form of a datasheet on the to show me the account status. to show what is billed, the date billed and the amount billed and the balance owed...then to also show payment date and payment amount with the updated balance. "Allen Browne" wrote: Could it ever happen that you want to invoice a client for 2 things at once? If so, you need tables like this: FeeType table, with one record for each type of fee, and a FeeTypeID primary key. Invoice table, with fields: - InvoiceID primary key - ClientID relates to the primary key of your Client table - InvoiceDate Date/Time - InvoiceHead Memo comments to show above details - InvoiceFoot Memo comments to show below details InvoiceDetail table, with fields: - InvoiceDetailID AutoNumber primary key - InvoiceID Number relates to Invoice.InvoiceID - FeeTypeID relates to FeeType.FeeTypeID - Fee Currency how much Now one invoice can contain multiple line items. You nees a main form for the invoice, and a subform for the line items. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "LTOSH" wrote in message ... Ok i know this is probably more simple than my mind will allow at this point so i'm asking for help. I have a client database that I am creating that I want to do a basic billing/payment component. There are approximately 5 different services each with their own cost. (For example...One-on-One Training - cost is $35.00 an hour.) So currently i have a client table established with the following fields: cClientID - Autonumber CFirstName CLastName *other basic demographis info* I want to have a subform (that looks like a datasheet) at the bottom of a client's form to show what is being charged and a payment and the current balance. but i want a button to click to add a new service and also a button to add a payment. i hope this is somewhat clear to what i am desiring...it is basically an billing/payment that i want to create invoices for and keep up with the accounting aspect. any help on the design of the tables would be appreciated OR if you know where i can find a good (simple in terminology) template to look at and learn from. All the templates i have found do not include the payment tables and examples. I am using Access 2002. Thanks!!! |
#4
|
|||
|
|||
Table Design Help / Query
A 1:
Use fields like this in the Invoice table: - Quantity Number (Double) how many hours or seminars or whatever. - FeeEach Currency how much per hour or seminar or ... Then create a query using this table, and type this into the Field row: Amount: [Quantity] * [FeeEach] Use this query as the source for your subform. Show the subform in Continuous Form view. In the Form Footer section, include a text box with Control Source: =Sum([Amount]) That shows the invoice total. A 2: Store Payments received in a different table. I suggest you don't make any direct connection between the 2 tables, because all sorts of weird things happen in the real world, such as: - Multiple payments of one invoice; - Partial payments; - One payment that covers multiple invoices (or parts of invoice); - Prepayments (i.e. money received before any invoices is written out.) So the simplest solution is just to sum all the invoices for a client, subtract the payments received, and the difference is what is owing. If you must go beyond that, you may need to understand how double-entry accounting works. A 3: I suggest a different form for payments received. If you wish, you can show a balance owing for the client on your form (a DSum() expression.) Once you have the above working, there is another approach that I've found very useful, and that is to have just one pair of tables to store invoices, receipts, credits (effectively negative invoice), and refunds (effectively negative receipts.) The main table has a field that indicates the transaction type. The TransactionDetail table contains the line items (Quantity, FeeEach, etc.) The transaction type table has a Multiplier field that contains 1 (for positive) or -1 (negative.) In a query you can therefore get the amount as: Amount: [Multiplier] * [Quantity] * [FeeEach] Then when you DSum() that amount for any client, you know what they owe. This alternative is very flexible, but you may find it easier to take onboard after you have examined the first approach. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "LTOSH" wrote in message ... This helps greatly...Yes it could be possible for an invoice to bill for 2 things at once...but in the billing i would need a field to indicate how many hours billing for/or months billed for. for example. one on one training is $35 hour....client is billed for 5 hours...i want the total billed on the invoice. which table do I put this field in. question 2....what about a payment table....when they send in the payment i want this to be inputed in the database...can you please give me an idea how that table would be set up based on the two tables you have described. question 3....now that i have these tables set up i want to put a "subform" in the form of a datasheet on the to show me the account status. to show what is billed, the date billed and the amount billed and the balance owed...then to also show payment date and payment amount with the updated balance. "Allen Browne" wrote: Could it ever happen that you want to invoice a client for 2 things at once? If so, you need tables like this: FeeType table, with one record for each type of fee, and a FeeTypeID primary key. Invoice table, with fields: - InvoiceID primary key - ClientID relates to the primary key of your Client table - InvoiceDate Date/Time - InvoiceHead Memo comments to show above details - InvoiceFoot Memo comments to show below details InvoiceDetail table, with fields: - InvoiceDetailID AutoNumber primary key - InvoiceID Number relates to Invoice.InvoiceID - FeeTypeID relates to FeeType.FeeTypeID - Fee Currency how much Now one invoice can contain multiple line items. You nees a main form for the invoice, and a subform for the line items. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "LTOSH" wrote in message ... Ok i know this is probably more simple than my mind will allow at this point so i'm asking for help. I have a client database that I am creating that I want to do a basic billing/payment component. There are approximately 5 different services each with their own cost. (For example...One-on-One Training - cost is $35.00 an hour.) So currently i have a client table established with the following fields: cClientID - Autonumber CFirstName CLastName *other basic demographis info* I want to have a subform (that looks like a datasheet) at the bottom of a client's form to show what is being charged and a payment and the current balance. but i want a button to click to add a new service and also a button to add a payment. i hope this is somewhat clear to what i am desiring...it is basically an billing/payment that i want to create invoices for and keep up with the accounting aspect. any help on the design of the tables would be appreciated OR if you know where i can find a good (simple in terminology) template to look at and learn from. All the templates i have found do not include the payment tables and examples. I am using Access 2002. Thanks!!! |
Thread Tools | |
Display Modes | |
|
|