You have repetition with an employee AND a supervisor table because a supervisor is a type of employee. I would add a SupervisorID field to the employee table which links the current record to the record of the supervisor. If the field is null, the Employee doesn't have a supervisor.
Also, for each record in your Issues table you have three types of issues which have exactly the same structure. In programming and data structures you always want to avoid repetition, avoid repetition, avoid repetition. The Issue table would be much more efficient if it has a structure more like ...
Code:
[B]tIssue[/B]
IssueID (PK)
EmployeeID (FK)
IssueTypeID (FK) [COLOR=green]' specifies whether the issue is TP, Verbal or CRL[/COLOR]
IssueDate
Received
Completed
BCSCompleted
And if you use date/time types for the Received, Completed and BCSCompleted fields, then you can determine outstanding issues by counting Nulls, for instance...
Code:
SELECT COUNT(*) As PendingIssueCountByEmployee
FROM tIssue
WHERE EmployeeID = <some_ee_id>
AND Completed IS NULL
AND BCSCompleted IS NULL
Good Morning. Thank you for the suggestions. I do have a few questions though. Should I delete the Supervisor Table all together? Also, what is the differenct from the Issue date and received date. In my case they would be the same, so I don't think I need that field. And one more, I think I should make the FBCS a "yes/no? since we only need to determine what percentage of the closes is FBCS. Do you think that would work?
Here is another try at this database. I set this one up based on your suggetions. Let me know what you think. have some queries written but they are really basic.
OK, have a question about a form that I made based on query. I put in the query that you need to enter the Associate name to open the form. However, the query opens nothing when you use the last name. You have to use the employee id number. How can I set this up so that I can enter the last name to pull that persons open issues. I have attached a copy of the database. the Query in question is "TblIssues by Writer" and the form has the same name.
OK, have a question about a form that I made based on query. I put in the query that you need to enter the Associate name to open the form. However, the query opens nothing when you use the last name. You have to use the employee id number. How can I set this up so that I can enter the last name to pull that persons open issues. I have attached a copy of the database. the Query in question is "TblIssues by Writer" and the form has the same name.
(unfortunately, your new attachment is still a replica...)
FYI: using a parameter [Enter Beginning Date] and [Enter End Date] in your query is not the preferred method (i know the books show you to do this, but let me show you a more robust method):
create a form ("frmDatePicker") with two unbound dropdowns.
name the first combo as cmbDateStart and the second as cmbDateEnd.
now for the control source in each, click on the elipsis "..." and make the source of your dropdown the field where you store your start date, the same in the second dropdown (it's ok if there is only one field. in that case, use that one as the source to both your dropdowns)
now, in your query, instead of [enter start date], type in
Code:
>= Forms!frmDatePicker.cmbDateStart
(which means "return all records with a start date that is greater than or equal to the date chosen in the control called "cmbDateStart" on the form called "frmDatePicker")
and for the [enter end date]:
Code:
<= Forms!frmDatePicker.cmbDateEnd
for a parameter where you want to return only one date, leave off the "<=" and ">=" bits.
if you have just one field to find a start and end date, then you would use the "Between" operand:
Code:
Between forms!frmDatePicker.cmbDateStart And forms!frmDatePicker.cmdDateEnd
it also helps to ensure that your controls (on the form and the report) as set to "short date" format, for easy reading, unless you really need the "time" details also.
this form-based date method is better because the user may not know what dates are available to choose from, and it's frustrating to choose dates and discover no records are returned because the dates you chose are two years prior to data collection.
the other frustration the form-based date method prevents, is the user entering the date with the wrong format or wrong "directionality", then having access error out and the user having to start all over again (e.g., "2009-12-09" or "12/09/2009" instead of "12/09/2009") - having the combo boxes with the dates 'pre-entered', as it were, makes life easier for user and developer alike (easier for developer to test that everything works).
Hi Wiklendt, I hope you are still around. Have a question about above. Can this work when I need to update 8 queries with the same start and end date? I have a report that needs 8 queries to get the results, so I would need to update 8 queries with the same start and end date. Hope you can help.
Can this work when I need to update 8 queries with the same start and end date? I have a report that needs 8 queries to get the results, so I would need to update 8 queries with the same start and end date.
yeah, sure - why not? just put the appropriate parameter condition into each of the queries and they will all draw their date values from the date picker form. just remember - the date picker form MUST the open for the query to get the data.
if you already have this working for one query, do the same to your others and you will see they all get their values from it.
i believe there are other methods to handle date picking, which requires a bit more VBA knowledge (or just perseverance in trying code you find in the forums), but i'm not up to speed on them (never used them myself) - but feel free to browse around the forums for these.
however, the method i described ought to work for you. good luck, let us know how it goes!
SOrry to bother you again Wiklendt, but I cannot get this datePicker thing to work. I am not sure what I am doing wrong. I have followed your instructions, but still cannot get it to work. I have a form and named it frmDatePicker. I want to attach it to the bjwQry_CompletedTruePres Select Query. I am attaching a copy of the database so that you may look at what I have done. You will see that I have made some nice progress on this. Again, you assistance is greatly appreciated.
SOrry to bother you again Wiklendt, but I cannot get this datePicker thing to work. I am not sure what I am doing wrong. I have followed your instructions, but still cannot get it to work. I have a form and named it frmDatePicker. I want to attach it to the bjwQry_CompletedTruePres Select Query. I am attaching a copy of the database so that you may look at what I have done. You will see that I have made some nice progress on this. Again, you assistance is greatly appreciated.
your date combos had no data in them. i've redone this for you and included a control source for your combos. also, i have provided both issue and completed date combos, depending on what you want to search (this can be made more elegant, but would require a lot of hand-coded SQL, which i'd really rather avoid!)
the combos i've also renamed to reflect which start and end you want (issued/completed) e.g., cmbDateStart_Comp.
Great, thanks. I did remove what I had because it did not work. That is why the control source was blank. ANyway, this looks good. Now, I just need to add a button to open the report, right?
in which case i can't help you determine what the original problem was. in any case, you can examine the form i made to see how it is different to what you were doing - to me it looked like it should have worked (if you had dates in the combos).
i've seen people use combos or listboxes to list the queries/reports they have/want and allows you to have one button and a selection of which report/query you want to open...
i haven't used this method myself, so you'll need to search the forums for that if you want to use it.
