Add a column with 3 status in a Query (1 Viewer)

[Cguizzardi]

Dear All
i have some issues on making progress on my access file.
my goal is to add a custom columns in a query, on which i can select the status of each project and the calculate the progress..

Taking as for example the file attached to this thread, i would like to add the columns described above on the Qry_Progress...

a part the resolution of this topic with new proposal, i would like to understand from "good practice" point of view where i making a concept mistake if possible

thanks

Cristian

 

[Galaxiom]

I don't have Access at home so I've not looked at the file but good practice when recording status of a process is to use a sequence of numeric codes to represent the states. Don't record the names of the states on each record.

The names corresponding to the numeric states are recorded in a lookup table which joins to the query via the numbers. The selection on the form can be done with combobox whose RowSource is a query on the lookup table that displays the word and has the number as the Bound Column so records that rather than the word.

This way you can query for things like

 WHERE ProcessStatus BETWEEN 3 AND 5

Much simpler than a condition like

WHERE ProcessStatus IN("Loaded", "Working", "Completed")

You can leave gaps in the numbers if you think more states could be added later. The displayed name is simply changed in the lookup table so you can easily convert even to a completely different language if you want.

 

[MajP]

Maybe if you can show a few rows of want you want to see. Your question is unclear.
The query shows systems, then related sub systems, activity per sub system, and then progress against those sub systems.

