Re: Concatenation for one field from two others in same table

Tech-Archive recommends: Fix windows errors by optimizing your registry

Thanks to Bob & John for your responses. I was hoping to accomplish this:
Use the client bill to ID (12AMR1234 as an example; Field 1) and the
individual job site sequence number (001 - many to one relationships; Field
2) to combine a unique client ID for use in access and set up as the primary
key (12AMR1234-001; 12AMR1234-002, 12AMR1234-003, etc.; Field 3).

I'm downloading the client files from a older DOS system (that still is the
primary business system running our business) into .xls and then importing it
as my base customer file in Access. It contains basic client information
along with those 2 identifying fields mentioned above to utilize as unique
identifiers to each client. Access will provide my team a more robust CRM
solution that the DOS cannot once the database is built.

If I let Access assign the primary key (1,2,3,4,etc. through an autonumber
choice) then the next time I download from the DOS system(weekly), I won't
maintain integrity because new clients will have been added from DOS - I.E.
what was once 2 might now be 4 so the CRM data entered won't match the right
client.
--
Dale


"boblarson" wrote:

Okay, now I have it. It is best to use an autonumber primary key and then
create a multi-field INDEX on the field you don't want duplicated.

I will post back soon with a URL to go see how to create that multi-field
index (in screenshots) so that it is easy to understand. An explanation here
might be too confusing.
--
Bob Larson
Free MS Access Tutorials and Samples at http://www.btabdevelopment.com

__________________________________


"John W. Vinson" wrote:

On Sat, 24 Jan 2009 16:18:01 -0800, boblarson
<boblarson@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

However, it is not necessarily a good thing to use a composite key as it can
be very difficult for novice users to set up the "foreign key" equivalent in
their other tables. I personally would just say - go for the autonumber
primary key and then use validation to ensure no duplicates. But that's me.

Validation, or a separate unique two-field index. Agreed about the foreign
keys!
--

John W. Vinson [MVP]

.

Relevant Pages

  • Re: TCP/IP comms problems between WinXP and DOS
    ... I have written client and server versions ... In the instance where I have a problem the DOS system is running as client, ... By simple changing of i/p addresses / network names I have run the client ...
    (microsoft.public.dotnet.languages.vc)
  • Re: Simply Accounting
    ... So now that directly updating client info is not looking to promising. ... how can you even assign the primary key to the reocrd you plan to add to ... Unfortunately we can't let that intern out of ...
    (microsoft.public.access.modulesdaovba)
  • Re: Access complains of duplicate key
    ... one-to-many referential integery, and set the field indexed, no duplicates? ... There really is only one assessment per client. ... with the same primary key value in each table. ...
    (microsoft.public.access.formscoding)
  • Re: Simple MYSQL Normalization question - input requested.
    ... a way that would sit that client and that client alone. ... int cost_id primary KEY ... a mail order catalog once a month of items for sale, ... Category3 and Category4 fields to the item database? ...
    (comp.lang.php)
  • Re: Grouped MAX Records?
    ... The subquery returns the primary key value of the record where the client ... Allen Browne - Microsoft MVP. ... >> Allen Browne - Microsoft MVP. ...
    (microsoft.public.access.queries)