Saving an equasion made in a text box to a table

[djoveryde]

Hi, I am attempting to create a weekly repair report for our customers which would save time however my main form has a subform which contains parts inside.
This in turn when I run the report shows the same record numerous times based on how many records it has in the subform.

Obviously this means that the end figure in the report is doubled at times because the labour charge shows up from the main form twice to three times depending on how many parts are in the subform.

I currently use the following in a text box to give me the total charges for hardware from the subform to the main form. =[Parts Subform].[Form]![PartsTotal]

What I want is to get that text box to also send the end figure to an available field in its table so I can use that end figure instead to prevent duplication of records.



I probably make no sense as access coding is fairly new to me.

Thanks

[Robeen]

Can you provide some screenshots showing data from your Tables, what your Form/SubForm looks like, what you want your report to look like . . .

[djoveryde]

I'm thinking if trying it differently but not sure if its even possible. Essentially when the report looks at all the records once it accesses the sub record it then duplicates the labour charge based on how many parts there are (the 'cost' section') messing up the end figure.

P331 is one such duplication. So in essence the parts end total is correct however the labour is not. Is there a way of making the report merge the 'cost' figures to recuce the record to only one instead of 2 seperate. Hopefully fixing the duplicated labour charge.

thanks

[Robeen]

You should not be having the problem that I see in your screenshot.
I believe the reason you ARE having it is because of how you are getting the data into your Report.
Are you using a Query as the data source for the report - or is your report based on a Table?
Follow my directions below - to create a Query and then make the query the Record Source [Property Sheet -> Data Tab -> Record Source] for the Report.

1. Create a Query that returns all the rows just like your report shows.
2. Then - still in Query Design mode - click the 'Totals' button at the top of your Query Design window.
This will add a Totals: row to the Query - and will put 'Group By' for all columns of the Query.
3. Leave the Group By for all fields except the Cost & Labour [QJump??] - for those . . . Change the Group By to Sum for the Cost field - and to Avg for the Labour field.
I don't know what the QJump field is - if it is something you want totalled - change it to Sum as well.
The Group By will not work in the above scenario if -for instance - there are two different Engineers - or two different Date Completed for RepairNo P331.

I hope this helps!! Let me know if you have more questions.

[djoveryde]

Thanks definately seems to be working much better now.

Only issue I have now is everytime i open the query it comes up with 'Query1.Date Completed - Enter Parameter Value' I just hit enter to load it.
It does still show all the dates so not sure why its asking for a value

[Robeen]

It is always a good idea to have no spaces in your field names.
In this case, do you have a field named 'Date Completed'?
The word 'Date' is a reserved word - and Access could see that word and think you're wanting to use a Date function.
Can you change your field name to 'DateCompleted' - or 'Date_Completed'?
Also - do you have something [in the Criteria row for the Query1 -> Date Completed field] . . . that Access could be deciphering as a Parameter?

OR

Do you actually HAVE a 'Date Completed' field in the Table that Query1 is referencing?
If you have a spelling error in a field name in a query and that field name doesn't match a field name in the table - Access will prompt you for a value for that field.

I'm curious to hear why you are getting that.