Turning look up values into fields in a query

[Clarendon]

Hello

I am designing a table with a look up field. When I make a query upon this
table, I need to use each of the look up value in the field as a field in the
query table to collect some facts pertaining to each value based on the first
table, for instance the number of records each value applies to.

In the first table the records are put into groups. So the query would be
asking for each group (row head) the number of records for each look up value
(column head).

My study of Access has shown me that queries select data but this is not a
selection task but a reorganization task. So my question would be if Access
queries reorganize data as well.

Thanks.

 

[John Spencer]

Most of us would tell you that it is almost always a problem to use a
lookup field in a table.

That aside, I don't understand what you are trying to do.

Perhaps you can use a totals query to get what you are looking for.

You can count the number of times some values is used, and if the field
is number field, you can get the average, the sum, the maximum, and the
minimum value. You are grouping by the values, so add the field again
to your query and change group by to Count.

That will give you the data in columns like
Alpha 10
Beta 20
Chaos 5

IF you wanted that to be reorganized like
Alpha Beta Chaos
10 20 5

Then you would need to use a Crosstab query. The crosstab query wizard
wants three values, but you can just use a calculated column for the row
source.

Take the above query (Group and Count) and select Query: Crosstab from
the menu while in design view.

Add a calculated column
Field: ""
Crosstab: Row

Field: YourGroupByfield
Crosstab: Column

Field: YourCountBy
Crosstab: Value

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[John W. Vinson]

Hello

I am designing a table with a look up field. When I make a query upon this
table, I need to use each of the look up value in the field as a field in the
query table to collect some facts pertaining to each value based on the first
table, for instance the number of records each value applies to.

In the first table the records are put into groups. So the query would be
asking for each group (row head) the number of records for each look up value
(column head).

My study of Access has shown me that queries select data but this is not a
selection task but a reorganization task. So my question would be if Access
queries reorganize data as well.

It sounds like you need to do two things: first, create a query Joining your
table to the lookup table; and second, changing the query to a Crosstab query
using the text value from the lookup table as the Column Head (the combo box
wizard will help here).

This is a good example of why many of us intensely dislike the misbegotten
Lookup Wizard (see http://www.mvps.org/access/lookupfields.htm for a
critique). It is very handy when you want to get from Point A to Point B in
your database design - but does not help (and even makes it harder) get to
Point C or D.