Please Help: Querying same field several times for a count in QBE

[pilch74]

Hi.

I am having no success with a task that I have been given in work.

The .csv that is required contains 20 fields and for the most part
these fields can be added to the query by double clicking.

Where I am completely baffled though is there will be 5 columns within
the .csv that are analysis of a field. It's not just a simple double
click and add this field to the query. The field in question contains
numbers from 0-26. Is it possible to do this.. I want to see results
for 0-4 here in column blah, results for 5-7 in the next column , 8-10
here, 11-12 here etc etc..

I have tried adding this field 5 times and added 'BETWEEN's' (to
specify the number ranges) but the minute I specify more than one
BETWEEN for this field in question the query results are completely
blank.

I did explore the option on adding the field ONCE to the query and
then by use of REPORTS somehow add custom fields to the report and say
'Show field WHERE it's this number range to this number range' and
have 5 custom fields like this... But I got completely lost with
that.. And also I have tried doing a query on a query and I didn't
get too far.

I hope I have explained this OK and I hope it makes sense after
perhaps the second read

Any help on this matter would really be most appreciated..

Thanks very much.

Richard Hellier.

 

[John Spencer]

Sounds as if you want a calculated field that would look something like the
following in the query grid

Field: Show0to4: IIF([YourField] between 0 and 4, [YourField], Null)

Field: Show5To7: IIF([YourField] between 5 and 7, [YourField], Null)

Without more details as to what you are trying to accomplish, that should
get you started.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[pilch74]

Sounds as if you want a calculated field that would look something like the
following in the query grid

Field: Show0to4: IIF([YourField] between 0 and 4, [YourField], Null)

Field: Show5To7: IIF([YourField] between 5 and 7, [YourField], Null)

Without more details as to what you are trying to accomplish, that should
get you started.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Hi.

I am having no success with a task that I have been given in work.

The .csv that is required contains 20 fields and for the most part
these fields can be added to the query by double clicking.

Where I am completely baffled though is there will be 5 columns within
the .csv that are analysis of a field. It's not just a simple double
click and add this field to the query. The field in question contains
numbers from 0-26. Is it possible to do this.. I want to see results
for 0-4 here in column blah, results for 5-7 in the next column , 8-10
here, 11-12 here etc etc..

I have tried adding this field 5 times and added 'BETWEEN's' (to
specify the number ranges) but the minute I specify more than one
BETWEEN for this field in question the query results are completely
blank.

I did explore the option on adding the field ONCE to the query and
then by use of REPORTS somehow add custom fields to the report and say
'Show field WHERE it's this number range to this number range' and
have 5 custom fields like this... But I got completely lost with
that.. And also I have tried doing a query on a query and I didn't
get too far.

I hope I have explained this OK and I hope it makes sense after
perhaps the second read

Any help on this matter would really be most appreciated..

Thanks very much.

Richard Hellier.

John.

Thanks for taking the time to reply, I really appreciate it.

I was only just paging my way through my Access Bible about the
Expression Builder and then I come to see if any replies have been
made to my post!! Great timing.

What I forgot to say is that

a) I needed an overall count within the columns and I've been able to
do this successfully by simply changing the Group By to Count.

b) The counts need to actually be based on just one other criteria
from another field called STATUS. I have to say only show me these
counts where the STATUS = 1. So, Is there a way of adding in this
additional criteria to the IIf condition or will I have to relook at
the way I'm trying to go about obtaining these figures? I cannot just
add this in to the overall query as I don't want all the other data
affected by this STATUS = 1.

Many, many thanks and Best Regards.

Richard Hellier

 

[John Spencer]

Field: Show0to4: IIF([YourField] between 0 and 4 AND Status = 1,
[YourField], Null)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Sounds as if you want a calculated field that would look something like
the
following in the query grid

Field: Show0to4: IIF([YourField] between 0 and 4, [YourField], Null)

Field: Show5To7: IIF([YourField] between 5 and 7, [YourField], Null)

Without more details as to what you are trying to accomplish, that should
get you started.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Hi.

I am having no success with a task that I have been given in work.

