Group Query

[TotallyConfused]

I would like someone to please help me with this please. I have to count and
identify groups by an id that consists of 8 characters. The characters can
consist of a combination of letters and numbers. How I need to group is the
id can be ???????A. However, I need to eliminate the ids with ???????A only.
I need to count and group ids that are in a group by a group meaning
???????A, ???????B, ???????C etc. the first 7 characters are all the same
except the last character changes Therefore, the groups would be the first
seven characters would all be the same. The last character would change
consecutively. How can I group this to count how many groups there are? I
hope I made sense and someone can please help me write criteria. Thank you.

 

[Allen Browne]

In query design view, enter this in the Field row:
Left([F1], 7)

Substitute your field name for F1.

Depress the Total icon on the toolbar.
Access adds a Total row to the grid.
Accept Group By under this field.

Drag the field into the grid grid again (another column of Field row.)
This time, choose Count in the Total row.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...

 

[TotallyConfused]

Thank you that worked perfectly for most of my list. However, I have found
another type of id where the characters are the same the first 4 or 5
characters are the same and characters 6 and 7 are different and then
character 8 is the same. That would be another query but how do I write
that? Thank you in advance for all your help.

In query design view, enter this in the Field row:
Left([F1], 7)

Substitute your field name for F1.

Depress the Total icon on the toolbar.
Access adds a Total row to the grid.
Accept Group By under this field.

Drag the field into the grid grid again (another column of Field row.)
This time, choose Count in the Total row.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message

I would like someone to please help me with this please. I have to count
and
identify groups by an id that consists of 8 characters. The characters
can
consist of a combination of letters and numbers. How I need to group is
the
id can be ???????A. However, I need to eliminate the ids with ???????A
only.
I need to count and group ids that are in a group by a group meaning
???????A, ???????B, ???????C etc. the first 7 characters are all the same
except the last character changes Therefore, the groups would be the
first
seven characters would all be the same. The last character would change
consecutively. How can I group this to count how many groups there are?
I
hope I made sense and someone can please help me write criteria. Thank
you.

 

[Allen Browne]

Use Left() to parse out the left characters, Mid() to parse out the middle
characters, and Right() to get the rightmost one. I'm not sure how you will
end up solving this, as it seems the widths are not fixed.

The real problem here is that you seem to have a field that contains
multiple pieces of data: the prefix, the middle bit, and the suffix
character at the end. One of the most basic rules of data normalization is
that your fields must be atomic: that is, you must break the data down so
each field contains only one kind of thing, not 3 things in one field.
Normalizing the field by breaking it into 3 might be the best way to solve
this problem.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message

Thank you that worked perfectly for most of my list. However, I have
found
another type of id where the characters are the same the first 4 or 5
characters are the same and characters 6 and 7 are different and then
character 8 is the same. That would be another query but how do I write
that? Thank you in advance for all your help.

In query design view, enter this in the Field row:
Left([F1], 7)

Substitute your field name for F1.

Depress the Total icon on the toolbar.
Access adds a Total row to the grid.
Accept Group By under this field.

Drag the field into the grid grid again (another column of Field row.)
This time, choose Count in the Total row.

message

I would like someone to please help me with this please. I have to
count
and
identify groups by an id that consists of 8 characters. The characters
can
consist of a combination of letters and numbers. How I need to group
is
the
id can be ???????A. However, I need to eliminate the ids with ???????A
only.
I need to count and group ids that are in a group by a group meaning
???????A, ???????B, ???????C etc. the first 7 characters are all the
same
except the last character changes Therefore, the groups would be the
first
seven characters would all be the same. The last character would
change
consecutively. How can I group this to count how many groups there
are?
I
hope I made sense and someone can please help me write criteria. Thank
you.