query

[Guest_imported]

Is it possible to "hide" columns after a query search has been done?

For example, I have 10 columns, but only 4 columns matches the criteria (based on the query statement). When the query returns with the result, I only want the 4 columns to appear.

Is this even possible in Access? Or is there a different approach to doing this?

 

[montrose]

Some suggestions-if you don't need to see the results of all 10 columns, do not include them in the qry design. If they correspond to your criteria and are needed for the qry parameters, un-click the show button in the design grid. When the qry is open in data sheet view, you can also highlight the columns you don't want to see, click on FORMAT, HIDE COLUMNS. HTH.

 

[Guest_imported]

Tried unchecking the "Show" and doing a query on that, but the results do not return the criteria specified.

Here is what I want to do....

Column 1 Colum 2 Column 3 Column4
Yes No Yes No

Do a query search that will only return Column 1 and Column 3. I can do this query search, but I do not want Columns 2 and 4 coming back .
I want the result to look like this after the query serach is done
Column 1 Column 3
Yes Yes

 

[montrose]

Have you tried just including columns 1 and 3 in your design? What criteria are you using?

 

[Guest_imported]

Criteria is that the checkboxes are checked off. So, I"m using -1 (checked) for the criteria.

Here is SQL statement:
SELECT [Variable Query].[Variable Report Form], [Variable Query].[CPU Manuf], [Variable Query].[Processor Class], [Variable Query].[Processor Speed], [Variable Query].[10/100]
FROM [Variable Query]
WHERE ((([Variable Query].[10])=-1) AND (([Variable Query].VGA)=-1));

The results of the query are correct, but I was wondering if there was something in the SQL statement or Access that needs to be invoked for the column hiding

 

[tempclerk]

You are seeing five columns because you are selecting them. Remove the fields you don't want to see from your select statement.

 

[THoey]

I don't know the answer, but it seems everyone else is misunderstanding the question (or maybe its just me). They want to select all the fields where a value is true, so they need to query all the fields. I do not know of a way to do this, but maybe some of the geniuses here do.

For a record in the table with these values:

Field1 = Yes
Field2 = No
Field3 = Yes
Field4 = No

They want to first query and find all the fields in that record that are yes and then requery to just display those fields. Maybe that is the key. First query returns the fields names that the value = yes, storing those field names and then requery to just select those field names.

Anyone else?

Terry M. Hoey

 

[Guest_imported]

th3856 has the right idea....how do I go about doing this? Or is this even possible in Access?

 

[THoey]

There are tables (that I am not really familiar with) that are system tables that "build" your tables. It has all of your tables field names, types, etc. Something like MSys? Maybe you could have a table to store the field names in, and then in a function query your tables for the yes values, returning the field names into this temporary table. Then you could run another query that would meet your criteria, but just pull those fields. Like I said, I am not familiar with using these system tables, but maybe one of the Experts here can help you with the rest of it... Terry M. Hoey

 

[montrose]

Jumping back in here after being gone for a while. th3856 is correct if you are trying to FIRST find all the YES's. From what I know, you need more than one qry to do this. Do you first query to find all the YES's. If you're trying to find all the NO's as well, do your second qry to find only the NO's. You can then combine them together in a Union Qry to show both YES/NO. I know this is all definitely possible in Access, but sometimes communicating how to do it isn't easy. Some of the 'pros' out there are extrememly skilled in their responses. Amazing how tough it is at times to put into print what we're trying to say! Hope this helps, and if no 'pros' jump in we'll keep trying 'til we get it right.

 

[montrose]

Forgot to say that I've never used a system tbl to do this-don't think you have to, IMHO.

 

[Sawatzky]

dear @me,

(wow that sounds good : dear me!)


1) From the names of your fields I assume that the resultset has not the values yes and no, but that you wanted to show with yes and no, which records to display , is that right?
2) How do you determine which of the columns are to be displayed?
3) how good are you in vba-coding (just in case)


regards Astrid

 

[BoxHead]

I am about as far from a genius on this as you can get, but I think you could accomplish what you need with one query, aliases, and function statements.

It seems that you want a list of the fields that have "Yes" values. From your query, it looks like you are harvesting features of products and want to list the features that each product has.

If my assumtions are correct, this may help.

I'm accustomed to using the QBE grid so that's how I'll describe it. You can look at the SQL view if you want to run it from code.

