Incorrect Sums

[Azyrus]

Hello,



I have two tables one for Jobs and one for Workers, in the Jobs table each Job is unique but may have more than one worker or more than one time a worker has been on the Job.

When I create a report I design it so that the workers are shown below each job however when I create a sum for all the jobs the total sum is incorrect.

From what I can see when a job has more than one entry for a worker it adds the total job cost for as many entries there are under that job.

I only want each job to be counted once.

Does anyone have any ideas how to get around this? Thanks very much.

[clean32]

i have had that problem before

what i did was link both tables in a query first, showed the job number from jobs first, then employes under. same as what you are doing now but using.

Sum [jobs].[jobnumbers] and not sum [employees]]job numbers]

i think if you use [employees]]job numbers]. because there is a record for each employee even though you doint see it, this is what it counts

[Azyrus]

Hi Clean,

Thank you for the reply.

Forgive me but I am still learning Access. I created a query with the jobs and workers as the sub query, when the query is created I can see all the jobs, however when there is a job that has been worked on more than once it comes up however many times it was worked on as its own job.

Each entry has its own job cost as well so if a job was worth $200 and it was worked on 3 times it shows up as three separate jobs totaling $600. I have been racking my brains trying to fix this.

Thanks.

[clean32]

Hi Clean,

Thank you for the reply.

Forgive me but I am still learning Access. I created a query with the jobs and workers as the sub query, when the query is created I can see all the jobs, however when there is a job that has been worked on more than once it comes up however many times it was worked on as its own job.

Each entry has its own job cost as well so if a job was worth $200 and it was worked on 3 times it shows up as three separate jobs totaling $600. I have been racking my brains trying to fix this.

Thanks.

you could always count the total in a simple query and a form that opens hidden before you open the report. the report geting the value from the form

[Azyrus]

Hey Clean,

That seemed to work a treat and will certainly fix the problem for now, however when this database gets enough information I will be looking at separating the jobs into weeks in a report.

Once I created this report if I used this method it would show me the total for the query not the week correct?

Any ideas how to work around that?

Thanks so much for your help

edit* I found this post here with the same problem however his link to the solution is no longer working :'(

[clean32]

pleased to be of help

but i am sure there is some one else with a better solution

as for the week, a filter in a querrie would do it

some thing like < 1/1/1900 00:00 and > 7/1/1900 00:00

or put one date in a form and have another box = that date + 7

i have had a lot of problems with dates my self.

i need to make up a schedule with a start date + 1 day or + 1 week or + 3 months etc as options.

[Azyrus]

To anyone searching for a detailed solution
http://www.utteraccess.com/forum/Lots-People-Answers-t1968709.html

*Solved.