Use entire table as a record in Access? Jaybird 24.02.2006 16:46:26 I am attempting to get Access to behave in a way that is similar to Excel in some respects... Currently, payroll is using excel to post weekly employee hours. The current list of employees is posted with the hours they worked for each day during the week on one worksheet, the next week's hours are posted on the next worksheet and so on. To get an Access application to be adopted by payroll, I need it to behave in the same way. I want to design a form which pulls up the current list of emploees with the hours they worked for that week as one record, with the next week's hours as the next record. Currently, Access wants to treat each employee and their hours as individual records. You have to scroll down the list of employees one at a time to find the weekly hours you want to enter. Obviously, you can design a report that organizes the data by employee and the weekly hours after they have been entered, but I want a way to enter this information in a form all at once. Does anyone know how to get Access to behave in this way?

Re: Use entire table as a record in Access? "tina" <nospam[ at ]address.com> 24.02.2006 18:42:44 you need minimum two tables: tblEmployees EmpID (primary key) FirstName LastName <any other fields that describe an employee.> tblEmpHours HourID (pk) EmpID (foreign key from tblEmployees) WorkDate WorkHours the table design is standard and simple. to do the data entry efficiently in forms, you can get about as creative as you need to be. exactly how you set up the data entry form depends on how the user needs to be able to enter the data. for instance, will the user enter all the hours for all employees for Monday 2/20/2006, then for Tuesday, etc? or will the user enter all the hours for one employee for each day of the week, Monday 2/20/2006 through Friday 2/24/2006, then for the next employee, etc? in either case above, you can add code to the data entry form to append records for all employees for each date of the work-week at one time, and then sort the records according to how the user needs to enter the data, thus presenting the user with a group of "already existing" records that s/he can quickly move through in order, entering just the hours - the employee and date information is already present in the record. hth "Jaybird" <Jaybird[ at ]discussions.microsoft.com> wrote in message news:BE378A2C-A089-4FEE-B133-F3BF127F66AC[ at ]microsoft.com... [Quoted Text] > I am attempting to get Access to behave in a way that is similar to Excel in > some respects... Currently, payroll is using excel to post weekly employee > hours. The current list of employees is posted with the hours they worked > for each day during the week on one worksheet, the next week's hours are > posted on the next worksheet and so on. To get an Access application to be > adopted by payroll, I need it to behave in the same way. I want to design a > form which pulls up the current list of emploees with the hours they worked > for that week as one record, with the next week's hours as the next record. > Currently, Access wants to treat each employee and their hours as individual > records. You have to scroll down the list of employees one at a time to find > the weekly hours you want to enter. Obviously, you can design a report that > organizes the data by employee and the weekly hours after they have been > entered, but I want a way to enter this information in a form all at once. > Does anyone know how to get Access to behave in this way?

Re: Use entire table as a record in Access? Jaybird 28.02.2006 18:22:21 Thanks, Tina... Right now I've got the tables behaving like I want by combining them in a form in datasheet view. The whole week's worth of hours is displayed all at once, adn they can be quickly inputted. I even managed to put a calendar pop up for the date entries. The problem, as I see it, is that for each new week, each employee must be specified in order to add the new hours. Is there a way to get the entire roster to display at once for the new week? "tina" wrote: [Quoted Text] > you need minimum two tables: > > tblEmployees > EmpID (primary key) > FirstName > LastName > <any other fields that describe an employee.> > > tblEmpHours > HourID (pk) > EmpID (foreign key from tblEmployees) > WorkDate > WorkHours > > the table design is standard and simple. to do the data entry efficiently in > forms, you can get about as creative as you need to be. exactly how you set > up the data entry form depends on how the user needs to be able to enter the > data. for instance, will the user enter all the hours for all employees for > Monday 2/20/2006, then for Tuesday, etc? or will the user enter all the > hours for one employee for each day of the week, Monday 2/20/2006 through > Friday 2/24/2006, then for the next employee, etc? > > in either case above, you can add code to the data entry form to append > records for all employees for each date of the work-week at one time, and > then sort the records according to how the user needs to enter the data, > thus presenting the user with a group of "already existing" records that > s/he can quickly move through in order, entering just the hours - the > employee and date information is already present in the record. > > hth > > > "Jaybird" <Jaybird[ at ]discussions.microsoft.com> wrote in message > news:BE378A2C-A089-4FEE-B133-F3BF127F66AC[ at ]microsoft.com... > > I am attempting to get Access to behave in a way that is similar to Excel > in > > some respects... Currently, payroll is using excel to post weekly > employee > > hours. The current list of employees is posted with the hours they worked > > for each day during the week on one worksheet, the next week's hours are > > posted on the next worksheet and so on. To get an Access application to > be > > adopted by payroll, I need it to behave in the same way. I want to design > a > > form which pulls up the current list of emploees with the hours they > worked > > for that week as one record, with the next week's hours as the next > record. > > Currently, Access wants to treat each employee and their hours as > individual > > records. You have to scroll down the list of employees one at a time to > find > > the weekly hours you want to enter. Obviously, you can design a report > that > > organizes the data by employee and the weekly hours after they have been > > entered, but I want a way to enter this information in a form all at once. > > Does anyone know how to get Access to behave in this way? > > >

