SQL question regarding multiple tables and columns 1

[cory1]

I have a report to show dbo_client.client_name, dbo.project.project_name, dbo_time.description, and dbo_project_hours.Hours. To the right of the periods are the tables and to the left is the column that I need displayed. I am having a tough time with this join. Can anyone help me out? Thanks in advance,
Cory

 

[genomon]

Actually to the RIGHT of the period is the column name.
Please post you code with the JOIN. Also, what exactly is the problem? "dbo_" suggests you are using linked SQL server tables...

I have great faith in fools; self-confidence my friends call it.
-Poe

 

[cory1]

I should add that dbo_client and dbo project both contain client_id. dbo_time and dbo_project both contain project_id, dbo_project_hours contains project_id.

 

[cory1]

SELECT dbo_client.client_name, dbo_project.project_name, dbo_time.description, Sum(CLng((CDbl([service_end])-CDbl([service_start]))*96)/4) AS Hours
FROM dbo_client, dbo_project, dbo_time, dbo_project_hours
WHERE dbo_employee.employee_id = '9';

That is what I have so far. the junk in the middle just totals the hours. This is working fine. This is in Access '97. I can't tell you if the tables are linked SQL server tables.

 

[Golom]

Don't see how it can be "working fine".

The table dbo_employee in the WHERE clause does not exist in the FROM clause.

Further, you are doing cross-joins which will result in

C * P * T * H records where
[tt]
C --> # of records in dbo_client
P --> # of records in dbo_project
T --> # of records in dbo_time
H --> # of records in dbo_project_hours
[/tt]
The tables are probably in SQL Server because dbo_ is a common table prefix in SQL Server.

 

[cory1]

You're right, I left out the dbo_employee, it is in the code, just omitted from here.

That is where I am having trouble is coding the join. I had the tables joined like this and kept getting an error.

INNER JOIN dbo_time ON dbo_time.project_id = dbo_project_hours.Hours

I am a bit of a noob with SQL. Thanks for the help.

 

[Golom]

Access is picky about parentheses. The usual form is

From [COLOR=red]((([/color]A 
     INNER JOIN B ON A.Fld = B.Fld[COLOR=red])[/color]
     INNER JOIN C ON C.Fld = B.Fld[COLOR=red])[/color]
     INNER JOIN D ON D.Fld = C.Fld[COLOR=red])[/color]
     INNER JOIN E ON E.Fld = D.Fld

where all the parens in red are required.

 

[lespaul]

check out the SQL Join link for additional information, you were creating a cartesian join. Let's say you have 10,000 records in each of your tables (not many in the whole scheme of things)......you have 5 tables....the result set you are creating before any of the filters are applied is:

10000*10000*10000*10000*10000 = 100,000,000,000,000,000,000 records!!!

you really want to learn about joins and the proper way to write them to minimize the result set you have to search through!!

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
Understanding SQL Joi