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
|
|||
|
|||
Sub Total a Sub Table
I have a simple Invoice System. There is a header table that contains generic
information for an invoice and then there is an item table that contains the line items for the invoice. I have an auto number that can link many items to a single header. Im trying to create a form to enter the Invoices and also the line items and i want to have a total populate to update the total cost of the invoice. This is where i am having trouble. Its not a sub form i am using, rather it appears to be a sub table or a linked table. when i click on the properties it says it is a child and in the form designer under the detail the box says at the top "Table.Invoice_Items". |
#2
|
|||
|
|||
Sub Total a Sub Table
Do not store the invoice total in the header table.
Doing so breaks a fundamental rules of data normalization: never store dependent data. -- 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. "Pringle" wrote in message ... I have a simple Invoice System. There is a header table that contains generic information for an invoice and then there is an item table that contains the line items for the invoice. I have an auto number that can link many items to a single header. Im trying to create a form to enter the Invoices and also the line items and i want to have a total populate to update the total cost of the invoice. This is where i am having trouble. Its not a sub form i am using, rather it appears to be a sub table or a linked table. when i click on the properties it says it is a child and in the form designer under the detail the box says at the top "Table.Invoice_Items". |
#3
|
|||
|
|||
Sub Total a Sub Table
Pringle,
That's a fairly "broad" question for a specific reply. Let me try to lay out the basic Main form/Subform setup. A typical Invoice form does have a Main form... (the ONE Invoice part of the relationship), and a subform... (The MANY Items part of the relationship). That subform can be a "datasheet" form view, but... more often, and in this case, it should be a "continuous" form... related to the Main form by a common key field value. (ex. tblInvoices/InvoiceNo to tblInvoiceItems/InvoiceNo) Using a "continuous" subform allows you to calculate sums in the subform Footer. In your case, the total amount due for items associated to that invoice. (ex. =Sum(LineTotal)) Get your Main, and continuous Subform set up, and working, and from there, we can do add totals -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Pringle" wrote in message ... I have a simple Invoice System. There is a header table that contains generic information for an invoice and then there is an item table that contains the line items for the invoice. I have an auto number that can link many items to a single header. Im trying to create a form to enter the Invoices and also the line items and i want to have a total populate to update the total cost of the invoice. This is where i am having trouble. Its not a sub form i am using, rather it appears to be a sub table or a linked table. when i click on the properties it says it is a child and in the form designer under the detail the box says at the top "Table.Invoice_Items". |
#4
|
|||
|
|||
Sub Total a Sub Table
Thank you for your reply. I changed the subform to a Continuous Form. I tried
to put a subtotal in both the footer of the sub form and the footer of the main form but it dosnt calculate anything, it just leaves blank. "Al Campagna" wrote: Pringle, That's a fairly "broad" question for a specific reply. Let me try to lay out the basic Main form/Subform setup. A typical Invoice form does have a Main form... (the ONE Invoice part of the relationship), and a subform... (The MANY Items part of the relationship). That subform can be a "datasheet" form view, but... more often, and in this case, it should be a "continuous" form... related to the Main form by a common key field value. (ex. tblInvoices/InvoiceNo to tblInvoiceItems/InvoiceNo) Using a "continuous" subform allows you to calculate sums in the subform Footer. In your case, the total amount due for items associated to that invoice. (ex. =Sum(LineTotal)) Get your Main, and continuous Subform set up, and working, and from there, we can do add totals -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Pringle" wrote in message ... I have a simple Invoice System. There is a header table that contains generic information for an invoice and then there is an item table that contains the line items for the invoice. I have an auto number that can link many items to a single header. Im trying to create a form to enter the Invoices and also the line items and i want to have a total populate to update the total cost of the invoice. This is where i am having trouble. Its not a sub form i am using, rather it appears to be a sub table or a linked table. when i click on the properties it says it is a child and in the form designer under the detail the box says at the top "Table.Invoice_Items". |
#5
|
|||
|
|||
Sub Total a Sub Table
I am not trying to store a value in a table, rather i want the form to
calculate and display the total so as you enter the items you can see the total cost of the invoice. "Allen Browne" wrote: Do not store the invoice total in the header table. Doing so breaks a fundamental rules of data normalization: never store dependent data. -- 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. "Pringle" wrote in message ... I have a simple Invoice System. There is a header table that contains generic information for an invoice and then there is an item table that contains the line items for the invoice. I have an auto number that can link many items to a single header. Im trying to create a form to enter the Invoices and also the line items and i want to have a total populate to update the total cost of the invoice. This is where i am having trouble. Its not a sub form i am using, rather it appears to be a sub table or a linked table. when i click on the properties it says it is a child and in the form designer under the detail the box says at the top "Table.Invoice_Items". |
#6
|
|||
|
|||
Sub Total a Sub Table
Place a text box in the Form Footer (not Page Footer) section of your
Continuous Form. Set its Control Source to an expression such as: =Sum([Quantity] * [UnitPrice]) substituting your field names for the names in square brackets. Set the Format property of this text box to Currency (or something numeric.) -- 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. "Pringle" wrote in message ... I am not trying to store a value in a table, rather i want the form to calculate and display the total so as you enter the items you can see the total cost of the invoice. "Allen Browne" wrote: Do not store the invoice total in the header table. Doing so breaks a fundamental rules of data normalization: never store dependent data. -- 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. "Pringle" wrote in message ... I have a simple Invoice System. There is a header table that contains generic information for an invoice and then there is an item table that contains the line items for the invoice. I have an auto number that can link many items to a single header. Im trying to create a form to enter the Invoices and also the line items and i want to have a total populate to update the total cost of the invoice. This is where i am having trouble. Its not a sub form i am using, rather it appears to be a sub table or a linked table. when i click on the properties it says it is a child and in the form designer under the detail the box says at the top "Table.Invoice_Items". |
#7
|
|||
|
|||
Sub Total a Sub Table
Pringle,
Your response doesn't tell us anything... You'll need to briefly describe the records in your subform. Describe the subform control you want to Sum up in the sub footer. Is it a calculated value? If so.. what is the calculation? What are the control names involved in the calculation? What Sum calculation did you place in the subform footer? -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Pringle" wrote in message ... Thank you for your reply. I changed the subform to a Continuous Form. I tried to put a subtotal in both the footer of the sub form and the footer of the main form but it dosnt calculate anything, it just leaves blank. "Al Campagna" wrote: Pringle, That's a fairly "broad" question for a specific reply. Let me try to lay out the basic Main form/Subform setup. A typical Invoice form does have a Main form... (the ONE Invoice part of the relationship), and a subform... (The MANY Items part of the relationship). That subform can be a "datasheet" form view, but... more often, and in this case, it should be a "continuous" form... related to the Main form by a common key field value. (ex. tblInvoices/InvoiceNo to tblInvoiceItems/InvoiceNo) Using a "continuous" subform allows you to calculate sums in the subform Footer. In your case, the total amount due for items associated to that invoice. (ex. =Sum(LineTotal)) Get your Main, and continuous Subform set up, and working, and from there, we can do add totals -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Pringle" wrote in message ... I have a simple Invoice System. There is a header table that contains generic information for an invoice and then there is an item table that contains the line items for the invoice. I have an auto number that can link many items to a single header. Im trying to create a form to enter the Invoices and also the line items and i want to have a total populate to update the total cost of the invoice. This is where i am having trouble. Its not a sub form i am using, rather it appears to be a sub table or a linked table. when i click on the properties it says it is a child and in the form designer under the detail the box says at the top "Table.Invoice_Items". |
#8
|
|||
|
|||
Sub Total a Sub Table
I found out what the issue is. I was right clicking the text box and
selecting expression builder. I needed to go thru the properties using the control source to enter the formula. Thanks for your support on this issue. "Allen Browne" wrote: Place a text box in the Form Footer (not Page Footer) section of your Continuous Form. Set its Control Source to an expression such as: =Sum([Quantity] * [UnitPrice]) substituting your field names for the names in square brackets. Set the Format property of this text box to Currency (or something numeric.) -- 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. "Pringle" wrote in message ... I am not trying to store a value in a table, rather i want the form to calculate and display the total so as you enter the items you can see the total cost of the invoice. "Allen Browne" wrote: Do not store the invoice total in the header table. Doing so breaks a fundamental rules of data normalization: never store dependent data. -- 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. "Pringle" wrote in message ... I have a simple Invoice System. There is a header table that contains generic information for an invoice and then there is an item table that contains the line items for the invoice. I have an auto number that can link many items to a single header. Im trying to create a form to enter the Invoices and also the line items and i want to have a total populate to update the total cost of the invoice. This is where i am having trouble. Its not a sub form i am using, rather it appears to be a sub table or a linked table. when i click on the properties it says it is a child and in the form designer under the detail the box says at the top "Table.Invoice_Items". |
Thread Tools | |
Display Modes | |
|
|