Table Design Help Required...

[rd.prasanna]

Hi All,

I'm New for DBMS and access.
I'm preparing a tool in access. need some help on the same.



below is the table which is used in excel now...
but my difficulty is that, how can i implement the same in access.
kindly help me designing the table for this.



thanks in advance.
Prasanna R.D

[June7]

Your image did not get posted properly, can't view it.

Start with gaining an understanding of relational data normalization http://support.microsoft.com/kb/283878

Also:
http://www.techrepublic.com/article/...able-analyzer/
http://office.microsoft.com/en-us/ex...010342703.aspx

[rd.prasanna]

Thank for the links June...


have uploaded the table kindly have a look...

Sl No	Process	Process Type	Scope	Man Hour	Extra Hour	Total	Scope	Step1	Step2	Step3	Step4	Step5	Step6	Step7	Step8	Total	Earned Valuw
1	asdf	xyz	Cum&	56	100	156	Cum&	0%	20%	40%	50%	70%	80%	90%	100%
1	asdf	xyz	Per	66	71	137	Per	0	2	2	8	10	2	1	3
1	asdf	xyz	Pln	93	77	170	Pln	3-Aug-13	5-Aug-13	7-Aug-13	15-Aug-13	25-Aug-13	27-Aug-13	28-Aug-13	31-Aug-13
1	asdf	xyz	Act	61	67	128	Act	3-Aug-13	5-Aug-13	7-Aug-13	15-Aug-13	25-Aug-13	27-Aug-13	31-Aug-13	6-Sep-13
1	asdf	xyz	FC	67	72	139	Forecast

[recyan]

Till someone comes along, a few questions, which might perhaps be helpful for others, to help you :
1) Why is Sl No 1 for all the records ?
2) Which fields together determine a unique record ?
3) Why do you have two Scope fields ?
4) What do Step1, Step2,...Step8 indicate ?
5) Why do you have different value types for the Steps, for eg. percent;Number; Date; Null ?

Thanks

[June7]

Here's what I get out of that:

tblProcess

SINO	Process	Type
1	asdf	xyz

tblSteps

SINO	Step	Cum	Per	Pln	Act	FC	Hour	ExtraHr
1	1	0	0	8/3/2013	8/3/2013
1	2	20	2	8/5/2013	8/5/2013
1	3	40	2	8/7/2013	8/7/2013
1	4	50	8	8/15/2013	8/15/2013
1	5	70	10	8/25/2013	8/25/2013
1	6	80	2	8/27/2013	8/27/2013
1	7	90	1	8/28/2013	8/28/2013
1	8	100	3	8/31/2013	8/31/2013

[rd.prasanna]

Hi Recyan,

1) Sl No is nothig but a Process ID.
3) Second scope is type error (My Apologies).
4) Step1, Step2,...Step8 indicates the phases to finish the process.
5) percent for % of work done ;Number for period allotted to finish that step ; Date for planned/Actual date when the step is completed; Null is nothing but a forecast date based on the planned-date and actual date

Thanks

[rd.prasanna]

Thank you June, This is really very helpful,
do i need to create separate tables for scopes ?

is it really required...?
and i did the relation on Sl no.. is it the right way to relate the tables...

And one more thing is that,
Each Process will have n number of Types and the steps may vary based in process type...

Thanks,

[recyan]

Sorry, more questions :
1) There are many unique PROCESSES.
2) Each PROCESS can have many TYPES.
3) Each PROCESS with a TYPE can have many SCOPES.
4) Each PROCESS with a TYPE and a SCOPE has a fixed "MAN HOUR" and "EXTRA HOUR" manually allocated against them.
5) Each PROCESS with a TYPE and a SCOPE has essentially 8 and only 8 steps, each manually recorded as a percent, number or Date (SCOPE - Cum as Percent, SCOPE - Per as Number, SCOPE - Pln as Date, SCOPE - Act as Date, SCOPE - FC as Date)

In the Table Data, that you have presented,

Each PROCESS with a TYPE and a SCOPE is a unique Record (i.e. they together form the Primary key).

If, you are allowed to / can post the practical scenario, behind the data, as to what the actual process in the physical world is, it, perhaps would be easier for people to help & think along lines of preventing pitfalls of wrong design suggestions.

Would suggest taking a look at the links suggested by June, in case you have not done so.

Thanks

[rd.prasanna]

Hi Recyan,

Attached is the sample excel template for ref...Sample.zipSample.zip

thanks

[recyan]

Hi,
Will try to take a look asap.
Forgot to mention in my previous post ( No 15).
Wanted to know from you, whether, the statements 1 to 5 and the rest, that I have written, are correct or not. If not, request you to correct the statements.

Thanks

[rd.prasanna]

1) There are many unique PROCESSES.- Right
2) Each PROCESS can have many TYPES. -Right
3) Each PROCESS with a TYPE can have many SCOPES.- No- Scopes will remain constant, %, Per, Pln, Act and FC.
4) Each PROCESS with a TYPE and a SCOPE has a fixed "MAN HOUR" and "EXTRA HOUR" manually allocated against them.- no Mah hour will change based on type.
5) Each PROCESS with a TYPE and a SCOPE has essentially 8 and only 8 steps, each manually recorded as a percent, number or Date (SCOPE - Cum as Percent, SCOPE - Per as Number, SCOPE - Pln as Date, SCOPE - Act as Date, SCOPE - FC as Date) -yes.

thanks

[rd.prasanna]

Hi June,

Kindly help me understanding this table design.

can u elaborate little more about this...

Thanks,