A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Primary Key w/Two Means of Data Input



 
 
Thread Tools Display Modes
  #1  
Old December 14th, 2008, 04:13 PM posted to microsoft.public.access.tablesdbdesign
Dale
external usenet poster
 
Posts: 292
Default Primary Key w/Two Means of Data Input

Here is a brief description of what I'm doing before asking the question.

I'm building a CRM type solution for tracking customer information -
contacts, quotes, etc. We have existing customer information that I can
download from our old, still in use, DOS system into an excel file that I can
import/link into our Access 2003 CRM Database. These customers have a unique
customer number: 12ACC1234 as an example. I figured I should use 12ACC1234
for a primary key so that when I update the excel file at some point in the
future, I can maintain integrity with the tracking CRM data we've added in
Access. If I assigned a generic primary key from Access (i.e. 1, 2, 3, 4)
that when I refresh the excel sheet with new customers entered into the old
DOS system, there would be a missmatch - customer number 12ACC1234 may have
had a primary key of 15 but now is 16 since we've entered a new customer in
the old DOS system that falls before customer 12ACC1234.

My dilema is a prospective customer that we want to create new in Access to
track CRM data but they won't as of yet be entered into the old DOS system -
only when we sell a product or service will a customer be given a customer
number such as 12ACC1234 in the old DOS system.

So the question is: am I faced with keeping two sets of tables of CRM data
and therfore two different primary keys? One based on existing clients
(12ACC1234) and one based on potentail clients (1,2,3,4)?

Thanks in advance from this novice user!
--
Dale, wishing we had one ERP database!
  #2  
Old December 14th, 2008, 05:06 PM posted to microsoft.public.access.tablesdbdesign
Stefan Hoffmann
external usenet poster
 
Posts: 991
Default Primary Key w/Two Means of Data Input

hi Dale,

Dale wrote:
So the question is: am I faced with keeping two sets of tables of CRM data
and therfore two different primary keys? One based on existing clients
(12ACC1234) and one based on potentail clients (1,2,3,4)?

The question is: What does happen with your old system? Do you migrate
to your new one? Do you use this customer number as a reference number
in (all) other systems.

While your in migration, I think, you need to create the customers in
your old system, otherwise you may get the mentioned conflicts.

But it is not wrong to design your new system using surrogat keys
(autonumbers).

I would use:

Table Customer:
ID, Primary Key, Autonumber
ReferenceKey, Text(), Unique Index
ReferenceImported, Boolean

Depending on the answers to the questions above, you may consider
storing the numbers in an extra table for imported values only:

CustomerReference:
CustomerID, Foreign Key
ReferenceKey, Text()



mfG
-- stefan --
  #3  
Old December 14th, 2008, 08:36 PM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Primary Key w/Two Means of Data Input

On Sun, 14 Dec 2008 07:13:00 -0800, Dale
wrote:

If I assigned a generic primary key from Access (i.e. 1, 2, 3, 4)
that when I refresh the excel sheet with new customers entered into the old
DOS system, there would be a missmatch - customer number 12ACC1234 may have
had a primary key of 15 but now is 16 since we've entered a new customer in
the old DOS system that falls before customer 12ACC1234.


Don't confuse an AUTONUMBER with a PRIMARY KEY. Microsoft does what it can to
foster this confusion by suggesting an autonumber when you create a table -
but an autonumber is *not* the only way to get a PK! A Primary Key can be a
Number, a Text value, even a Date/Time - anything but a Memo field. It can
even consist of multiple fields (up to ten in fact).

If your customer number is stable, unique, and can be generated easily, by all
means use a Text datatype containing 12ACC1234 as your primary key. This will
maintain consistancy with your existing system and should work fine.

--

John W. Vinson [MVP]
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 07:06 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.