query problems

[Guest_imported]

Hello gang,

Small query problem w/report. Someone designed this database really bad by using duplicate fields i.e:
Action Item 1
Action Item 2
...
Action Item 5

Followed by a little tick which tells us whether the action item has been completed ie:
Completed 1
Completed 2
...
Completed 5

Heres the thing I am to generate a report based on these actions and the problem im having is I cannot set the criteria for false on the fields Completed 'x', because then if one of those action items was completed I would not get the resulting record. I mean and I know im not being that clear...Say I have a record which has these 5 actions and next to them these 5 completed tick fields, If one of these ticks has been checked off and the others havent then my query wouldnt pull up the correct record it would ignore it. So I dont think there is a way to query it. So I decided to just not set any criteria which is fine, it shows all the actions whether they are completed or not and then I added the tick fields next to the actions on the reports and specified "yes/no" as the format. So it shows all the actions along with a "yes/no" based on the tick field. Now here's the problem if one of the action items was not filled, i.e: empty text field/memo then I still get a yes/no to be output. I've set the Can Shrink Property to no on the whole detail section and on all the fields...it works for all the fields EXCEPT the tick field y/n. That still shows up...anyone have a better approach to this? Thanks

 

[PaulF]

If I understand what you want, and that is to display all records where there is atleast one of these Yes/No (tick) Fields that is False, then it can be done atleast 2 different ways.

1. Placing = False in the Criteria Row for each of these Five Yes/No Fields, which I think you tried. I believe you're not getting the results you want because you are placing the = False criteria on the same Criteria Row for each of the five Yes/No Fields. They need to be on different Rows (drop down one row for each field), this creates the OR statement, placing them on the same Criteria Row creates an AND statement.

2. Because this is Tick field is really a Yes/No field (boolean) it is stored as 0 for False, and -1 for True and you can create an Expression Field where you total the values of the five fields. Then for the Criteria Row use <> -5.

PaulF

 

[biggoober]

You could write 5 simple queries like this,

Select Action Item 1 as Action Item, Completed 1 as completed from whatever;

Select Action Item 2 as Action Item, Completed 2 as completed from whatever;

etc.


then write a UNION query.
select action item, completed from query1
UNION
select action item, completed from query1
UNION
...... ect.

This would let you handle each action item, completed box as if they were normalized.

 

[Guest_imported]

Dear Paul:

I've used your method originally ... but again I get all action items...even the ones that are ticked off...I want ONLY the uncompleted unticked action items to be displayed.

See the ticks are represented by a field Complete
Complete 1
Complete 2 ... and so on upto Complete 5

And the action item is another field text field :
Action Item 1
...All the way to action item 5

Now these ticks are placed next to each action item on a form. If they have been marked this means they have been completed. But my report I only want to show the unticked action items...And I did the query by putting =False in every row not next to each other OR'd it. Anything else I should try?

 

[Guest_imported]

I think it is because each action item and each complete fields all 1-5 are all on the same record show it displays the whole record...the query takes each field individually as =false as it's criteria. That may be why I keep getting on the report all the action items...so then I decided to keep all on there and add a field on the report Completed/Uncompleted but I try to use the can shrink method and it doesnt work when I set a text box controls to the field complete. It displays yes/no which is correct, but it displays it even on empty action items that are never filled out...I thought can shrink gets rid of that.

 

[PaulF]

another possibility for the report is to have one textbox where you can gather the info and display it. Just use the Report's On Format Event and some IF statements to build the result


txtInfo = &quot;&quot;
If Condition1 = False then
txtInfo = &quot;ThisIs1 &quot;
end if
If Condition2 = False then
txtInfo = txtInfo & &quot;ThisIs2 &quot;
End If
etc....


PaulF