asked on

Access Query

Hello,

I have a query that returns 2752 records. JobNumber may be Null, but that's ok.

-------------------
SELECT dbo_tblCSI.ID, dbo_tblCSI.JobNumber, dbo_tblCSI.CSIIssueType
FROM dbo_tblCSI
GROUP BY dbo_tblCSI.ID, dbo_tblCSI.JobNumber, dbo_tblCSI.CSIIssueType;
--------------------

I have a second query that returns 3944 records. JobNumber will never be Null.

----------------
SELECT dbo_tblRevisions.JobNumber, First(dbo_tblRevisions.Engineer) AS FirstOfEngineer
FROM dbo_tblRevisions
WHERE (((dbo_tblRevisions.DrawingStage)="Ready For Fab Docs"))
GROUP BY dbo_tblRevisions.JobNumber;
------------------

I want a join that will show all 2752 records from the first table, but add the FirstOfEngineer column if the JobNumber matches. The result shold show 2752 records. I've tried it many ways and can't seem to make it come out.

Thanks for your help.
Joel

peter57r

Calling the queries q1 and q2...

Select q1.*, q2.firstofengineer from
q1. left join q2
on nz(q1.Jobnumber, 0) = q2.jobnumber

Genius123

ASKER

Is there a way to do it where it's all in one query and not to make a query that calls 2 queries?

GRayL

How does this work?

SELECT a.ID, a.JobNumber, a.CSIIssueType, First(b.Engineer) AS FirstOfEngineer
FROM dbo_tblCSI a LEFT JOIN dbo_tblRevisions b ON a.JobNumber = b.JobNumber
WHERE (((b.DrawingStage)="Ready For Fab Docs"))
GROUP BY a.ID, aJobNumber, a.CSIIssueType;

Richard Daneke

When you run a query that is made up of two queries, it appears as one query to the operator. Why would you want to insist on a single query to effect this?

Genius123

ASKER

GRayL, that query you gave me an aggregate function error.

peter57r, the query you gave me a syntax error on join operation. Maybe it's that "nz" - I've never seen that before.

Thanks,
Joel

ASKER CERTIFIED SOLUTION

peter57r

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Genius123

ASKER

Thanks, it worked. What is the nz for though?

GRayL

SELECT a.ID, a.JobNumber, a.CSIIssueType, First(b.Engineer) AS FirstOfEngineer
FROM dbo_tblCSI a LEFT JOIN dbo_tblRevisions b ON a.JobNumber = b.JobNumber
WHERE (((b.DrawingStage)="Ready For Fab Docs"))
GROUP BY a.ID, a.JobNumber, a.CSIIssueType;
---------------------^--needed this dot. Does this work?

peter57r

Nz provides a default value for nulls. In this case it converts nulls to 0.

It may work without the nz but I just wanted to be sure.

GRayL

I say you should not have the Nz() function. What if you had an actual Jobnumber of 0 in tblCSI - I would argue that all the numbers in tblCSI that were not in tblRevisions would normally show a null value for FirstOfEngineer? With the Nz() function, they all would join to that 0 JobNumber.

peter57r

You may be right Ray. I just wanted to be sure Access wouldn't object to Null values in a join.
I can't recall ever using a join involving a Nullable field in any DBMS my whole life (and that's a looooong time!!)