[AccessD] Basic MS Access query question

[Gary Kjos]

You have the criteria on your projections table

AND ((tblProjections.AsOfPeriod)=[Enter Period?]))

So left join or not you will only ever get the matches to that table.

On Tue, Feb 16, 2021 at 5:08 PM Keith Williamson
<KWilliamson at designcollective.com> wrote:
>
> Hi guys....I know this not the most complicated query in the world.....
>
> Until I added the tblProjections table to my over all query, my results were fine (Project information, including Current, MTD, YTD, and JTD billings on all projects.)  I then added a new table in Access to store Projected billings for Mth1, Mth2, Mth3, etc.  I added that table to my overall query with a right join....and a parameter for the period that the stored monthly projected billings pertain to (ie. Period 202102...mth1 would be Feb billings, mth2 would be Mar billings...etc.)  I don't know why...but even though the table was added as a right join....my results are ONLY the two projects (period 202102) that I added projections on my new table. I only added one period, and populated two projects (as test data) to the tblProjections table.  I'm expecting all the projects (with the mtd, ytd, jtd billings, etc.)....and the projections for ONLY to two projects I added projection data for.  Why am I getting only two records in the results?
>
>
> SELECT qryProjectRecords.dbo_Project_1.prjID,
> qryProjectRecords.dbo_Project_1.prjDescription AS [Project Description], qryProjectRecords.prjTotalContractAmount AS [Contract Amount],
> IIf(IsNull([reportprincipal]),[principal in charge],[reportprincipal]) AS [Reporting Principal], qryProjectRecords.eDescription AS Sector,
> qryProjectRecords.ptDescription AS [Project Type],
> qryProjectEarnings.SumOftGLBilledAmount AS [JTD Billed],
> [Contract Amount]-[JTD Billed] AS [Billing Backlog],
> qryBillingTotalYTD.[YTD Billed],
> qryBillingTotalMTD.[MTD Billed],
> tblProjections.Mth1, tblProjections.Mth2, tblProjections.Mth3, tblProjections.Mth4, tblProjections.Mth5, tblProjections.Mth6, tblProjections.Mth7, tblProjections.Mth8, tblProjections.Mth9, tblProjections.Mth10, tblProjections.Mth11, tblProjections.Mth12
> FROM tblProjections RIGHT JOIN (qryBillingTotalYTD RIGHT JOIN (qryBillingTotalMTD RIGHT JOIN (qryProjectEarnings RIGHT JOIN (tblPrReportingPrincipal RIGHT JOIN qryProjectRecords ON tblPrReportingPrincipal.ProjectNumber = qryProjectRecords.dbo_Project_1.prjID) ON qryProjectEarnings.prjKey = qryProjectRecords.dbo_Project_1.prjKey) ON qryBillingTotalMTD.tMainProject = qryProjectRecords.dbo_Project_1.prjKey) ON qryBillingTotalYTD.tMainProject = qryProjectRecords.dbo_Project_1.prjKey) ON tblProjections.ProjNum = qryProjectRecords.dbo_Project_1.prjID
> WHERE (((qryProjectRecords.dbo_Project_1.prjID) Is Not Null) AND ((qryProjectRecords.prjStatus)<=4) AND ((Left([qryProjectRecords].[dbo_Project_1].[prjID],1))<>9) AND ((tblProjections.AsOfPeriod)=[Enter Period?]))
> ORDER BY qryProjectRecords.dbo_Project_1.prjID;
>
> Thanks for anyone that can take a look.
>
> Keith E. Williamson
> Chief Financial Officer
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com



-- 
Gary Kjos
garykjos at gmail.com