Re: Concatenate and Null Values -- Features

I have to say it's hard for me to see how a sequence number such as I have
described or a sequential invoice number (00001, 00002, etc.) is meaningful
except that it is the real-world reference number that people will use to
identify the form, invoice, or whatever. In the end it just represents a
chronological sequence of records.
Here's something I'm wondering. I can store 01, 02, etc. and combine that
with the year part of a date field, but I cannot use a unique index for the
field because it starts over with 01 next year. If it is indexed (allow
duplicates), I don't see how the order will be meaningful, so I suppose the
best thing would be not to bother with an index on that field at all. I
could sort by date, since that is the order in which I wish the records to
appear (filtered in some cases, but in chronological order), but I suppose
that would happen in a query, or in a form's OrderBy property, or in a
report's Sorting and Grouping, rather than as an index.
I should probably mention that this database is unlikely to generate more
than 30 records per year, so inefficient indexing and sorting will probably
make little discernible difference. However, I'd like to develop good
habits now, so I try to determine what would be best with millions of
records. I wonder if storing 07-01 (probably without the dash, which I
would add just for display) would allow more efficient ordering of the
records.
I think I know what you're saying about learning by teaching. While
answering questions in these forums is not quite the same as teaching,
sometimes I see a problem that I know I would like to solve some day, so I
go ahead and experiment with it. Sometimes I find the question has already
been answered by the time I come up with a solution, but that's OK, because
it usually either confirms what I have done or shows me a better way to do
it.

"strive4peace" <strive4peace2006@xxxxxxxxx> wrote in message
news:OCDycPdNHHA.5104@xxxxxxxxxxxxxxxxxxxxxxx
Hi Bruce,

IMHO, every table should have an autonumber primary key (even if you do
not need it for a relationship) and it is not a good idea to use
meaningful data for relationships.

On the field with 07-01, 07-02, etc, you could set a Unique index so
duplicates would not be allowed. If you HAVE the date this comes from,
there is really no reason to store it because it can always be calculated.

"embarassed by some of my early attempts"

I know how you feel! Every day, I learn more about Access -- each
application is better than the last ...

I learned Access by teaching it; I had been programming with DOS-based
databases for about 10 years when I was approached to teach Access running
under Windows 3.0 <g> ... they just asked if I could do it, not if I had
ever used it ;) So I crammed like crazy and no one ever knew that the
teacher was as new as the students! I researched every question that
anybody had, and was usually making handouts for the afternoon during my
lunch break. Teaching is a great way to learn.

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



BruceM wrote:
I've only been doing this for a few years (and not full-time at that),
and am already embarassed by some of my early attempts. I am aware of
Michael Hernandez' Database Design for Mere Mortals, which I will be
acquiring as soon as I get organized enough to order it. The light has
come on for me, but I'm still squinting a lot. I'm also trying to decide
how much improvising I can do. For instance, I have a situation in which
a number increments 07-01, 07-02, etc., then starts over with 08-01 etc.
next year. It is a unique number, so I could make it the PK, but I would
be storing part of the date, which is already in another field. I could
either add a PK field, combine two fields into a PK (although I'm not
sure what the other field would be), or just go ahead and store 07-01,
etc. In the first case I am creating a new field in the parent table
(autonumber, maybe); in the second I am creating a new field in the child
table, since a two-field PK needs a two-field FK (as I understand it); in
the third case I have one PK and one FK, but arguably some redundancy in
the date. So I ask myself if storing part of the date like that is less
redundant than adding a field just so I can avoid storing part of the
date. I've about decided to store 07-01, but my point is that I am
thinking about this in a way that would not have been possible a year
ago.

"Tom Wickerath" <AOS168b AT comcast DOT net> wrote in message
news:D088C525-31BB-4DB7-AF4A-167A33ED48B5@xxxxxxxxxxxxxxxx
Hi Bruce,

For what it's worth, I floundered like a fish out of water for a few
years
with Access. I had been attempting to work with Access since version 2
was
released. The light finally came on for me one evening back in '96 (or
perhaps '97) when I was at a meeting of the Pacific NW Access
Developer's
Group. The speaker was Michael Hernandez, that author of the paper I
cited in
my previous post. Several of the examples of problems he showed were the
exact types of problems I had been encountering. I made an effort to
start
studying database design after that, even though the subject can be a
bit
dry. The effort really payed off.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

"BruceM" wrote:

Hoopster, this is a lot of stuff being tossed your way, but it is
really
good stuff. The people who have added to this thread since you posted
the
table structure are very skilled and knowledgeable (excepting this
posting;
I can claim only that I am making decent progress for a relative
beginner,
but I know good advice when I see it). I urge you not to be daunted by
all
of the new information. Once you start to get the hang of relational
databases you will have a powerful tool at your disposal.


.

Relevant Pages

  • Re: Concatenate and Null Values -- Features
    ... I hate to be a pest but I have been reading ACC: Database Normalization ... Data Table and linked to this one using the Audit Number. ... I wonder if storing 07-01 (probably without the dash, ... I think I know what you're saying about learning by teaching. ...
    (microsoft.public.access.reports)
  • Re: problem using identity column as primary key
    ... >> I am thinking of creating an identity column to use it as primary key ... More and more programmers who have absolutely no database training are ... the gap in the sequence is not filled in and the sequence ... vin CHARNOT NULL REFERENCES Motorpool); ...
    (microsoft.public.sqlserver.programming)
  • Re: Table Design Question
    ... > requires more than two probes, no matter how large the database. ... > acceptable (in the relational model) to have an Identity attribute to ... the gap in the sequence is not filled in and the sequence ... > vin CHARNOT NULL REFERENCES Motorpool); ...
    (microsoft.public.sqlserver.programming)
  • Re: Ideas on storing and retriving files in a Windows Application
    ... files etc. 2) Because it is stored in a relational database, ... blobs takes some minor modifications other than just using SELECT blah FROM ... horror stories of people who started storing BLOB data in a db only to have ... > will open the appropiatte application to display the file and 2. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Number + 1
    ... Using the same number in a variety of databases may be a reason for storing ... incremented portion and the extended portion. ... The value for dmrdate is assigned automatically as today's date and time, ... My experience not only with my database but with other customers & ...
    (microsoft.public.access.gettingstarted)