Crosstab Query

[user]

Hello

I have created a crosstab query, with dates as row heading (9/1/09, 10/2/09,
11/3/09), and then have row heading with Will and data 1,2,3. My problem is
that when I run the query, it create 3 rows and I need it to create 1 row.
9/1/09 10/2/09 11/3/09
Will 1
Will 2
Will 3.

I need to see
9/1/09 9/2/09 9/3/09
Will 1 2 3.

Thanks

 

[KARL DEWEY]

I would help if you post the SQL of your query. Open in design view, click

 

[Wayne-I-M]

Assuming that you only have the 3 records you have shown with the data you
have shown then this will work

TRANSFORM First(TableName.NumberField) AS FirstOfNumberField
SELECT TableName.TextField
FROM TableName
GROUP BY TableName.TextField
PIVOT TableName.DateField;

but for anything else - like Karl says - you need to give more details of
what you're lookin for and doing (post the sql)

 

[user]

Karl
Below is the sql. I guess its creating seperate rows for each date.

TRANSFORM Sum(DATA.Num) AS SumOfNum
SELECT [FR]) AS FR
FROM DATA
WHERE (((Day([DATE]))<=3))
GROUP BY [FR], Day[DATE],
ORDER BY DATA_DATE
PIVOT DATE;

 

[Wayne-I-M]

TRANSFORM Sum(Data.Num) AS SumOfNum
SELECT Data.FR
FROM Data
GROUP BY Data.FR
PIVOT Data.Data_Date;

Not sure what this is doing (((Day([DATE]))<=3))

I assume you have fields called
Num (a number field)
Data_Date (date field)
FR (text field)

This could be ?? showing the orders from someone (FR) on a date (Data_Date)
with the number of order being saved into a field (Num)

Something like this

FR Num Data_Date
Will 1 09/01/2009
John 1 10/02/2009
Will 2 10/02/2009
John 2 09/01/2009
Will 3 11/03/2009
John 6 11/03/2009

If not - can you give an example of the data in the table

--
Wayne
Manchester, England.

Karl
Below is the sql. I guess its creating seperate rows for each date.

TRANSFORM Sum(DATA.Num) AS SumOfNum
SELECT [FR]) AS FR
FROM DATA
WHERE (((Day([DATE]))<=3))
GROUP BY [FR], Day[DATE],
ORDER BY DATA_DATE
PIVOT DATE;

--
Thanks

I would help if you post the SQL of your query. Open in design view, click

 

[user]

Wayne

I tried the sql you suggested with First(), but I am still getting multiple
rows.
Any other suggestions...

 

[Wayne-I-M]

The 1st assumes that you have one entry per person per day and only one
person - as per your information but I don't think thats the case - have a
look at the other answer i gave above (I have just tested it and it works
fine).

You could create a table - with John and Will as I showed and then create
the query as per my example. This will show you how pivot tables work.

eg

FR Num Data_Date
Will 1 09/01/2009
John 1 10/02/2009
Will 2 10/02/2009
John 2 09/01/2009
Will 3 11/03/2009
John 6 11/03/2009


give it a try and see if it helps a little. The idea I "think" you were
looking for is more like that - a number of people who have have different
numbers (of something?) on different dates.

If you use the names I gave in this

TRANSFORM Sum(Data.Num) AS SumOfNum
SELECT Data.FR
FROM Data
GROUP BY Data.FR
PIVOT Data.Data_Date;


you will get this

FR 09/01/2009 10/02/2009 11/03/2009
John 2 1 6
Will 1 2 3

which I think is what you're looking for