dsum

[AlexBen]

Good day! am a newbie with Access so please bear with me... I tried using Dsum but am just getting a blank result... here is the SQL



SELECT tblMain.SowID, tblpiglet.SowIds, tblMain.BornAlive, Abs([WeanNumber]) AS Wean, tblMain.SplitNumber, tblpiglet.PigNum, tblpiglet.Reason, IIf(IsNull([Reason]),[PigNum],[PigNum]*-1) AS Foster, DSum("Foster","tblpiglet","SowIds = '" & [tblMain]![SowID] & "'") AS NetFoster FROM tblMain INNER JOIN tblpiglet ON tblMain.SowID = tblpiglet.SowIds;




Thanks in advance!

[Micron]

Could be one or a few issues there.
- not "Foster" but Foster. [Foster] would be another possibility but I think that is somewhat remote.
- in Access, DSum is a Domain (table or query) function. Since Foster is not in the domain but is an alias for your IIF function, I doubt you can Dsum on it even as Foster or [Foster]. Perhaps you can Sum it instead. However, as Sum I think you'll have to group by at least one field. You might have to use a separate query or function to perform the sum.

Sometimes it's a great help (even necessary) if you copy the db, compact/repair the copy, zip it and upload to a post.
If info is sensitive, perhaps see Randomizing Data for Posted db's

[Welshgasman]

I would expect anything with ID in it to be numeric, so no need for single quotes, they are for strings?

"SowIds = '" & [tblMain]![SowID] & "'")

[AlexBen]

Thanks Micron and Welshgasman for your reply. I ended up making two queries to get the sum.

[NoellaG]

In normal T-SQL you can only use aliases in the ORDER BY clause, because that is executed after the SELECT. Normally a query is parsed and executed in the following order:
first the from,
then the where,
then the GROUP BY,
then the HAVING
then the SELECT part, here the aliases are created
and as last the ORDER BY, so here the aliases already exist

As each part is executed as a whole, the aliases only come to exist after the complete select part is . So it cannot be used in the select itself.

Of course, this is T-SQL and I know Access SQL is different from T-SQL. But maybe it works the same way for this bit.

[June7]

The IIf expression could be within the DSum(), however, a subquery should be more efficient.