Solved Query missing record (1 Viewer)

sunnytaru

Member
Local time
Today, 09:20
Joined
Mar 24, 2022
Messages
41
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

SQL:
[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
SQL:
[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
SQL:
[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

I’m here to help
Staff member
Local time
Today, 06:20
Joined
Oct 29, 2018
Messages
21,358
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

Member
Local time
Today, 09:20
Joined
Mar 24, 2022
Messages
41
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
 
Last edited:

plog

Banishment Pending
Local time
Today, 08:20
Joined
May 11, 2011
Messages
11,613
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:


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.
 

Users who are viewing this thread

Top Bottom