Hyphenated numbers

[google3luo359]

Any hyphenated number experts around here?

I have a db whose primary key field in all tables is about to change
from a regular 9 digit number to a hyphenated 9 digit number. Eg.
800400200 will be going to 800-400-200.

There are many ways to approach this and I'd like input on the best
way.
I'd like to know how I should store the numbers in my Access tables,
with or without hyphens?
If I store with hyphens, are these still number fields, and if so
which format do I chose for the field designation in the table?

If I store without hyphens I could always format the numbers to appear
on forms with the hyphens for easier reading.
But how should I approach importing these numbers?
I don't know if I can get these numbers without hyphens (I'll find
out on Monday).
If I can't I guess I'd have to create a query to remove the
hyphens if that's the better format for Access.

TIA Ric

 

[Marshall Barton]

Any hyphenated number experts around here?

I have a db whose primary key field in all tables is about to change
from a regular 9 digit number to a hyphenated 9 digit number. Eg.
800400200 will be going to 800-400-200.

There are many ways to approach this and I'd like input on the best
way.
I'd like to know how I should store the numbers in my Access tables,
with or without hyphens?
If I store with hyphens, are these still number fields, and if so
which format do I chose for the field designation in the table?

If I store without hyphens I could always format the numbers to appear
on forms with the hyphens for easier reading.
But how should I approach importing these numbers?
I don't know if I can get these numbers without hyphens (I'll find
out on Monday).
If I can't I guess I'd have to create a query to remove the
hyphens if that's the better format for Access.

Since these are identifiers, not numbers that are used in
calculations, they should probably have been in a Text field
already.

OTOH, the dashes are just a formatting thing that should
probably be optional when importing or during data entry.
Think about not storing them in the field and removing them
if they are they are entered. This is easy to do using
Replace(thefield, "-", "")

If the data may have other idiosyncracies such as missing
leading zeros, then you have to massage the field to get it
into its standard form anyway so the above is just one more
step in the process.

Then use a custom format to display them:
@@@\-@@@\-@@@

OOTOH, the reverse is also true, you can break it up and
insert the dashes when needed on data entry in case they
were missing. For searching and sorting purposes, the
important thing is to have the same standard form in every
record.

 

[google3luo359]

Thanks very much Marshall.

It's good to know I have several options available.
I didn't know that I should have been using text fields all along.
So they will sort/treat numbers as identifiers, just as number fields
would?

So you are recommending not storing the data with hyphens?
Would it make any difference if I did?
Could I treat all the primary key identifiers in the database the same
way throughout; using hyphens? Would it pose any problem in queries
etc.?

TIA Eric

 

[Marshall Barton]

Thanks very much Marshall.

It's good to know I have several options available.
I didn't know that I should have been using text fields all along.
So they will sort/treat numbers as identifiers, just as number fields
would?

So you are recommending not storing the data with hyphens?
Would it make any difference if I did?
Could I treat all the primary key identifiers in the database the same
way throughout; using hyphens? Would it pose any problem in queries
etc.?

Well, I don't want to sound wishy-washy here, but I was
trying to say that it doesn't matter if the hyphens are
included or not (except that fewer characters has a very
small advantage). What is critically important is that all
records with this value be in the same style.

How you import external data could be the deciding factor.
If you are using something like TransferText on a lot of
files on a routine basis, then you should probably go with
the style in the imported data. Don't forget that, as long
as it is a Text field, it's pretty easy to fix up the data
after importing by using an UPDATE query on each table that
has these values:

To remove the hyphens:
UPDATE table SET field = Replace(field, "-", "")
WHERE field Like "*-*"

or to add the hyphens:
UPDATE table SET field = Format(field, "@@@\-@@@\-@@@")
WHERE Not field Like "*-*"

OTOH, if you need to use a VBA procedure to parse the data
before saving it to your table, then you can massage it to
either style as part of the process.

 

[google3luo359]

To remove the hyphens:

UPDATE table SET field = Replace(field, "-", "")
WHERE field Like "*-*"

or to add the hyphens:
UPDATE table SET field = Format(field, "@@@\-@@@\-@@@")
WHERE Not field Like "*-*" ......

Excellent! Thanks very much Marsh.
I'll have to think about which way to go.

Right now I'm leaning towards importing the file with hyphens directly
into a Temp table.
The Temp table will be set up exactly like the main table (all text
fields now) and then I'll throw the data into the main table with an
update query.

Can I use an update query to get the ball rolling (entering the first
set of student data into an empty table)? Or do I have to use code to
check if the table is empty and use an append query instead?

After the first transfer into the main table there will always be data
in it and an update query will be proper.

TIA Ric

 

[Marshall Barton]

Excellent! Thanks very much Marsh.
I'll have to think about which way to go.

Right now I'm leaning towards importing the file with hyphens directly
into a Temp table.
The Temp table will be set up exactly like the main table (all text
fields now) and then I'll throw the data into the main table with an
update query.

Can I use an update query to get the ball rolling (entering the first
set of student data into an empty table)? Or do I have to use code to
check if the table is empty and use an append query instead?

After the first transfer into the main table there will always be data
in it and an update query will be proper.

I'm not sure I understand what you're saying about using an
UPDATE query to move data. Maybe your imported data is a
newer version of the same records?? If so, then an UPDATE
query may be sufficient. However, if there might be some
new records in the imported data, you will need an append
query for those records.

In this kind of situation I usually use two queries in this
order. The first updates the existing records and the
second adds the new ones.

UPDATE table LEFT JOIN temp
ON table.ID = temp.ID
SET table.somefield = temp.somefield
WHERE temp.ID Is Not Null

INSERT INTO table
SELECT temp.ID. temp.somefield
FROM temp LEFT JOIN table
ON temp.ID = table.ID
WHERE table.ID Is Null

I fyou need to massage the ID field to add or remove
hyphens, you can use an expression linke one of the one I
posted earlier in the ON clause and ing the second query's
field list.

 

[google3luo359]

I'm not sure I understand what you're saying about using an
UPDATE query to move data. Maybe your imported data is a
newer version of the same records?? If so, then an UPDATE
query may be sufficient. However, if there might be some
new records in the imported data, you will need an append
query for those records.

Sorry Marshall I wasn't thinking too clearly when I wrote the last
message.

Once the initial data is in the students table, then each year the
process for updating the data will be the following:
1. Delete students who haven't logged in for more than 2+ years (that
time may be adjusted)
2. Run a query to increase the grade level for all students. (All
students automatically move up a grade at our school.) Grade level is
very important in this db.
3. Import the full students list (student#, last,first,grade) into
StudentsTemp table.
4. Run a Find Unmatched query with Students and StudentsTemp tables to
find the new students.
5. Run an append query to add these new students to the Students table.

I don't know why I was thinking about an Update query earlier.

Ric

 

[Marshall Barton]

Right! That sounds like a workable scheme.
Go to it ;-)