MS Access 2007 - Need DB Help

[SSG Snuggle Bunny]

I'm not a programmer by any stretch of the imagination nor have I ever presented myself as one but evryone around here seems to think I'm some sort of computer genius because I can pump MS Word for all it's worth and do half decent on Photoshop. Efforts on my part to disabuse them of their fantasies are dismissed as my being overly-modest (as if THAT would ever happen!).

One of the branch chiefs wants a spreadsheet to track the progress of his project managers as they handle their 30 some-odd tasks. I told him Excel is not the animal he wants but he is tone-deaf to my pleas and has extreme psycholofgical hurdles to Access. Whatever, I'm perfectly content to ignore what people ask for in order to give them what they need/truly want but I lack the know-how to get over the biggest hump in this project.

helpzz!

A. I've gotten fields to look up other tables as a drop-down but here is what I need to happen:

When selecting which position the employee works in I want a drop-down menu that will present job positions depending upon which branch the employee works in, i.e. If working in Branch 1 positions A, B or C are presented but if the emplyee works in Branch 2 then positions X, Y and Z are presented in the drop-down.

B. This is the biggie.

The projects here go through TWENTY-EIGHT defineable phases. I need to track:

1. When each phase is expected to begin (I'm using a date picker)

2. When said phase is completed (date picker)

3. Track whether or not the phase is overdue based on whether or not it has been completed based on point 1.

I'm not sure how I want to write the table. Should I have a seperate table for the target dates and another for the completion dates then add a third for the on-time/overdue status or just one hugenormus table (28 x 3 = no. of fields)

--AND--

My google-fu is really helping when it comes to writing code for checking if date is overdue.

[LC EFA]

A is fairly easy - You create a table (lets call it positions) containing [ID , Branch , Position] - and the contents of your drop down should be derived similar to SELECT position FROM Positions WHERE Positions.branch=Employee.Branch

B - I might do by creating a table (lets call it JobProgress) containing [ID , JobID , StatusID, DateStarted , DateCompleted] and link it on JobID to the table containing your basic Job details. Status ID is linked to a table containing [ID , Status Description].

[SSG Snuggle Bunny]

I hate smart people.

[Schadenfreude]

I think you may need to maintain planned and actual dates in the job progress table to track overdue status (ie late start)  Just a thought.

[NHSparky]

You sure you wouldn't prefer to do that in MS Project?

[SSG Snuggle Bunny]

You sure you wouldn't prefer to do that in MS Project?

I'm debating that but I would have to make a helluva sales pitch for them to spring for new software and to take the time to learn it.

[SSG Snuggle Bunny]

A is fairly easy - You create a table (lets call it positions) containing [ID , Branch , Position] - and the contents of your drop down should be derived similar to SELECT position FROM Positions WHERE Positions.branch=Employee.Branch

So what am I making the table look like?

Is it:

ID	Branch	Position
1	Admin	Supervisor
2	Admin	Assistant
3	Budget	Supervisor

Sorry to be so n00bish.

[Thor]

Microsoft Project is normally included in the Office Pack. All you need to do is look at what's installed under Microsoft Office. If it's there, it shouldn't be a tough sale.

[SSG Snuggle Bunny]

Microsoft Project is normally included in the Office Pack. All you need to do is look at what's installed under Microsoft Office. If it's there, it shouldn't be a tough sale.

Alas, 'tis not the case.

These are gov't computers after all. There's no reason to believe the gov't would allow itself to acquire at no additional cost the exact same things available to the average ass-scratching finger-sniffing paean.

[LC EFA]

So what am I making the table look like?

Is it:

ID	Branch	Position
1	Admin	Supervisor
2	Admin	Assistant
3	Budget	Supervisor

Sorry to be so n00bish.

That's the general idea ..

You have [ID (Integer, Primary Key) , Branch (text) , Position (Text) ]
so you can end up with a table that looks like

[1 , Sales , Droid ]
[2 , Sales , Manager]
[3 , Research , Lackey]
[4 , Research , Manager]
[5 , ...]

That way you can run a query - SELECT ID,Position FROM Positions WHERE Branch=Sales - and it should return

[1 , Droid]
[2 , Manager]

You Store the value of the index field (ID) in the Employee Table, so that when you display the employees data - the Query to show their correct role is SELECT branch,Position FROM positions WHERE Employee.Position=Positions.ID

Assuming Employee.Position = 1

[Sales , Droid]

Should be the result.

Sorry if I'm not very clear - It's been quite some time since I used Access/SQL and I'm not the best at explaining these things in the first place.