• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 311
  • Last Modified:

Dsum Question

When using Dsum, it is necessary to include the same conditions in the query qryLCIssued_Sum_Import-CSM_2?   It seems as though I do.  I ask this because within the qry I already have the condition of [Actual Status]<>"GEC" but Dsum seems to ignore this condtion within the qry which means I have to reference the same condition in Dsum?

=Nz(DSum("[SumOfApproved Amount In USD Equivalent]","qryLCIssued_Sum_Import-CSM_2","[Guarantor Description] Like '*" & Left([txtBankName2],6) & "*'"),0) And [Actual Status]<>"GEC"


oh and btw the condition syntax is not correct at least I dont think it is because I get a #name and have a question out here: http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_27422785.html
0
pdvsa
Asked:
pdvsa
  • 3
  • 2
1 Solution
 
Rey Obrero (Capricorn1)Commented:
try this


=Nz(DSum("[SumOfApproved Amount In USD Equivalent]","qryLCIssued_Sum_Import-CSM_2","[Guarantor Description] Like '*" & Left([txtBankName2],6) & "*'" And [Actual Status]<>'GEC'"),0)
0
 
Dale FyeCommented:
No, it is not necessary to include conditions that are expressed in the underlying query within the DSUM() domain function call.

Can you post the SQL for "qryLCIssued_Sum_Import-CSM_2"

0
 
pdvsaAuthor Commented:
fyed:  it is posted.  It is long.  
SELECT [Import-CSM].[Reference Number], [Import-CSM].[Approved Amount In USD Equivalent], Switch([Activity Code] Like "DH*","Onshore Houston",[Activity Code] Like "LA*","Onshore Claremont",[Activity Code] Like "GS*","Subsea",[Activity Code] Like "ES*","Engineering Offshore",[Activity Code] Like "KT*","KTI",[Activity Code] Like "MS*","Overhead Charges",[Activity Code] Like "TFP*","Offshore Fixed Platform",[Activity Code] Like "*TOF*","Offshore Finland",[Activity Code] Like "MX*","Onshore Mexico",[Activity Code] Like "DI*","What is DI???",True,"") AS MyBusUnit, Switch([Nature] Like "PB*","Performance Bond",[Nature] Like "APB*","Advance Payment GT",[Nature] Like "PC*","Parent Company GT - TUSA",[Nature] Like "Misc*","Miscellaneous",[Nature] Like "Surety*","Surety Bond",[Nature] Like "BB*","Bid Bond",[Guarantor Description] Like "*S.A.*","Head Office PCG",True,"") AS LCType, Count(Switch([Nature] Like "PB*","Performance Bond",[Nature] Like "APB*","Advance Payment GT",[Nature] Like "PC*","Parent Company GT",[Nature] Like "Misc*","Miscellaneous",[Nature] Like "Surety*","Surety Bond",[Nature] Like "BB*","Bid  Bond",True,"")) AS LCTypeCount, [Import-CSM].[Guarantor Description], [Import-CSM].[Actual Status], tblBanks.GuarantorCode

FROM tblBankFacilityAmts INNER JOIN ([Import-CSM] INNER JOIN tblBanks ON [Import-CSM].[Guarantor Code] = tblBanks.GuarantorCode) ON tblBankFacilityAmts.BankName_Facility = tblBanks.BankID

GROUP BY [Import-CSM].[Reference Number], [Import-CSM].[Approved Amount In USD Equivalent], Switch([Activity Code] Like "DH*","Onshore Houston",[Activity Code] Like "LA*","Onshore Claremont",[Activity Code] Like "GS*","Subsea",[Activity Code] Like "ES*","Engineering Offshore",[Activity Code] Like "KT*","KTI",[Activity Code] Like "MS*","Overhead Charges",[Activity Code] Like "TFP*","Offshore Fixed Platform",[Activity Code] Like "*TOF*","Offshore Finland",[Activity Code] Like "MX*","Onshore Mexico",[Activity Code] Like "DI*","What is DI???",True,""), Switch([Nature] Like "PB*","Performance Bond",[Nature] Like "APB*","Advance Payment GT",[Nature] Like "PC*","Parent Company GT - TUSA",[Nature] Like "Misc*","Miscellaneous",[Nature] Like "Surety*","Surety Bond",[Nature] Like "BB*","Bid Bond",[Guarantor Description] Like "*S.A.*","Head Office PCG",True,""), [Import-CSM].[Guarantor Description], [Import-CSM].[Actual Status], tblBanks.GuarantorCode

HAVING ((([Import-CSM].[Actual Status])<>"GEC"));

Open in new window

0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
pdvsaAuthor Commented:
SELECT [Import-CSM].[Reference Number], [Import-CSM].[Approved Amount In USD Equivalent], Switch([Activity Code] Like "DH*","Onshore Houston",[Activity Code] Like "LA*","Onshore Claremont",[Activity Code] Like "GS*","Subsea",[Activity Code] Like "ES*","Engineering Offshore",[Activity Code] Like "KT*","KTI",[Activity Code] Like "MS*","Overhead Charges",[Activity Code] Like "TFP*","Offshore Fixed Platform",[Activity Code] Like "*TOF*","Offshore Finland",[Activity Code] Like "MX*","Onshore Mexico",[Activity Code] Like "DI*","What is DI???",True,"") AS MyBusUnit, Switch([Nature] Like "PB*","Performance Bond",[Nature] Like "APB*","Advance Payment GT",[Nature] Like "PC*","Parent Company GT - TUSA",[Nature] Like "Misc*","Miscellaneous",[Nature] Like "Surety*","Surety Bond",[Nature] Like "BB*","Bid Bond",[Guarantor Description] Like "*S.A.*","Head Office PCG",True,"") AS LCType, Count(Switch([Nature] Like "PB*","Performance Bond",[Nature] Like "APB*","Advance Payment GT",[Nature] Like "PC*","Parent Company GT",[Nature] Like "Misc*","Miscellaneous",[Nature] Like "Surety*","Surety Bond",[Nature] Like "BB*","Bid  Bond",True,"")) AS LCTypeCount, [Import-CSM].[Guarantor Description], [Import-CSM].[Actual Status], tblBanks.GuarantorCode
FROM tblBankFacilityAmts INNER JOIN ([Import-CSM] INNER JOIN tblBanks ON [Import-CSM].[Guarantor Code] = tblBanks.GuarantorCode) ON tblBankFacilityAmts.BankName_Facility = tblBanks.BankID

GROUP BY [Import-CSM].[Reference Number], [Import-CSM].[Approved Amount In USD Equivalent], Switch([Activity Code] Like "DH*","Onshore Houston",[Activity Code] Like "LA*","Onshore Claremont",[Activity Code] Like "GS*","Subsea",[Activity Code] Like "ES*","Engineering Offshore",[Activity Code] Like "KT*","KTI",[Activity Code] Like "MS*","Overhead Charges",[Activity Code] Like "TFP*","Offshore Fixed Platform",[Activity Code] Like "*TOF*","Offshore Finland",[Activity Code] Like "MX*","Onshore Mexico",[Activity Code] Like "DI*","What is DI???",True,""), Switch([Nature] Like "PB*","Performance Bond",[Nature] Like "APB*","Advance Payment GT",[Nature] Like "PC*","Parent Company GT - TUSA",[Nature] Like "Misc*","Miscellaneous",[Nature] Like "Surety*","Surety Bond",[Nature] Like "BB*","Bid Bond",[Guarantor Description] Like "*S.A.*","Head Office PCG",True,""), [Import-CSM].[Guarantor Description], [Import-CSM].[Actual Status], tblBanks.GuarantorCode

HAVING ((([Import-CSM].[Actual Status])<>"GEC"));
0
 
Dale FyeCommented:
As I indicated above, you should not need to include the [Actual Status] <> "GEC" in the criteria string of your DSUM domain function call.

Although I frequently use the SWITCH function, doing so in this case could cause proplems when you add another Activity Code in the future.  If I were you, I would consider creating a new table (tbl_ActCodeLike) that contains two fields (LikeThis, UseThis) with values of:

LikeThis   UseThis
DH           Onshore Houston
LA            Onshore Claremont
GS            Subsea
ES            Engineer Offshore
KT            KTI

Then join that table to your [Import-CSM] table using a non-equi join.  This can only be done through the SQL view, but you can start out in the query design view by creating an INNER JOIN between the [Activity Code] field and the [LikeThis] field.  Then add the [UseThis] field to the query grid.  Because this is an INNER JOIN, if you ran the query immediately, it would return no records.

But if you then switch to the SQL view, you can change the join so that it looks like:

INNER JOIN [Import-CSM].[Activity Code] LIKE [tbl_ActCodeLike].[LikeThis] & "*"

0
 
pdvsaAuthor Commented:
fyed:  appreciate the insight.  thanks for the tip too.  I do have a table in the db now with those business units.  I am using a dl of data from their other db and trying to make sense of it by the use of that Switch.  It is an awful db and needs to be redone.  I am using Access instead.  
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now