MS Access How can I have a drop down in a form control the user input in the datasheet below?
I have a project/task sample database attached. The form frmEmployeeWeeklyInput includes a datasheet of projects and a Select Employee drop down. How can this drop down control the datasheet input for the employee & filter the records from the tblEmployeeweekyinput table?
I.E.
-User selects name from Select Employee drop down.
-Users records from the tblEmployeeweekyinput are displayed below in the datasheet (frmEmployeeweeklyinputSub
-When user inputs a new record there name is recorded in the WEEKLYEMID field in the tblEmployeeweeklyinput table.
One of the goals here is for the user not have to select his/her name for each record input.
TimeInputTest.accdb
I.E.
-User selects name from Select Employee drop down.
-Users records from the tblEmployeeweekyinput are displayed below in the datasheet (frmEmployeeweeklyinputSub
-When user inputs a new record there name is recorded in the WEEKLYEMID field in the tblEmployeeweeklyinput table.
One of the goals here is for the user not have to select his/her name for each record input.
TimeInputTest.accdb
ASKER
I need the employee inputted in the tblEmployeeweeklyinput table.
The version you have will not display separate records per employee. 2 employees could input on the same record which is not desired.
The version you have will not display separate records per employee. 2 employees could input on the same record which is not desired.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Pat,
- Create one table with the 7 days of the week
- How do I combine the tables for employee input? (for the datasheet in frmEmployeeWeeklyInput)
3. tblEmployeeweeklyinput violates first normal form. You have a repeating group - 7 week days. There should be a separate table with one row per weekday, per task which leaves the input table with only the first 3 fields and the last field.
- Create one table with the 7 days of the week
- How do I combine the tables for employee input? (for the datasheet in frmEmployeeWeeklyInput)
For reporting, you can simply use a crosstab which will give you all the days as columns but for data entry, each row should be for a single date (day) and a single project. So if you worked on 5 projects on Monday, you'll have 5 rows for Monday.
If you are feeling adventurous, you can try to adapt my "Bound denormalized form example" for this purpose. It translates very well to what you want to do. Using it, you would have one row on the form for a project with 7 columns for time entry. The data will actually be stored as I described earlier. You will need a small amount of code behind the controls because the actual date f the week day will need to be calculated. The form header will have either week ending or week starting date and using that anchor, the date of the weekday must be calculated.
I'll attach the database so you can see how it works. The relevant example would be expenses by month which you would translate as projects by day.
Sorry, I'm getting some kind of "stream" error and cannot upload the file so I attached a PPT that explains how it works. If you think you understand the concept (the PPT includes the queries for each month (day in your case) as well as the code behind the month(day) control so you see how the non-visible value is calculated), then send me a PM asking for "bound denormalized sample" so I'll know what you are talking about.
Bound-Denormalized-Forms.pptx
If you are feeling adventurous, you can try to adapt my "Bound denormalized form example" for this purpose. It translates very well to what you want to do. Using it, you would have one row on the form for a project with 7 columns for time entry. The data will actually be stored as I described earlier. You will need a small amount of code behind the controls because the actual date f the week day will need to be calculated. The form header will have either week ending or week starting date and using that anchor, the date of the weekday must be calculated.
I'll attach the database so you can see how it works. The relevant example would be expenses by month which you would translate as projects by day.
Sorry, I'm getting some kind of "stream" error and cannot upload the file so I attached a PPT that explains how it works. If you think you understand the concept (the PPT includes the queries for each month (day in your case) as well as the code behind the month(day) control so you see how the non-visible value is calculated), then send me a PM asking for "bound denormalized sample" so I'll know what you are talking about.
Bound-Denormalized-Forms.pptx
ASKER
Pat,
I could not open your .pptx file - I receive an error due to corruption.
My customer for this project wants to input in a datasheet view, with a view of filtering weekly via a date selector, this may be a little inefficient however I would like to satisfy the customer. Should I keep my existing format for the (tblEmployeeweeklyinput) table or would you suggest another method?
I could not open your .pptx file - I receive an error due to corruption.
My customer for this project wants to input in a datasheet view, with a view of filtering weekly via a date selector, this may be a little inefficient however I would like to satisfy the customer. Should I keep my existing format for the (tblEmployeeweeklyinput) table or would you suggest another method?
The download opened for me but I saved it as a PDF in case you have an old version of PP.
Leaving the design as you have it will result in analysis and reporting issues later on. Take a look at the pdf and see if you understand the concept. It really takes very little code to implement and you will have fewer queries than I did because I was doing an example for 12 months.
Usually customers can be convinced by cost if you can show them the difference. Remind the Boss that he still will get to look at reports that replicate the old data entry forms because you can make them very easily with crosstab queries. The only thing that would change would be the actual data entry form. The data entry form is the smallest part of the problem. I can see where it sure looks simple but until you get on with the rest of what the app has to do, you won't realize the impact of the improper design. This is a case of 7 columns vs 1 and here's an example of two different queries that do the same thing.
Select EmpID, ProjectID, Nz(Mon,0) + Nz(Tue,0) + Nz(Wed, 0), +Nz(Thrus, 0) + Nz(Fri, 0) + Nz(Sat, 0) + Nz(Sun,0) As WeekHours
From tblHours Where WeekStartDT = Forms!yourform!WeekStartDT
Select EmpID, ProjectID, Sum(DayAmt) As WeekHours, Avg(DayAmt) As AvgDailyHours
From tblWeeks Left Join tblHours Where tblHours.WeekStartDT = Forms!yourForm!WeekStartDT
Guess what you have to do to the first query to make an average? Just try it. I'll give you a hint - you need to count the number of days where there are hours worked and use that as the divisor. With the second query, access works this out. But it is important that you understand the ramification of the non-normalized schema in order to sell the client. Of course, if you understand my example, you can create the normalized tables but still give him an unnormalized data entry form. But trust me. You really need to add the average to the first query to fully understand the impact of a repeating group on even simple analysis operations.
Bound-Denormalized-Forms.pdf
Leaving the design as you have it will result in analysis and reporting issues later on. Take a look at the pdf and see if you understand the concept. It really takes very little code to implement and you will have fewer queries than I did because I was doing an example for 12 months.
Usually customers can be convinced by cost if you can show them the difference. Remind the Boss that he still will get to look at reports that replicate the old data entry forms because you can make them very easily with crosstab queries. The only thing that would change would be the actual data entry form. The data entry form is the smallest part of the problem. I can see where it sure looks simple but until you get on with the rest of what the app has to do, you won't realize the impact of the improper design. This is a case of 7 columns vs 1 and here's an example of two different queries that do the same thing.
Select EmpID, ProjectID, Nz(Mon,0) + Nz(Tue,0) + Nz(Wed, 0), +Nz(Thrus, 0) + Nz(Fri, 0) + Nz(Sat, 0) + Nz(Sun,0) As WeekHours
From tblHours Where WeekStartDT = Forms!yourform!WeekStartDT
Select EmpID, ProjectID, Sum(DayAmt) As WeekHours, Avg(DayAmt) As AvgDailyHours
From tblWeeks Left Join tblHours Where tblHours.WeekStartDT = Forms!yourForm!WeekStartDT
Guess what you have to do to the first query to make an average? Just try it. I'll give you a hint - you need to count the number of days where there are hours worked and use that as the divisor. With the second query, access works this out. But it is important that you understand the ramification of the non-normalized schema in order to sell the client. Of course, if you understand my example, you can create the normalized tables but still give him an unnormalized data entry form. But trust me. You really need to add the average to the first query to fully understand the impact of a repeating group on even simple analysis operations.
Bound-Denormalized-Forms.pdf
TimeInputTest---Copy--2-.accdb