In Over My Head 3

[CluelessRink]

Being a "kind of" Access user and thinking it would be easy I volunteered to make a database to track tips that servers receive in our restaurant.

I have a table set up with the following fields:
totalsales, chargedsales, chargedtips, totaltips, tipoutscash, tipoutscharge. (all are set at number/currency)

What I would like is a "box" that would keep track of each running sum.

I do believe that once that is in place I can make a query for a report to get totals based upon a time frame.

Is it as simple as making a text box that will pull the data from the appropriate field?

 

[scriverb]

On a form with individual textboxes you can use the DSum function as the control source for each textbox:

=DSum("totalsales", "[i]yourtablename[/i]")

This control on a form will provide you with the sum of all of the records in the table for the field "totalsales". This can be done for each textbox referencing each field in the table.

Hope this is what you were looking for.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[CluelessRink]

I'll give it a shot and post back here to let you know, but it sounds like it is what I want.

I'll probably do another post when I go to make reports to get week, month, quarter and year to date totals.

Thanks!

 

[CluelessRink]

Great! Works wonderfully. Thanks for the help.

 

[CluelessRink]

One more question on the same topic...

I did just what you said
=DSum("totalsales", "tips") and that give me a running totall for everyone.

How can I do it on a form for just a certain employee?

The table field name is emname.

Would it be something along the lines of
=DSum("totalsales", "tips:emname")

 

[scriverb]

The DSum function has three arguments. The third argument is the criteria argument where you can specifiy an expression to filter the records selected for the Sum process.

=DSum("totalsales", "tips", "[emname] = '" & FORMS![[i]formname[/i]]![[i]controlname[/i]] & "'")

I am assuming in the above code the [emname] is a field name and that it is a text/string type field. For this reason I have surrounded the value of the form control with single-quotes to match the data type. If the field is a numeric type then the single quotes are not necessary.

Post back if you have more questions.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[CluelessRink]

Ok, the way I understand your reply is that
formname should be the name of my form, in this case "tips" and controlname should be a filter or maybe a query.

So I'd make it look like this....

=DSum("totalsales", "tips", "[emname] = '" & FORMS![tips]![qry total tips by employee] & "'")

And by the way emname is a text field.


Hey! This TGML stuff is pretty neat!

 

[scriverb]

No, not quite. But, close. [emname] is the name of the field in the table [tips]. Now you make an expression to filter the records from [tips] by the expression [emname] = the text control with the name value on your form. Actually, an ID field rather than a name is better here because there could be more than one name being the same.

Does this make sense? actually you could use the Me![controlname] rather than the long format as initially displayed:

=DSum("totalsales", "tips", "[emname] = '" & Me![[i]namecontrol[/i]] & "'")

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[CluelessRink]

I saw you use the ME! in another post and it makes a bit more sense to me.

What confuses me is controlname or namecontrol, what is that?

Is that the expression you talked about? Like if I want all of Bob Smith's tips, do I put [Bob Smith] in that box?

 

[scriverb]

Let me give you a better explanation. The Me![controlname] and FORMS![formname]![controlname] mean and return the same value if referred to in a form or its code. But in a query outside of the form the Me! doesn't mean anything so you must use the long full reference back to the appropriate form and control.

But, since we are using this function within your form we want to select records from a particular table that match the record that is visible on the form. Now you didn't provide that detail of information so I made an assumption from your posting that [emname] was field in your table as well as a control on your form. What we are looking for here in building the 3rd argument of the DSum function is an expression that selects the records you want to be added together. If there is an ID field/control we could use that. Or, like I said before the name field may work but there are drawbacks as Dave Smith and Dave Smith two completely different individuals may have records that both would match the criteria.

So, looking at your form can you give me the name of a control and its corresponding field in table [tips] that we can setup a criteria expression to select the right records to be "DSummed".

Clear as mud right???

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[CluelessRink]

Ok, I *think* I understand.

Employee Bob Smith is #2 (in the table)

So the control is 2 and the corresponding field is Bob Smith

=DSum("totalsales", "tips", "[emname] = '" & FORMS![tips]![2] & "'")

 

[scriverb]

I think you are getting closer. What is the name of the control on your form that relates to the fiele [emname]? This is the control name that we want to make a comparison in this function to select the correct records. Is the name of the control 2? Or is it more like [name]?

Do this. What is the true identifier of the record on the form? Is there a unique ID field. Something that is only unique to this record. Please post back with the name of the form control. What is the corresponding field in the table? It is these two names that we will use to make up the expression.

Waiting your response.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[CluelessRink]

Ahh the name on the form (which is a text box) is emsalessum
and the field on the table is emname (text)

 

[scriverb]

The textbox [emsalessum] is where you are going to put this function call, correct? We need to identify the sales person. The record on the form is who??? Who's ID or Name is on the form. What is the name of that control and the corresponding field in the [tips] table?

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[CluelessRink]

Correct.

"We need to identify the sales person. The record on the form is who???" On the form it is a combo box called [employee name]and the name/control in the tips table is called employee which is assigned an autonumber in another field.

 

[scriverb]

Try this:

=DSum("totalsales", "tips", "[employee] = '" & FORMS![tips]![employee name] & "'")

Now remember that there is a downside to using the name as the identifier if there are two people with the same name but actually different people.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[CluelessRink]

Ok, I understand that, what if I make a field in the table and call it employeeid which would be a unique number. Would that serve the same purpose?

 

[CluelessRink]

I tried it and get #ERROR, would it be because we have "totalsales" in the figure?

 

[scriverb]

Please post back the code that you have in the control. [totalsales] is a legitimate field in the table [tips] isn't it? If so it is okay as is.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[scriverb]

CluelessRink: I didn't see you first posting. Yes, normally there is a unique field that identifies the records. If you have more than one record in the table with this unique ID such as in a sales table then the index property has to be set to Duplicates allowed. Why don't you type in the field of your table and their field types and what is your primary index. If there are a lot of fields just give me the important ones that we are trying to work with here.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]