1) Cols from a query TO rows in a list. 2) Report both stats & details

[ViAn]

Issue 1:
How to put e g 4 column values from a predefined gruop query containing a lot of columns into one column (4 rows) in a list?

Issue 2:

I want to report several parameters from a database based on the user's options in a "FindData"-form. Typical parameters: Amplitude, period, rate. For each parameter, the max, min, avg and variance should be displayed, and also each single value:

        Amplitude   Period   Rate
Avg      ...          ...    ...
Min 
Max
Var
[+/-]
        All single values for each parameter
        ...           ...    ... 
        ...           ...    ...

The [+/-] indicates that it would have been nice to drill in and out.

Is there a quite simple way to do this. For now I have made one "list" for each Min/Max etc value for each parameter. Each one of these controls need to be coded to display a given value from a direct query. The details are put into a subform in table-view that is simply programmed to show a predefined query (where the SQL-sentence is altered based on the user input). This involves quite a bit of coding that seems unnecessary.

NB: It should be possible to alter what parameters to be shown at runtime (based on user-input).

Y.S.
-van

 

[dpimental]

If I understand you correctly, You could do a DLookup to get the value then do the avg, min ect on the value, or if you already have it, skip the first step.

As far as drilling down, I am not clear if you want to drill down on each value or all of them. You could link 2 tables in a 1-to-many relationship, and you could drill down that way.

If you want something more complex, there is a database that implemented the standard expander that you see on the microsoft web site. I think it's called GeoTree.

But, I am still not clear on what you need to do.

David Pimental
(US, Oh)
dpimental@checkfree.com

 

[ViAn]

Thank you for answering. I was probably not quite clear about my problem.

First to Issue 1.
-It is easy to display "Select val1, val2, val3, val4 from MyQuery" in a list. The problem is that this information will be displayed in 4 columns, and I want them to be displayed in 4 rows.

Issue 2:
I could not find the GeoTree on the Microsoft web site. Probably I should buy some new glasses, but I would be glad to have the exact link

Thank You In Advance!

-van

 

[dpimental]

I will look into Issue 2. Here is what I am hearing.
You select the values (fields) from each record or from 1 record, and they will of course be displayed in columns because they are fields within a record and you want them displayed in rows separate records.

OK, you will need 2 recordsets / tables. You will only need the recordsets if you don't need to keep the data in this format permanently.

RecordSet1 will hold the original data.
RecordSet2 will hold the new data.
RecordSet2 might have 2 fields, ValueType and Value.

If RecordSet1 displays percentage, variance, min, max,ect, all in one record set, when you loop through that recordset, for each of the 4 values you add a record to RecordSet2 where, for the first value (i.e. "percentage&quot, you set the ValueType = "percentage" and the Value to 75, for the 2nd value, you set the ValueType = "variance", Value = "3.5", ect.
?
Does that help

David Pimental
(US, Oh)
dpimental@checkfree.com

 

[dpimental]

For Issue 2 you can contact KimJacobson@CyRiv.Com

The Web Site for GeoTree is

http://www.CyRiv.Com

But I don't see a download for it anymore.

David Pimental
(US, Oh)
dpimental@checkfree.com

 

[tonyflavell]

Hi Guys

Use a crosstab query to change a row to a column or vica versa, surely?

Tony

 

[ViAn]

Thank you for the helping replies. Based on them I have reconsidered my needs slightly.

This is what I need to do:

1) Assign a user-input-dependant SQL to a query in the QueryDefs of my database - Fixed
2) Display this query in a subform. - Fixed
3) Display "Avg, Max and Var" for column nr 2:end - Problem !
4) Be able to hide the details (= subform mentioned in 2) - Should be easy to fix

Result:

ColID_0 ColID_1 ColID_2 Col_ID3 ColID_4
 --      --      --       --     --
 --      --      --       --     --   - The detail section should be
 --      --      --       --     --     easy to hide. Some kind of navigation 
 --      --      --       --     --     buttons has to be present. 
____________________________________

       Avg       --       --     -- 
       Max       --       --     --    - Remember that the number of 
       Var       --       --     --      columns will alter

I would be grateful to get some input on how this can be accomplished.
Maybe 2 subforms will do. The advantage is that the data will then be separated and more easy to plot. It would almost be a bit strange if this functionality was not incorporated in Access (But remember: the number of columns will alter!)


One short question:
How does a dao.recordset work? When using: "Set MyRst = MyForm!MyList.Recordset", MyRst.Updateable is set to "False". How can I alter the recordset?


-van