I am making that same database and I am expanding. We are across the nation, each division has a main call number, like ABC. Then within that region we are broken down even more so ABCA, ABCB, ABCC, etc. I am on say ABCA, so all my team is ABCA, but we are ABC also. I have a table "tblTeam" with ABC,ABCA,ABCB, as the sole field, and PK. Now I can expand ABCA, and see all us, can we make it so when I add ABCB, I can expand ABC and both groups are one, but still have them listed under ABCA & ABCB? I think I only can if I make another field
Sorry sorry What is above is the Team table. It is linked to Employees. So when team is opened and expanded, the employees table is the subdatasheet. So I need all employees under ABC and ABCA for example.
If I understand correctly the teams can be organized into an hierarchical tree-structure. Each employee can be a member of zero-to-many teams.
Have you explored the use of MSComctlLib.TreeCtrl.2 for the graphical representation? This control has to be populated each time it is loaded but if you use it then it dictates to some extent how to organize and store your team member data.
No, everybody is under team ABC and a sub team, ABCA. They are normally just called by the sub team. But sometimes it is nessicary to view everybody as one huge clump. Hence why I need them listed under ABC AND like ABCA.
If I understand it correctly, each employee belongs to a division and each division belongs to a region. Each region may have many divisions and each division may have many employees.
You need a region table (tblRegion), a division table (tblDivision) and an employee table (tblEmployee). Put a field (RegionID) in tblDivision to link it to the tblRegion and put a field (DivisionID) in the tblEmployee to link it to tblDivision. When running your query link all three. If constructed correctly, a criteria of RegionID "ABC" will show all divisions within that region and all employees in that region.
That's sort of the idea. The table you suggested would look like this in datasheet view....if I understand you correctly.
In your idea, the employee belongs to the region just as much as the division. I am also only dealing with one region.
[EDIT]: Ok, I have it now. Hopefully I won't need to hit every region now. We are lets say international...about 20-30 regions, and each region has 5-20 divisons
[EDIT2]:I re-looked it over and it isn't displaying right
yet another one of my infamous devil's advocate posts....
What exactly is this for? Just displaying employees?
Would you consider a different format?
If so, consider this:
A form with two listboxes in the form header that control the filter for the form. The first listbox is Regions, the second is divisions. You could also code an "all" option for both listboxes. When the user selects a region, the form filters to that region, and the listbox displays divisions for the region. If/when the user selects a division, then the results are filtered further.
As a bonus, this method would also allow multiple selective divisions to be displayed concurrently, using the multi-select properties of the listbox.
I think this would be a better format and easier for your users to handle.
ok. but how will this go in accordance with that other thread
How wouldn't it? In the other thread you need to pull out team leaders, that's a seperate issue entirely. You could kill two birds with one stone and have a team leaders list box populated based on the first two listbox selections as well.