The .csv that is required contains 20 fields and for the most part
these fields can be added to the query by double clicking.

Where I am completely baffled though is there will be 5 columns within
the .csv that are analysis of a field. It's not just a simple double
click and add this field to the query. The field in question contains
numbers from 0-26. Is it possible to do this.. I want to see results
for 0-4 here in column blah, results for 5-7 in the next column , 8-10
here, 11-12 here etc etc..

I have tried adding this field 5 times and added 'BETWEEN's' (to
specify the number ranges) but the minute I specify more than one
BETWEEN for this field in question the query results are completely
blank.

I did explore the option on adding the field ONCE to the query and
then by use of REPORTS somehow add custom fields to the report and say
'Show field WHERE it's this number range to this number range' and
have 5 custom fields like this... But I got completely lost with
that.. And also I have tried doing a query on a query and I didn't
get too far.

I hope I have explained this OK and I hope it makes sense after
perhaps the second read

Any help on this matter would really be most appreciated..

Thanks very much.

Richard Hellier.

John.

Thanks for taking the time to reply, I really appreciate it.

I was only just paging my way through my Access Bible about the
Expression Builder and then I come to see if any replies have been
made to my post!! Great timing.

What I forgot to say is that

a) I needed an overall count within the columns and I've been able to
do this successfully by simply changing the Group By to Count.

b) The counts need to actually be based on just one other criteria
from another field called STATUS. I have to say only show me these
counts where the STATUS = 1. So, Is there a way of adding in this
additional criteria to the IIf condition or will I have to relook at
the way I'm trying to go about obtaining these figures? I cannot just
add this in to the overall query as I don't want all the other data
affected by this STATUS = 1.

Many, many thanks and Best Regards.

Richard Hellier

 

[pilch74]

Field: Show0to4: IIF([YourField] between 0 and 4 AND Status = 1,
[YourField], Null)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Sounds as if you want a calculated field that would look something like
the
following in the query grid
Field: Show0to4: IIF([YourField] between 0 and 4, [YourField], Null)
Field: Show5To7: IIF([YourField] between 5 and 7, [YourField], Null)
Without more details as to what you are trying to accomplish, that should
get you started.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Hi.
I am having no success with a task that I have been given in work.
The .csv that is required contains 20 fields and for the most part
these fields can be added to the query by double clicking.
Where I am completely baffled though is there will be 5 columns within
the .csv that are analysis of a field. It's not just a simple double
click and add this field to the query. The field in question contains
numbers from 0-26. Is it possible to do this.. I want to see results
for 0-4 here in column blah, results for 5-7 in the next column , 8-10
here, 11-12 here etc etc..
I have tried adding this field 5 times and added 'BETWEEN's' (to
specify the number ranges) but the minute I specify more than one
BETWEEN for this field in question the query results are completely
blank.
I did explore the option on adding the field ONCE to the query and
then by use of REPORTS somehow add custom fields to the report and say
'Show field WHERE it's this number range to this number range' and
have 5 custom fields like this... But I got completely lost with
that.. And also I have tried doing a query on a query and I didn't
get too far.
I hope I have explained this OK and I hope it makes sense after
perhaps the second read
Any help on this matter would really be most appreciated..
Thanks very much.
Richard Hellier.

John.

Thanks for taking the time to reply, I really appreciate it.

I was only just paging my way through my Access Bible about the
Expression Builder and then I come to see if any replies have been
made to my post!! Great timing.

What I forgot to say is that

a) I needed an overall count within the columns and I've been able to
do this successfully by simply changing the Group By to Count.

b) The counts need to actually be based on just one other criteria
from another field called STATUS. I have to say only show me these
counts where the STATUS = 1. So, Is there a way of adding in this
additional criteria to the IIf condition or will I have to relook at
the way I'm trying to go about obtaining these figures? I cannot just
add this in to the overall query as I don't want all the other data
affected by this STATUS = 1.

Many, many thanks and Best Regards.

Richard Hellier

John.

Thanks very much - this has worked and is a lifesaver..

Many, many thanks.

Richard Hellier.