Re: Use entire table as a record in Access? Jaybird 28.02.2006 21:59:29 Aha, I didn't read your post carefully enough. Sorry. I'll concentrate my efforts on looking for an example of just such and appending code. I appreciate the response. Of course, my VBA skills stink. Maybe someone could walk me through it? Hint, hint... Jaybird "tina" wrote: [Quoted Text] > you need minimum two tables: > > tblEmployees > EmpID (primary key) > FirstName > LastName > <any other fields that describe an employee.> > > tblEmpHours > HourID (pk) > EmpID (foreign key from tblEmployees) > WorkDate > WorkHours > > the table design is standard and simple. to do the data entry efficiently in > forms, you can get about as creative as you need to be. exactly how you set > up the data entry form depends on how the user needs to be able to enter the > data. for instance, will the user enter all the hours for all employees for > Monday 2/20/2006, then for Tuesday, etc? or will the user enter all the > hours for one employee for each day of the week, Monday 2/20/2006 through > Friday 2/24/2006, then for the next employee, etc? > > in either case above, you can add code to the data entry form to append > records for all employees for each date of the work-week at one time, and > then sort the records according to how the user needs to enter the data, > thus presenting the user with a group of "already existing" records that > s/he can quickly move through in order, entering just the hours - the > employee and date information is already present in the record. > > hth > > > "Jaybird" <Jaybird[ at ]discussions.microsoft.com> wrote in message > news:BE378A2C-A089-4FEE-B133-F3BF127F66AC[ at ]microsoft.com... > > I am attempting to get Access to behave in a way that is similar to Excel > in > > some respects... Currently, payroll is using excel to post weekly > employee > > hours. The current list of employees is posted with the hours they worked > > for each day during the week on one worksheet, the next week's hours are > > posted on the next worksheet and so on. To get an Access application to > be > > adopted by payroll, I need it to behave in the same way. I want to design > a > > form which pulls up the current list of emploees with the hours they > worked > > for that week as one record, with the next week's hours as the next > record. > > Currently, Access wants to treat each employee and their hours as > individual > > records. You have to scroll down the list of employees one at a time to > find > > the weekly hours you want to enter. Obviously, you can design a report > that > > organizes the data by employee and the weekly hours after they have been > > entered, but I want a way to enter this information in a form all at once. > > Does anyone know how to get Access to behave in this way? > > >

Re: Use entire table as a record in Access? "tina" <nospam[ at ]address.com> 01.03.2006 03:59:36 just create an Append query to append all the necessary records to tblEmpHours. then run the query, from a macro or VBA. then requery the form that's bound to tblEmpHours. you can set the OrderBy property of the form in VBA. something along the lines of: Dim db As DAO.Database, strSQL As String Set db = CurrentDb strSQL = db.QueryDefs("AppendQueryName").SQL db.Execute strSQL, dbFailOnError Me.Requery Me.OrderBy = "SomeField, SomeOtherField" Me.OrderByOn = True hth "Jaybird" <Jaybird[ at ]discussions.microsoft.com> wrote in message news:9CFEF83D-5162-4204-A92E-F4A62BABEAC8[ at ]microsoft.com... [Quoted Text] > Aha, > > I didn't read your post carefully enough. Sorry. I'll concentrate my > efforts on looking for an example of just such and appending code. I > appreciate the response. Of course, my VBA skills stink. Maybe someone > could walk me through it? Hint, hint... > > Jaybird > > "tina" wrote: > > > you need minimum two tables: > > > > tblEmployees > > EmpID (primary key) > > FirstName > > LastName > > <any other fields that describe an employee.> > > > > tblEmpHours > > HourID (pk) > > EmpID (foreign key from tblEmployees) > > WorkDate > > WorkHours > > > > the table design is standard and simple. to do the data entry efficiently in > > forms, you can get about as creative as you need to be. exactly how you set > > up the data entry form depends on how the user needs to be able to enter the > > data. for instance, will the user enter all the hours for all employees for > > Monday 2/20/2006, then for Tuesday, etc? or will the user enter all the > > hours for one employee for each day of the week, Monday 2/20/2006 through > > Friday 2/24/2006, then for the next employee, etc? > > > > in either case above, you can add code to the data entry form to append > > records for all employees for each date of the work-week at one time, and > > then sort the records according to how the user needs to enter the data, > > thus presenting the user with a group of "already existing" records that > > s/he can quickly move through in order, entering just the hours - the > > employee and date information is already present in the record. > > > > hth > > > > > > "Jaybird" <Jaybird[ at ]discussions.microsoft.com> wrote in message > > news:BE378A2C-A089-4FEE-B133-F3BF127F66AC[ at ]microsoft.com... > > > I am attempting to get Access to behave in a way that is similar to Excel > > in > > > some respects... Currently, payroll is using excel to post weekly > > employee > > > hours. The current list of employees is posted with the hours they worked > > > for each day during the week on one worksheet, the next week's hours are > > > posted on the next worksheet and so on. To get an Access application to > > be > > > adopted by payroll, I need it to behave in the same way. I want to design > > a > > > form which pulls up the current list of emploees with the hours they > > worked > > > for that week as one record, with the next week's hours as the next > > record. > > > Currently, Access wants to treat each employee and their hours as > > individual > > > records. You have to scroll down the list of employees one at a time to > > find > > > the weekly hours you want to enter. Obviously, you can design a report > > that > > > organizes the data by employee and the weekly hours after they have been > > > entered, but I want a way to enter this information in a form all at once. > > > Does anyone know how to get Access to behave in this way? > > > > > >

