DSum Question 1

[CluelessRink]

I have a DSum that goes

=DSum("totalsales","tbl_tips"," [transdate] = #" & [transdate] & "#")

And it works wonderfully.

I would like to add another variable to it though, one that will pull the data from another field called dept from the table named tbl_employees.

I've tried

=DSum("totalsales","tbl_tips"," [transdate] = #" & [transdate] & "#","dept","tbl_employees")

but that didn't work.

How would I phrase it?

What I am trying to do is get the total amount of tips by day (transdate) by department (dept).

 

[gol4]

one weakness of the aggreate functions like dsum-dcount-dlookup is it can only pull the value from one table. Could you build a query based on both tables then do your dsum() on the query rather then the tables.

 

[PHV]

Create a saved query that joins tbl_tips and tbl_employees, and uses the aggregate Sum(totalsales) with fields transdate and dept grouped:
SELECT T.transdate, E.dept, Sum(T.totalsales) As SumOfSales
FROM tbl_tips T INNER JOIN tbl_employees E
ON T.employeeID = E.employeeID
GROUP BY T.transdate, E.dept;
Then, change your DSum like this:
=DLookUp("SumOfSales", "NameOfQuery", "transdate=#" & [transdate] & "# AND dept='" & [Name of dept control] & "'")
If dept is defined as numeric, get rid of the single quotes.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[CluelessRink]

gol4, I see what your saying, it *is* a weakness.

PHV, it that a SQL specific query? I'm a bit confused.

 

[PHV]

PHV, it that a SQL specific query?
Sorry, don't understand the question.
I showed you the SQL code of the saved query.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[CluelessRink]

Ok, I understood your post and I put in

=DLookUp("SumOftotalSales","qry_sales_by_dept","transdate=#" & [transdate] & "# AND dept='" & [es] & "'")

The es is the department code I would like to look up.

With it the way I have it right now I get #NAME

 

[gol4]

Hope I don't step out of bounds on this one but in reviewing your previous posts they seem to be about using aggerate functions. Any time I find myself having to use those I begin to look at the design of my database or my forms. Based on what I read in your previous posts it appears you are attempting to track tips for staff. And display it on a form, I suggest you look into trying this in a totals query. Then display it either on a subform or a list. The query that PHV posted is very close to what you would want to make. One query grouping by the appropriate fields can save a lot of dlookups.
Good luck

 

[PHV]

=DLookUp("SumOftotalSales","qry_sales_by_dept","transdate=#" & [transdate] & "# AND dept='" & [es] & "'")
Are all the following true ?
- qry_sales_by_dept is the saved query
- SumOftotalSales is the alias for the Sum() in the select list
- transdate and dept are in the select list and the group by list
- [transdate] and [es] are controls in the current form

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[CluelessRink]

Yes all are true except for [es], it is not a control but the name of a dept (which is a control).

es is the name of the dept that I want the sumoftotalsales for.

 

[PHV]

OK, you may try this:
=DLookUp("SumOftotalSales","qry_sales_by_dept","transdate=#" & [transdate] & "# AND dept='es'")


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[CluelessRink]

Hmm, sure looks like it should work to me.

But now I am just getting an empty box.

 

[PHV]

And this ?
=DLookUp("SumOftotalSales","qry_sales_by_dept","transdate=#" & Format([transdate], "m/d/yyy") & "# AND dept='es'")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[CluelessRink]

Hey gol, your not out of line (at least I don't think you are), but I don't mind the dlookups, especially since I am learning while I am putting this thing together.

I took on the project being a "kind of" access user and didn't really know what I was getting into.

But I sure am learning a lot!

 

[CluelessRink]

PH, same thing...

I've tried manipulating my data, adding new records and then going back and forth.

Same thing, box remains empty.

 

[PHV]

In the AfterUpdate event procedure of [transdate], add this line :
[Name of Total control].ReQuery
In the Current event procedure of the form:
Me.ReCalc

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[CluelessRink]

I also put dept in [ ], still no-go.

 

[PHV]

When you open qry_sales_by_dept from the query window in data view, are the infos you want displayed ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[CluelessRink]

By [Name of Total control] do you mean the main table where my form gets the data?

 

[CluelessRink]

You know, I never though of trying the query, and no, there is no data. So that's where the problem is. Eh?

 

[PHV]

I mean the name of the control with the ControlSource=the DLookUp function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244