sql and/or pivot?

[mbar]

I'm not sure how to explain (and therefore search) this so forgive me if this has already been answered.

Here is my current table (I've changed the field names to make it easier):

Date Deposit
4-5-11 $1000
4-5-11 $2000
4-5-11 $500
4-6-11 $200
4-6-11 $100

how can I modify the results to show:
Date shift1 shift2 shift3
4-5-11 $1000 $2000 $500
4-6-11 $200 $100 $0 or null

I think I'm missing an SQL command?

Any help would be greatly appreciated

thanks a lot
I

[ajetrumpet]

TRANSFORM table.deposit

SELECT table.date

FROM table

GROUP BY table.date

PIVOT table.deposit

[mbar]

thanks for the reply- this almost worked...

how do I set the field names to "shift1", "shift2", ect..?

basically, its output is that of a crosstab and the current field names are that of the record. in other words, if the record is $1000, the field name is "1000". unless another record is $1000, then the field is blank for other dates.

thanks

[kennejd]

If you have a field that tracks shift, you'll want to include it in your query.

[ajetrumpet]

the current field names are that of the record. in other words, if the record is $1000, the field name is "1000". unless another record is $1000, then the field is blank for other dates.

I understand that. I've not done many crosstab queries in my lifetime. Only financials. I did not know anything about this. I simply used the crosstab wizard and modified the sql.

I don't know how to use aliases in the names. I don't believe you can, but you can experiment on your own. If it turns out you can't, you can always loop the table and change them that way.

[kennejd]

Your original data needs some way to identify the shift....something like below. Then you can make the shift field your column header and the Deposit the value in the crosstab.

Date Deposit shift
4-5-11 $1000 Shift1
4-5-11 $2000 Shift2
4-5-11 $500 Shift3
4-6-11 $200 Shift1
4-6-11 $100 Shift1

[ajetrumpet]

mbar,

I have unsubscribed to your thread. Letting you know. I'm not interested in confusing posters with more than one road to follow. good luck to ya.