Solved Invoice System for access

[Halked]

Good day!

I am working on a few components of my database and have arrived at invoicing. I have created the following tables:

• tblInvoice
• tblInvoiceCost fk = fldInvoiceID
• tblTempInvoiceTotals

Issue:
I may be approaching this incorrectly, so I’m open to suggestions.

In my Invoice table, I have a field called fldInvoiceTotal (Currency). This total is calculated based on the records entered in a subform (shown in the picture “Invoice 2”). The user fills out the invoice details in the subform, and currently they need to press a “Refresh Total” button to update the value stored in fldInvoiceTotal. ("Invoice Total:" as shown in "Invoice 1")

The code in the refresh button does the following

• Checks if there is a tblTempInvoiceTotals + creates it if there isn't.
• Deletes previous records in tblTempInvoiceTotals
• Inserts all records from tblInvoiceCosts into tblTempInvoiceTotals grouped by fldInvoiceID. (SQL query also sums each currency field)
• Update our current record through SQL by setting fldInvoiceTotal to what every is in the tblTempInvoiceTotals where the ID = ID.

Now, the record for the Invoice total is calculated and set. I am uncertain if the cost should have stayed with the invoice, and then the invoice total would be a calculated field. Where should the invoice total live? Additionally, should I be using temp tables like this? I have no issue with other users running this code when they use runtime.

Further, I tried using a Dlookup and calculate it by each record as needed for the invoice, however that runs extremely slow.

I hope these questions are clear and concise. Thank you.

 

[GPGeorge]

There is a basic example of invoicing in the Northwind Developers Edition template. Consult it for ideas on creating invoices.
https://support.microsoft.com/en-us...emplates-e14f25e4-78b6-41de-8278-1afcfc91a9cb

As a general rule, one does not need to store totals as calculated fields in tables. Totals can be calculated in queries for display on reports and forms as needed. Therefore, this whole procedure seems redundant.

Some values, such as the selling price of a product at the point of sale, should be stored because, even if there is a table with current pricing for products, discounts can be applied, inflation raises prices, and so on. This is one of the few times we do redundantly store a value. But the total is based on the "as of" values in the sale, so it does not need to be stored again as a calculation.

 

[Halked]

There is a basic example of invoicing in the Northwind Developers Edition template. Consult it for ideas on creating invoices.

As a general rule, one does not need to store totals as calculated fields in tables. Totals can be calculated in queries for display on reports and forms as needed. Therefore, this whole procedure seems redundant.

Some values, such as the selling price of a product at the point of sale, should be stored because, even if there is a table with current pricing for products, discounts can be applied, inflation raises prices, and so on. This is one of the few times we do redundantly store a value. But the total is based on the "as of" values in the sale, so it does not need to be stored again as a calculation.

Good day, George.

Thank you very much for your insight George. It completely eluded me the idea of a "as of" value.

 

[LarryE]

Could you please paste a screenshot of your database table design in the database relationships window, so we can see what you are attempting to track and how your tables are designed and related. That will give us a better shot at helping you.

 

[Halked]

Could you please attach a screenshot of your database table design in the database relationships window, so we can see what you are attempting to track and how your tables are designed and related. That will give us a better shot at helping you.

Sure, here is a picture of my relationships that I have setup.

 

[LarryE]

What is this database designed for? What are you attempting to keep track of? Is this some sort of expense tracking system?

 

[Halked]

What is this database designed for? What are you attempting to keep track of? Is this some sort of expense tracking system?

Good day, Larry,

The relationship table I sent is supposed to be designed for residential contractors. However, the original post references my invoice system for trades that reside in our residential buildings where we invoice them for each night they stay. For both systems, I use a refresh total to set fldAmount to whatever is in the subform. It is designed to bill organizations that we have rendered services to.

 

[mike60smart]

Sure, here is a picture of my relationships that I have setup.

Edit***

I should have said can you show the Relationships for the other tables you currently have.

 

[LarryE]

Edit***

I should have said can you show the Relationships for the other tables you currently have.

We are going to need a complete design picture, not just a partial one. What you did post is not in relational database format. We don't keep duplicate data in tables, and we don't keep calculated totals in separate tables either. They are calculated at the time of input or calculated on reports. George alluded to that earlier.

