Karen-
Yes, your tables are designed incorrectly.
Can I assume a Rep works in only one Department? If so, your tables should be:
tblDepartments:
DepartmentID (PKey), DepartmentName, etc…
tblReps:
RepID (PKey), DepartmentID, RepName, etc…
tblActivityReps:
ActivityID (PKey 1), RepID (PKey 2), DateAssigned, etc…
tblActivities:
ActivityID (PKey), ActivityDate, other info relevant to activities…
With this layout, an Activity can have multiple reps, and a rep can be associated with multiple activities.
To find all the reps from a given department who are "available" on a selected date, do this:
PARAMETERS [Enter department ID:] Integer, [Enter Activity Date:] DateTime;
SELECT tblReps.*
FROM tblReps
WHERE tblReps.DepartmentID = [Enter department ID:]
AND tblReps.RepID NOT IN
(SELECT tblActivityReps.RepID
FROM tblActivityReps INNER JOIN tblActivities
ON tblActivityReps.ActivityID = tblActivities.ActivityID
WHERE tblActivities.ActivityDate = [Enter Activity Date:])
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
(Paris, France)
On Jul 29, 2015, at 2:10 PM, Karen k2j1203@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Hi John. I am sorry for the late response. Work has been crazy and this is the first day I'll get to work on this.
A sales rep is available if he doesn't have any activity on a given day.
The tblActivities has the Dept info because a sales rep from any of four departments can bring in an activity. So we keep the originating rep and their Dept with the activity info. The tblRepCmbd table is the list if all reps in all departments. This is in a separate table because one activity can have more than one rep.
Did I set up my tables wrong?
Karen
Sent from my Verizon Wireless 4G LTE DROID
"John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:
Karen-
Clear as mud. 😃
What defines an available sales rep? Is the rep available if there is no activity logged for the specified date?
What is in tblRepsCmbd? It seems strange that you have outer joined this with activities, yet it has a rep name in it and no department info. Isn't there a separate table that lists all reps and their departments?
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
(Paris, France)
On Jul 26, 2015, at 10:37 PM, k2j1203@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Hello everyone. I hope everyone is doing well. I'm stuck on a query that I'm trying to create.
I want to create a query where an user can type in a date and department and have the query results be all of the sales reps that are available for that date. I have two tables: tblActivities and tblRepsCmbd. They are left joined on tblActivities.ActivityID_PK to tblRepsCmbd.ActivityID_FK. The field that I want to have the user enter the date is tblActivities.ActivityDate. The field for department is tblActivities.Dept. The sales reps names are in tblRepsCmbd.RepName field.
Let me know if you need more information.
Thanks so much.
Karen
__._,_.___
Posted by: John Viescas <johnv@msn.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (5) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar