Extracting info from a string

[Alex]

Thanks to all who helped me with my previous post....

I have another problem with the same DB, basically I have a field which has
a string with the following input mask
-fisrt six characters are always letters...
-next 6 characters are numbers, consistently a persons date of birth
yy/mm/dd, followed by a two digit number which corresponds to the county...
-then a letter character which can be M or F (male or female)
-finally three numbers, assigned progressively as an ID number.

I need to make a query or something which will search through the string,
and tell me how many records are male, and how many records are female...

also if possible divide by age groups... how many between 18 and 25, and
stuff like that...

can anybody help???? thanks in advance...

 

['69 Camaro]

Hi, Alex.

can anybody help????

Yes. But you aren't going to accept this advice, either. Your field is
being used as if it were just a pickle barrel. When you throw every type of
item into the pickle barrel, it's hard to sort out these items afterwards,
not to mention get all the pickle juice off.

You need to change the table's design because every time you need to sort
out a "piece" of information stored in this field, you'll have to go through
gyrations, instead of making a quick, simple SELECT query to get the
information you need. Your table isn't normalized, and you're going to put
in a _lot_ of extra effort in the future to work around this if you don't
fix it early on.

basically I have a field which has
-fisrt six characters are always letters...

This is an individual text field. Name it something like "Code," or
whatever attribute these six characters represent.

-next 6 characters are numbers, consistently a persons date of birth
yy/mm/dd

Another individual field. Name it "DOB." Make sure it uses the Date data
type, not Text data type, so that you can use date functions easily.

followed by a two digit number which corresponds to the county...

Another individual numerical field. Name it "CountyNumber," as per the
advice Dirk gave you.

-then a letter character which can be M or F (male or female)

Another individual text field. Name it "Sex" or "Gender."

-finally three numbers, assigned progressively as an ID number.

Another individual numerical field. Name it according to its attribute,
too. (Perhaps CensusID?)

Now with this normalized table structure and each record containing values
in each of these separate fields, a simple SELECT query using the COUNT
aggregate function for each sex will determine how many males and females
are listed in the table:

SELECT COUNT(Sex) AS NumMales
FROM Census
WHERE (Sex = 'M');

SELECT COUNT(Sex) AS NumFemales
FROM Census
WHERE (Sex = 'F');

.... where Census is the name of your table. With this normalized table
structure, one can also easily create other queries to count specific items,
like the number of persons between 18 and 25 years old:

SELECT COUNT(DOB) AS NumBetween18And25
FROM Census
WHERE (DateDiff("yyyy", DOB, Date()) + Int(Format(Date(), "mmdd") <
Format(DOB, "mmdd")) BETWEEN 18 AND 25);

If you want help to separate your field into multiple fields to normalize
the data, please let us know. Despite what you think, you aren't going to
find it easier to redesign the database later, only more time-consuming and
aggravating than it will be to do it now.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

 

[Alex]

Very Well... I will take your advice... in which case I do need help into how
to spread my info into many fields as you suggested...

 

[Alex]

If someone helps me by giving me code for this, can they also help me, with
having the code apply itself to the whole recordset... cause for example, I
know how to use the Left function and what not to extract a single record, by
making a form with textboxes, and putting the following into the OnCurrent
event, this method, however, I have to scroll down 250,000 records... very
time consuming....
-------------------------------------------------------------------------------------
Dim compoundstring, name, sex as String
Dim DOB, CountyNumber, IDNumber as Integer

'pass the info from the textbox to the string
compoundstring = Compound.value

'pass the info into each variable
name = Left(compoundstring, 6)
DOB = Mid(compoundstring, 7,6)
CountyNumber = Mid(compoundstring,13,2)
Sex = Mid(compoundstring,15,1)
IDNumber = Right(compoundstring,3)

'pass the values into appropiate textboxes

 

[Larry Daugherty]

Hi Alex,

A MASK means that you're enforcing data entry to be as you've specified.

The real issue is that your data design is flawed. Data in a field in a
table in a relational database must be "atomic". That is to say that one
field will contain information about a single attribute of the entity that
the record is about.

To someone just getting started with Access it may seem very clever to
concatenate several information from several attributes into one field,
maybe they believe that fewer fields means less storage space. Neither of
those things is true. As you continue to build your application you will
have an interest in a single attribute across several records. If your
attribute of interest is buried in some string you'll always have to work
harder to get it. Now it may be that you already have all of those
attributes in their separate fields and that you have concatenated them for
some mysterious purpose.

I recommend that you do some motivated reading of some of the basic books
about Access. I also recommend that you lurk
microsoft.public.access.tablesdesign and
microsoft.public.access.gettingstarted where others are posting similar
issues. Also, for purposes of really opening your eyes, I recommend that
you also look into www.mvps.org/access to get some background information
and lots of Access lore.

No, I won't try to help you solve the issue you posted. To do so would be
to help you dig a deeper hole that you would later have to surmount.

HTH

 

[Alex]

I did not design this table.... I did not capture the information on this
table.... I got it as is... and now I have to work around the design flaws
that it has... That is what I got... I thought this place was for helping
people out... not chiding them and saying

No, I won't try to help you solve the issue you posted. To do so would be
to help you dig a deeper hole that you would later have to surmount.

if it wasnt your intention to help, then why waste your time and post... I
know the structures is flawed, but I cant help it... if you are not able or
not willing to help, then please dont post... I need help in figuring out a
problem, not finger wagging for not following standard conventions on a table
that I did not design.

Now if anyone can help me in dividing the info in a string across a couple
of fields, like Camaro suggested, then please, help me do that... taking into
account what my last post says... then I would be inmensely thankful to you...

 

['69 Camaro]

Hi, Alex.

If someone helps me by giving me code for this, can they also help me, with
having the code apply itself to the whole recordset....
250,000 records... very
time consuming....

Of course. Using SQL, one can do this very easily and fairly quickly, even
with 250,000 records, as long as:

1.) Every record has the requisite 19 characters in the "Compound" field in
the order that you described; and
2.) Your table doesn't already have fields matching the new field names;
and
3.) Your table isn't close to exceeding the maximum 255 fields, or the
maximum record length, or the 1 GB size limitations; and
4.) The database file itself isn't close to meeting the 2 GB size
limitation.

