I have two sum values
=DSum("TOTALJOB_COST","Table2","[Job#] = '" & [Job#] & "'")
and
=DSum("QUOTATION_COST","Table1","[Job#] = '" & [Job#] & "'")
What is SQL to Aggregate both of these values?
I have two sum values
=DSum("TOTALJOB_COST","Table2","[Job#] = '" & [Job#] & "'")
and
=DSum("QUOTATION_COST","Table1","[Job#] = '" & [Job#] & "'")
What is SQL to Aggregate both of these values?
How about:
=DSum("TOTALJOB_COST","Table2","[Job#] = '" & [Job#] & "'") + DSum("QUOTATION_COST","Table1","[Job#] = '" & [Job#] & "'")
@pbaldy
Thank you! That works!
@pbaldy
One final question. What would be the expression to get % Gross Margin, if:
Gross Margin is:
=DSum("TotalQuote","Table1","[Job#] = '" & [Job#] & "'")-DSum("TOTALJOB_COST","Table2","[Job#] = '" & [Job#] & "'")-DSum("QUOTATION_COST","Table1","[Job#] = '" & [Job#] & "'")
and, Total Cost is:
=DSum("TOTALJOB_COST","Table2","[Job#] = '" & [Job#] & "'")+DSum("QUOTATION_COST","Table1","[Job#] = '" & [Job#] & "'")
I am not familiar with the division process...
Not sure where you're stuck. The operator for division is /, so
=(DSum(...) - DSum(...)) / (DSum(...) + DSum(...))
Note the parentheses to force the addition/subtraction before the division.
@pbaldy
Great, that works. Thank you. I need to start learning SQL...
No problemo!
&pbaldy
One other question. I am trying to string together the "Business Name" and "Job#" into an expression.
My String has a syntax error, and I don't know what it is. Can you provide the correct expression?
BUSINESS NAME: ("BUSINESS NAME","Table1","[Job#] = '" & [Job#] & "'") & " " & ("Job#","Table1","[JoB#] = '" & [Job#] & "'")
Are those meant to be DLookup() formulas? If so, you forgot the DLookup part. Also, if that's in a query, you'd normally get the info by joining the tables, not a DLookup().
@pbaldy
Yes, this in a query but I am stumped by the duplicate name Business Name and that the Job# can be associated with many Business Names. So, I wanted to create a SQL expression and that is where the syntax error comes in. Not sure about DLookup. I'm just starting to poke around into SQL...
Well, if the table (table1) is joined in the query, simply:
BUSINESS NAME: [BUSINESS NAME] & " " & [Job#]
What you have is like the syntax of a domain aggregate function without the actual function name. It would look like:
BUSINESS NAME: DLookup("BUSINESS NAME","Table1","[Job#] = '" & [Job#] & "'") & " " & DLookup("Job#","Table1","[JoB#] = '" & [Job#] & "'")
But again, it is unusual and inefficient to use DLookup() in a query. You'd normally join table1 to whatever other table(s) is involved and get the values the first way.
Sorry, should have thought of that.
BUSINESSNAME: table1.[BUSINESS NAME] & " " & table1.[Job#]
By the way, the spaces and symbols in your names are more trouble than they're worth in the long run.
@pbaldy,
That works great now! thank you!