How to access and use subform data/calculations

85 pts.
Tags:
Calculations
Database programming
Form
Microsoft Access
Microsoft Access 2007
Parameters
Reports
Subform
Access 2007 Database; Employee Info; Form:Sick Days Docking/Pay. I created a form with a subform that keeps track of Employee sick days. It transfers totals from the subform onto the main form...Then the main form does a couple calculations on those amounts...But I need to access that information in a report for each employee. Reports can't be made from forms. When I try to get a query to do the calculations, it keeps asking for the parameter value of the calculated fields. I believe the problem is in accessing info from a variable field. I need the system to calculate for each employee and then give me a report. Is that impossible?

Answer Wiki

Thanks. We'll let you know when a new response is added.

You could have the main form do the calculation and store the answer in the underlying table. The report could just display that answer.
Or, the report should be able to do the same caclulation and display the same answer. I would suggest if you do that, you have a function that does the cacluation and both the form and report use that function. That way, you don’t end up with different ways of calculating the result and any changes only need to be done once.
Or, if the report is just display information about the one employee that is currently being displayed on the main form, you could have the report reference that field: =forms!MainForm!CalcField

Also, it shouldn’t be impossible for the query to do the calculation. It is just a matter of getting all the source fields in the query first.

*********************************************************
It is very much possible by creating a transaction table which stores the exact data that you are producing for your subform. This table will contain the data of each employee with its each leave details. In your report you can call data from this table and write query for doing subtotals and totals as you want.

Discuss This Question: 5  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • OBI Girl
    I sincerely appreciate the discussion, but I can't seem to make progress on this issue. The FORM does the calculation alright, but the data isn't carried to the underlying TABLE. The field in the table remains empty. I need to report on all employees, not just the one currently displayed on the main form. I've tried creating a transaction TABLE which stores the exact data as the subform (several absences for several employees), but can't get the data grouped and totaled for each employee. My Absences SUBFORM holds the "many" side of the database relationships. The SUBFORM works to collect all the absences for all employees, gather respective data in individual records, total, and then the main FORM calculates how much salary docking must be done individually. But those calculated fields don't transfer to reports. Queries can't seem to group employee records. Reports group and subtotal, but can't do further complex calcuations.
    85 pointsBadges:
    report
  • Randym
    Without seeing your application, it is hard to say exactly what to do. In your first message, you mentioned that the totals are "transferred" to the main form and then some calculations are done on them. How is that being done? In order for a field to be automaically saved to the underlying table, the field needs to be bound to the table in the control source not the name. But if the field's control source is based on a calculation (ex. Control Source: =field1+field2), it is an unbound field and it will not be saved to the table. You need to copy the contents of the unbound field to the bound field. You can do this with a macro or with VB in the form in the forms BeforeUpdate event. Once the field is updated in the table, your report should be able to use it. Also, you should be able to do calcuations in queries and reports. With reports, you may need the VB again. With queries, it might take a few queries working together and/or maybe even some "work" tables to get the job done. If you still have problems, I'll be glad to help you out by looking at your app.
    1,740 pointsBadges:
    report
  • Jaideep Khanduja
    I think once you have basic data in place and you are able to achieve what you want in the main form, exactly it can be achieved in your report also. The same set of queries you are using in the main form, can be part of your report. Think of creating a view in this case and give it a try.
    9,295 pointsBadges:
    report
  • OBI Girl
    YES! The answer came with building query upon query. Still a few more bugs to work out, but I'm on the right track. Thank you for your input!
    85 pointsBadges:
    report
  • Jaideep Khanduja
    that was great to know that you arrived at some conclusion, may be some query enhancement is still required to get final desired results.
    9,295 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following