SQL Join....

[ItIsHardToProgram]

Could any one point me out the mistake in the fallowing SQL...

SELECT ProjetBaseDon.*, ProjetBaseDon.[Valeur des honoraires]-((jourFtemps.TotalH)*(Emp.[taux horraire])) AS [Valeur des honnoraires], JourFtemps.TotalH AS honnoraire, JourFtemps.No_Ftemps
FROM Emp, Ftemps, (Contacts INNER JOIN (ProjetBaseDon INNER JOIN Projet_a_contact ON ProjetBaseDon.[# Projet]=Projet_a_contact.no_projet) ON Contacts.no_contact=Projet_a_contact.no_client) INNER JOIN JourFtemps ON Projet_a_contact.no_projet=JourFtemps.No_projet2
WHERE (((JourFtemps.TotalH)>0));





I know it is in my join, since the Query sends back many times the same project # with sometimes the same values.........

I can't quite figure out how to have only 1 row / project.

I tried distinct....

 

[jebry]

Hi!

You have two tables listed that aren't joined at all. Access will automatically do a cartesian join with them. Say Emp has ten records and Ftemps has 12 and your join returns 20 records, the final query will have 10x12x20 or 2400 records.

Just add joins for those tables, or remove them completely.

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[ItIsHardToProgram]

By removing the tables, will the link between them stay?

...

 

[ItIsHardToProgram]

Nevermind, Ill try to sorth out my SQL, I don't quite like the way it is written.

 

[lespaul]

Here's an article that explains what the different types of joins are and how they work, Understanding SQL Joins

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[jebry]

Hi!

Removing the tables will have no effect outside of the query. So, if the are joined by a relationship, removing the tables will have no effect on that.

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[ItIsHardToProgram]

Thanks for helping I actually have a prety good tutorial, and to do what I want I can't really use joins, instead of using joins I have a better idea, but I can't seem to find the function I need, its probably quite simple:

What I need the function to do is to Sum up the total of all the rows with the same number. Fast example:

row # | hours spent
1 12
1 1
1 13
2 24
2 24
2 1


I would want it to show like this:

Row # | hours spent
1 26
2 49


Thanks in advance

 

[ItIsHardToProgram]

Sorry for not changing threads hence it is no more a join but more a select problem.

 

[jebry]

Hi again!

Select [row#], Sum([hours spent]) As TotalHours From YourTable Group By [row#]

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[ItIsHardToProgram]

Sorry I found my own answer.... GROUP BY

SELECT Column, sum(other column)
FROM which ever
GROUP BY column;