Should be simple.....

[CEH]

OK, I have a strange problem... This doesn't seem to work..... It did... then I made some changes.... Can someone clue me in on what I am doing wrong? A query, simply summing some values in fields... no biggy...... BUT...
The field
TotalHours: ([SundayHours]+[MondayHours]+[TuesdayHours]+[WednesdayHours]+[ThursdayHours]+[FridayHours]+[SaturdayHours])

So why isn't this working?
Can I use a simple VBA statement to sum these fields?

 

[maxxximaniac]

type sum before the (), make sure they are all the same data type

 

[CEH]

no....

Nope. didnt work

 

[CEH]

Answer.....

Well, in case anyone else ever has this problem, I'll post what I found out was wrong. The fields I was trying to sum were all set to number, which has a default value access sets to "0"...... I didn't want to see zeros in the fields so I highlighted the zeros in the table and deleted them. Wrong move I guess... I am guessing (and someone correct me if I am wrong) but Access apparently doesn't add or sum on null fields. After I changed them back to zeros as defaults the query worked.
I'm sure there probably is a way to write this query, SQL or VBA to deal with null fields. If someone cares to post it I'll be happy to use it Thanks

 

[CEH]

How..........

OK...... Tell me how this should be if it was correctly normalized. One table for days, one for hours?
Thanks

 

[CEH]

well............

OK...I think......... Not really.... I do tend to think more in "spreadsheet mentality" (Accountant in me) But I'm still not following..... I know it is hard for you too without knowing the DB.... So I'll try to explain the reason I set it up this way... Info is to be entered on a form for a new job. There are fields for the seven days. These fields are filled in by how many hours each day to be worked. So if there are no hours to be worked they need to see a zero in this field.
I was thinking of normalization of more .... Not repeating fields in other tables...breaking down to the simplest table.... (As you can tell I am still pretty new to access) Any suggestions would help. I've tried to find some examples of something close to this.... but no luck yet......
Thanks

 

[WayneRyan]

CEH,

If you set up tables:

tblBad
========
Employee
SundayHours
MondayHours
TuesDayHours
WednesdayHours
ThursdayHours
FridayHours
SaturdayHours

tblGood
=======
Employee
WorkDate
Hours

If you need hours worked for an employee:

Select SundayHours   + MondayHours + TuesDayHours + WednesdayHours +
       ThursdayHours + FridayHours + SaturdayHours
From tblBad
Where Employee = 'Smith'

Select Sum(Hours)
From   tblGood
Where Employee = 'Smith'

The second is obviously more brief. But there are some things that
you can't do with tblBad. Has there ever been a day where 'Jones'
worked more hours than 'Smith'?

Select A.DayOfWeek TheDay, A.Hours Jones, B.Hours Smith
From   tblGood A Left Join tblGood B On
       A.WorkDate = B.WorkDate
Where  A.Employee = 'Jones' And
       B.Employee = 'Smith' And
       A.Hours > B.Hours

You just can't do things like that with a "spreadsheet type" table.

Just some thoughts,
Wayne

 

[CEH]

Thanks

Thanks for the example

 

[CEH]

Yes........ But

Wayne,
I see your point to the "GoodTable" If hours and employees were what I was after this would be fine. As I said before... It's sometimes hard to find the answers here when the people trying to help have not seen the DB or the purpose of the form. And of course my explanation isnt too hot
But..... I think I am going to go with unbound text boxes on this form. The sole purpose of this form is to figure a partial invoice. Not to figure hours or hours applying to employees. On the form the input of hours (the number doesn't matter) will only be saying that the job will be worked on that day... be it Monday...Tuesday... etc... The reason for using days instead of maybe just a checkbox for each day worked is to determine that partial billing.
Let me put it another way.... Say I know my monthly billing. If Monday,Wednesday, and Friday are "checked" (with a checkbox or hours) then I know it is a 3 day a week job. Therefore I can figure a daily charge. After this has been established, Say the beginning date is 06/15/2005.... I need to figure how many Mondays, Wednesdays and Fridays are left in June and figure the partial invoice accordingly. The whole point of this form is to print out a one time report.
I have one now that is getting close...... using something I found on the MS Office page... =DateDiff("ww",[StartDate],[EndDate],[DayOfWeek])-Int([DayOfWeek]=Weekday([StartDate]))

It's getting close....But still lacking......

 

[Mile-O]

Seriously, reconsider. You would add this stuff to a subform when you have information.

Database normalisation is the best practice for database design for a reason. People don't need to see your database to know it is wrong; you've described it as not normalised and that requires too much effort; presently and ongoing...

 

[CEH]

ok....what would you advise

OK... I'd like to do it right. Since this form can stand alone, not needing to be related to anything else in the other DB, I put it in one of its own... I am struggling with this form... probably for the exact reasons you say. But you can see what I am trying to do by looking at the form... and its rather lengthy "DateDiff" statements As you see the figures work using all 7 days... but I want to be able to pick what days to calculate the invoice price on. So if you have a minute, take a look. Any advise would be welcome.
Thanks

 

[Mile-O]

I couldn't make head nor tail of your form but...

What we're talking about is doing something like what you can see in the attachment?

 

[CEH]

Well.........

Your form and table are fine... If I were doing a DB for employees and hours that is the way I would set it up. But I'm not.
On my form, you insert the Start Date... it returns the ending date. You pick a day from the dropdown and it returns how many time that day occurs within your dates.
The monthly field is simply a monthly amount, it returns a daily amount.
The list of days below that simply returns how many times each day occurs within the "Start" to "End" dates. The final calculation just figures all days between the date frame at the daily rate.
The other text boxes "hours" do not do anything at the moment. When I get it figured out it should work like this..... If there is ANYTHING (except zeros) in the hours boxes it will include that DAY in the calculation (amount of times the day occurs NOT hours)... ie ... figure in the Tuesday box... figure in the Thursday box... It will pull how many times Tuesday and Thursday occur within the dates given and calculate the partial invoice.
Fairly simple... So I thought.
Employee names and hours really mean nothing to this form, nor will they to anything else in this DB. That information is entered into another DB... software program, that this DB cannot be tied to.
When I finally get it done I will post it so you can see what I was trying to do. I think it will probably end up being a VBA form.
Thanks