From the Database window, select Queries > New > Design. Select the table or tables you want and double-click on the fields you want included.

Your field names are: Variable Report Form, CPU Manuf, Processor Class, Processor Speed, and 10/100

In the 'Field' for Variable Report Form, type:

YES1: IIf([Variable Report Form] = -1, "Variable Report Form  ", "")

In the 'Field' for CPU Manuf, type:

YES2: IIf([CPU Manuf] = -1, "CPU Manuf  ", "")

Do the same for your other fields with aliases: YES3, YES4, YES5.

When you run your query, any of the yes values will show the field name followed by a couple of spaces.

Now, in the next open column of the grid, type the following in the field space:

FEATURES: [YES1] & [YES2] & [YES3] & [YES4] & [YES5]

Select this last column as the only one to show. When you run the query, you should (fingers crossed) see one column with a header: "FEATURES" and a string of the field names where the checkboxes were ticked.

FEATURES
Rec1 Variable Report Form Processor Speed
Rec2 CPU Manuf Processor Class 10/100
Rec3 Processor Speed
Rec4 Variable Report Form Processor Class 10/100


HTH


John

 

[Guest_imported]

I have two reponses, one for Sawatzky and one for BoxHead

Sawatzky:
1. -the recordset has already been assigned either a -1 or 0 depending on the check off state when the record was created.
2.the columns that will be displayed will be the columns that meet the criteria of having -1
3.i would say that i'm fairly knowledgable in vba coding


BoxHead:
I tried your method and I almost got the answer I wanted!!!
When I uncheck the Show columns on every column except the last (which would be the Feature), I get a parameter box popping up prompting me for an parameter. When I leave the checkbox on the Show checked off, I get a listing of all the Features, like your example indicates. But I still get a listing of all the other columns.

Does the query statement in the "Field" section need to be modified?

 

[Guest_imported]

BoxHead, I have figured out how to display the Features column only. I "hid" all the other columns.

My next question, is it possible to create a form that has all the fields in it with the checkboxes and have a command button on the form, that will run the 'features' query to display the columns that i want only.

For example....On the form, I have ten recordsets. I want to run a query that will display all the records that meet 4 out of 10 criteria.

Is this possible, if so, how can it be done?


By the way, thanks to all those who have helped me already!!!

 

[BoxHead]

Me,

Good job!

With the new question (I want to run a query that will display all the records that meet 4 out of 10 criteria.) I'm not sure if you want to see records that match four specific criteria or ANY four criteria.

Basically, what you've done is created a string that concatenates all of the yes values.

You could build a Combo Box with your new column as the rowsource and use that combo box value as criteria to filter your form. This would show you all records that have the same four 'features'.

If you want to drill down to records that have ANY four features, you'll have to test the values of each string and generate a "score" for each record. I don't see this working in QBE because of the limitation on expression size, but you stated you're pretty good with code so I'll outline the basic idea. I'll tell you up front that I'm not sure where you would place this code, but it will let you identify records by the number of features each has. I'll take it as far as the 5 features you've mentioned.

Dim MyScore as Integer, Ftr1 as Integer, Ftr2 as Integer, Ftr3 as Integer, Ftr4 as Integer, Ftr5 as Integer, 


If (InStr(1, FEATURES, "Variable Report Form" ,1)) > 0 Then
         Ftr1 = 1
     Else
         Ftr1 = 0
End If

If (InStr(1, FEATURES, "CPU Manuf" ,1)) > 0 Then
         Ftr2 = 1
     Else
         Ftr2 = 0
End If

If (InStr(1, FEATURES, "Processor Speed" ,1)) > 0 Then
         Ftr3 = 1
     Else
         Ftr3 = 0
End If

If (InStr(1, FEATURES, "Processor Class" ,1)) > 0 Then
         Ftr4 = 1
     Else
         Ftr4 = 0
End If

If (InStr(1, FEATURES, "10/100" ,1)) > 0 Then
         Ftr5 = 1
     Else
         Ftr5 = 0
End If

MyScore = Ftr1 + Ftr2 + Ftr3 + Ftr4 + Ftr5

Since you've determined the text for the string in your query, you should be able to dependably determine if it exists in the string. MyScore simply counts how many times there is a match.

I'm not sure if you'd want to store the MyScore variable in an unbound textbox on your form for filtering or if you would need to add yet another column to your query.

I hope this helps and good luck.

John