Re: Concatenate and Null Values -- Features

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
    ... Hernandez' Database Design for Mere Mortals, which I will be acquiring as ... It is a unique number, so I could make it the PK, but I would be storing ... needs a two-field FK; in the third case I have one PK ... but arguably some redundancy in the date. ...
    (microsoft.public.access.reports)
  • Re: Join on varchar and int columns
    ... Identities are nice and useful, but they are not the only way to allocate ... >> internal key to store it as a varchar. ... >> Pro SQL Server 2000 Database Design ...
    (microsoft.public.sqlserver.programming)
  • Re: Updating table
    ... I need guidance on my database design: ... I have a table that lists out several tasks in field 1, ... Am I better off storing this info in 2 tables, ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Using IIF with multiple values
    ... You may want to rethink your database design. ... Instead of storing the data as six ... fields in Table1, you should add a second table so that each of the 6 values ...
    (microsoft.public.access.queries)
  • Re: How to tell if a database contains new data.
    ... code every form's update event to set and store the date, ... event would need to store the date. ... Access MVP 2002-2005, 2007 ... Even if no changes were made to the database design OR data. ...
    (microsoft.public.access.modulesdaovba)