What does it mean to "show status of each project and the calculate the progress."
Example 23. What would you want to calculate / show?
Would that be the highest progress level for 23 (startup)? Or the highest lever for each sub system (001 Startup,002 Startup...

Qry_Progress Qry_Progress

Unit Tag	System Number	System Name	Sub System Number	BASIC ACTIVITY LIST	PROJECT PHASE
23	23-058-P	STORM/FIRE WATER RUN OFF - AMMONIA PLANT AREA	23-058-P-001	Motor solo run	PRE-COMMISSIONING
23	23-058-P	STORM/FIRE WATER RUN OFF - AMMONIA PLANT AREA	23-058-P-001	Run in with process fluid	START-UP
23	23-058-P	STORM/FIRE WATER RUN OFF - AMMONIA PLANT AREA	23-058-P-002	Cold Alignement	PRE-COMMISSIONING
23	23-058-P	STORM/FIRE WATER RUN OFF - AMMONIA PLANT AREA	23-058-P-002	Motor solo run	PRE-COMMISSIONING
23	23-058-P	STORM/FIRE WATER RUN OFF - AMMONIA PLANT AREA	23-058-P-002	Run in with process fluid	START-UP
23	23-058-P	STORM/FIRE WATER RUN OFF - AMMONIA PLANT AREA	23-058-P-004	Cold Alignement	PRE-COMMISSIONING
23	23-058-P	STORM/FIRE WATER RUN OFF - AMMONIA PLANT AREA	23-058-P-004	Motor solo run	PRE-COMMISSIONING
23	23-058-P	STORM/FIRE WATER RUN OFF - AMMONIA PLANT AREA	23-058-P-004	Run in with process fluid	START-UP
23	23-058-P	STORM/FIRE WATER RUN OFF - AMMONIA PLANT AREA	23-058-P-004	Cold Alignement	PRE-COMMISSIONING
23	23-058-P	STORM/FIRE WATER RUN OFF - AMMONIA PLANT AREA	23-058-P-004	Motor solo run	PRE-COMMISSIONING
23	23-058-P	STORM/FIRE WATER RUN OFF - AMMONIA PLANT AREA	23-058-P-004	Run in with process fluid	START-UP
23	23-059-P	STORM/FIRE WATER RUN OFF - AMMONIA STORAGE AREA	23-059-P-001	Cold Alignement	PRE-COMMISSIONING
23	23-059-P	STORM/FIRE WATER RUN OFF - AMMONIA STORAGE AREA	23-059-P-001	Motor solo run	PRE-COMMISSIONING
23	23-059-P	STORM/FIRE WATER RUN OFF - AMMONIA STORAGE AREA	23-059-P-001	Run in with process fluid	START-UP
23	23-060-P	SANITARY WATER SYSTEM	23-060-P-001	Cold Alignement	PRE-COMMISSIONING
23	23-060-P	SANITARY WATER SYSTEM	23-060-P-001	Motor solo run	PRE-COMMISSIONING
23	23-060-P	SANITARY WATER SYSTEM	23-060-P-001	Run in with process fluid	START-UP
23	23-060-P	SANITARY WATER SYSTEM	23-060-P-001	Cold Alignement	PRE-COMMISSIONING
23	23-060-P	SANITARY WATER SYSTEM	23-060-P-001	Motor solo run	PRE-COMMISSIONING
23	23-060-P	SANITARY WATER SYSTEM	23-060-P-001	Run in with process fluid	START-UP

 

[Cguizzardi]

the idea is like this:
- there is a list of activity listed on the column qry_progress[basic activity list] and for each of this activity is associated a number of man hour indicated on the column qry_progress[MH]

- using the treeview i ll select the right path up to when i ll select the activity and i ll give it the job card value.. the job card value il the % of completion of the activity itself... example: the activity with the job card "ONGOING" have progress 45% and this value of % will moltiply the man hours indicated before....at the end i ll know that the MH achieved for that activity will be 10*0,45=4,5h...this last value will be recorded into the new additional column of the query [MH Earned]

attached to this thret the latest revision of the DB.... the Job CArd status will change also the icon of the basic activity (the battery will change the color)

 

[mike60smart]

the idea is like this:
- there is a list of activity listed on the column qry_progress[basic activity list] and for each of this activity is associated a number of man hour indicated on the column qry_progress[MH]

View attachment 107876
- using the treeview i ll select the right path up to when i ll select the activity and i ll give it the job card value.. the job card value il the % of completion of the activity itself... example: the activity with the job card "ONGOING" have progress 45% and this value of % will moltiply the man hours indicated before....at the end i ll know that the MH achieved for that activity will be 10*0,45=4,5h...this last value will be recorded into the new additional column of the query [MH Earned]

View attachment 107877

attached to this thret the latest revision of the DB.... the Job CArd status will change also the icon of the basic activity (the battery will change the color)

Hi
Well there are all sorts of things wrong with your table structures.

Normally the PK in all tables is an Autonumber, but you have Text Fields set as the PK
Also, you would normally link the Parent PK to the related FK in the table that contains related Child Records.
You are linking Text Field to Text Field and this is wrong.
You also are using Lookup Fields in your tables and again this is wrong. Search Google for "The Evils of Lookup Fields in Access Tables"
You should not have any spaces or non-text characters in field names.

 

[MajP]

I agree with some @mike60smart some I disagree.

Normally the PK in all tables is an Autonumber, but you have Text Fields set as the PK

This should read "Often" instead of "Normally." There are advantages of using an autonumber, but there are advantages of using a natural key at times. I have worked on massive ERPs where the PKs are text. That by itself is not a problem.

Also, you would normally link the Parent PK to the related FK in the table that contains related Child Records

That I would change from normally to "Always". If a "link" means a relation then you only do this from PK to FK. You can do a "join" on whatever you want, but not the same thing.

You also are using Lookup Fields in your tables and again this is wrong. Search Google for "The Evils of Lookup Fields in Access Tables"

IMO this should be changed from "wrong" to "absolute nightmare!" It is even worse than wrong.

This shows the magnitude of the problems.

PrjPhase to tbl_Item_Activiity is almost correct. You are going from PK to FK. But a link only happens if you go PK to FK of the same data type. ID Prj Phase is numeric and so project phase should also be numeric but it is text, but it is not storing the Project Phase name it is actually storing the project phase ID. You just cannot see it. It is storing a project phase number (1,2,3..) but unfortunately it is storing it as text.
Here is the lookup "SELECT [PRJ PHASE].[ID PRJ PHASE], [PRJ PHASE].[PROJECT PHASE] FROM [PRJ PHASE]; "
Bound column: 1

Now it gets worse and even more confusing. Tbl_CMC joins by Project Phase name not the ID. However it does not even do that, because it too stores the ID. Here is the lookup
SELECT [PRJ PHASE].[ID PRJ PHASE], [PRJ PHASE].[PROJECT PHASE], [PRJ PHASE].[ID] FROM [PRJ PHASE] ORDER BY [ID];
Bound column 1
So it would appear possible to create a join since you would see names in both fields, but you cannot even do a join since tbl_CMC stores a hidden numeric (stored as text). Wow!

The purpose of the relationship window is TO ENSURE REFERNTIAL INTEGRITY. It also defaults the join for future queries, but that is simply a feature and not it real purpose. These relations should be enforced and correctly established.

I strongly concur that you fix this before going further. This is truly a nightmare in the making. The farther you get in development, the worse this will be.
You need to establish clean table relations and then enforce them in the relationship window. I would strongly recommend adopting a better naming convention. I like
ID_Prj_Phase for the PK
ID_Prj_Phase_FK as the foreingn key.

You have a PK
ID Prj Phase and then a second field Project Phase. You then have pseudo foreign keys with the name Project Phase which is the name of a field that is not the PK

All of this makes you DB super confusing.

 

[Cguizzardi]

Thanks @mike60smart and @MajP for your comments.. i really appreciate
i restarted to built the DB on this way
- Re-define the DB referential integrity links remouving the lookup into the table
- make a new DB without any lookup
- Now i have some issue on how to manage the name of the cells and other controls on the forms but i m studying how to fix it

the new DB is as for the image below... i ll restart from here...what do you think about the new links?

 

[CarlettoFed]

To help concretely, it would be necessary to have a detailed description, from start to finish, of how the activity you would like to reproduce in the database takes place.

 

[Cguizzardi]

the file and his aim are very easy...is just my poor knowledge on DB that is huge..
let me go trough the main steps
Step.1 - the basic table (manual input) are
- Cmc
- CmcType
-Scmc
-Activities
-Class05 - List of the equipments that i can find in all projects
- Cil (list of the equipment that i find in the actual project)
-PrjPhase - Phase of the project
-Milestone - Milestone of the project
-Syslist - List of attribute
- Discipline

Step.2 - Create the the table [Tbl_ItemActivities]. in this table i make the link between the activity and the item...
Step.3 - after this i made a query and i create the Qry_database that is the general table which is linking the CMC,SCMC,Item,Activity
Step.4 - on this step is required to make the query between the Cil (list of equipment of the project) with the general database (Qry_database)- the output of this query is the Qry_Progress and will be used for calculate the sum of the man hour based on Activity, Scmc, etc etc etc
Step.5 - this is the step on which i m lost - for calculate the man hours i need to change one parameter for each activity. this status is the job card parameter....

basically i would like to use the form below for update the query progress