Data Sort Question

  • Thread starter Thread starter S Jackson
  • Start date Start date
S

S Jackson

A few days ago I posted a question with a subject line Data Normalization -
Dispute. This is a continuation of that discussion, but involves a new
question.

Currently, the DHSNo field is a text field. The information contain is
entered in the following format: yy-xxxx-k. The yy represents the year.
The xxxx represents the case number and the k is a case type designation.

If I separate this field into three different fields: year (4-digit date
field), number (numeric field) and type (text field), can I print a case
list that will be sorted first chronologically by year and then numerically
by case number, disregarding the type field, and then print a report with
the case number appearing in the following format? Example:

01-0001-k
02-0001-k
02-0002-i
02-0003-k

I think the answer is yes. Am I right? Or does the text field at the end
cause a problem?

S. Jackson
 
Even though you're concatenating the fields for display purposes, you can
still sort on the underlying fields.

In other words, you can do something like:

SELECT Right(Format([MyYear], "0000"), 2) & "-" & [MyNumber] & "-" & [Type]
AS ConcatenatedKey
FROM MyTable
ORDER BY MyYear, MyNumber
 
Thanks for your help.

Now more questions:

Just as background, I have designed a database with MS Access to replace an
existing db designed with - I don't know what. I the original db was a
web-based product. The original db used a DHS No. entered into one field in
this format: yyyyxxxxk.

Question:

Scenario No. 1:
- Leave the text field DHSNo as one field in the new db with users entering
information in this format: yy-xxxx-k:
Question: Can I import information from the original db into the new db
with one DHSNo text field and have the information translated into the new
format (yy-xxxx-k)?

Scenario No.2:
Create three separate fields for DHSNo in the new db: year (date field),
number (numeric field) and case type (text field) - yyyy-xxxx-k
Question: Can I import the DHSNo field in the original db into the new db
with the three-field format?

Hope this isn't too complicated!
TIA
S. Jackson
 
Just want to make sure you're not reading too much into that!

All it does is create an Alias for the concatenated value. It doesn't make
it a key or anything.
 
Relational database theory will tell you that you should never combine
multiple values into a single field. Option 2 is definitely preferable

If your existing one field is always yyyyxxxxk, then you can write a query
that splits that into its 3 component fields as Left([OldField], 4),
Mid([OldField], 5, 4) and Right([OldField], 1) respectively, and then import
that query, rather than the table.
 
Back
Top