[AccessD] Number vs text data type

Michael R Mattys michael.mattys at adelphia.net
Tue Oct 14 06:09:29 CDT 2003


Cynthia & All,

A client was exporting from an accounting package that
uses text keys into a 'Main' Access database.
I was to work on a db that linked the Customers table,
read-only, where the primary key was two concatenated
fields to ensure uniqueness.

Because I could not edit/update the data in the main database,
which was daily updated by the accounting package, I had
to redundantly store the data in a Local Customers table.

A combobox rowsource in this database was a union query
of 'Main' Customers and Local Customers.

Consider this combo on a subform that writes to tblDailyActivity.
The addition of new customers (not-in-list) means creating an
identity function for new data unique to the division that uses the
linked Customers table because the union query requires that the
primary keys be of the same data type (text).

Data must be written to the Local Customers table from a new
CustomerInfo form, the combobox must requery, and the
DailyActvity table will now store the data without creating
a blank row (you'd have to try it to see what I mean).

The point is that AutoNumber or Long Integer is
a far better choice of primary key if possible.

Michael R. Mattys
www.mattysconsulting.com



----- Original Message -----
From: "CYNTHIA SPELL" <CSPELL at jhuccp.org>
To: <accessd at databaseadvisors.com>
Sent: Monday, October 13, 2003 7:24 AM
Subject: [AccessD] Number vs text data type


> I inherited a budget number database with the primary key being a 7-digit
number.  The data type for the field is currently number (long integer).
Since the field requires no calculation, just some sorting, I'm considering
changing the data type to text.  The budget number table links to quite a
few other databases, so I'll have to change the field in other places.  Is
there a benefit to or problem with using one or the other data type?  I
remember an instructor once saying that if there's a choice between using a
text or number type, use number since numbers sort faster than text, but I'm
not so sure he knew what he was talking about.
>
> TIA,
> Cindy Spell
>
>
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>



More information about the AccessD mailing list