[AccessD] Basic MS Access query question

[Keith Williamson]

Thanks Gary and Paul...I’ll give this a re-thought.  I appreciate the feedback!

Keith Williamson
Sent from my iPhone

> On Feb 16, 2021, at 6:27 PM, Paul Wolstenholme <Paul.W at industrialcontrol.co.nz> wrote:
> 
> 
> [THIS EMAIL IS FROM AN EXTERNAL SENDER]
> 
> 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
> https://urldefense.proofpoint.com/v2/url?u=http-3A__www.IndustrialControl.co.nz&d=DwICAg&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=cCa049KXg2Due8I6xkXzoVrfvG83jjeYLf7LB9M_3qY&m=0cHwZkZIEZa1zbSYQvnTIDwjavn5sWwC7YsJReaC294&s=Yockihj3tm8j8T68YI_pDbdfkj1kc7ho8u-xAGqFxv4&e=
> 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://urldefense.proofpoint.com/v2/url?u=https-3A__databaseadvisors.com_mailman_listinfo_accessd&d=DwICAg&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=cCa049KXg2Due8I6xkXzoVrfvG83jjeYLf7LB9M_3qY&m=0cHwZkZIEZa1zbSYQvnTIDwjavn5sWwC7YsJReaC294&s=VV6m7UbvC6a-GtzhHmJPEOFcFKw61wvrypuxC5id6Ek&e=
>> Website: https://urldefense.proofpoint.com/v2/url?u=http-3A__www.databaseadvisors.com&d=DwICAg&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=cCa049KXg2Due8I6xkXzoVrfvG83jjeYLf7LB9M_3qY&m=0cHwZkZIEZa1zbSYQvnTIDwjavn5sWwC7YsJReaC294&s=gTXSO4iAjVCsnc2BsU4xElj6qnMOYYWbZ712F_hPnG8&e=
>> 
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://urldefense.proofpoint.com/v2/url?u=https-3A__databaseadvisors.com_mailman_listinfo_accessd&d=DwICAg&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=cCa049KXg2Due8I6xkXzoVrfvG83jjeYLf7LB9M_3qY&m=0cHwZkZIEZa1zbSYQvnTIDwjavn5sWwC7YsJReaC294&s=VV6m7UbvC6a-GtzhHmJPEOFcFKw61wvrypuxC5id6Ek&e=
> Website: https://urldefense.proofpoint.com/v2/url?u=http-3A__www.databaseadvisors.com&d=DwICAg&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=cCa049KXg2Due8I6xkXzoVrfvG83jjeYLf7LB9M_3qY&m=0cHwZkZIEZa1zbSYQvnTIDwjavn5sWwC7YsJReaC294&s=gTXSO4iAjVCsnc2BsU4xElj6qnMOYYWbZ712F_hPnG8&e=