Grouping Issues???? HELP!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to extract components from query fields and then group the resulting
into two records in the end. What I have is data which contain the word "PAL"
or "SFSV" some place within the body of text in the query field. The field
name is: "AcctDept_Prod_Cat" and the data looks pretty much as the sample
below does.

SFSV Profile Substrate
342A - SFSV Airwear
PAL Ovation Airwear

What I need is a method of grabbing/extracting the "PAL" and "SFSV" from
these and then group these three or more records into only two records in one
pass, if possible. Is this possible?

Thanks
 
I need to extract components from query fields and then group the resulting
into two records in the end. What I have is data which contain the word "PAL"
or "SFSV" some place within the body of text in the query field. The field
name is: "AcctDept_Prod_Cat" and the data looks pretty much as the sample
below does.

SFSV Profile Substrate
342A - SFSV Airwear
PAL Ovation Airwear

What I need is a method of grabbing/extracting the "PAL" and "SFSV" from
these and then group these three or more records into only two records in one
pass, if possible. Is this possible?

Thanks
Click to expand...

I'm not quite sure what or how you want to group, but try this: put a
calculated field in the query

ContainsSFSV: InStr([AcctDept_Prod_Cat], "SFSV") > 0

and another

ContainsPAL: InStr([AcctDept_Prod_Cat], "PAL") > 0

This isn't going to be completely reliable though: if the field
contains a word such as "unpalatable" then it does contain PAL (though
not in the sense you want). This shouldn't be as much of a problem for
SFSV, that consonant combination doesn't even come up in Russian! <g>
If you can be CERTAIN that the PAL will always either be first in the
field, or be flanked by blanks, and that you'll never have something
like "My Pal Joey", you can use

ContainsPal: Left([AcctDept_Prod_Cat], 4) = "PAL " OR
InStr([AcctDept_Prod_Cat], " PAL ") > 0

These fields will be -1 or True if the field contains the string, 0 of
False if it does not.

John W. Vinson[MVP]
 
Mr. Vinson:

Thanks for your reply. There may be as many as hundreds of records
containing the words "PAL" or "SFSV" within the table/query structure. What
I'd like to do is roll these into a grouping of only two values and extract a
sum for each group via a grouping report.

I think I may have found the answer already as I wrote this. However, I'd
still like to hear how the experts would address such a thing. My immediate
solution, run two seperate queries behind reports and embed one into the
other or run them individually. Or use a union query linking on a
common/matching field.

Thanks for your input. I appreciate your solution as well!

John Vinson said:
I need to extract components from query fields and then group the resulting
into two records in the end. What I have is data which contain the word "PAL"
or "SFSV" some place within the body of text in the query field. The field
name is: "AcctDept_Prod_Cat" and the data looks pretty much as the sample
below does.

SFSV Profile Substrate
342A - SFSV Airwear
PAL Ovation Airwear

What I need is a method of grabbing/extracting the "PAL" and "SFSV" from
these and then group these three or more records into only two records in one
pass, if possible. Is this possible?

Thanks
Click to expand...

I'm not quite sure what or how you want to group, but try this: put a
calculated field in the query

ContainsSFSV: InStr([AcctDept_Prod_Cat], "SFSV") > 0

and another

ContainsPAL: InStr([AcctDept_Prod_Cat], "PAL") > 0

This isn't going to be completely reliable though: if the field
contains a word such as "unpalatable" then it does contain PAL (though
not in the sense you want). This shouldn't be as much of a problem for
SFSV, that consonant combination doesn't even come up in Russian! <g>
If you can be CERTAIN that the PAL will always either be first in the
field, or be flanked by blanks, and that you'll never have something
like "My Pal Joey", you can use

ContainsPal: Left([AcctDept_Prod_Cat], 4) = "PAL " OR
InStr([AcctDept_Prod_Cat], " PAL ") > 0

These fields will be -1 or True if the field contains the string, 0 of
False if it does not.

John W. Vinson[MVP]
Click to expand...
 
Mr. Vinson:

Thanks for your reply. There may be as many as hundreds of records
containing the words "PAL" or "SFSV" within the table/query structure. What
I'd like to do is roll these into a grouping of only two values and extract a
sum for each group via a grouping report.
Click to expand...

ok... how about:

GroupCode: Switch(ContainsSFSV: InStr([AcctDept_Prod_Cat], "SFSV") >
0, "SFSV", Left([AcctDept_Prod_Cat], 4) = "PAL " OR
InStr([AcctDept_Prod_Cat], " PAL ") > 0, "PAL", True, "Neither")

Do your Totals query or Report grouping by GroupCode.
I think I may have found the answer already as I wrote this. However, I'd
still like to hear how the experts would address such a thing. My immediate
solution, run two seperate queries behind reports and embed one into the
other or run them individually. Or use a union query linking on a
common/matching field.
Click to expand...

That may end up being the best bet, but you might try the suggestion
above.

How would the experts manage it? Diplomatically (or not, as the case
may be) give the person who designed this non-atomic, ill-designed
field a lesson in normalization! If it's important to know whether a
record is in the PAL or the SFSV (or some other) category, then there
should be a separate, atomic field conveying that information, rather
than burying it in a human-readable text string where it could very
well get lost.

John W. Vinson[MVP]
 
Back
Top