Data Sort Question

[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

 

[Douglas J. Steele]

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

 

[Rolls]

"AS ConcatenatedKey"

Learned something new! Thanks.

 

[S Jackson]

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

 

[Douglas J. Steele]

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.

 

[Douglas J. Steele]

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.