Despair...

[an]

Hello!
I have a table with Date, Unity and Value fields.

In simple query return as next:

Date Unity Value

Date1 Unity1 Value1
Date1 Unity2 Value2
Date1 Unity3 Value3
Date2 Unity1 Value4
Date2 Unity2 Value5
date2 Unity3 Value6
Date3 Unity1 Value7
Date3 Unity2 Value8
Date3 Unity3 Value9

I would like a code query to return each Value in distinct
Unity colmun where there is the same Date, as next sample:

Date Unity1 Unity2 Unity3

Date1 Value1 Value2 Value3
Date2 Value4 Value5 Value6
Date3 Value7 Value8 Value9

Any help is welcome!
Thanks in advance.
an

 

[Allen Browne]

This is a crosstab query:

1. Create a query into this table.

2. Change it to a Crosstab query: Crosstab on Query menu.

3. Drag the Date into the grid. In the Crosstab row under this field,
choose:
Row Heading
In the Total row, accept Group By.

4. Drag the Unity into the grid. In the Crosstab row under this field,
choose:
Column Heading
In the Total row, accept Group By.

5. Drag the Value column into the grid. In the Crosstab row, choose:
Value
In the Total row, choose First or Sum.

If there can never be 2 records that both have the same Date and Unity
combination, First will be okay: it just returns the first match it finds.
If there could be duplicates, choose First if you want Access to show only
one of the values, or Sum if you want it to add the values together.

 

[an]

Exactly, AB

With this good explanation is easy!!!
(I'm a person with luck...)
Have you a good week.
Many thanks for your help.
an