Results 1 to 13 of 13

Thread: Database Design

  1. #1
    Join Date
    Jun 2004
    Posts
    7

    Unanswered: Database Design

    Hi,

    I'm trying to build a database at work. It's been years since I did this, and I wasn't exactly excelent at it even then. I thought it would come back to me quicker than it has been . . .

    I am trying to setup a payroll system. It needs to trakc employees hours each day and the related cost codes to those hours so we can bill the hours to the correct account. There are about 75 employees.

    What I am having real trouble with is in one of the reports they want. They want to generate a report that will list weekly hours for each employee. There needs to be an employee list down the left margin. Along the top there needs to be column headings correspoonding to each day of the specified week. The hours for each day will be listed under the day of the week and beside the appropriate employees name. There will be a total at the end of each row for each employee and at the bottom for total man-hours for that day.

    I can't figure out how to create colum headings with dates. The only thing I can come up with that hepls (with my limited database knowledge) would be to create a new table for each day or week, but that hardly seems like a good idea. There must be a better way for me to set up my tables to make this possible?

    Thanks very much fro any help at all . . . I'm getting soo frusterated!

    James

  2. #2
    Join Date
    Jun 2003
    Location
    USA
    Posts
    1,032
    <<
    Along the top there needs to be column headings correspoonding to each day of the specified week. The hours for each day will be listed under the day of the week and beside the appropriate employees name.
    >>

    Well in your query you'd probably group by employee and also create calculated fields for each day of the week:

    Monday: IIf(Weekday([TheDateField])=1,[TheHoursField],0)

    But of course you'd probably be summing the hours.

    Hopefully at least the above will give you some ideas to run with...
    J. Paul Schmidt, Freelance Web and Database Developer
    www.Bullschmidt.com
    Access Database Sample, Web Database Sample, ASP Design Tips

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Reports are reports, not tables my friend.

    Headings are easily accomplished by placing your column headers in the page header section.

    As far as returning costs according to days of the week, that will depend on your table structure.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  4. #4
    Join Date
    Jun 2004
    Posts
    7
    Quote Originally Posted by Bullschmidt
    Well in your query you'd probably group by employee and also create calculated fields for each day of the week:

    Monday: IIf(Weekday([TheDateField])=1,[TheHoursField],0)

    But of course you'd probably be summing the hours.

    Hopefully at least the above will give you some ideas to run with...
    Thank you very much. That was definately a step in the right direction. My problem now is that if I enter two employees, on different days, I can't total his hours for the week because they are in different records. I need to keep adding to the same employees record I think, adding hours for the current day of the week as I go. Is that possible?

    Thanks for your help so far though!

    James

  5. #5
    Join Date
    Dec 2003
    Posts
    268

    Another thought

    Try making a crosstab query. column heading woudl be the day of the week. Row heading would be year, month, week, employeeid. With this information you should be able to create a report looking exactly the way you want it.

    food for thought: Focus on normalization and tbl structure rather than reports. especially if you are out of practice.

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Sum the field in your section footer.

    Assuming you have columns setup for monday that are calculated with the formula in question, you could then preform a simple "Sum(MondayPayroll)" in the footer.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    If I understand you correctly you are going to have data that looks like:

    EMP1...6/28/04...2.5
    EMP1...6/29/04...5.2
    EMP1...6/30/04...5.6
    EMP2...6/28/04...1.8

    And you want it to look like:

    EMP.......6/28/04.........6/29/04.........6/30/04
    EMP1.....2.5................5.2...............5.6
    EMP2.....1.8

    In order to do this you will probably want to look into a Cross Tab query. You might need a query or two to get the data to look like what is showing at the top, but once you have it then create a query, change it from a Select query to a Crosstab query, then choose the Employee as the Row Header and the Date as the Column Headers and the hours as the Value.

    Keep in mind that with a Crosstab, Access will never know exactly how many columns there will be until it calculates them. So when you base you report on the Crosstab and view the fields, it actually has to run the query to find out the field names (6/28/04, 6/29/04, etc.)

    Crosstabs can be pretty confusing, but very useful at times. Good luck.

  8. #8
    Join Date
    Jun 2004
    Posts
    7
    Quote Originally Posted by mjweyland
    Try making a crosstab query. column heading woudl be the day of the week. Row heading would be year, month, week, employeeid. With this information you should be able to create a report looking exactly the way you want it.

    food for thought: Focus on normalization and tbl structure rather than reports. especially if you are out of practice.
    You sir, are a genius

    Works like a charm. Thank you very, very much for your time . . .

    I'm sure I'll have more questions yet, but the first report is generated and it is exactly what I wanted.

  9. #9
    Join Date
    Jun 2004
    Posts
    7
    Quote Originally Posted by DCKunkle
    If I understand you correctly you are going to have data that looks like:

    EMP1...6/28/04...2.5
    EMP1...6/29/04...5.2
    EMP1...6/30/04...5.6
    EMP2...6/28/04...1.8

    And you want it to look like:

    EMP.......6/28/04.........6/29/04.........6/30/04
    EMP1.....2.5................5.2...............5.6
    EMP2.....1.8

    In order to do this you will probably want to look into a Cross Tab query. You might need a query or two to get the data to look like what is showing at the top, but once you have it then create a query, change it from a Select query to a Crosstab query, then choose the Employee as the Row Header and the Date as the Column Headers and the hours as the Value.

    Keep in mind that with a Crosstab, Access will never know exactly how many columns there will be until it calculates them. So when you base you report on the Crosstab and view the fields, it actually has to run the query to find out the field names (6/28/04, 6/29/04, etc.)

    Crosstabs can be pretty confusing, but very useful at times. Good luck.
    Thanks for the input, that's exactly what I had to do. I have never used cross tab queries before, but I still can't believe how easy a sollution to my problem it was!

  10. #10
    Join Date
    Jun 2004
    Posts
    7
    ok, another problem has come up . . .

    When I go to enter a date in the next month, it duplicates the employeeID. This is a problem seeing that a week can start in one month and end in another. As far as I can see this will continue as long as I leave Month as a row heading. However, if I eliminate it all together I can't differentiate between different months to go back and generate reports from a few months ago...

    Any ideas?

    Thanks again.

  11. #11
    Join Date
    Jun 2004
    Posts
    7
    One other thing, can someone be so kind as to tell me how I would go about requesting start/end date from the user so I can generate the correct weekly reports? Thanks!

  12. #12
    Join Date
    Jun 2004
    Posts
    9

    Well....

    I am really just a newbie, but as for requesting start and end date this is how i would do it.

    I asume the start and end dates are critrea in queries, well instead of putting a constant vaule there put something like
    [Week Begining] for the start of the week
    [Ending] for the end date

    That will cause a input box to pop up when the querry is run, the first will have the caption "Week Begining" and will act ast the critera in whatever collumn you placed the "[week beginging]" The next one will have the caption of "Ending" and will ask for the end date. If you just have one colloumn with dates I think the Critera row in the querry should look like

    "Between [Week Starting] And [Endding]"

    Hope that helps

  13. #13
    Join Date
    Jun 2004
    Posts
    7
    Thanks for the idea . . .

    It doesn't seem to be an option in a cross-tab query though. And I can't create another query to get my date field because I need to use the dates from the same table that has all my hours info and everything, so I get ambiguous relationship (or soemthing) errors.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •