Queries and Reports

[Father Murphy]

It's been a long time since I have worked with Access. Here is what I did and what I am trying to do. I'm basically at wits end.

I created a table which consists of fields that contain data pertaining to project management. The Engineering manager wants to be able to do reports on the different projects based on what steps of the project has been finished and who it was assigned to.

Here is an example.

Step 2

Heat Transfer complete? Yes/No drop down box
Step Assigned to: Employee drop down box
Date Completed? Date/time field
Commments: A field for text that pertains to the step if there was a promlem etc etc.

As it stands right now there is a total of 12 steps with each step ranging from 1 to 6 questions like the above example.

For the first report he wants me to create, I need to the following data (which I have the query pulling from my table) Project #, Customer, Engine Make, Engine Model, and the first step where any task is marked at No, and the person assigned to the task that is marked no, the step comments, and he wants is sorted by step.

I have all the data fields from the table included in the query as the way I understand it, the query needs to run through each record, and stop when it finds the first no answer for a step and report it for each record. My problem is, I have no idea on how to accomplish this. Anyone have any ideas?

 

[neileg]

I'm assuming that you have all of these fields for all 12 steps in one record? That is not the right design, you should have twelve records, one for each step. If each step is numbered then you can run a query that selects those records not completed and take the minimum step number for each project.

By the way, you don't need the Completed yes/no field. If there's no date in the date completed field, you know it's not complete.

 

[twychopen22]

Ok, I am not great at access but I did this once in a class I took. I am not sure if it will work for you because I was not using yes/no and if u look at my problem I had posted just a little while ago, I am having a problem doing something just like this. Anyways, try this, create another field in the query and use a large if statement, if you know SQL (which I don't or else I would not be having my problem) write it in that, but write a large if assigning a value to a new field (deselect this field so it is not visible in the report) like this.

(IIf ([first yes/no]=no,[new field+1],0))
(IIf ([second yes/no]=no,[new field+2],0))
(IIf ([third yes/no]=no,[new field+3],0))

etc.
Then sort them ascending to view them in order, or group them by their values. The 0 would be all yes obviously. Hope this helps.

Sorry, I didn't read the question right, I thought there was one record with 12 y/n boxes.

 

[Father Murphy]

I'm assuming that you have all of these fields for all 12 steps in one record? That is not the right design, you should have twelve records, one for each step. If each step is numbered then you can run a query that selects those records not completed and take the minimum step number for each project.

By the way, you don't need the Completed yes/no field. If there's no date in the date completed field, you know it's not complete.

Yes all of these fields for all 12 steps are contained in one record. I kind of figured that was poor design, but like I said, I have not used Access for about 3 years, so all of my access skills are pretty much gone. I did however start remembering things as I worked on it.