Re: Use entire table as a record in Access? Jaybird 01.03.2006 12:39:29 Muy facil, Tina! Gracias! However, as you'd expect, the update query wants to update all of the records in the table, not just the ones from the latest week. I'm thinking of several solutions. Tell me which sounds more promising... 1) Set the critera of the append query to soemthing that will limit the records it updates... Can't think of one that will work right now. 2) Instead of appending the same table, how about updating to a temp table, and appending to the original table each time the action is run. That way, only the records from the previous week are modified and added to the original table. 3) Suggestions I haven't thought of? Thanks again... Jaybird "tina" wrote: [Quoted Text] > just create an Append query to append all the necessary records to > tblEmpHours. then run the query, from a macro or VBA. then requery the form > that's bound to tblEmpHours. you can set the OrderBy property of the form in > VBA. something along the lines of: > > Dim db As DAO.Database, strSQL As String > Set db = CurrentDb > strSQL = db.QueryDefs("AppendQueryName").SQL > db.Execute strSQL, dbFailOnError > Me.Requery > Me.OrderBy = "SomeField, SomeOtherField" > Me.OrderByOn = True > > hth > > > "Jaybird" <Jaybird[ at ]discussions.microsoft.com> wrote in message > news:9CFEF83D-5162-4204-A92E-F4A62BABEAC8[ at ]microsoft.com... > > Aha, > > > > I didn't read your post carefully enough. Sorry. I'll concentrate my > > efforts on looking for an example of just such and appending code. I > > appreciate the response. Of course, my VBA skills stink. Maybe someone > > could walk me through it? Hint, hint... > > > > Jaybird > > > > "tina" wrote: > > > > > you need minimum two tables: > > > > > > tblEmployees > > > EmpID (primary key) > > > FirstName > > > LastName > > > <any other fields that describe an employee.> > > > > > > tblEmpHours > > > HourID (pk) > > > EmpID (foreign key from tblEmployees) > > > WorkDate > > > WorkHours > > > > > > the table design is standard and simple. to do the data entry > efficiently in > > > forms, you can get about as creative as you need to be. exactly how you > set > > > up the data entry form depends on how the user needs to be able to enter > the > > > data. for instance, will the user enter all the hours for all employees > for > > > Monday 2/20/2006, then for Tuesday, etc? or will the user enter all the > > > hours for one employee for each day of the week, Monday 2/20/2006 > through > > > Friday 2/24/2006, then for the next employee, etc? > > > > > > in either case above, you can add code to the data entry form to append > > > records for all employees for each date of the work-week at one time, > and > > > then sort the records according to how the user needs to enter the data, > > > thus presenting the user with a group of "already existing" records that > > > s/he can quickly move through in order, entering just the hours - the > > > employee and date information is already present in the record. > > > > > > hth > > > > > > > > > "Jaybird" <Jaybird[ at ]discussions.microsoft.com> wrote in message > > > news:BE378A2C-A089-4FEE-B133-F3BF127F66AC[ at ]microsoft.com... > > > > I am attempting to get Access to behave in a way that is similar to > Excel > > > in > > > > some respects... Currently, payroll is using excel to post weekly > > > employee > > > > hours. The current list of employees is posted with the hours they > worked > > > > for each day during the week on one worksheet, the next week's hours > are > > > > posted on the next worksheet and so on. To get an Access application > to > > > be > > > > adopted by payroll, I need it to behave in the same way. I want to > design > > > a > > > > form which pulls up the current list of emploees with the hours they > > > worked > > > > for that week as one record, with the next week's hours as the next > > > record. > > > > Currently, Access wants to treat each employee and their hours as > > > individual > > > > records. You have to scroll down the list of employees one at a time > to > > > find > > > > the weekly hours you want to enter. Obviously, you can design a > report > > > that > > > > organizes the data by employee and the weekly hours after they have > been > > > > entered, but I want a way to enter this information in a form all at > once. > > > > Does anyone know how to get Access to behave in this way? > > > > > > > > > > > >

