Right Joins

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have the following query which I have used before with the same joins, but
it is not populating the validation question like I need it to do. Can
someone take a quick look and try to explain to me what I may be doing wrong.

SELECT tblAMUWork.AMUWrkID, tblAMUWork.DateRcd, tblAMUWork.IncomingKeyedBy,
tblAMUWork.WorkOfDate, tblAMUWork.AmtRcd, tblAMUWork.TypeOfWork,
tblAMUWork.MISNumber, tblAMUWork.RptName, tblAMUWork.RepAssgnd,
tblAMUWork.CompletedDate, tblAMUWork.CompletedAmt, tblAMUWork.CompletedAmt1,
tblAMUWork.CompletedDate1, tblAMUWork.CompletedAmt2,
tblAMUWork.CompletedDate2, tblAMUWork.CompletedAmt3,
tblAMUWork.CompletedDate3, tblAMUWork.WorkNotCompleted,
tblAMUWork.TotalAmtCompleted, tblAMUWork.EODKeyedBy, tblAMUWork.EODKeyedBy1,
tblAMUWork.EODKeyedBy2, tblAMUWork.EODKeyedBy3, tblAMUWork.ExceptionDate,
tblAMUWork.RsnForChng, tblAMUWork.WhatChgd, tblAMUWork.CngMdBy,
tblAMUWork.VolReferred, tblAMUWork.DATEVLDTD, tblAMUWork.NUMBEROFACCTSVLDTD,
tblAMUWork.VLDTNCOMPBY, tblAMURptsRecd.VALQuestions, tblAMUWork.Acct1,
tblAMUWork.Answer1, tblAMUWork.Acct2, tblAMUWork.Answer2, tblAMUWork.Acct3,
tblAMUWork.Answer3, tblAMUWork.Acct4, tblAMUWork.Answer4, tblAMUWork.Acct5,
tblAMUWork.Answer5, tblAMUWork.[Audited By], tblAMUWork.FindingstoInvest,
tblAMUWork.DateofData, tblAMUWork.VolumesMatched,
tblAMUWork.InvestRsltsFinalDocumd, tblAMUWork.BthHdrMtchsComet,
tblAMUWork.MISMtchsBatch, tblAMUWork.MISMtchsComet, tblAMUWork.Discrepancies,
tblAMUWork.BatchNum, tblAMUWork.CometNum, tblAMUWork.MISDNWNum,
tblAMUWork.MISRvwsNum, tblAMUWork.Comments
FROM tblAMUWork RIGHT JOIN tblAMURptsRecd ON tblAMUWork.RptName =
tblAMURptsRecd.AMUReportName
WHERE (((tblAMUWork.DateRcd)=[Enter the date that the work was received])
AND ((tblAMUWork.MISNumber)=[Enter the MIS number ( located to the left of
the report name)]));
 
1) I don't think you mean to use a right join.

After all, why bother using a right join if you're going to use the
table that will have null values in your WHERE clause?

Essentially what you're doing is this:
table1:
Field1, Field2
1, hello
2, goodbye
3, hooray

table2:
Field1
1
2
3
4
5

Query1:
SELECT table2.Field1, table1.field1, table1.field2
FROM table1 RIGHT JOIN table2
ON table1.Field1 = table2.Field1

will return:
1, 1, hello
2, 2, goodbye
3, 3, hooray
4, null, null
5, null, null

So if I used table A's info in my WHERE clause I would get this:
Query2:
SELECT table2.Field1, table1.field1, table1.field2
FROM table1 RIGHT JOIN table2
ON table1.Field1 = table2.Field1
WHERE table1.field2 is not null

returns:
1, 1, hello
2, 2, goodbye
3, 3, hooray

I might as well have used an inner join and avoided the where clauses.

So "probably" either your WHERE clauses point at the wrong table or
your join should be a LEFT JOIN

Cheers,
Jason Lepack

I have the following query which I have used before with the same joins, but
it is not populating the validation question like I need it to do. Can
someone take a quick look and try to explain to me what I may be doing wrong.

SELECT tblAMUWork.AMUWrkID, tblAMUWork.DateRcd, tblAMUWork.IncomingKeyedBy,
tblAMUWork.WorkOfDate, tblAMUWork.AmtRcd, tblAMUWork.TypeOfWork,
tblAMUWork.MISNumber, tblAMUWork.RptName, tblAMUWork.RepAssgnd,
tblAMUWork.CompletedDate, tblAMUWork.CompletedAmt, tblAMUWork.CompletedAmt1,
tblAMUWork.CompletedDate1, tblAMUWork.CompletedAmt2,
tblAMUWork.CompletedDate2, tblAMUWork.CompletedAmt3,
tblAMUWork.CompletedDate3, tblAMUWork.WorkNotCompleted,
tblAMUWork.TotalAmtCompleted, tblAMUWork.EODKeyedBy, tblAMUWork.EODKeyedBy1,
tblAMUWork.EODKeyedBy2, tblAMUWork.EODKeyedBy3, tblAMUWork.ExceptionDate,
tblAMUWork.RsnForChng, tblAMUWork.WhatChgd, tblAMUWork.CngMdBy,
tblAMUWork.VolReferred, tblAMUWork.DATEVLDTD, tblAMUWork.NUMBEROFACCTSVLDTD,
tblAMUWork.VLDTNCOMPBY, tblAMURptsRecd.VALQuestions, tblAMUWork.Acct1,
tblAMUWork.Answer1, tblAMUWork.Acct2, tblAMUWork.Answer2, tblAMUWork.Acct3,
tblAMUWork.Answer3, tblAMUWork.Acct4, tblAMUWork.Answer4, tblAMUWork.Acct5,
tblAMUWork.Answer5, tblAMUWork.[Audited By], tblAMUWork.FindingstoInvest,
tblAMUWork.DateofData, tblAMUWork.VolumesMatched,
tblAMUWork.InvestRsltsFinalDocumd, tblAMUWork.BthHdrMtchsComet,
tblAMUWork.MISMtchsBatch, tblAMUWork.MISMtchsComet, tblAMUWork.Discrepancies,
tblAMUWork.BatchNum, tblAMUWork.CometNum, tblAMUWork.MISDNWNum,
tblAMUWork.MISRvwsNum, tblAMUWork.Comments
FROM tblAMUWork RIGHT JOIN tblAMURptsRecd ON tblAMUWork.RptName =
tblAMURptsRecd.AMUReportName
WHERE (((tblAMUWork.DateRcd)=[Enter the date that the work was received])
AND ((tblAMUWork.MISNumber)=[Enter the MIS number ( located to the left of
the report name)]));
Click to expand...
 
