If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Needing some input on how to take this db to the next level...
I'm trying to create a time sheet database. I have some of the basics worked
out, but would like some input on how to deal with the subform that displays the days in the current pay period. Some how I would like the dropdown to generate payperiods so I don't have to enter every one myself. I'd like the subform to display the 14 days for that pay period. Any input would be appreciated Kelvin here is what I have pretty much so far: http://www.mccsa.com/Temp/TimeAndBilling.zip PS I haven't gone through and fixed tab orders and the like so there is lots of work to polish it up... |
#2
|
|||
|
|||
Needing some input on how to take this db to the next level...
Actually, you probably don't have it "worked out" just yet. If you're
talking Excel spreadsheets then you should post your issue in an Excel newsgroup. My response assumes that you are really trying to get something done in Access. You may get some flak about generating empty records. It begins he Don't generate empty records unless that turns out to be the best way to solve a particular data issue. Of course, I'm young and have a lot to learn but I haven't seen a convincing case yet. In just about any case in which you need to show events for a person over a pay period you can *calculate* in order to display meaningful results. You can do some very intelligent things with child tables ~ tblPay and a subform based on that table. You can establish a default date for each new record that will make a lot of sense in the context of what you are doing. For example you could have the date value default to the next working date higher than the last date entered for that person. Unless you have highly variable work (day) schedules, that proposed date will be the correct date. If it wasn't, either over write it or double click to bring up a calendar from which you can pick a date.... On it goes. But creating an empty record except for the date so that you'll know that the date was there and that nothing was noted just doesn't make sense. It makes better sense that if the date isn't there then nothing was noted for that date. If you have digested the above and you or your management absolutely, positively insist that the "wrong way" is the way you want to do it then post back. In any case, post back with questions- HTH - -Larry- -- "Kelvin Beaton" kelvin at mccsa dot com wrote in message ... I'm trying to create a time sheet database. I have some of the basics worked out, but would like some input on how to deal with the subform that displays the days in the current pay period. Some how I would like the dropdown to generate payperiods so I don't have to enter every one myself. I'd like the subform to display the 14 days for that pay period. Any input would be appreciated Kelvin here is what I have pretty much so far: http://www.mccsa.com/Temp/TimeAndBilling.zip PS I haven't gone through and fixed tab orders and the like so there is lots of work to polish it up... |
#3
|
|||
|
|||
Needing some input on how to take this db to the next level...
Hi Larry
I hear what you're saying about blank records... did you look at the database? http://www.mccsa.com/Temp/TimeAndBilling.zip We are currently using a spreadsheet and I would like to develope an Access database, hence the post and the link to a copy of what I'm working on. My thought is, in the spreadsheet user can see a pay period at a time. If you have any other ideas on how to structure this differently I'd be glad to hear them. Thanks Kelvin "Larry Daugherty" wrote in message ... Actually, you probably don't have it "worked out" just yet. If you're talking Excel spreadsheets then you should post your issue in an Excel newsgroup. My response assumes that you are really trying to get something done in Access. You may get some flak about generating empty records. It begins he Don't generate empty records unless that turns out to be the best way to solve a particular data issue. Of course, I'm young and have a lot to learn but I haven't seen a convincing case yet. In just about any case in which you need to show events for a person over a pay period you can *calculate* in order to display meaningful results. You can do some very intelligent things with child tables ~ tblPay and a subform based on that table. You can establish a default date for each new record that will make a lot of sense in the context of what you are doing. For example you could have the date value default to the next working date higher than the last date entered for that person. Unless you have highly variable work (day) schedules, that proposed date will be the correct date. If it wasn't, either over write it or double click to bring up a calendar from which you can pick a date.... On it goes. But creating an empty record except for the date so that you'll know that the date was there and that nothing was noted just doesn't make sense. It makes better sense that if the date isn't there then nothing was noted for that date. If you have digested the above and you or your management absolutely, positively insist that the "wrong way" is the way you want to do it then post back. In any case, post back with questions- HTH - -Larry- -- "Kelvin Beaton" kelvin at mccsa dot com wrote in message ... I'm trying to create a time sheet database. I have some of the basics worked out, but would like some input on how to deal with the subform that displays the days in the current pay period. Some how I would like the dropdown to generate payperiods so I don't have to enter every one myself. I'd like the subform to display the 14 days for that pay period. Any input would be appreciated Kelvin here is what I have pretty much so far: http://www.mccsa.com/Temp/TimeAndBilling.zip PS I haven't gone through and fixed tab orders and the like so there is lots of work to polish it up... |
#4
|
|||
|
|||
Needing some input on how to take this db to the next level...
No, I haven't seen the database. You've saved it in 2K2 format. I
don't have that version mounted anywhere and am not curious enough about anything in 2k2 to mount it. Are you striving to meet a demand of your manager or of the user base or have you simply assumed that they'll only want what they already have? You can only go so far in making Access look and feel like Excel. They aren't minor variations on a common theme. They are hugely different in their internals and in their specific behaviors. Microsoft folks have worked long and hard to make many things behave the same in both platforms. It sounds like the view of the whole pay period at once is a requirement that you've made up. Your user base may be willing to deal with a different paradigm, have you polled them and their managers? HTH -- -Larry- -- "Kelvin Beaton" kelvin at mccsa dot com wrote in message ... Hi Larry I hear what you're saying about blank records... did you look at the database? http://www.mccsa.com/Temp/TimeAndBilling.zip We are currently using a spreadsheet and I would like to develope an Access database, hence the post and the link to a copy of what I'm working on. My thought is, in the spreadsheet user can see a pay period at a time. If you have any other ideas on how to structure this differently I'd be glad to hear them. Thanks Kelvin "Larry Daugherty" wrote in message ... Actually, you probably don't have it "worked out" just yet. If you're talking Excel spreadsheets then you should post your issue in an Excel newsgroup. My response assumes that you are really trying to get something done in Access. You may get some flak about generating empty records. It begins he Don't generate empty records unless that turns out to be the best way to solve a particular data issue. Of course, I'm young and have a lot to learn but I haven't seen a convincing case yet. In just about any case in which you need to show events for a person over a pay period you can *calculate* in order to display meaningful results. You can do some very intelligent things with child tables ~ tblPay and a subform based on that table. You can establish a default date for each new record that will make a lot of sense in the context of what you are doing. For example you could have the date value default to the next working date higher than the last date entered for that person. Unless you have highly variable work (day) schedules, that proposed date will be the correct date. If it wasn't, either over write it or double click to bring up a calendar from which you can pick a date.... On it goes. But creating an empty record except for the date so that you'll know that the date was there and that nothing was noted just doesn't make sense. It makes better sense that if the date isn't there then nothing was noted for that date. If you have digested the above and you or your management absolutely, positively insist that the "wrong way" is the way you want to do it then post back. In any case, post back with questions- HTH - -Larry- -- "Kelvin Beaton" kelvin at mccsa dot com wrote in message ... I'm trying to create a time sheet database. I have some of the basics worked out, but would like some input on how to deal with the subform that displays the days in the current pay period. Some how I would like the dropdown to generate payperiods so I don't have to enter every one myself. I'd like the subform to display the 14 days for that pay period. Any input would be appreciated Kelvin here is what I have pretty much so far: http://www.mccsa.com/Temp/TimeAndBilling.zip PS I haven't gone through and fixed tab orders and the like so there is lots of work to polish it up... |
#5
|
|||
|
|||
Needing some input on how to take this db to the next level...
Larry Daugherty wrote:
No, I haven't seen the database. You've saved it in 2K2 format. I don't have that version mounted anywhere and am not curious enough about anything in 2k2 to mount it. Are you striving to meet a demand of your manager or of the user base or have you simply assumed that they'll only want what they already have? You can only go so far in making Access look and feel like Excel. They aren't minor variations on a common theme. They are hugely different in their internals and in their specific behaviors. Microsoft folks have worked long and hard to make many things behave the same in both platforms. It sounds like the view of the whole pay period at once is a requirement that you've made up. Your user base may be willing to deal with a different paradigm, have you polled them and their managers? HTH Kevin, While you're polling, maybe I can generate some ideas. See: http://groups.google.com/group/micro...899be712104374 That effort got placed on hold so I still don't have code to post for it. My point is that the logic for handling time tickets in Access will eventually get a little involved. Excel does have some advantages over Access when it comes to complicated conditions for pay periods, but it also has some notable drawbacks. One way to begin to handle the fact that your pay period is two weeks is to use an expression to calculate whether a given date is in week 1 or week 2 based on the very first pay period starting date: InitialPPDate: 3/3/1997 (Monday) PPWeekNumber: (DateDiff("d", #3/3/1997#, Date())) \ 7 Mod 2 + 1 For today (May 8, 2007) this returns 2, indicating that today falls in the second week if the initial pay period started on 3/3/1997. This expression is a warm-up for later. To get the date of a previous or following day I use the following two module functions: Public Function LEDay(dtX As Date, vbDay As Integer) As Date LEDay = DateAdd("d", -(7 + WeekDay(dtX) - vbDay) Mod 7, dtX) End Function Public Function GEDay(dtX As Date, vbDay As Integer) As Date GEDay = DateAdd("d", (7 + vbDay - WeekDay(dtX)) Mod 7, dtX) End Function So, for today (May 8, 2007), I can find the Monday on or before today as follows: LEDay(#5/8/2007#, 2) So if you have a given date and want to know which pay period it falls in, you could do something like (but don't): dtStartOfPP = IIf((DateDiff("d", #3/3/1997#, Date())) \ 7 Mod 2 + 1 = 1, LEDay(Date(), 2), LEDay(DateAdd("ww", Date(), -1), 2)) dtEndOfPP = DateAdd("ww", 2, dtStartOfPP) That is, go back to the previous Monday on or equal to Date() if Date() is in week 1. Go back to the previous Monday on or equal to a week ago if Date() is in week 2. That was more warm-up. Although the logic above works for any input date, it makes sense to show just the initial date for each pay period in your dropdown. Maybe the RowSource can look something like (air SQL): SELECT DISTINCT LEDay([TimeTicketDate], 2) AS PPStarting FROM tblTimeTickets WHERE (DateDiff("d", #3/3/1997#, LEDay([TimeTicketDate], 2))) \ 7 Mod 2 = 0; That should put every distinct pay period starting date for every existing time ticket into your dropdown. That may or may not give you all the dates you want. When one of the dates is selected, the query for the subform's RecordSource can use something like 'BETWEEN Nz(Forms!MyForm!MyDropdown.Value, Date()) AND DateAdd("d", 13, Nz(Forms!MyForm!MyDropdown.Value, Date())' to select the date range of time tickets you want to show on the subform (it may be necessary to requery the subform in an event such as the dropdown's AfterUpdate event). Another way to get dates into your dropdown is to use a table of integers: tblI ID AutoNumber I Long ID I 1 1 2 2 3 3 .... N N where N is large enough that your pay period date range is always within N weeks. Your dropdown's RowSource can look something like: PARAMETERS Forms!MyForm!txtStartDate.Value DateTime, Forms!MyForm!txtEndDate.Value DateTime; SELECT DateAdd("ww", I - 1, LEDay(Forms!MyForm!txtStartDate.Value, 2)) AS PPStarting FROM tblI WHERE DateAdd("ww", I - 1, LEDay(Forms!MyForm!txtStartDate.Value, 2)) = Forms!MyForm!txtEndDate.Value; That will put all the Monday's between the two dates into the dropdown. But you want only the Mondays whose week number is 1: PARAMETERS Forms!Form1!txtStartDate.Value DateTime, Forms!Form1!txtEndDate.Value DateTime; SELECT DateAdd("ww", I - 1, LEDay(Forms!Form1!txtStartDate.Value, 2)) AS PPStarting FROM tblI WHERE DateAdd("ww", I - 1, LEDay(Forms!Form1!txtStartDate.Value, 2)) = Forms!Form1!txtEndDate.Value And (DateDiff("d", #3/3/1997#, DateAdd("ww", I - 1, LEDay(Forms!Form1!txtStartDate.Value, 2)))) \ 7 Mod 2 + 1 = 1; I created a form to test this out and used a start date of 3/3/2007 and an end date of 5/1/2007. The query produced the following: PPStarting 3/5/2007 3/19/2007 4/2/2007 4/16/2007 4/30/2007 When I changed #3/3/1997# to #3/10/1997# I got: PPStarting 2/26/2007 3/12/2007 3/26/2007 4/9/2007 4/23/2007 To add a final touch you can add some kind of calendar control for each of the dates that are to be placed in txtStartingDate and txtEndingDate on the form. Lock the textboxes, have appropriate default dates placed in the text boxes and only let the user change dates by using the calendar control. Hopefully the information above gives you a better idea of the differences between Access and Excel when it comes to doing something like time tickets. Access can use a single table for everything but the implementation can get a little tricky later when all the weird things that can happen with time tickets start cropping up. The single table allows much more flexibility for all kinds of report calculations, totals and averages. A time ticket table I use includes a job number that allows a form to compute in almost real time how much labor has been charged to that job. James A. Fortune |
#6
|
|||
|
|||
Needing some input on how to take this db to the next level...
Thanks James for your input...
Yes Excel doesn't sound so bad... ;-) I'll continue to work on this and will incoporate your comments! Thanks again! Kelvin "James A. Fortune" wrote in message ... Larry Daugherty wrote: No, I haven't seen the database. You've saved it in 2K2 format. I don't have that version mounted anywhere and am not curious enough about anything in 2k2 to mount it. Are you striving to meet a demand of your manager or of the user base or have you simply assumed that they'll only want what they already have? You can only go so far in making Access look and feel like Excel. They aren't minor variations on a common theme. They are hugely different in their internals and in their specific behaviors. Microsoft folks have worked long and hard to make many things behave the same in both platforms. It sounds like the view of the whole pay period at once is a requirement that you've made up. Your user base may be willing to deal with a different paradigm, have you polled them and their managers? HTH Kevin, While you're polling, maybe I can generate some ideas. See: http://groups.google.com/group/micro...899be712104374 That effort got placed on hold so I still don't have code to post for it. My point is that the logic for handling time tickets in Access will eventually get a little involved. Excel does have some advantages over Access when it comes to complicated conditions for pay periods, but it also has some notable drawbacks. One way to begin to handle the fact that your pay period is two weeks is to use an expression to calculate whether a given date is in week 1 or week 2 based on the very first pay period starting date: InitialPPDate: 3/3/1997 (Monday) PPWeekNumber: (DateDiff("d", #3/3/1997#, Date())) \ 7 Mod 2 + 1 For today (May 8, 2007) this returns 2, indicating that today falls in the second week if the initial pay period started on 3/3/1997. This expression is a warm-up for later. To get the date of a previous or following day I use the following two module functions: Public Function LEDay(dtX As Date, vbDay As Integer) As Date LEDay = DateAdd("d", -(7 + WeekDay(dtX) - vbDay) Mod 7, dtX) End Function Public Function GEDay(dtX As Date, vbDay As Integer) As Date GEDay = DateAdd("d", (7 + vbDay - WeekDay(dtX)) Mod 7, dtX) End Function So, for today (May 8, 2007), I can find the Monday on or before today as follows: LEDay(#5/8/2007#, 2) So if you have a given date and want to know which pay period it falls in, you could do something like (but don't): dtStartOfPP = IIf((DateDiff("d", #3/3/1997#, Date())) \ 7 Mod 2 + 1 = 1, LEDay(Date(), 2), LEDay(DateAdd("ww", Date(), -1), 2)) dtEndOfPP = DateAdd("ww", 2, dtStartOfPP) That is, go back to the previous Monday on or equal to Date() if Date() is in week 1. Go back to the previous Monday on or equal to a week ago if Date() is in week 2. That was more warm-up. Although the logic above works for any input date, it makes sense to show just the initial date for each pay period in your dropdown. Maybe the RowSource can look something like (air SQL): SELECT DISTINCT LEDay([TimeTicketDate], 2) AS PPStarting FROM tblTimeTickets WHERE (DateDiff("d", #3/3/1997#, LEDay([TimeTicketDate], 2))) \ 7 Mod 2 = 0; That should put every distinct pay period starting date for every existing time ticket into your dropdown. That may or may not give you all the dates you want. When one of the dates is selected, the query for the subform's RecordSource can use something like 'BETWEEN Nz(Forms!MyForm!MyDropdown.Value, Date()) AND DateAdd("d", 13, Nz(Forms!MyForm!MyDropdown.Value, Date())' to select the date range of time tickets you want to show on the subform (it may be necessary to requery the subform in an event such as the dropdown's AfterUpdate event). Another way to get dates into your dropdown is to use a table of integers: tblI ID AutoNumber I Long ID I 1 1 2 2 3 3 ... N N where N is large enough that your pay period date range is always within N weeks. Your dropdown's RowSource can look something like: PARAMETERS Forms!MyForm!txtStartDate.Value DateTime, Forms!MyForm!txtEndDate.Value DateTime; SELECT DateAdd("ww", I - 1, LEDay(Forms!MyForm!txtStartDate.Value, 2)) AS PPStarting FROM tblI WHERE DateAdd("ww", I - 1, LEDay(Forms!MyForm!txtStartDate.Value, 2)) = Forms!MyForm!txtEndDate.Value; That will put all the Monday's between the two dates into the dropdown. But you want only the Mondays whose week number is 1: PARAMETERS Forms!Form1!txtStartDate.Value DateTime, Forms!Form1!txtEndDate.Value DateTime; SELECT DateAdd("ww", I - 1, LEDay(Forms!Form1!txtStartDate.Value, 2)) AS PPStarting FROM tblI WHERE DateAdd("ww", I - 1, LEDay(Forms!Form1!txtStartDate.Value, 2)) = Forms!Form1!txtEndDate.Value And (DateDiff("d", #3/3/1997#, DateAdd("ww", I - 1, LEDay(Forms!Form1!txtStartDate.Value, 2)))) \ 7 Mod 2 + 1 = 1; I created a form to test this out and used a start date of 3/3/2007 and an end date of 5/1/2007. The query produced the following: PPStarting 3/5/2007 3/19/2007 4/2/2007 4/16/2007 4/30/2007 When I changed #3/3/1997# to #3/10/1997# I got: PPStarting 2/26/2007 3/12/2007 3/26/2007 4/9/2007 4/23/2007 To add a final touch you can add some kind of calendar control for each of the dates that are to be placed in txtStartingDate and txtEndingDate on the form. Lock the textboxes, have appropriate default dates placed in the text boxes and only let the user change dates by using the calendar control. Hopefully the information above gives you a better idea of the differences between Access and Excel when it comes to doing something like time tickets. Access can use a single table for everything but the implementation can get a little tricky later when all the weird things that can happen with time tickets start cropping up. The single table allows much more flexibility for all kinds of report calculations, totals and averages. A time ticket table I use includes a job number that allows a form to compute in almost real time how much labor has been charged to that job. James A. Fortune |
Thread Tools | |
Display Modes | |
|
|