A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Right Joins



 
 
Thread Tools Display Modes
  #1  
Old February 15th, 2007, 05:01 PM posted to microsoft.public.access.queries
Welthey
external usenet poster
 
Posts: 52
Default 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  
Old February 15th, 2007, 05:44 PM posted to microsoft.public.access.queries
Jason Lepack
external usenet poster
 
Posts: 600
Default 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  
Old February 15th, 2007, 07:50 PM posted to microsoft.public.access.queries
Welthey
external usenet poster
 
Posts: 52
Default 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  
Old February 15th, 2007, 10:02 PM posted to microsoft.public.access.queries
Jason Lepack
external usenet poster
 
Posts: 600
Default 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  
Old February 20th, 2007, 02:48 PM posted to microsoft.public.access.queries
Welthey
external usenet poster
 
Posts: 52
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:14 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.