Create Query to Compare Values

[shannon9585]

I have a table that stores weekly status entries for projects managed by my office. Five columns in the table (named Scope, Schedule, Staffing, Finances, & Technology) have 3 valid values: "No Issues", "Minor Issues", "Critical Issues". I also have a report set up that shows the most current status entry for each project.
Here's what I want to do:
On the report, I'd like to include an indication of how the status of the project has changed from one week to the next. I need some sort of query that looks at the value of the previous week (for the columns listed above) and compares it to the current week. For example - if a project's current Scope value is "Minor Issues" and the previous week's value was "No Issues" I want to show a minus sign or indicator that says something like "declining" to indicate that the Scope status has declined from the previous week. A plus sign or "improving" message would be shown if the value has improved from one week to the next. One indicator value would be shown next to each of the values form the 5 columns listed above.


How do I set up a query to do this? For Scope, I want to say:
"Compare last week's value to this week's value.
Display 'No change' indicator if the values match.
Display 'Declining' indicator if this week's value is 'Critical Issues' and last week's value was not 'Critical Issues'.
Display 'Declining' indicator if this week's value is 'Minor Issues' and last week's value was 'No Issues'.
Display 'Improving' indicator if this week's value is 'No Issues' and last week's was not 'No Issues'.
Display 'Improving' indicator if this week's value is 'Minor Issues' and last week's value was 'Critical Issues'."
Then I'd have to repeat this for each of the other 4 fields.
I don't know the syntax for writing a query or macro like this.

[jzwp11]

Welcome to the forum!

First, the 5 columns (i.e. fields) in the table you discuss should probably be records in a related table since a project can have many of these items (one-to-many relationship). Furthermore, since an item can relate to many projects, you have another one-to-many relationship. When you have 2 one-to-many relationships between the same two tables you need a junction table.

First, a table to hold the basic project info:

tblProject
-pkProjectID primary key, autonumber
-txtProjectName

Next, a table to hold the items (I'll let you decide on a more appropriate name); each items is a record (not a field) that way if you want to expand your items in the future it would just be a matter of adding a record rather than changing your table structure and any related forms, queries or reports.

tblItems
-pkItemID primary key, autonumber
-txtItem

Now the junction table

tblProjectItems
-pkProjectItemID primary key, autonumber
-fkProjectID foreign key to tblProjects
-fkItemID foreign key to tblItems

The next thing I would recommend is to have a table that stores your evaluation criteria along with a numerical rating value since a numeric value can be more easily compared than a text value

For example, you may assign a 0 to No Issues, 10 to minor, 20 to critical. Stepping upward by 10 allows you to add intermediate evaluations in the future. At present, you would have 3 records in this table

tblEvaluations
-pkEvalID primary key, autonumber
-evalNumber
-evalText

Now since you have to evaluate each project on a regular basis, you need to store that evaluation

tblProjectItemEvaluations
-pkProjItemEvalID primary key, autonumber
-fkProjectItemID foreign key to tblProjectItems
-dteProjEval (date of the evaluation)
-fkEvalID foreign key to tblEvaluations

I'll let you digest this structure before moving on to your query question.

[weekend00]

I am too late.

[shannon9585]

Thanks for your reply, jpwz. What you've proposed would require a lot of reworking to my database, which is already established and populated with data. I think it is set-up similar to what you suggest, minus the junction tables.

Basic project info (project name, manager, etc.) is stored in the 'Projects' table:
tblProjects
-pkProject_ID primary key, autonumber
-txtProject_Name

Projects in that table are related to weekly status entries via a one-to-many relationship. The 'Weekly Status Call' table stores the weekly status entries (along with a memo field where a description is entered):
tblWeekly_Status_Call
-pkWeekly_Status_ID primary key, autonumber
-fkProject_ID foreign key to tblProjects
-txtScope
-txtSchedule
-txtStaffing
-txtFinances
-txtTechnology
-dteEntry_Date

I already have a query set-up to identify the most recent status entry for each active project. Here's the SQL:
SELECT [Projects - Active].[Project Name], Max([Weekly Status Call].[Entry Date]) AS [MaxOfEntry Date]
FROM [Weekly Status Call] INNER JOIN [Projects - Active] ON [Weekly Status Call].[Project Name] = [Projects - Active].[Project ID]
GROUP BY [Projects - Active].[Project Name];

I think I'm just stuck on the comparison part.

[jzwp11]

I understand that you do not want to rework the design, but I am concerned that sometime in the future, someone will ask for another project attribute to track. If that happens, you will have to redesign the weekly status call table and all associated queries, forms and reports. That is not something I would want to do.

Whether you decide to stay with your current design or opt for a design similar to what I proposed, you will need a nested/sub query to get the previous week's evaluation similar to the following (I just show how you would bring in the previous week's scope for each record in the weekly status call table (you'll have to adjust for your own table/query and field names.

SELECT [Projects - Active].Projectname, tblWeekly_Status_Call.dteEntry_Date, tblWeekly_Status_Call.txtScope, (Select top 1 Q1.txtScope from tblWeekly_Status_call as Q1 where Q1.dteEntry_Date<tblWeekly_Status_Call.dteEntry_Da te and Q1.fkProjectID=[Projects - Active].pkProjectID order by Q1.dteEntry_date desc) AS LastScope
FROM [Projects - Active] INNER JOIN tblWeekly_Status_Call ON [Projects - Active].pkProjectID = tblWeekly_Status_Call.fkProjectID
Order by tblWeekly_Status_Call.dteEntry_Date desc