Creating Query Groups.

[Dilly]

I have a database that contains Items procured for differant projects. I have created a query that works out the delivery time of the items using date recieved - date demanded. I want to group these in a count of items recieved in the following time scales.

1. Less than 3 days
2. greter than 3 less than 7
3. greter than 7

I want to create a crosstab query but require to input my own groups as above.

Help please!

 

[tomschafer]

Try the Iif function, it's like an if then statement....

select
Iif(date recieved - date demanded <3, &quot;Less than 3 days
&quot;, &quot;&quot,
Iif(date recieved - date demanded between 3 and 7, &quot;greter than 3 less than 7
&quot;, &quot;&quot,
Iif(date recieved - date demanded >7, &quot;greter than 7
&quot;, &quot;&quot

 

[Dilly]

I created this in my query that gives me the correct figures but the headings are <>, 0, 1. Any suggestions, I did not fully understand your solution.

 

[tomschafer]

You have to add aliases to get the column names...

select
Iif(date recieved - date demanded <3, &quot;Less than 3 days
&quot;, &quot;&quot as [Less than 3 days],
Iif(date recieved - date demanded between 3 and 7, &quot;greter than 3 less than 7
&quot;, &quot;&quot as [greter than 3 less than 7]
,
Iif(date recieved - date demanded >7, &quot;greter than 7
&quot;, &quot;&quot as [greter than 7]