In your case, invoice totals should be calculated on invoice reports at the time they are printed and presented to the contractors for payment.

Please paste a complete table design, so we know what tables you have and how they relate to one another.

 

[Halked]

We are going to need a complete design picture, not just a partial one. What you did post is not in relational database format. We don't keep duplicate data in tables, and we don't keep calculated totals in separate tables either. They are calculated at the time of input or calculated on reports. George alluded to that earlier.

In your case, invoice totals should be calculated on invoice reports at the time they are printed and presented to the contractors for payment.

Please paste a complete table design, so we know what tables you have and how they relate to one another.

Good day, Larry,

It seems I have a long mountain to climb with this. I am going to break this down in a way that I can understand. Bare with me.

"...design picture, not just a partial one"

• There is no other tables to show as there are none. Again, I am trying to figure out specifically the totals section which you explained should be calculated on invoice reports at the time they are printed or required.

"What you did post is not in relational database format. We don't keep duplicate data in tables..."

• I get it Larry. I shouldn't be keeping company data in the invoice and rather should have just reference it by my tblCompany (which keeps all my companies and their details) That's a good catch. However, yes, I will be linking it directly to my company data table instead of listing it here. I wasn't done developing it yet as I was more pressed about the totals. (I will reattach a new relationships table. Let me know if this one is improved.)

"we don't keep calculated totals in separate tables either."

• This one is where I am getting lost. My total value in my tblInvoice isn't a calculated field. Can you explain this one more in-depth Do you mean I will never have the total amount stored anywhere, only when it is queried?

 

[mike60smart]

Can you upload a copy of your database?

 

[LarryE]

If I wanted to keep track of job contractor employees that stayed in various rooms in various buildings and then bill them for the time they stayed, I might start with a design like this:

I didn't include all of the fields you need in the Contractor and ContractorEmployee tables, but you can add them easily. You can see how a relational system is designed. Each piece of data is kept in only one place and when it comes time to invoice a contractor or individual contractor employee, you develop a query that brings together all the fields and calculate invoice amounts based upon:

1. Calculate the Total days each contractor employee stayed in each room. If the time of day is relevant, then use the StartTime and EndTime also.
2. Total days the employee stayed x the BuildingRoom DailyRate
3. Plus Total days the employee stayed x Tax DailyRate

Notice no calculation is stored in a table anywhere.

I hope this will help you. I attach the ACCESS file with the tables.

 

[Halked]

If I wanted to keep track of job contractor employees that stayed in various rooms in various buildings and then bill them for the time they stayed, I might start with a design like this:
View attachment 123279
I didn't include all of the fields you need in the Contractor and ContractorEmployee tables, but you can add them easily. You can see how a relational system is designed. Each piece of data is kept in only one place and when it comes time to invoice a contractor or individual contractor employee, you develop a query that brings together all the fields and calculate invoice amounts based upon:

1. Calculate the Total days each contractor employee stayed in each room. If the time of day is relevant, then use the StartTime and EndTime also.
2. Total days the employee stayed x the BuildingRoom DailyRate
3. Plus Total days the employee stayed x Tax DailyRate

Notice no calculation is stored in a table anywhere.

I hope this will help you. I attach the ACCESS file with the tables.

Good answer, Larry.

I can see now that no calculated totals are being stored directly in the tables. So, I am guessing that means if I want to view an invoice total, it should be calculated from the related invoice payment records instead. In essence, the total would be like a display value on the main form for the user’s reference, but it would not be saved as a stored value, only calculated and shown when needed? That is such a weird concept for me to understand but I get it now. I will adjust my tables to accommodate that.

 

[Halked]

Can you upload a copy of your database?

Hey Mike,

I'll upload a copy later on. However, I think Larry has solved my question.

Thank you very much though!

 

[mike60smart]

Hey Mike,

I'll upload a copy later on. However, I think Larry has solved my question.

Thank you very much though!

No problem

 

[Ken Sheridan]

I've attached a couple of little demo files, one which illustrates how to output invoices as PDF documents, and also shows how to handle VAT or some other form of sales/service tax, the other which illustrates how to consolidate multiple orders or parts thereof (analogous to multiple stays in the same or different accommodation in your case) into a single invoice. The latter does not include provision for VAT, but that would be handled as in the former.