Group By, Count, and Like

Hi

I have a table containing a Voucher_Number field and a Status field. The
Status field data type is text, and records contain values: Used, Expired,
Void, and (here's the problem) a text string of unpredictable length followed
by three spaces then Used e.g. "K7SWDS Used" or "25MAY05 Y6WWLI Used".

Is there a way of having a 'group by' query grouping these sort of strings
into strings that are just 'Used', so it would return a count of the number
of records in just the three categories Used; Expired; and Void? My
experiments with 'Like' in the query criteria haven't been successful.... I
could add a new field to the table, which contains the same data as the
'Status' field, but just 'cleaned', but if I can do what I want with a
'smart' query, that'd be preferable.

Any guidance gratefully accepted

Paul

Perhaps, try using a calculated field of
MID(StatusField,Instr(1,[StatusField]," ")+3)

You can group on that formula.
That does assume that there is only one place with 3 spaces in the field.


"LFNFan" wrote in message
...
Hi

I have a table containing a Voucher_Number field and a Status field. The
Status field data type is text, and records contain values: Used, Expired,
Void, and (here's the problem) a text string of unpredictable length
followed
by three spaces then Used e.g. "K7SWDS Used" or "25MAY05 Y6WWLI Used".

Is there a way of having a 'group by' query grouping these sort of strings
into strings that are just 'Used', so it would return a count of the
number
of records in just the three categories Used; Expired; and Void? My
experiments with 'Like' in the query criteria haven't been successful....
I
could add a new field to the table, which contains the same data as the
'Status' field, but just 'cleaned', but if I can do what I want with a
'smart' query, that'd be preferable.

Any guidance gratefully accepted

Paul

LFNFan:
If I were in your shoes this is what I would do.

Create a new field in your query not your table and and use the immediate if
funciton to assign the status to this new field. Then I would sort and group
on this new field.

To create the new field and assign the status all you should have to do is
type something like

NewField:IIF(right[StatusField],4)="ired","Expired",right[StatusField],4))

The above if statement reads: If the first four characters from the right
of the StatusField are "ired" then assign "Expired" to NewField else assign
the first four characters from the right of StatusField (Void or Used) to
NewField.

I think this should work as all it is doing is looking at the first four
characters starting from the right of your status field. These four
characters should always be either Used, Void or ired (Expired).

Now, someone smarter than me (wouldn't take much) would tell you to use a
function that looks at the three blanks and do some magic from there but that
is beyond me.

Hope this helps.
FatMan


"LFNFan" wrote:

Hi

I have a table containing a Voucher_Number field and a Status field. The
Status field data type is text, and records contain values: Used, Expired,
Void, and (here's the problem) a text string of unpredictable length followed
by three spaces then Used e.g. "K7SWDS Used" or "25MAY05 Y6WWLI Used".

Is there a way of having a 'group by' query grouping these sort of strings
into strings that are just 'Used', so it would return a count of the number
of records in just the three categories Used; Expired; and Void? My
experiments with 'Like' in the query criteria haven't been successful.... I
could add a new field to the table, which contains the same data as the
'Status' field, but just 'cleaned', but if I can do what I want with a
'smart' query, that'd be preferable.

Any guidance gratefully accepted

Paul

What about using the like function
so set the filter in you query to:
Like "* used*"
I included the three spaces before used.
Hope this helps.
Fons

"LFNFan" wrote:

Hi

I have a table containing a Voucher_Number field and a Status field. The
Status field data type is text, and records contain values: Used, Expired,
Void, and (here's the problem) a text string of unpredictable length followed
by three spaces then Used e.g. "K7SWDS Used" or "25MAY05 Y6WWLI Used".

Is there a way of having a 'group by' query grouping these sort of strings
into strings that are just 'Used', so it would return a count of the number
of records in just the three categories Used; Expired; and Void? My
experiments with 'Like' in the query criteria haven't been successful.... I
could add a new field to the table, which contains the same data as the
'Status' field, but just 'cleaned', but if I can do what I want with a
'smart' query, that'd be preferable.

Any guidance gratefully accepted

Paul

I should have also mentioned that you might need the criteria to be

Field: StatusField
Totals: WHERE
Criteria: Like "* Used" OR Like "* Expired" OR Like "* Void"

"John Spencer" wrote in message
...
Perhaps, try using a calculated field of
MID(StatusField,Instr(1,[StatusField]," ")+3)

You can group on that formula.
That does assume that there is only one place with 3 spaces in the field.


"LFNFan" wrote in message
...
Hi

I have a table containing a Voucher_Number field and a Status field. The
Status field data type is text, and records contain values: Used,
Expired,
Void, and (here's the problem) a text string of unpredictable length
followed
by three spaces then Used e.g. "K7SWDS Used" or "25MAY05 Y6WWLI
Used".

Is there a way of having a 'group by' query grouping these sort of
strings
into strings that are just 'Used', so it would return a count of the
number
of records in just the three categories Used; Expired; and Void? My
experiments with 'Like' in the query criteria haven't been successful....
I
could add a new field to the table, which contains the same data as the
'Status' field, but just 'cleaned', but if I can do what I want with a
'smart' query, that'd be preferable.

Any guidance gratefully accepted

Paul