Re: Use entire table as a record in Access? Jaybird 01.03.2006 13:00:27 Silly me! Of course, using the Make Table Query is going to produce similar results... It's still referring to the original table for those dates. I guess I'm going to have to refresh the criteria for the temp table with the new dates and append that. Currently, I'm thinking of creating a form with a calendar add in to select the new dates and using that. I'll let you know how it turns out! Jaybird "Jaybird" wrote: [Quoted Text] > Muy facil, Tina! Gracias! However, as you'd expect, the update query wants > to update all of the records in the table, not just the ones from the latest > week. I'm thinking of several solutions. Tell me which sounds more > promising... > > 1) Set the critera of the append query to soemthing that will limit the > records it updates... Can't think of one that will work right now. > 2) Instead of appending the same table, how about updating to a temp table, > and appending to the original table each time the action is run. That way, > only the records from the previous week are modified and added to the > original table. > 3) Suggestions I haven't thought of? > > Thanks again... > > Jaybird > > "tina" wrote: > > > just create an Append query to append all the necessary records to > > tblEmpHours. then run the query, from a macro or VBA. then requery the form > > that's bound to tblEmpHours. you can set the OrderBy property of the form in > > VBA. something along the lines of: > > > > Dim db As DAO.Database, strSQL As String > > Set db = CurrentDb > > strSQL = db.QueryDefs("AppendQueryName").SQL > > db.Execute strSQL, dbFailOnError > > Me.Requery > > Me.OrderBy = "SomeField, SomeOtherField" > > Me.OrderByOn = True > > > > hth > > > > > > "Jaybird" <Jaybird[ at ]discussions.microsoft.com> wrote in message > > news:9CFEF83D-5162-4204-A92E-F4A62BABEAC8[ at ]microsoft.com... > > > Aha, > > > > > > I didn't read your post carefully enough. Sorry. I'll concentrate my > > > efforts on looking for an example of just such and appending code. I > > > appreciate the response. Of course, my VBA skills stink. Maybe someone > > > could walk me through it? Hint, hint... > > > > > > Jaybird > > > > > > "tina" wrote: > > > > > > > you need minimum two tables: > > > > > > > > tblEmployees > > > > EmpID (primary key) > > > > FirstName > > > > LastName > > > > <any other fields that describe an employee.> > > > > > > > > tblEmpHours > > > > HourID (pk) > > > > EmpID (foreign key from tblEmployees) > > > > WorkDate > > > > WorkHours > > > > > > > > the table design is standard and simple. to do the data entry > > efficiently in > > > > forms, you can get about as creative as you need to be. exactly how you > > set > > > > up the data entry form depends on how the user needs to be able to enter > > the > > > > data. for instance, will the user enter all the hours for all employees > > for > > > > Monday 2/20/2006, then for Tuesday, etc? or will the user enter all the > > > > hours for one employee for each day of the week, Monday 2/20/2006 > > through > > > > Friday 2/24/2006, then for the next employee, etc? > > > > > > > > in either case above, you can add code to the data entry form to append > > > > records for all employees for each date of the work-week at one time, > > and > > > > then sort the records according to how the user needs to enter the data, > > > > thus presenting the user with a group of "already existing" records that > > > > s/he can quickly move through in order, entering just the hours - the > > > > employee and date information is already present in the record. > > > > > > > > hth > > > > > > > > > > > > "Jaybird" <Jaybird[ at ]discussions.microsoft.com> wrote in message > > > > news:BE378A2C-A089-4FEE-B133-F3BF127F66AC[ at ]microsoft.com... > > > > > I am attempting to get Access to behave in a way that is similar to > > Excel > > > > in > > > > > some respects... Currently, payroll is using excel to post weekly > > > > employee > > > > > hours. The current list of employees is posted with the hours they > > worked > > > > > for each day during the week on one worksheet, the next week's hours > > are > > > > > posted on the next worksheet and so on. To get an Access application > > to > > > > be > > > > > adopted by payroll, I need it to behave in the same way. I want to > > design > > > > a > > > > > form which pulls up the current list of emploees with the hours they > > > > worked > > > > > for that week as one record, with the next week's hours as the next > > > > record. > > > > > Currently, Access wants to treat each employee and their hours as > > > > individual > > > > > records. You have to scroll down the list of employees one at a time > > to > > > > find > > > > > the weekly hours you want to enter. Obviously, you can design a > > report > > > > that > > > > > organizes the data by employee and the weekly hours after they have > > been > > > > > entered, but I want a way to enter this information in a form all at > > once. > > > > > Does anyone know how to get Access to behave in this way? > > > > > > > > > > > > > > > > > >

