Create 2nd form using totals from 1st form

[TKI]

I have a main form called an invoice form where a user enters in information
into Access regarding an invoice such as Invoice #, Invoice date, Vendor,
etc. There is also a subform embedded in the Invoice form that shows lawyer
details such as hours, rates, and projects and calculates a total amount per
lawyer.

My problem is I need another form which groups by invoice # the total fee
amount per project, NOT per lawyer - and allows the user on this form to code
the fees to a specific department and account so that the invoice can get
paid. Does anyone have any idea how to do this? I created a query that
grouped the projects together and totaled the fees by project. but then when
i create a subform based on the query, it does not allow me to do any editing
in the subform.

 

[John W. Vinson]

I have a main form called an invoice form where a user enters in information
into Access regarding an invoice such as Invoice #, Invoice date, Vendor,
etc. There is also a subform embedded in the Invoice form that shows lawyer
details such as hours, rates, and projects and calculates a total amount per
lawyer.

My problem is I need another form which groups by invoice # the total fee
amount per project, NOT per lawyer - and allows the user on this form to code
the fees to a specific department and account so that the invoice can get
paid. Does anyone have any idea how to do this? I created a query that
grouped the projects together and totaled the fees by project. but then when
i create a subform based on the query, it does not allow me to do any editing
in the subform.

Well... of course not. If the subform is displaying a value which is the sum
of eleven records, and you edit some field in that "sum", which of the eleven
records should Access edit!?

No Totals query is EVER updateable (even some which logically ought to be).

Your data should be stored in tables (it sounds like you may still have the
mindset that you're putting data into a Form; you're not, the form is just a
tool to let you edit tables). Calculations based on the data would be done
using Totals queries, or controls in the footer of a form with =Sum([field]),
or other techniques - but totals should not be stored in tables.

 

[Tia]

Okay sorry - trust me, I know I'm no Access expert. I was hoping for some
sort of work around answer. I understand that a form is just another way to
edit a table. I tried to use a make table query that stored data by project
totals and then created a form based off of this table, but that wasn't
updateable either. Why is that?

I have a main form called an invoice form where a user enters in information
into Access regarding an invoice such as Invoice #, Invoice date, Vendor,
etc. There is also a subform embedded in the Invoice form that shows lawyer
details such as hours, rates, and projects and calculates a total amount per
lawyer.

My problem is I need another form which groups by invoice # the total fee
amount per project, NOT per lawyer - and allows the user on this form to code
the fees to a specific department and account so that the invoice can get
paid. Does anyone have any idea how to do this? I created a query that
grouped the projects together and totaled the fees by project. but then when
i create a subform based on the query, it does not allow me to do any editing
in the subform.

Well... of course not. If the subform is displaying a value which is the sum
of eleven records, and you edit some field in that "sum", which of the eleven
records should Access edit!?

No Totals query is EVER updateable (even some which logically ought to be).

Your data should be stored in tables (it sounds like you may still have the
mindset that you're putting data into a Form; you're not, the form is just a
tool to let you edit tables). Calculations based on the data would be done
using Totals queries, or controls in the footer of a form with =Sum([field]),
or other techniques - but totals should not be stored in tables.

 

[John W. Vinson]

Okay sorry - trust me, I know I'm no Access expert. I was hoping for some
sort of work around answer. I understand that a form is just another way to
edit a table. I tried to use a make table query that stored data by project
totals and then created a form based off of this table, but that wasn't
updateable either. Why is that?

Well, a make-table query is probably not the right answer either. That would
waste space by storing data redundantly. Don't confuse data STORAGE with data
EDITING with data CALCULATIONS - they are three separate (although
interrelated) problems.

Just what is it that you want to update? Do you want to sum up 1, 2, 3 and 4
and see 10, and then be able to replace that 10 by 12 (so that the sum that
you see will be WRONG)? Or do you want to update data in individual records
and see the sum someplace else?

Please explain the real-life situation; the name of the table and its fields;
and just what it is that you want to see, and what that you want to update.