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
|
|||
|
|||
Invoicing database design
I am redesigning an existing database and need to put some invoicing
capabilities in it need to carry forward some payments an credits to the next invoice. the existing database was a simple one table database to store info for the amount of credit cards that we need to reimburse our customers. Now we are setting up the invoice for gas purchased less credit cards to be reimbursed. Not always equal need to carry credit to next invoice. I have made a form to add credit to next invoice but seems to be alot of manual work and could run into problems if the the credit doesn't get entered Thanks for your help |
#2
|
|||
|
|||
Invoicing database design
I noticed that nobody answered. I think that this is due to several issues
and complexities. If I may mention and address a few and then provide an idea to hopefully get you pointed in the right direction. - Semantics / definitions problem. In most business circles invoicing is a one time event, THE billing for goods or services provided. Anything else (e.g. reflecting payments, a repeated listing of still open items (but NO first billings for items) would be a different item which is a "statement." - Your description was hard to understand. This is understandable, give the complexity of what you're trying to do. While the specific were not rigorously understandable, I did get the general idea. - What you are describing is a complex application, not just a question to be answered. For example, it is fundamentally different and more complicated than the Northwind example. - If you want something that is quick or easy to get to the point where it is ready to use, you might have to buy/use enterprise software rather than developing it on Access. As an overview to do it in Access, you'll need to set up a table which is an "accounts receivable" ("A/R") ledger for each customer. One field will be the customer ID number. Each transaction involving invoicing or receipt of payment from that customer, or other items affecting (such as credits) affecting what they owe will be a record in this table. It's up to you (based on your particular needs) whether the individual line items from your invoice each become a record, or whether the whole invoice becomes a one-record summary. You need to set it up so that invoicing an order generates ("posts") the appropriate entry in your "accounts receivable" table. Statements would be reports generated from the A/R table. Again, this is just an overview, what you're describing will require a lot of work and details. "the bp Guy" wrote: I am redesigning an existing database and need to put some invoicing capabilities in it need to carry forward some payments an credits to the next invoice. the existing database was a simple one table database to store info for the amount of credit cards that we need to reimburse our customers. Now we are setting up the invoice for gas purchased less credit cards to be reimbursed. Not always equal need to carry credit to next invoice. I have made a form to add credit to next invoice but seems to be alot of manual work and could run into problems if the the credit doesn't get entered Thanks for your help |
#3
|
|||
|
|||
Invoicing database design
Thank you for the reply. I think invoicing might have been the wrong term for
what I want to accomplish with this app. It might be more of a tracking or a double check for our accounting software. the original database was designed to keep track of daily credit cards that were taken by our customers and needed to be reimbursed to them via eft ever 3 to 4 days. which worked fine all we had to do was reimburse there bank acct. Now a customer wants to subtract the amount of credit card reimbursement from the cost of fuel that is purchased from us. So what I did was created a table [customer] [InvoiceDate] [invoice#] [InvoiceAmt] to enter data what the customer owes. This works fine. I setup a query to get info for the tblinvoice and tblcreditcard and then created a report to show the invoice and list creditcards due and did calculations to show invoice amt and credit cards owed. the problem is the two don't match example invoice 25,000 credit cards owed 28,000 which would produce a credit of 3,000 which would have to be applied to next invoice. So I put another field in tblinvoice table [creditinvoice]and made the adjustment to the query and report settings. what I am afraid of is not getting that credit applied to the next report. this does work pretty well just afraid of human error. I hope this makes a little more sense than my first post . Thanks for your help "Fred" wrote: I noticed that nobody answered. I think that this is due to several issues and complexities. If I may mention and address a few and then provide an idea to hopefully get you pointed in the right direction. - Semantics / definitions problem. In most business circles invoicing is a one time event, THE billing for goods or services provided. Anything else (e.g. reflecting payments, a repeated listing of still open items (but NO first billings for items) would be a different item which is a "statement." - Your description was hard to understand. This is understandable, give the complexity of what you're trying to do. While the specific were not rigorously understandable, I did get the general idea. - What you are describing is a complex application, not just a question to be answered. For example, it is fundamentally different and more complicated than the Northwind example. - If you want something that is quick or easy to get to the point where it is ready to use, you might have to buy/use enterprise software rather than developing it on Access. As an overview to do it in Access, you'll need to set up a table which is an "accounts receivable" ("A/R") ledger for each customer. One field will be the customer ID number. Each transaction involving invoicing or receipt of payment from that customer, or other items affecting (such as credits) affecting what they owe will be a record in this table. It's up to you (based on your particular needs) whether the individual line items from your invoice each become a record, or whether the whole invoice becomes a one-record summary. You need to set it up so that invoicing an order generates ("posts") the appropriate entry in your "accounts receivable" table. Statements would be reports generated from the A/R table. Again, this is just an overview, what you're describing will require a lot of work and details. "the bp Guy" wrote: I am redesigning an existing database and need to put some invoicing capabilities in it need to carry forward some payments an credits to the next invoice. the existing database was a simple one table database to store info for the amount of credit cards that we need to reimburse our customers. Now we are setting up the invoice for gas purchased less credit cards to be reimbursed. Not always equal need to carry credit to next invoice. I have made a form to add credit to next invoice but seems to be alot of manual work and could run into problems if the the credit doesn't get entered Thanks for your help |
#4
|
|||
|
|||
Invoicing database design
Your second post provided additional insight, but did not provide the
"structure of data" type info that would be needed to solidly spec a solution. So I'm taking a guess here. If I may be direct in a crucial area, your second posts seems to continue the confusion between invoicing and statements. If this is a double check on another system, then the invoicing ACT might be occurring in that system. In that case it gets simpler. Then just set up the described A/R ledger. Then you should be able to do any or all of that in reports. For example, if your group by the customer ID, and run a subtotal of all of their items, that subtotal will be the net amount owed. You could put a page break with the grouping in which cashe you'll print one page (+) per customer. Maybe / hopefully this helps a little Sincerley, Fred |
#5
|
|||
|
|||
Invoicing database design
"Fred" wrote: Your second post provided additional insight, but did not provide the "structure of data" type info that would be needed to solidly spec a solution. So I'm taking a guess here. If I may be direct in a crucial area, your second posts seems to continue the confusion between invoicing and statements. If this is a double check on another system, then the invoicing ACT might be occurring in that system. In that case it gets simpler. Then just set up the described A/R ledger. Then you should be able to do any or all of that in reports. For example, if your group by the customer ID, and run a subtotal of all of their items, that subtotal will be the net amount owed. You could put a page break with the grouping in which cashe you'll print one page (+) per customer. Maybe / hopefully this helps a little Sincerley, Fred |
#6
|
|||
|
|||
Invoicing database design
My post didn't go through so will try again. I have kinda got hung up on
invoice statement is a better choice of words the a/r ledger would be a great solution if I would be able to get data from the two tables that I now get the info for what I am doing. The query that I use now is [tblinvoice](Invoicedate,customer,invoice#,Invoiceamount,credi tamount) and [tblcreditcard](customer,creditcarddate,Gross,fee,net) the problem I run into in this query that the invoicedates and credit card dates are not equal. If I could make a table from this query to get a/r ledger I could see where I could list this in a statement. The relationships of the two tables seems to be a problem as it is now. Hopefully I am not adding more confusion to this . "Fred" wrote: Your second post provided additional insight, but did not provide the "structure of data" type info that would be needed to solidly spec a solution. So I'm taking a guess here. If I may be direct in a crucial area, your second posts seems to continue the confusion between invoicing and statements. If this is a double check on another system, then the invoicing ACT might be occurring in that system. In that case it gets simpler. Then just set up the described A/R ledger. Then you should be able to do any or all of that in reports. For example, if your group by the customer ID, and run a subtotal of all of their items, that subtotal will be the net amount owed. You could put a page break with the grouping in which cashe you'll print one page (+) per customer. Maybe / hopefully this helps a little Sincerley, Fred |
#7
|
|||
|
|||
Invoicing database design
Hello,
Again, I'm just going on my best guess of what's happening. I guess that you are putting the applicable payment/credit into the same record as the invoice. And i'm assuming that you have NO DB linkage from the credit card table to the invoices table. If this assumption is wrong, that changes some things. My A/R idea would take a fundamental restructuring of your tables. Here's the broad brush (via the Fred LOW TECH armchair method) if you are interested: 1. Save a backup copy or two of your database & everything 2. Add a "description" field to your table. 2.1 Undo the PK status of your PK field 3. Add a "closed" field, and load a "Y" in it for all invoices which are exactly zeroed out 4. Copy your original Table1 to Table2 5. In Table1 wipe out all records (if any) that don't have an invoice amount 6. In Table 1 Rename the InvoiceAmount field to "Amount", and delete the "CreditAmount " field 7. In Table 2 wipe out all fields that don't have a CreditAmount 8. In table 2 If "CreditAmount" doesn't already have the opposite mathematical sign as "InvoiceAmount", mathematically invert it. 9. In table 2 change the "CreditAmount" field name to "Amount" 10. Append table 2 into table 1 11. Add an autonumber field "EntryNumber" and make it the PK. For your creti card charge details, you'll either "squeeze" them into the fields in this table (description etc.) or put them into a seperate linked "CreditChargeDetails" table. Well, that might make a mess out of things, but I think it will give you a solid foundation for doing everything that you describe. Sincerely, Fred "the bp Guy" wrote: My post didn't go through so will try again. I have kinda got hung up on invoice statement is a better choice of words the a/r ledger would be a great solution if I would be able to get data from the two tables that I now get the info for what I am doing. The query that I use now is [tblinvoice](Invoicedate,customer,invoice#,Invoiceamount,credi tamount) and [tblcreditcard](customer,creditcarddate,Gross,fee,net) the problem I run into in this query that the invoicedates and credit card dates are not equal. If I could make a table from this query to get a/r ledger I could see where I could list this in a statement. The relationships of the two tables seems to be a problem as it is now. Hopefully I am not adding more confusion to this . "Fred" wrote: Your second post provided additional insight, but did not provide the "structure of data" type info that would be needed to solidly spec a solution. So I'm taking a guess here. If I may be direct in a crucial area, your second posts seems to continue the confusion between invoicing and statements. If this is a double check on another system, then the invoicing ACT might be occurring in that system. In that case it gets simpler. Then just set up the described A/R ledger. Then you should be able to do any or all of that in reports. For example, if your group by the customer ID, and run a subtotal of all of their items, that subtotal will be the net amount owed. You could put a page break with the grouping in which cashe you'll print one page (+) per customer. Maybe / hopefully this helps a little Sincerley, Fred |
Thread Tools | |
Display Modes | |
|
|