Clicky

I have a report in Crystal where I created a SQL query that is:

FROM JobProd jp LEFT OUTER JOIN JobHead jh
ON jh.JobNum = jp.TargetJobNum
LEFT JOIN JobHead jh1
ON jh1.JobNum = jp.JobNum

I'm trying to get this same statement to work in an Access Query but can't seem to get it to work properly..

Does anyone have any ideas on the setup that needs to exist for this to work correctly?

Thanks in advance

asked 12/08/2011 01:48

SeyerIT's gravatar image

SeyerIT ♦♦


6 Answers:
For Access, change your LEFT OUTER JOIN  to  LEFT JOIN
link

answered

mbizup's gravatar image

mbizup

I don't thing there's change in join type of SQL in access

Take a look into the link and expand the links in the reference for SQL samples in Access

http://office.microsoft.com/en-us/access-help/creating-an-outer-join-query-in-access-HA001034555.aspx
link

answered 2011-12-08 at 09:55:01

santhimurthyd's gravatar image

santhimurthyd

Is the problem that it doesn't run or that the data that is returned is incorrect?
link

answered 2011-12-08 at 09:55:12

UnifiedIS's gravatar image

UnifiedIS

I apologize.. I realized that I needed to add more criteria to get it to pull the other.  The layout of the tables was correct, I just needed to add an Or to the criteria under one of my other fields.. Here is a screen shot of the working query;

link

answered 2011-12-08 at 10:02:30

SeyerIT's gravatar image

SeyerIT

The full SQL statement from Crystal was actually;
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
SELECT DISTINCT jp.JobNum AS jpJobNum,
jp.TargetJobNum,
jp.OrderNum,
jh.JobNum jhJobNum,
jh1.JobNum jh1JobNum,
jh1.PartDescription

FROM JobProd jp LEFT OUTER JOIN JobHead jh
ON jh.JobNum = jp.TargetJobNum
LEFT JOIN JobHead jh1
ON jh1.JobNum = jp.JobNum



WHERE (jh.JobNum = '{?JobNum}' 
OR jp.JobNum = '{?JobNum}')


I simply for got to paste the Where portion and since ignored it when I was trying to run the query. I was originally only putting the job number under the jhJobNum field when it also needed to be placed under the jpJobNum field.


Sorry for dragging you all in on this but thanks for the prompt responses! I'm just starting with the large project and I'm sure I will be posting more question to which I can send out some points:)

link

answered 2011-12-08 at 10:35:34

SeyerIT's gravatar image

SeyerIT

Pieced it together myself.

link

answered 2011-12-08 at 10:37:37

SeyerIT's gravatar image

SeyerIT

Your answer
[hide preview]

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Tags:

×60
×3
×17
×132
×7

Asked: 12/08/2011 01:48

Seen: 261 times

Last updated: 12/12/2011 05:18