I'm not quite sure where you were going. I don't know much about access but
I have tried changing it to a left join and it still is not pulling the
question into the record. Is there something else that I can try?

Jason Lepack said:
1) I don't think you mean to use a right join.

After all, why bother using a right join if you're going to use the
table that will have null values in your WHERE clause?

Essentially what you're doing is this:
table1:
Field1, Field2
1, hello
2, goodbye
3, hooray

table2:
Field1
1
2
3
4
5

Query1:
SELECT table2.Field1, table1.field1, table1.field2
FROM table1 RIGHT JOIN table2
ON table1.Field1 = table2.Field1

will return:
1, 1, hello
2, 2, goodbye
3, 3, hooray
4, null, null
5, null, null

So if I used table A's info in my WHERE clause I would get this:
Query2:
SELECT table2.Field1, table1.field1, table1.field2
FROM table1 RIGHT JOIN table2
ON table1.Field1 = table2.Field1
WHERE table1.field2 is not null

returns:
1, 1, hello
2, 2, goodbye
3, 3, hooray

I might as well have used an inner join and avoided the where clauses.

So "probably" either your WHERE clauses point at the wrong table or
your join should be a LEFT JOIN

Cheers,
Jason Lepack

I have the following query which I have used before with the same joins, but
it is not populating the validation question like I need it to do. Can
someone take a quick look and try to explain to me what I may be doing wrong.

SELECT tblAMUWork.AMUWrkID, tblAMUWork.DateRcd, tblAMUWork.IncomingKeyedBy,
tblAMUWork.WorkOfDate, tblAMUWork.AmtRcd, tblAMUWork.TypeOfWork,
tblAMUWork.MISNumber, tblAMUWork.RptName, tblAMUWork.RepAssgnd,
tblAMUWork.CompletedDate, tblAMUWork.CompletedAmt, tblAMUWork.CompletedAmt1,
tblAMUWork.CompletedDate1, tblAMUWork.CompletedAmt2,
tblAMUWork.CompletedDate2, tblAMUWork.CompletedAmt3,
tblAMUWork.CompletedDate3, tblAMUWork.WorkNotCompleted,
tblAMUWork.TotalAmtCompleted, tblAMUWork.EODKeyedBy, tblAMUWork.EODKeyedBy1,
tblAMUWork.EODKeyedBy2, tblAMUWork.EODKeyedBy3, tblAMUWork.ExceptionDate,
tblAMUWork.RsnForChng, tblAMUWork.WhatChgd, tblAMUWork.CngMdBy,
tblAMUWork.VolReferred, tblAMUWork.DATEVLDTD, tblAMUWork.NUMBEROFACCTSVLDTD,
tblAMUWork.VLDTNCOMPBY, tblAMURptsRecd.VALQuestions, tblAMUWork.Acct1,
tblAMUWork.Answer1, tblAMUWork.Acct2, tblAMUWork.Answer2, tblAMUWork.Acct3,
tblAMUWork.Answer3, tblAMUWork.Acct4, tblAMUWork.Answer4, tblAMUWork.Acct5,
tblAMUWork.Answer5, tblAMUWork.[Audited By], tblAMUWork.FindingstoInvest,
tblAMUWork.DateofData, tblAMUWork.VolumesMatched,
tblAMUWork.InvestRsltsFinalDocumd, tblAMUWork.BthHdrMtchsComet,
tblAMUWork.MISMtchsBatch, tblAMUWork.MISMtchsComet, tblAMUWork.Discrepancies,
tblAMUWork.BatchNum, tblAMUWork.CometNum, tblAMUWork.MISDNWNum,
tblAMUWork.MISRvwsNum, tblAMUWork.Comments
FROM tblAMUWork RIGHT JOIN tblAMURptsRecd ON tblAMUWork.RptName =
tblAMURptsRecd.AMUReportName
WHERE (((tblAMUWork.DateRcd)=[Enter the date that the work was received])
AND ((tblAMUWork.MISNumber)=[Enter the MIS number ( located to the left of
the report name)]));
Click to expand...
Click to expand...
 
Tell me exactly what you are trying to get with this query and how
tblAMUWork relates to tblAMURptsRecd.

The logic that I read in the query is this:
1) Filter all records in tblAMUWork so as only to return those records
that have a given DateRcd and MISNumber.
2) Return the selected fields from tblAMUWork and tblAMURptsRecd where
RptName = AMURptName

Is that what you're trying to get out of this?

Cheers,
Jason Lepack
 
What I want the report to show me is this. When the records are filtered and
I am given the records based on the date and the MIS number, I wanted to have
the Validation Questions populate based on the report name that is coming up.

So if the record chosen is for a fraud app report, I want to have the
question that is related to that report automatically populate.

I hope this helps. Thank you again for all your help.
 
Back
Top