Issue with sum returning double the amount

[Adrian Tache]

Hi all, I'm having a serious issue with a sum returning double the amount it should.

I'm including the code below maybe you guys can see the problem:

SELECT clients.clientreadableid, Sum(projects.[total hours]) AS Hours
 
 
FROM clients, projects, timesheet
 
 
WHERE projects.clientreadableid=clients.clientid
And projects.projectid=timesheet.projectreadableid 
 
And clients.clientid=forms![hours per client]!combo1 
 
And Month([sheetdate])=forms![hours per client]!combo3 
And Year([sheetdate])=forms![hours per client]!text5
 
And InStr([typereadableid],"Abonament")>0 
 
 
GROUP BY clients.clientreadableid;

[Mr Key]

U may use a crosstab-Query to sum instead.

[Adrian Tache]

thanks for the reply, I'll try to do that but from what I'm seeing on the web it's somewhat complicated.

from what I've read on the web it's because of some strange join error meaning it basically just finds them twice

[Rabbit]

What is the relationship between projects, clients, and timesheet? One-to-many? Many-to-many? One-to-one?

[Adrian Tache]

Hi Rabbit, thanks for the reply.

They are all many to many.

[Rabbit]

There's the problem then. If one project has multiple timesheets, the project hours gets duplicated.

Can you show us some sample data? Choose one client, choose one project, and show us the relevant client data, project data, and all the timesheet data. Just the fields that are used in this query, we don't need to see any of the other fields.

[Adrian Tache]

don't exactly have the file here but let me give you a quick breakdown of fields.

clients table: clientreadableid is client name; clientid is autonumber. (ex: clientreadableid="Microsoft" clientid=80)
projects table: projectid is autonumber, [total hours] is number, typereadableid is project type, also autonumber FK for another table (ex: projectid = 10, total hours=2.5, typereadableid="Abonament")
timesheet table: [sheet date] is the date at which the timesheet is completed (ex: 10/01/2011)

that's all the fields that are used for this query and I find it weird that I'm getting this error, I've been checking and double-checking all day and nothing showed up as weird.

furthermore, i've been getting even weirder results.

for example with two timesheets of value 1 and 1.5 i get
a sum of 5 but if i add another value of 1 the sum jumps to 11.5.

I'll try to do the sum on timesheets rather than project hours, see if that helps. But that'll have to wait until morning.

[Rabbit]

Do you mean you get 10.5?

(1 project hour + 1.5 project hours) times 2 timesheets = 5 hours total.

(1 project hour + 1.5 project hours + 1 project hour) times 3 timesheets = 10.5 hours total.

You'll need to either return just one timesheet per project, or use a subquery to find the projects you need.

[Adrian Tache]

yes, I'm sorry, 10.5 is right, i remembered it incorrectly. odd thing is that only the ones under category "abonament get multiplied", even in a normal query (without instr).

so for 3.5 abon i get 10.5 hours
but for 3.5 abon and 1 dd i get 11.5 hours

quite odd.

wow, i actually solved it. by counting the hours in the timesheet rather than the hours in the project it now returns the proper value.

thanks again for all the help and helping me figure this out!

[Rabbit]

No problem, good luck.