General information
Category:
Forms & Form designer
>>Can anyone point me in the right direction with my dilemma below?
>>
>>OBJECTIVE:
>>Combine information from two tables distinctly into one grid. Then send changes to one of the tables.
>>
>>WHAT I THINK SHOULD HAPPEN:
>>User tracks time spent daily on projects. This information is stored in a table called hours.
>>User also has a list of favorite projects. This is stored in table called active.
>>
>>I have a form that allows the user to increment through the days of the years and see their hours in a grid displaying the results of a requeried view that pulls information from the hours table based on date.
>>
>>When the user decides to edit/add hours they press the edit button. This hides the browsing grid to display the edit grid. This grid should include a list of all the hours already entered for that day, as well as all the list of favorite projects from active.
>>
>>My idea is to query hours and create table1. Then query active based on userid to create table2. Then select table1 and append from table2. Then query table1 to select distinct the projects and send them to an array. Then popluate the grid with the array. Then in the afterRowColChange Event I will identify if the hours have changed, and if they have, seek it in the hours table and replace or insert it. Then when they click done, delete table1 and table2, hide the edit grid, requery the hours view and show the browsing grid.
>>
>>Will this work? Is there a better way?
>
>I'm not sure why you want to join the Active table to the Hours table. Do they have a common key?
>If so, you can create an updatable view and make the change directly to the view without creating temporary cursors and arrays.
Thank you for your help!
They do have a common key -- empid
I am combining the tables to display a list of favorite project even if hours have not been entered for those favorites yet, because the active table holds the list of favorite projects and the hours table has the list of only those projects who have time worked on the given day. I don't want to add time to the favorite projects in the active table. I want to either add time to existing projects listed in the hours table or I want to insert a new project for that day into the hours table if the user adds time to a task that was not already there.
table structure
ACTIVE HOURS
activeid hoursid
empid empid
project project
account account
activity activity
groupid groupid
date (empty) date (specific)
time (empty) time (hours worked)
I tried making the view but it came out like this:
activid hoursid empid_a empid_b project_a project_b time_a time_b
Rather than place them distinctly "under" each other. The view placed them "beside" each other.
Maybe there is a better way to make this work. Any ideas?
Previous
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only