Re: Use entire table as a record in Access? Jaybird 01.03.2006 14:00:26 Well, actually, it can't be a temp table referring to the original Employee Hours table because I've appended that to include new dates... Duh! But if it's a permanent table with dates updateable by a form (with a calendar control) that refers to the original roster of employees, I think that gets me close to where I want to be. Wish me luck. Jaybird "Jaybird" wrote: [Quoted Text] > Silly me! Of course, using the Make Table Query is going to produce similar > results... It's still referring to the original table for those dates. I > guess I'm going to have to refresh the criteria for the temp table with the > new dates and append that. Currently, I'm thinking of creating a form with a > calendar add in to select the new dates and using that. I'll let you know > how it turns out! > > Jaybird > > "Jaybird" wrote: > > > Muy facil, Tina! Gracias! However, as you'd expect, the update query wants > > to update all of the records in the table, not just the ones from the latest > > week. I'm thinking of several solutions. Tell me which sounds more > > promising... > > > > 1) Set the critera of the append query to soemthing that will limit the > > records it updates... Can't think of one that will work right now. > > 2) Instead of appending the same table, how about updating to a temp table, > > and appending to the original table each time the action is run. That way, > > only the records from the previous week are modified and added to the > > original table. > > 3) Suggestions I haven't thought of? > > > > Thanks again... > > > > Jaybird > > > > "tina" wrote: > > > > > just create an Append query to append all the necessary records to > > > tblEmpHours. then run the query, from a macro or VBA. then requery the form > > > that's bound to tblEmpHours. you can set the OrderBy property of the form in > > > VBA. something along the lines of: > > > > > > Dim db As DAO.Database, strSQL As String > > > Set db = CurrentDb > > > strSQL = db.QueryDefs("AppendQueryName").SQL > > > db.Execute strSQL, dbFailOnError > > > Me.Requery > > > Me.OrderBy = "SomeField, SomeOtherField" > > > Me.OrderByOn = True > > > > > > hth > > > > > > > > > "Jaybird" <Jaybird[ at ]discussions.microsoft.com> wrote in message > > > news:9CFEF83D-5162-4204-A92E-F4A62BABEAC8[ at ]microsoft.com... > > > > Aha, > > > > > > > > I didn't read your post carefully enough. Sorry. I'll concentrate my > > > > efforts on looking for an example of just such and appending code. I > > > > appreciate the response. Of course, my VBA skills stink. Maybe someone > > > > could walk me through it? Hint, hint... > > > > > > > > Jaybird > > > > > > > > "tina" wrote: > > > > > > > > > you need minimum two tables: > > > > > > > > > > tblEmployees > > > > > EmpID (primary key) > > > > > FirstName > > > > > LastName > > > > > <any other fields that describe an employee.> > > > > > > > > > > tblEmpHours > > > > > HourID (pk) > > > > > EmpID (foreign key from tblEmployees) > > > > > WorkDate > > > > > WorkHours > > > > > > > > > > the table design is standard and simple. to do the data entry > > > efficiently in > > > > > forms, you can get about as creative as you need to be. exactly how you > > > set > > > > > up the data entry form depends on how the user needs to be able to enter > > > the > > > > > data. for instance, will the user enter all the hours for all employees > > > for > > > > > Monday 2/20/2006, then for Tuesday, etc? or will the user enter all the > > > > > hours for one employee for each day of the week, Monday 2/20/2006 > > > through > > > > > Friday 2/24/2006, then for the next employee, etc? > > > > > > > > > > in either case above, you can add code to the data entry form to append > > > > > records for all employees for each date of the work-week at one time, > > > and > > > > > then sort the records according to how the user needs to enter the data, > > > > > thus presenting the user with a group of "already existing" records that > > > > > s/he can quickly move through in order, entering just the hours - the > > > > > employee and date information is already present in the record. > > > > > > > > > > hth > > > > > > > > > > > > > > > "Jaybird" <Jaybird[ at ]discussions.microsoft.com> wrote in message > > > > > news:BE378A2C-A089-4FEE-B133-F3BF127F66AC[ at ]microsoft.com... > > > > > > I am attempting to get Access to behave in a way that is similar to > > > Excel > > > > > in > > > > > > some respects... Currently, payroll is using excel to post weekly > > > > > employee > > > > > > hours. The current list of employees is posted with the hours they > > > worked > > > > > > for each day during the week on one worksheet, the next week's hours > > > are > > > > > > posted on the next worksheet and so on. To get an Access application > > > to > > > > > be > > > > > > adopted by payroll, I need it to behave in the same way. I want to > > > design > > > > > a > > > > > > form which pulls up the current list of emploees with the hours they > > > > > worked > > > > > > for that week as one record, with the next week's hours as the next > > > > > record. > > > > > > Currently, Access wants to treat each employee and their hours as > > > > > individual > > > > > > records. You have to scroll down the list of employees one at a time > > > to > > > > > find > > > > > > the weekly hours you want to enter. Obviously, you can design a > > > report > > > > > that > > > > > > organizes the data by employee and the weekly hours after they have > > > been > > > > > > entered, but I want a way to enter this information in a form all at > > > once. > > > > > > Does anyone know how to get Access to behave in this way? > > > > > > > > > > > > > > > > > > > > > > > >

