Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Table Structure in Access

Posted on 2012-03-30
4
Medium Priority
?
323 Views
Last Modified: 2012-06-22
I am creating an access database to create weekly reports on the activities of employees in our company. I have most of my tables set, but have run into a complication with one and am hoping to get some advice.

My tables are as follows:
  tbl_Employees (fields are: empID, empFirst, empLast)
  tbl_TaskOrders (fields are: toID, toNo, toTitle)
  tbl_SubTaskOrders (fields are: stoID, stoNo, stoTitle, toID)

The way this works is that there are task orders (categories that activities and people fit into for purposes of invoicing), and within the task orders are subtask orders. For each subtask order there are either individuals or groups of people that work on it.

What I originally did was include a field in the Subtaskorder table called "stoMembers" and this was a multiple item, look up field that would contain the employee ID numbers of all those on the subtask orders. However, that is not working and I suspect it is poor design.

Does anyone have any advice for how this should be designed?

Thank you!
0
Comment
Question by:Megin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 9

Accepted Solution

by:
OCDan earned 400 total points
ID: 37789374
personally I would have another table STOGroups with the fields:
STGID, stoID, empID

So for one task you would end up with a list like this:

stgID      stoID      empID
1                  1                  253
2                  1                  276
3                  1              289
4                  1                   246

This links to your sto table via stoID and then you have 4 employees assigned to one sto.
0
 
LVL 5

Assisted Solution

by:hafeezmca
hafeezmca earned 400 total points
ID: 37789420
Hi,
You can acheive this by the following structure:

tbl_Employees ( empID -- Primarykey , empFirst, empLast)
  tbl_TaskOrders (toID -- Primarykey, toNo, toTitle)
  tbl_SubTaskOrders ( stoID -- Primarykey, stoNo, stoTitle, toID -- Foreign Key)

As you mentioned each subtask may have multiple employes you need to create one more table like below:

Create one more table say EmpSubTasks(empsubtaskId -- Primarykey, stoId -- Foreignkey, EmpId -- Foreignkey)

Note: empsubtaskId is not mandatory, you can even move ahead with stoId & Empid for the new table. But in case if you add two records with same value delete operation will be difficult.

Hope you will be able to achieve your task with the above mentioned solution.
0
 
LVL 9

Expert Comment

by:OCDan
ID: 37789432
I'm guessing you didn't mean to just post the exact same answer as me but changing  stgid > empsubtaskId?
0
 

Author Closing Comment

by:Megin
ID: 37789466
Thank you for the answers, both of you! I am trying it out now. It makes sense to me.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question