Thrown in at the deep end!!

[Denz]

I am trying to create a contact sheet for a helpdesk, we have various floor support teams over the country, and in various buildings, on various floors. So for example, building ‘A’ may have 8 Floors and have 5 Departments on each floor, with a different team for each department. And the same for other building, except different floor numbers and different departments.
Could someone give me a few pointers on how to get up the database tables and forms? (I have read somewhere how to get combo boxes to be hierarchical, and this would work really well, but I’m unsure of how to set up the tables).

Any help would be greatly appreciated

Dave

 

[outpatient]

I'll give it a try, but i'm not a pro, so its not the gospel

Here's my recommendation for the basic structure of the tables

Building Floor Department
___________________________________

BuildID FloorID DeptID
buildesc floordesc deptdesc
buildID floorID

I did a combobox for a help desk which let users select a major category and then the sub-category would populate accordingly. I put this in the ROW SOURCE field in the properties of the second combo box(sub cat):

SELECT Subcategory.SubID, Subcategory.Title, Subcategory.CategoryID FROM MajorCategory INNER JOIN Subcategory ON MajorCategory.CategoryID = Subcategory.CategoryID WHERE (((Subcategory.CategoryID)=[forms]![Customers]![problem entry].[form]![ComboMaj])) ORDER BY Subcategory.Title;

I think what I did was generate a combo box with the values I wanted, which produced the part upto the second = sign.

After the = tells access how to filter, so you would say something like
"WHERE (((Floor.FloorID)=[forms]![MYFORM].[BuildingCombo]&quot)


My SQL Statement was for a subform so it may be different from yours. I'm not sure if the '.' inbetween [myform] and [buildingcombo] should be a '.' or a '!'.

Do some searches on referring to controls and you should find some stuff about reffering controls on forms(the . and !)


Hopefully this helps out. -Sam Greene

 

[Denz]

Although i'm at home looking at this now, it looks god, and i'm looking forward to playing with that tomorrow! However will this allow the end user to select building (A,B,C etc) then the floors (only listing this avalible to that building) then the departments (1,2,3,4 etc), which will then display a suppot group?
i.e. 3 combos?

Thanks for your help so far!

Dave

 

[outpatient]

Yes, You'll have the building combobox that won't depend on anything, then your floor combo will filter based on the buildingID and finally your dept combo will filter based on FloorID. Just with the first two to get it going. If you need any help with the SQL, let me know Sam Greene
anyone in need of a rock induced headache? if so

http://www.mp3.com/outpatient

 

[outpatient]

Heres the tables again, they looked a little skewed.

Building Floor Department
___________________________________

BuildID FloorID DeptID
buildesc floordesc deptdesc
buildID floorID

Sam Greene
anyone in need of a rock induced headache? if so

http://www.mp3.com/outpatient

 

[Denz]

ok, i've got the Building refering to the floors, but i'm a little confused about the last table/combo, let's assume you have a department called 'Credit' and they are based in building 'A' on floor 5 and on building 'B' floor 2, but they have 2 completely different support groups to support them... would this use the same table setup, or would i need to make some changes to the table set up you suggested?

Thanks

Dave

 

[outpatient]

Ok,
Let me make sure I got this right. Each department can be divided into divisions that have separate locations. This setup assumes a division can have only 1 support group. If they can have more than one, you'll have to add another table called an associative entitity to link the support groups and the divisions. Like this plus all the tables below

associative
__________
SuppID
DivID

But if a division can have only one group , go with this:


Building Floor Division Department SupportG
_________________________________________________________
BuildID FloorID DivID DeptID SuppID
buildesc floordesc divdesc DeptDesc SuppDesc
buildID floorID
DeptId
SuppID
Hopefully this will get you started
Sam Greene
anyone in need of a rock induced headache? if so

http://www.mp3.com/outpatient