Re: Use entire table as a record in Access? "tina" <nospam[ at ]address.com> 01.03.2006 17:26:19 good luck :) "Jaybird" <Jaybird[ at ]discussions.microsoft.com> wrote in message news:53AC76E1-3FC7-4564-8474-F21E7BA9C8A9[ at ]microsoft.com... [Quoted Text] > Well, actually, it can't be a temp table referring to the original Employee > Hours table because I've appended that to include new dates... Duh! But if > it's a permanent table with dates updateable by a form (with a calendar > control) that refers to the original roster of employees, I think that gets > me close to where I want to be. Wish me luck. > > Jaybird > > "Jaybird" wrote: > > > Silly me! Of course, using the Make Table Query is going to produce similar > > results... It's still referring to the original table for those dates. I > > guess I'm going to have to refresh the criteria for the temp table with the > > new dates and append that. Currently, I'm thinking of creating a form with a > > calendar add in to select the new dates and using that. I'll let you know > > how it turns out! > > > > Jaybird > > > > "Jaybird" wrote: > > > > > Muy facil, Tina! Gracias! However, as you'd expect, the update query wants > > > to update all of the records in the table, not just the ones from the latest > > > week. I'm thinking of several solutions. Tell me which sounds more > > > promising... > > > > > > 1) Set the critera of the append query to soemthing that will limit the > > > records it updates... Can't think of one that will work right now. > > > 2) Instead of appending the same table, how about updating to a temp table, > > > and appending to the original table each time the action is run. That way, > > > only the records from the previous week are modified and added to the > > > original table. > > > 3) Suggestions I haven't thought of? > > > > > > Thanks again... > > > > > > Jaybird > > > > > > "tina" wrote: > > > > > > > just create an Append query to append all the necessary records to > > > > tblEmpHours. then run the query, from a macro or VBA. then requery the form > > > > that's bound to tblEmpHours. you can set the OrderBy property of the form in > > > > VBA. something along the lines of: > > > > > > > > Dim db As DAO.Database, strSQL As String > > > > Set db = CurrentDb > > > > strSQL = db.QueryDefs("AppendQueryName").SQL > > > > db.Execute strSQL, dbFailOnError > > > > Me.Requery > > > > Me.OrderBy = "SomeField, SomeOtherField" > > > > Me.OrderByOn = True > > > > > > > > hth > > > > > > > > > > > > "Jaybird" <Jaybird[ at ]discussions.microsoft.com> wrote in message > > > > news:9CFEF83D-5162-4204-A92E-F4A62BABEAC8[ at ]microsoft.com... > > > > > Aha, > > > > > > > > > > I didn't read your post carefully enough. Sorry. I'll concentrate my > > > > > efforts on looking for an example of just such and appending code. I > > > > > appreciate the response. Of course, my VBA skills stink. Maybe someone > > > > > could walk me through it? Hint, hint... > > > > > > > > > > Jaybird > > > > > > > > > > "tina" wrote: > > > > > > > > > > > you need minimum two tables: > > > > > > > > > > > > tblEmployees > > > > > > EmpID (primary key) > > > > > > FirstName > > > > > > LastName > > > > > > <any other fields that describe an employee.> > > > > > > > > > > > > tblEmpHours > > > > > > HourID (pk) > > > > > > EmpID (foreign key from tblEmployees) > > > > > > WorkDate > > > > > > WorkHours > > > > > > > > > > > > the table design is standard and simple. to do the data entry > > > > efficiently in > > > > > > forms, you can get about as creative as you need to be. exactly how you > > > > set > > > > > > up the data entry form depends on how the user needs to be able to enter > > > > the > > > > > > data. for instance, will the user enter all the hours for all employees > > > > for > > > > > > Monday 2/20/2006, then for Tuesday, etc? or will the user enter all the > > > > > > hours for one employee for each day of the week, Monday 2/20/2006 > > > > through > > > > > > Friday 2/24/2006, then for the next employee, etc? > > > > > > > > > > > > in either case above, you can add code to the data entry form to append > > > > > > records for all employees for each date of the work-week at one time, > > > > and > > > > > > then sort the records according to how the user needs to enter the data, > > > > > > thus presenting the user with a group of "already existing" records that > > > > > > s/he can quickly move through in order, entering just the hours - the > > > > > > employee and date information is already present in the record. > > > > > > > > > > > > hth > > > > > > > > > > > > > > > > > > "Jaybird" <Jaybird[ at ]discussions.microsoft.com> wrote in message > > > > > > news:BE378A2C-A089-4FEE-B133-F3BF127F66AC[ at ]microsoft.com... > > > > > > > I am attempting to get Access to behave in a way that is similar to > > > > Excel > > > > > > in > > > > > > > some respects... Currently, payroll is using excel to post weekly > > > > > > employee > > > > > > > hours. The current list of employees is posted with the hours they > > > > worked > > > > > > > for each day during the week on one worksheet, the next week's hours > > > > are > > > > > > > posted on the next worksheet and so on. To get an Access application > > > > to > > > > > > be > > > > > > > adopted by payroll, I need it to behave in the same way. I want to > > > > design > > > > > > a > > > > > > > form which pulls up the current list of emploees with the hours they > > > > > > worked > > > > > > > for that week as one record, with the next week's hours as the next > > > > > > record. > > > > > > > Currently, Access wants to treat each employee and their hours as > > > > > > individual > > > > > > > records. You have to scroll down the list of employees one at a time > > > > to > > > > > > find > > > > > > > the weekly hours you want to enter. Obviously, you can design a > > > > report > > > > > > that > > > > > > > organizes the data by employee and the weekly hours after they have > > > > been > > > > > > > entered, but I want a way to enter this information in a form all at > > > > once. > > > > > > > Does anyone know how to get Access to behave in this way? > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >

