Update query for multiple data items (Acc:97) 1

[JustLJ]

Using Access 97
Greetings.

I'm so lost on this I don't even think I have any of the terminology right!

So let me just sort of say it in English (as it were)…

In table one (ProjectTracker) there is a ton of data regarding a project.
In table two (ProjectActivity) there is date and hours data, by task for each project.
There is a one-to-many relationship from PT to PA based on the projectid (that is, in PT the project occurs once, in PA it occurs as many times as there is an activity with a date and hours).

I'm trying to do an update query where I can match task on PA with an associated field on PT for each project; for example, Coding on Project 1234 for 8 hours on 11/25/02.
The rub is that there are like 10 different activities to track into 20 fields.

Okay, here's the question: Is it possible for me to have a single query to grab this data, or will I have to have one for each activity….(In other environments, or languages, it would be a big case or if statement).

Any thoughts on this (besides to hand it over to a programmer that knows their stuff -- everyone has abandoned me on this Access stuff!)?

Thanks.

LJ

 

[RickSpr]

Let me get this right. ProjectActivity has a Task field that describes the task for which the row records time. You want to match that to a column in ProjectTracker?

You can do almost anything with code that processes the tables, of course, but you can't relate rows in one table to columns in another with SQL. The reason you can't is that, in terms of this example, the different tasks should not have been made separate columns in ProjectTracker. They should have been rows in a TaskTypes table, also a one-to-many child table of ProjectTracker.

(In relational design terms, your ProjectTracker table isn't sufficiently normalized.)

If I'm on the right track about what you want to do, I strongly recommend you reconsider your design. Otherwise you're going to have to do a bunch of VBA code, probably not only to solve this problem but in the future, too. Rick Sprague

 

[JustLJ]

Hmmmm Rick. Maybe this can help.

ProjectTracker
Sample of Columns:
ProjectNum Description ReqStart ReqEnd ReqHours CodingStart CodingEnd CodingHours TestingStart TestingEnd TestingHours etc.
Sample of Data:
1234 BlahBlahBlah 1/1/02 1/15/02 50 2/1/02 3/1/02 100 3/5/02 4/10/02 80
5678 More Blah etc.

ProjectActivity
Sample of Columns:
ProjectNum Activity Date Hours
Sample of Data:
1234 Coding 2/1/02 8
1234 Testing 3/5/02 8

So, trying to tie the activity shown in ProjectActivity rows (keyed by the ProjectNum) to ProjectTracker (keyed by ProjectNum). I don't understand how that is so completely non-normalized.

My issue is pulling the activity (coding, testing, et al) from ProjectActivity into ProjectTracker without needing a query for EACH of the activity types. There are like 10 activities, so I don't really want to run 10 queries to update ProjectTracker from this time data.

Thanks for the reply!

LJ

 

[RickSpr]

I apologize if I offended you. That was not my intent.

I'm afraid I still think this structure needs more normalization, though. I can't guess with complete confidence what every column means, so I might be wrong.

At least it appears that you want to accumulate hours spent on a task in ReqHours, CodingHours, etc.

Accumulating the hours isn't necessarily a bad thing. If you don't intend to keep the activity details in your database, only the totals, then accumulating is just what you need to do. But if you are keeping the details, then keeping the totals as well is redundant, because you can derive the totals from the details at any time. The problem with holding a redundancy is that the information can potentially get out of sync, in which case the database becomes unreliable. You could ask a question (build a query) two different ways, and get different answers, with no indication that one of them was wrong.

I can't tell whether the Date in the activity also relates to the StartDate and/or EndDate in the tracking table. My guess is that, the first time you see Coding activity, you want to put the date in the CodingStart column. Maybe each time you see Coding activity, you also put the date in CodingEnd (if CodingEnd isn't already greater), or maybe CodingEnd gets filled in independently.

Anyway, if you aren't going to keep the activity details around, I would suggest that breaking up the ProjectTracker table would make your data easier to process. I would use the following structure:

    ProjectTracker
        ProjectNum    PK
        Description

    ProjectTasks
        ProjectNum    PK
        Activity      PK
        StartDate
        EndDate
        TotalHours

You could then use a query like this to accumulate the hours:

    UPDATE ProjectTasks INNER JOIN ProjectActivity
        ON ProjectTasks.ProjectNum = ProjectActivity.ProjectNum
           AND ProjectTasks.Activity = ProjectActivity.Activity
       SET ProjectTasks.TotalHours = ProjectTasks.TotalHours
            + ProjectActivity.Hours

One more query may be needed. If you create rows in ProjectTasks in anticipation of the activity you will have for that project, you won't need it. Otherwise, you'll need a query to make the initial entry in ProjectTasks for a given project and activity, when you first see that activity. (The INNER JOIN in the first query will exclude these, because there is no matching row in ProjectTasks.) This query is:

    INSERT INTO ProjectTasks (ProjectNum, Activity, StartDate, TotalHours)
       SELECT ProjectActivity.ProjectNum, ProjectActivity.Activity,
              ProjectActivity.Date, ProjectActivity.Hours
         FROM (ProjectActivity LEFT JOIN ProjectTasks 
              ON ProjectActivity.ProjectNum = ProjectTasks.ProjectNum
                AND ProjectActivity.Activity = ProjectTasks.Activity)
         WHERE ProjectTasks.ProjectNum IS NULL

See how easy the normalization makes it?

There's another advantage, too. Suppose some day you have a project that needs an extra phase for some reason, such as Quality Assurance. You won't have to add columns for it to either ProjectTracker or ProjectTasks, nor add any queries, etc.. Just add it to your master list of activity names. Most everything else should work without change--at least until you get down to the reporting side of the system, where you *might* need to make changes to a pretty-formatted report. (I expect you have just such a report, because your original ProjectTracker table looks like it was designed from one.)

---------------------------------

Getting back to your original question now, I have to tell you that, no, there's no way a single SQL query can do all this for you with your ProjectTracker table designed the way it is now. The problem is that you want to match data in the activity to column names in the project table. Relational database systems aren't designed to do that--they match data with data.

You can do it with VBA code, by computing the column name from the data, but you've given me the impression that you lack the time or staff support you'd need for that.

I can only see two choices left--either further normalize your table structure, or deal with all those queries. And if you do the latter, you will probably find that the effort it takes to maintain it keeps increasing over time. Rick Sprague

 

[JustLJ]

Thanks for the explanation Rick! You really made that clear.

I reckon since I can't control the structure of the data that it will have to be multiple queries... but you can be sure your message is printed and I'm ready to talk to those techies in control!

Appreciate the time and the reply!

Ciao.
LJ