Solved Query missing record

[sunnytaru]

Hello All,

I have 2 single Query and one combined query. Query 1 record are displayed when i run it and so are Query 2 records displayed.

When I combine both (query 1 an 2) Query 3 for the report, one record of query 1 does not appear, how ever it does appear in Query 1.

SQL QRY 1

[B]SELECT[/B] BARDirectInvoicePaymentTBL.ProjectID, APLTBL.ProjectTitle, ProjectStatusTBL.ProjectStatus, MemberTBL.MemberName, DocumentTypeTBL.DocumentType, BARDirectInvoicePaymentTBL.FundingAmount, BARJPPJPAPaymentOptionTBL.PaymentOptionType, BARDetailPaymntScheduleTBL.DetailPymntSchdType, BARDirectInvoicePaymentTBL.TotalMilestones, BARDirectInvoicePaymentTBL.COGInvoicedSchDate, BARDirectInvoicePaymentTBL.COGInvoiceNum, BARDirectInvoicePaymentTBL.COGInvIssdDate, BARDirectInvoicePaymentTBL.COGInvAmt, BARDirectInvoicePaymentTBL.Notes, BARActionOnTBL.ActionOn, BARDirectInvoicePaymentTBL.LastUpdate, BARDirectInvoicePaymentTBL.FollowUP
[B]FROM[/B] ProjectStatusTBL INNER JOIN (BARActionOnTBL INNER JOIN (((APLTBL INNER JOIN ((BARDirectInvoicePaymentTBL INNER JOIN MemberTBL ON BARDirectInvoicePaymentTBL.MemberID = MemberTBL.MemberID) INNER JOIN DocumentTypeTBL ON BARDirectInvoicePaymentTBL.DocumentTypeID = DocumentTypeTBL.DocumentTypeID) ON APLTBL.ProjectID = BARDirectInvoicePaymentTBL.ProjectID) INNER JOIN BARJPPJPAPaymentOptionTBL ON BARDirectInvoicePaymentTBL.PymentOptionID = BARJPPJPAPaymentOptionTBL.PymentOptionID) INNER JOIN BARDetailPaymntScheduleTBL ON BARDirectInvoicePaymentTBL.DetailPymntSchID = BARDetailPaymntScheduleTBL.DetailPymntSchID) ON BARActionOnTBL.ActionOnID = BARDirectInvoicePaymentTBL.ActionOnID) ON ProjectStatusTBL.PStatusID = APLTBL.PStatusID
[B]WHERE[/B] (((BARDirectInvoicePaymentTBL.FollowUP)=True));

SQL Query 2

[B]SELECT[/B] BARPOIssuedTBL.ProjectID, APLListOfAllProjectsQRY.ProjectTitle, ProjectStatusTBL.ProjectStatus, MemberTBL.MemberID, MemberTBL.MemberName, DocumentTypeTBL.DocumentType, BARPaymentScheduleTypeTBL.PaymentScheduleType, BARPOIssuedTBL.TotalMilestones, BARPOStatusTBL.POStatus, BARPOIssuedTBL.MemberPONum, BARPOIssuedTBL.MemberPORecvdDate, BARPOIssuedTBL.MemPOAmt, BARPOIssuedTBL.Notes, BARActionOnTBL.ActionOn, BARPOIssuedTBL.LastUpdate
[B]FROM[/B] ProjectStatusTBL INNER JOIN (BARPaymentScheduleTypeTBL INNER JOIN ((APLListOfAllProjectsQRY INNER JOIN ((BARActionOnTBL INNER JOIN (BARPOStatusTBL INNER JOIN BARPOIssuedTBL ON BARPOStatusTBL.POStatusID = BARPOIssuedTBL.POStatusID) ON BARActionOnTBL.ActionOnID = BARPOIssuedTBL.ActionOnID) INNER JOIN DocumentTypeTBL ON BARPOIssuedTBL.DocumentTypeID = DocumentTypeTBL.DocumentTypeID) ON APLListOfAllProjectsQRY.ProjectID = BARPOIssuedTBL.ProjectID) INNER JOIN MemberTBL ON BARPOIssuedTBL.MemberID = MemberTBL.MemberID) ON BARPaymentScheduleTypeTBL.PaymentScheduleTypeID = BARPOIssuedTBL.[PaymentScheduleTypeID]) ON ProjectStatusTBL.PStatusID = APLListOfAllProjectsQRY.PStatusID;

SQL Query 3