Re: Use entire table as a record in Access? Jaybird 02.03.2006 12:25:29 I think I'm on the right track... Thanks for your help! Jaybird "tina" wrote: [Quoted Text] > good luck :) > > > "Jaybird" <Jaybird[ at ]discussions.microsoft.com> wrote in message > news:53AC76E1-3FC7-4564-8474-F21E7BA9C8A9[ at ]microsoft.com... > > Well, actually, it can't be a temp table referring to the original > Employee > > Hours table because I've appended that to include new dates... Duh! But > if > > it's a permanent table with dates updateable by a form (with a calendar > > control) that refers to the original roster of employees, I think that > gets > > me close to where I want to be. Wish me luck. > > > > Jaybird > > > > "Jaybird" wrote: > > > > > Silly me! Of course, using the Make Table Query is going to produce > similar > > > results... It's still referring to the original table for those dates. > I > > > guess I'm going to have to refresh the criteria for the temp table with > the > > > new dates and append that. Currently, I'm thinking of creating a form > with a > > > calendar add in to select the new dates and using that. I'll let you > know > > > how it turns out! > > > > > > Jaybird > > > > > > "Jaybird" wrote: > > > > > > > Muy facil, Tina! Gracias! However, as you'd expect, the update query > wants > > > > to update all of the records in the table, not just the ones from the > latest > > > > week. I'm thinking of several solutions. Tell me which sounds more > > > > promising... > > > > > > > > 1) Set the critera of the append query to soemthing that will limit > the > > > > records it updates... Can't think of one that will work right now. > > > > 2) Instead of appending the same table, how about updating to a temp > table, > > > > and appending to the original table each time the action is run. That > way, > > > > only the records from the previous week are modified and added to the > > > > original table. > > > > 3) Suggestions I haven't thought of? > > > > > > > > Thanks again... > > > > > > > > Jaybird > > > > > > > > "tina" wrote: > > > > > > > > > just create an Append query to append all the necessary records to > > > > > tblEmpHours. then run the query, from a macro or VBA. then requery > the form > > > > > that's bound to tblEmpHours. you can set the OrderBy property of the > form in > > > > > VBA. something along the lines of: > > > > > > > > > > Dim db As DAO.Database, strSQL As String > > > > > Set db = CurrentDb > > > > > strSQL = db.QueryDefs("AppendQueryName").SQL > > > > > db.Execute strSQL, dbFailOnError > > > > > Me.Requery > > > > > Me.OrderBy = "SomeField, SomeOtherField" > > > > > Me.OrderByOn = True > > > > > > > > > > hth > > > > > > > > > > > > > > > "Jaybird" <Jaybird[ at ]discussions.microsoft.com> wrote in message > > > > > news:9CFEF83D-5162-4204-A92E-F4A62BABEAC8[ at ]microsoft.com... > > > > > > Aha, > > > > > > > > > > > > I didn't read your post carefully enough. Sorry. I'll > concentrate my > > > > > > efforts on looking for an example of just such and appending code. > I > > > > > > appreciate the response. Of course, my VBA skills stink. Maybe > someone > > > > > > could walk me through it? Hint, hint... > > > > > > > > > > > > Jaybird > > > > > > > > > > > > "tina" wrote: > > > > > > > > > > > > > you need minimum two tables: > > > > > > > > > > > > > > tblEmployees > > > > > > > EmpID (primary key) > > > > > > > FirstName > > > > > > > LastName > > > > > > > <any other fields that describe an employee.> > > > > > > > > > > > > > > tblEmpHours > > > > > > > HourID (pk) > > > > > > > EmpID (foreign key from tblEmployees) > > > > > > > WorkDate > > > > > > > WorkHours > > > > > > > > > > > > > > the table design is standard and simple. to do the data entry > > > > > efficiently in > > > > > > > forms, you can get about as creative as you need to be. exactly > how you > > > > > set > > > > > > > up the data entry form depends on how the user needs to be able > to enter > > > > > the > > > > > > > data. for instance, will the user enter all the hours for all > employees > > > > > for > > > > > > > Monday 2/20/2006, then for Tuesday, etc? or will the user enter > all the > > > > > > > hours for one employee for each day of the week, Monday > 2/20/2006 > > > > > through > > > > > > > Friday 2/24/2006, then for the next employee, etc? > > > > > > > > > > > > > > in either case above, you can add code to the data entry form to > append > > > > > > > records for all employees for each date of the work-week at one > time, > > > > > and > > > > > > > then sort the records according to how the user needs to enter > the data, > > > > > > > thus presenting the user with a group of "already existing" > records that > > > > > > > s/he can quickly move through in order, entering just the > hours - the > > > > > > > employee and date information is already present in the record. > > > > > > > > > > > > > > hth > > > > > > > > > > > > > > > > > > > > > "Jaybird" <Jaybird[ at ]discussions.microsoft.com> wrote in message > > > > > > > news:BE378A2C-A089-4FEE-B133-F3BF127F66AC[ at ]microsoft.com... > > > > > > > > I am attempting to get Access to behave in a way that is > similar to > > > > > Excel > > > > > > > in > > > > > > > > some respects... Currently, payroll is using excel to post > weekly > > > > > > > employee > > > > > > > > hours. The current list of employees is posted with the hours > they > > > > > worked > > > > > > > > for each day during the week on one worksheet, the next week's > hours > > > > > are > > > > > > > > posted on the next worksheet and so on. To get an Access > application > > > > > to > > > > > > > be > > > > > > > > adopted by payroll, I need it to behave in the same way. I > want to > > > > > design > > > > > > > a > > > > > > > > form which pulls up the current list of emploees with the > hours they > > > > > > > worked > > > > > > > > for that week as one record, with the next week's hours as the > next > > > > > > > record. > > > > > > > > Currently, Access wants to treat each employee and their hours > as > > > > > > > individual > > > > > > > > records. You have to scroll down the list of employees one at > a time > > > > > to > > > > > > > find > > > > > > > > the weekly hours you want to enter. Obviously, you can design > a > > > > > report > > > > > > > that > > > > > > > > organizes the data by employee and the weekly hours after they > have > > > > > been > > > > > > > > entered, but I want a way to enter this information in a form > all at > > > > > once. > > > > > > > > Does anyone know how to get Access to behave in this way? > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >

RE: Use entire table as a record in Access? Jaybird 30.03.2006 14:12:02 For those of you searching through these postings to find the answers to similar questions, I'll just tell you what I've found out subsequently... Best way to make this happen is to set up your employee data on one table and your hourly data on another. Make sure that your hourly data is identified by employee ID and by date. In your table, use the long form of the date. You may want to use some aspect of it in the future that you can't predict now. Tie these two tables together in a query that includes all records from your employees table and only those records from the hours table where the joined fields are equal. Don't worry about the result of this query. Save this query and and use it to create a crosstab query using the crosstab query wizard. Follow the instructions. Set your interval as daily. To observe one week at a time, you'll need to set some parameters. In order to ensure that the columns are in the sequence you desire, you'll need to do some other things. In order to make your column headings appear as "Monday", "Tuesday",... etc instead of 3/27/06, 3/28/06,... set the resulting expression in your column heading, to "expr1: Format([daily_hrs],"dddd"). I'm still working on this, so if you want updates on how to do this, I should be able to give them to you in the future. Likely as not, nobody will read this. Sigh... At least I learned something. Jaybird