Access 2007 Changing My Data Types

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello:

I'm kinda a noob at Access, but am trying to make a fairly sophisticated
database.

One simple thing that is bothering me; I have a main table (a line listing
of patients) and then I have multiple other tables which I am using lookup
wizard to link so that for many of the items for each patient, you can only
choose a pre-defined value.

This seems to be working well, but for some reason, I have some items where
when I do this, it automatically changes the data type from Text to Number
for reasons that I cannot understand.

Of note, I had originally designed these cells to be numbers, but then went
in and changed everything to text.

I haven't done so yet, but I could completely delete all these references
and sub tables because it wouldn't take very long and I assume this will fix
things (idunno?); but am wanting to know some reasons why this could be
happening in the first place.

This probably doesn't make any sense at all, but I would appreciate any tips
that might steer me in the correct direction.

Thanks!
 
Of note: I am noticing that Access is making this change to any fields which
I do the lookup wizard now..... I can't seem to figure out any pattern.

Any help would be appreciated; otherwise, I will have to recreate the entire
database (thankfully, I'm still in the phase of just creating the structure;
I've only entered in two fields of data so far for testing purposes).
 
This seems to be working well, but for some reason, I have some items where
when I do this, it automatically changes the data type from Text to Number
for reasons that I cannot understand.
Click to expand...

That's precisely what the Lookup Wizard does, and one of many reasons that
lots of developers despise it. See
http://www.mvps.org/access/lookupfields.htm
for a critique.

A Lookup Field is *STORED* as a number, a foreign key link to the primary key
of the lookup table. That fact is concealed from your view by the combo box in
the table datasheet. The table LOOKS like it contains text, but it doesn't; it
contains a numeric value which you cannot see.

A2007 is moving more and more away from the normalized database paradigm (with
lookup fields being emphasized, subdatasheets, and <gag> multivalued fields).
If you understand what Access is doing "under the hood" and want to work with
these features, more power to you - but they are NOT essential; you can very
easily (say) create a Form with a combo box on it, even if there is no lookup
defined for that field in your table.

John W. Vinson [MVP]
 
Interesting; So I think I see the pattern now actually. I had all these
additional tables where the primary key was assigned to a unique text
variable. But I'm trying to figure out what the primary key is for and such,
and decided to change this to an autonumber - I guess that explains why the
variable in the main table changed from a text to a number.

Thanks!
 
The primary key is simply a unique identifier to the information to each
record within the fields in your table. Access needs this to distinguish the
records from one another.
 
Back
Top