Cross-table problem

M

MadeleineP

Hi,
I have a table that contains data as follow:

ID DIAMETER PASSING
1 0,25 100
1 23 50
1 10 60

I try to create a query that return data in one line as follow
ID D_025 D_23 D_10
1 100 50 60

I do not want to use the option "Dynamic cross-table" query to see the
result , I want the query return data so I can use them in other application.
I already try to create subqueries for diameter 0,25 23 and 10 and then
combine them together. It works fine however if I have a lots of diameter, I
have to create too many sub-queries. If you have any other solutions, please
help me. Thank
 
D

Duane Hookom

Have you tried a crosstab where the Row Heading is ID, Column Heading is
"D_" & DIAMETER, and the value is Sum of PASSING?
 
J

jahoobob

Create a CrossTab query, using the wizard, with the ID as the row
heading, Diameter as the column heading and First(Passing) as the
calculated field. After the query is completed, delete the Total of
Passing field in the query.
 
M

MadeleineP

Hi Duane,

I already tried a crosstab however I want to see the passing value for each
passing and not the Sum of Passing.


"Duane Hookom" a écrit :
 
D

Duane Hookom

Is it possible to have records like:
ID DIAMETER PASSING
1 0,25 100
1 0,25 50
1 0,25 20
1 23 50
1 10 60
If a combination of ID and DIAMETER is unique, then it doesn't make any
difference whether you use Sum, Min, Max, First,...
 
M

MadeleineP

Hi Duane,

It is a unique diameter record. It cannot be like following:
1 0,25 100
1 0,25 50

As I mention in the previous record, the croostab query return the Sum,
Last, First of each diameter passing, However, it cannot return the value of
passing. That's the problem I try to figure out :(



"Duane Hookom" a écrit :
 
D

Duane Hookom

What value do you get when you use Sum()? I would expect the one value that
matches the ID and Diameter.
 
Top