missing one sql line?

[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

 

[pbaldy]

I assume you also have a shift field? Try the crosstab query wizard and see if you get what you want.

 

[mbar]

thanks for the reply

I don't have a shift field. I was hoping I can create it in sql...

something like Select (this is the part I don't know- record #1?) as [Shift1]

ect..

 

[pbaldy]

So you're relying on the order of records in the table to determine shift? I'm not sure that's a good idea; it's generally not considered to be reliable. I'm not sure how to achieve what you want with SQL. I suppose you could create a derived field that essentially numbered the records 1, 2 & 3, but even that would rely on some other field for reliable sorting (an autonumber field for instance).

Personally, I'd have a shift field.

 

[mbar]

thanks for your reply.

I guess I'll try to include a "shift" field however, this almost seems like the reverse of a union query. I can't believe that their isn't a good way to do this with SQL

thanks anyway