[AccessD] Basic MS Access query question

[Paul Wolstenholme]

Keith,

It sounds to me as if, instead of joining to tblProjections, you wanted to
do a right join on a sub-query consisting of tblProjections  WHERE
 ((tblProjections.AsOfPeriod)=[Enter Period?]))

You could write the sub-query as a saved query and alter the main query to
refer to that.
Alternatively, you can put the sub-query in-line by replacing
tblProjections RIGHT JOIN
with something like
(SELECT ... FROM tblProjections WHERE ...) AS q1
and removing the AND ((tblProjections.AsOfPeriod)=[Enter Period?]))
and changing every tblProjections. to q1.

Regards,
Paul Wolstenholme
-- 
Industrial Control Engineering Ltd.
PO Box 33-127,  Wellington Mail Centre
Lower Hutt 5045, New Zealand
Ph: +64 21 150 7222
www.IndustrialControl.co.nz
e-mail: Paul.W at IndustrialControl.co.nz




On Wed, 17 Feb 2021 at 12:09, 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
>