[B]SELECT[/B] BARReportDirectInvoiceQRY.ProjectID, BARReportDirectInvoiceQRY.ProjectTitle, BARReportDirectInvoiceQRY.ProjectStatus, BARReportDirectInvoiceQRY.DocumentType, BARReportDirectInvoiceQRY.MemberName, BARReportDirectInvoiceQRY.FundingAmount, BARPOList2QRY.PaymentScheduleType, BARPOList2QRY.TotalMilestones, BARPOList2QRY.POStatus, BARPOList2QRY.MemberPORecvdDate, BARPOList2QRY.MemberPONum, BARPOList2QRY.MemPOAmt, BARReportDirectInvoiceQRY.PaymentOptionType, BARReportDirectInvoiceQRY.COGInvoicedSchDate, BARReportDirectInvoiceQRY.COGInvoiceNum, BARReportDirectInvoiceQRY.COGInvIssdDate, BARReportDirectInvoiceQRY.COGInvAmt, BARPOList2QRY.Notes, BARPOList2QRY.ActionOn, BARPOList2QRY.LastUpdate, BARReportDirectInvoiceQRY.Notes, BARReportDirectInvoiceQRY.ActionOn, BARReportDirectInvoiceQRY.LastUpdate, Month([BARReportDirectInvoiceQRY].[LastUpdate]) AS Expr3, [Forms]![BARReportSelectionFRM]![SelectMonthCombo] AS Expr2, BARPOList2QRY.MemberID, [Forms]![BARReportSelectionFRM].[SelectMemberCombo]
[B]FROM[/B] BARPOList2QRY INNER JOIN BARReportDirectInvoiceQRY ON (BARPOList2QRY.MemberName = BARReportDirectInvoiceQRY.MemberName) AND (BARPOList2QRY.ProjectStatus = BARReportDirectInvoiceQRY.ProjectStatus) AND (BARPOList2QRY.ProjectID = BARReportDirectInvoiceQRY.ProjectID) AND (BARPOList2QRY.DocumentType = BARReportDirectInvoiceQRY.DocumentType)
[B]WHERE[/B] (((Month([BARReportDirectInvoiceQRY].[LastUpdate]))=[Forms]![BARReportSelectionFRM]![SelectMonthCombo]) AND ((BARPOList2QRY.MemberID)=[Forms]![BARReportSelectionFRM].[SelectMemberCombo])) OR ((([Forms]![BARReportSelectionFRM]![SelectMonthCombo]) Is Null) AND (([Forms]![BARReportSelectionFRM].[SelectMemberCombo]) Is Null)) OR (((Month([BARReportDirectInvoiceQRY].[LastUpdate]))=[Forms]![BARReportSelectionFRM]![SelectMonthCombo]) AND ((BARPOList2QRY.MemberID)=[Forms]![BARReportSelectionFRM].[SelectMemberCombo])) OR ((([Forms]![BARReportSelectionFRM]![SelectMonthCombo]) Is Null) AND (([Forms]![BARReportSelectionFRM].[SelectMemberCombo]) Is Null));

Please guide

Thanks
Taruna

 

[theDBguy]

If you want all the records from one table to show up in the result, regardless of whether or not it has a matching record in the other table, then you'll have to use an OUTER JOIN (LEFT or RIGHT).

 

[sunnytaru]

If you want all the records from one table to show up in the result, regardless of whether or not it has a matching record in the other table, then you'll have to use an OUTER JOIN (LEFT or RIGHT).

will that apply to all query ? Thanks I fixed it

 

[plog]

will that apply to all query ? Thanks I fixed it

I'll give a 2 minute lesson about JOINs, but you should really read up on them at w3schools:

W3Schools.com

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com

Imagine a Venn diagram with circle A and circle B. Circle A is red and represents data from table/query A, Circle B is blue and represents data from table/query B. Where they overlap is purple.

INNER JOIN effectively acts as criteria. It will only return results where A and B overlap--just the purple area.

FROM A LEFT JOIN B will show everything from A and any matching data in B. So, all the red area as well as the purple area.

FROM B LEFT JOIN A will show everything from B and any matching data in A. So all the blue area as well as the purple area.

If you think you are missing a record when you do an INNER JOIN then you either need a LEFT JOIN from whichever table/query that record is in. Or you need to revaluate what you want--perhaps that record shouldn't be in the results because it doesn't match with anything from the other datasource.

 

[theDBguy]

will that apply to all query ? Thanks I fixed it

Glad to hear you got it sorted out. Good luck with your project.