Unlikely circumstances, but I thought I'd let you know what the show
stoppers are.

First, back up your database in case something goes wrong. Next, create a
new query and open the SQL View pane. Copy the following SQL statement and
paste it into the SQL View pane:

ALTER TABLE tblCensus
ADD COLUMN NameID Text (6),
DOB Date,
Sex Text (1),
CountyNumber Long,
IDNumber Long;

You'll need to change the table name, tblCensus, to whatever table name you
are using. I changed your "Name" field because "Name" is a reserved key
word, and should not be used in identifiers, such as table names, field
names, variables, et cetera. Save the query with a name such as
qryAddTblCensusColumns, but use your own table's name. While in the SQL
View pane, run the query. This query just added the new fields to your
table.

Next, create a new query and open the SQL View pane. Copy the following SQL
statement and paste it into the SQL View pane:

UPDATE tblCensus
SET NameID = MID(Compound, 1, 6),
DOB = CDate(MID(Compound, 9, 2) & "/" & MID(Compound, 11, 2) & "/" &
MID(Compound, 7, 2)),
CountyNumber = MID(Compound, 13, 2),
Sex = MID(Compound, 15, 1),
IDNumber = MID(Compound, 16, 3);

Again, you'll need to change the table name, tblCensus, to whatever table
name you are using. Save the query with a name such as
qryUpdTblCensusColumns, but use your own table's name. While in the SQL
View pane, run the query. This query just updated the new fields in your
table with the parsed data from the Compound field.

Next, create a new query and open the SQL View pane. Copy the following SQL
statement and paste it into the SQL View pane:

ALTER TABLE tblCensus
DROP COLUMN Compound;

Again, you'll need to change the table name, tblCensus, to whatever table
name you are using. Save the query with a name such as
qryDropTblCensusColumn. Don't run this query just yet, because it will
remove the "Compound" field, and any form, query, property or VBA code
currently using this field will fail.

If you have Access 2003, run the Object Dependencies feature for this table
and check whether any of these dependent objects are specifically using the
Compound field. If they are, you will need to alter these so that they use
the new fields instead. If you don't have Access 2003, you may purchase a
tool, such as Speed Ferret or FMS Total Access Analyzer to search for these
items for you. Short of that, I would rename this field temporarily to
"Compound_Old," search through the VBA code for the word "Compound," and if
I found any, make changes to use the new fields, and test the entire
application and see whether anything breaks. Whatever breaks needs to be
fixed so that it uses the new fields.

When you are sure that nothing else is dependent on the original field, back
up the database, rename the altered Compound_Old field back to Compound,
then run the qryDropTblCensusColumn query to get rid of this field.
Compact/repair the database and you're done.

well thats, simple...

I agree. Good thing you're doing it now instead of later when you have 50
more queries, forms, and modules dependent on this table's original
structure. Good luck.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

 

[Larry Daugherty]

Ah, but it was and is my intention to help and the information I gave you
was indeed helpful to the situation which you posted. It doesn't help you
out of your present problem in the direction you want to go for the reasons
stated.

Nothing written was intended to scold or to chide you. But, to the extent
that I may have hurt your feelings, I apologize.

That you didn't capture the information or design its structure is about
laying blame and disavowing responsibility. Not my business except that the
problem you presented cause those of us reading it to assume that you are
the author. That you are not may be a good deal but the data design is
still screwy and apparently you now own it. If your client, boss, or
whomever has dictated the data design you might make it a point of educating
him or her to the fact that it will always be more expensive to manage that
if the data is in the tables in 3rd normal form.

People here are volunteers, helping as they can and as they choose.

If you are now in control of the application's design then I recommend that
you redo the data design and then build functionality on your better design.
If you need the permission of powers that be to redo it, I recommend that
you get it. It will be increasingly expensive to maintain a dysfunctional
data design. My response to your earlier post was intended to be more
helpful than helping you dig a deeper hole.

HTH