Have various cells that need to be summed up into a text box

[ssanfu]

You would have to store the date and the time for "StartTime" and "EndTime", using the format function to display only the time.

[ssanfu]

Yes, you cut out too much.

Don't know if this will work, since I edited your mdb using A2010.

I changed the query SQL and added a control to the form.

Relink this FE to the BE and try it out.

[bradp1979]

It does work for each individual, but the total hours is displaying 112829.083333333. When I changed the format of the cell to "short time," it displayed 2:00. This is being displayed whether I have a job on the screen or not.

It seems to be totaling up all the hours for the entire column instead of just the hours worked for the specific event..

[bradp1979]

So this is what I'm looking at now:

[ssanfu]

When I changed the format of the cell to "short time," it displayed 2:00

Uh, this is totaling hours worked. Why change the format to "short time"?

So the Total Hours text box is totaling ALL of the hours entered, not just the total hours that are displayed in the subform

I changed the name of the wkHrs control from "Text41" To "txtWkHrs". Then I changed the control source for the "TOTAL HRS" control in main form to "=Sum(Me!fsubRelEventEmployee.Form!txtWkhrs)". It's referencing the specific control on a specific sub form.
Looks like you should have 17 total hours..?

I'm pretty much guessing because the BE had too much removed.

[bradp1979]

Hmm...now I'm getting #Error? in the total employee hours worked box. What might I be able to change to get it to register a number?

What might I be able to add back into the BE to make it more functional for you?

[ssanfu]

What might I be able to add back into the BE to make it more functional for you?

The tables that the queries are based on.
I can't even put in test (fake) data to test the queries because the required tables are not there.

[bradp1979]

Ok, try this.

[ssanfu]

Abend!!
Still missing tables.
I tried to open frmEvent; qryEvent won't execute because of missing tables. Can't even open the subform to look at the data.

[bradp1979]

Does it tell you which tables are missing?

[bradp1979]

Ok, I left the majority of the tables in there. They will likely be missing the data you might need, but since the table itself hasn't been deleted, you can just input fake info if necessary.

I only deleted the tables that I couldn't see any relationship with the form I'm dealing with, and then only to bring the size down to under 2mb.

[bradp1979]

Ok, I deleted a ton of information in each table, and removed all sensitive information (I think), but left all tables. When I try to open it and input data, it works.

When you open the FE and click "Special Events," click on staffing, hit save, then edit again, and you can input data.

I've done the changes on the FE that I discussed in the other post. If you do any changes to queries and what not, please do them in the new FE that I'm attaching here.


Thanks!!

Hope this works!CPV DB v.1.7 BE.zipNew 3.05.zip

[bradp1979]

I just realized that I might be going about this all wrong. I am trying to get a total of cells, but this isn't Excel.

At the top of frmEvent, is an EventID. About halfway down frmEvent is a subform, fsubRelEventEmployee. Within this subform, are cells that are totaling up hours for the specific event, which is found on tblRelEventEmployee in the column WkHrs. The first column for tblRelEventEmployee is EventID. There are 16000 EventIDs to choose from. What I want the totals box to do is to give me the sum of all the numbers in the column WkHrs that have the same EventID as is found at the top of the page.

Does that simplify things, or make them more difficult?

If it simplifies, what might be the expression in the totals box?

[bradp1979]

This thread seems to be dead, but I'm going to try one more time before posting a new one. I've been toying with DSum, but having no luck there either. Since the cells that I want to have totaled are on a subform, but the numbers are specific to the event ID, I tried "=DSum("fsubRelEventEmployee","txtTotalWkHrs",[EventID]), but am still getting "#Error" in the box. I tried this in both the footer on the main form, and on the footer for the sub form.

[ssanfu]

I finally got the two dBs linked. I added the total hours to the subform "fsubRelEventEmployee" footer.

Why is there a "WkHrs" column in the query for the form "frmEvent" AND in the query for the subform "fsubRelEventEmployee"? as far as I can tell, the "WkHrs" calculation should only be in the query for the subform "fsubRelEventEmployee".


I do have some concerns about the dB.
1) You really should fix the field names. No spaces, punctuation or special characters.
2) There are reserved words as field names. Some that I saw are: "Date", "From", "To", "Index", "Field", "Row", "Error", "Rate".
3) Your dB is not normalized. The design looks like a spreadsheet. "#Jackets", "#Vests", "#Raingear", "#BowTies" are examples of field names that are really data. There are many tables that should be split into related tables.

Having said that, I'm not meaning to burn you. It's your dB - you get to design it the way you want. It just I saw things that bother me.