If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Right Joins
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)])); |
#2
|
|||
|
|||
Right Joins
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 On Feb 15, 11:01 am, Welthey wrote: 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)])); |
#3
|
|||
|
|||
Right Joins
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" wrote: 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 On Feb 15, 11:01 am, Welthey wrote: 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)])); |
#4
|
|||
|
|||
Right Joins
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 |
#5
|
|||
|
|||
Right Joins
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. "Jason Lepack" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|