Access Lookup function procedure

[BYarn]

I am creating a database to log client activity. We have most of the details on the clients in another table/DB such as their account #, name etc. The new table has additional details that can only be recorded at client contact plus some fields with data that is the same as the other DB/table.
The problem: I want to be able to open the new DB and put in identifying info like account # and have it pull over info that is the same in the exisitng DB into new one.
Example: exisitng DB has, say acct number, client name and say address. New DB will have a field for accouint number, name, address but also the new fields. Instead of typing everything I want to pull it over so it appears when say the account # is entered.
I haven't worked with lookup tables so if anyone has an idea or can point me to a good website to read up or see examples???

THANKS!!!!!!!!!!!!!

 

[JoeAtWork]

Any chance you could link to the existing Clients table? Then in the new DB, you could create a Contacts table that only has the new fields, and create a relation to the existing Clients table.

This has the advatage of having less code, but what is much more important is that client information only has to be updated in one system.

 

[TheAceMan1]

How are ya BYarn . . .

First I have to believe [blue]clients can have more than one contact.[/blue] This locks the relationship of the tables (Clients as parent table, ContactInfo as child table) to a [blue]One to Many[/blue].

As for linking . . . you can't/shouldn't link Contacts to the new DB table for the following reason:

[blue]When you link a table to a DB and try to set any realtionship, [purple]you'll loose Referential Integrity[/purple] (it'll be disabled) and have to take care of it in code yourself!

You need [purple]Referential Integrity[/purple] to prevent the user from entering data in the ContactInfo table (the child) before data is entered in the Clients table (the parent). Without it you could easily generate orphan records in the ContactInfo table . . .[/blue]

To get around this setup the new table in the DB that holds the Client table. Make the relationship there and then link both tables to the new db . . .

Ideally your form in the new DB will be a [blue]mainform Of Clients[/blue] with a [blue]subform of ContactInfo . . .[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

 

[BYarn]

Thanks for the feedback! To try to comment of both answers:Yes I can link to the clients table and yes there could (but not likely) be more than one contact.

Rather than link directly to the original clients table, how about I create a "data" table with the client info table and the new contact table which is linked to client info. Ithen use a separate db to work in (and perhaps use the main/sub form in a form? (does that make sence or are we saying the same thing? Ideally I would like the mainform to pull in any existing info from exisitng data so it is faster!.

 

[TheAceMan1]

BYarn . . .

Consider since your working with realworld data you need [blue]the new DB should be split (frontend/backend) anyway![/blue] Why? . . . other than the tables themselves, all design changes are made in the frontend [blue]leaving the intergrity of the data in the backend intact/untouched.[/blue] What if you had one or more additional tables to consider in this secnario? . . . What would you do then? . . . [blue]Hellooooo linking![/blue] Albeit there's a finite cost in speed (even if the DB's reside on the same local drive) but for all my experience its a small price to pay compared to the power of linking and the ease of design it provides. Also consider with the speed of todays machines I doubt you'd notice any speed loss unless your talking server.

[blue]Rather than link directly to the original clients table, how about I create a "data" table with the client info table and the new contact table which is linked to client info.[/blue]

A 3rd table only compounds your problem. How would you intend to get the data into this 3rd table and keep it synchronized with the DB holding the Clients table? Sounds like import to me.

[blue]Since your worried about speed you may want to consider reversing the residence of the Clients table. Put the Clients table in the new DB (making it local) and link the table in its former residence from the new DB!)[/blue]

My prior post provides the following attributes for design of this secnario:
[ol][li]No relational DB design rules are violated![/li]
[li][blue]No synchronizing of the Clients tables in the two DB's is required[/blue] since they both read/write to the same table![/li]
[li]For both DB's client data is [blue]automatically displayed![/blue][/li]
[li][green]Its easy to do and design time is minimal![/green][/li][/ol]
What more could you ask for!

I'm not saying you don't know but at your leisure, grab your favorite beverage and parse thru the following links (the heart & soul of any DB). Understand these and the door will be open . . .

Fundamentals of Relational Database Design

Normalizing Tables

Table Relationships

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .