HELP WITH A QUERY

[NewComer]

Hi,
I have a database that tracks maintenance on an aircraft using task numbers. Sometimes the maintenance on an aircraft changes from one task number to another. I have a field in a table that says whether the task is ACTIVE, RETIRED, or CHANGED. What I need to show is if a task is CHANGED, I need the query results to sum the task manhours and display the results under the new task number only. Any help with this would be greatly appreciate.


Thanks!

 

[dawnd3]

In your query put "Changed" in the criteria for that field. Then do a summery query by grouping by another field (not task number)(I don't know your other fields...) but then you would "sum" the man hours field. As far as displaying the results under a new task number, I would create another query, from these results, and use an "append query" to update the table. It this isn't clear enough, let me know. Of if someone else knows an easier way....Take Care Dawn

 

[NewComer]

thanks Dawn,
What the engineering ultimately wants is a chart that displays all task man-hours (which includes repair man-hour, QC man-hour and Inspection man-hours), displaying only current tasking numbers. He wants all man-hours that were formally under old task numbers converted to the new task numbers and summed, but only for charting purposes. Not actually changed to new task numbers in the tables. I hope this is making some kind of sense.

 

[dawnd3]

I guess I am lost I am not sure how you have your tables set up or the fields in those tables, so I am not sure if I am going to be of any help. If you want to give more info, or email the database, I will give it a shot. Why does he want a new task number for the sum of hours? Dawn

 

[NewComer]

he wants a chart giving the average and standare deviation for each current task number for each aircraft. However, since the program started two years ago, some the task numbers have changed to new task numbers. He said he wanted each aircraft to display all current task numbers, if the task was formally under an old task number, to accumulate the man-hours under the new task number and to display only the new task number.

The fields involved in this query would be BUNO(as serial number assigned to each aircraft), task number, phase(we currently have 3 phases of maintenace that the work is done in), task number status(active, retired, changed)and the three fields containing the man-hours (repair man-hours, qc man-hours, and inspection man_hours). That's it for this query.

What i need is a hint of vb code to accomplish this.

Thanks for your effort.

 

[dawnd3]

Feel free to send me your database. dawnd3@yahoo.com I will look to see if it is a quick thing to help ya with this. It is confusing in that I am not sure what exactly your are tracking and what you mean by a task. I am sure it would be clearer to look at.

 

[NewComer]

Dawn,
Sorry I'm not clearer on what I need. I can't send a copy of my database because it is a government secured database, would be in big trouble. What i'm tracking is the time it takes to accomplish a job in hours. Each job is assigned what is called a task number in order to track exactly what that job is, one example of a task number is 1QA001 which is to remove panels off of an aircraft and the man-hours (rpr_mhrs) might be 2.5. That's how long it took to remove the panels. However, some of our task numbers have changed to new task numbers and instead of just going in and actually updating the old task numbers to the new task numbers (which I think makes more sense), they want me to leave them under the old task number and write a query to add the man-hours to the new task number for charting purposes. I'm not the best at coding, but i'll start it out just to try to give you an example of what I'm trying to achieve(I'm sure there is a much better way of writing it). Maybe it will help, if not thanks for trying anyway.

iif[task_number]=1QA001 and [rpr_manhours]or[QC_MHRS]OR [INSPECTION_MHRS]>0,THEN TAKE THOSE MAN-HOURS AND ADD THEM TO THE MAN-HOURS OF THE NEW TASK NUMBER(1FEI001),ELSE DO NOTHING.

I have to do this with about 30 different task numbers.

 

[THoey]

Is this going to be a one time update from the olf task to the new task, or do you need to keep tracking this all the time? If it is reoccuring, I think I would create another table to relate the old task number to the new task number. Then when you create your reports, you would first find all related tasks and then get the hours, etc, for all related tasks.

Hope that helps for an idea or that someone else has a better one... (Where's MichaelRed?) Terry M. Hoey

 

[NewComer]

TERRY,
YES, THIS WILL BE A REOCCURING TASK, SO I THINK I'LL TAKE YOUR ADVICE AND CREATE ANOTHER TABLE TO RELATE THE OLD TASK NUMBER WITH THE NEW ONE. THANKS FOR THE HELP!