Format/Set up an Access query correctly

[newaccessuser4545]

Hi,

I am trying to set up a query in Access that will return data based on the date. For example, if I have the following table:

Company Revenue Date
Co 1 1000 1/15/06
Co 1 2000 2/15/06
Co 2 5000 1/15/06
Co 2 8000 2/08/06

I need a query that will give me the following output:

Company Jan Revenue Feb Revenue
Co 1 1000 2000
Co 2 5000 8000

The actual data is a little more complicated, I just don't know how to structure the query to get the basic info. Any thoughts???

Thanks in advance for any suggestions!

 

[PHV]

Have you tried a crosstab query ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[newaccessuser4545]

That is a little better, but still not really what I'm looking for...here is the output with a crosstab query:

Company 1/15/05 2/08/05 2/15/05
1 1000
1 2000
2 5000
2 8000

 

[PHV]

Have a look at the Month or Format functions ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[newaccessuser4545]

That helps, but I am unable to write functions against different columns this way. For example, if I build an "Iif" statement another way(which I think is the answer, I just don't know how to structure it), then I can write other expressions to calculate %growth, etc...any other thoughts?

Thanks again for your help!