• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 323
  • Last Modified:

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
0
Genius123
Asked:
Genius123
  • 4
  • 3
  • 3
  • +1
1 Solution
 
peter57rCommented:
Calling the queries q1 and q2...

Select q1.*, q2.firstofengineer from
q1. left join q2
on nz(q1.Jobnumber, 0) = q2.jobnumber
0
 
Genius123Author Commented:
Is there a way to do it where it's all in one query and not to make a query that calls 2 queries?
0
 
GRayLCommented:
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;
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
Richard DanekeCommented:
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?
0
 
Genius123Author Commented:
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
0
 
peter57rCommented:
There was a stray . in my sql

Select q1.*, q2.firstofengineer from
q1 left join q2
on nz(q1.Jobnumber, 0) = q2.jobnumber
0
 
Genius123Author Commented:
Thanks, it worked.  What is the nz for though?
0
 
GRayLCommented:
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?
0
 
peter57rCommented:
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.
0
 
GRayLCommented:
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.
0
 
peter57rCommented:
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!!)
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now