cross reference data bases

[Rhonda]

I have a master client database and a proposal database. In the client master db we use a lookup on the proposal db to populate the master client db. What I need to do is after the new client record has been created, I need for the client number to be then written back to the proposal database. The relationship key being the proposal number.

Can someone point me in the correct direction to get this accomplished?

[Rhonda]

I have a master client database and a proposal database. In the client master db we use a lookup on the proposal db to populate the master client db. What I need to do is after the new client record has been created, I need for the client number to be then written back to the proposal database. The relationship key being the proposal number.

Can someone point me in the correct direction to get this accomplished?

[Rhonda]

I have a master client database and a proposal database. In the client master db we use a lookup on the proposal db to populate the master client db. What I need to do is after the new client record has been created, I need for the client number to be then written back to the proposal database. The relationship key being the proposal number.

Can someone point me in the correct direction to get this accomplished?

[Rob 7 Collins]

I'm a little unclear.

Sounds like you have a one-to-many relationship. The one-side is the proposal table (each proposal has one and only one master), and the many side is the master table (a master can have many proposals).

Do you have a record in the master table for each proposal? (I hope!)

--This might be recreating the wheel, and there are several steps involved, but they are all fairly basic. Hope this helps!

Assuming multiple records in the master table, normalized data in the tables, and enough information in the master table exists to say that any record's client fields that match any other record must both refer to the same client:

Find all records in the master table who's client-relevant fields are the same.

Write/run a script that calls itself passing the key of the current visible record in the master table, goes to the next record (don't forget to error capture and stop the self-calling!), compares the keys. If they are equal, it calls itself with the key (again). Otherwise, it creates a record in a new table, named maybe "clients" -- that auto-enters it's key and has fields where appropriate for client data. Finally it calls itself and passes the key from the current visible record in the master.

Swap the shown and ommitted records. These are clients with only 1 proposal.

Write/run a script to iterate through each record in the found set, and populate the client table -- the effect is the same result as the above script, but you don't need recursion or parameters to do it.

At this point you have a populated client's table, and unchanged and populated proposal and master tables.

Create a table that contains all the fields you want from the master table, that don't exist in the proposals table or clients table, but it has a field for the client key. It could be called the "client_proposal_map" -- at a minimum, it need only contain its own key, a field for the client table key, and a field for the proposal table field.

Show all records in the master table.

Write/run a script that iterates through each record, copy/paste each field necessary to find the client table record that matches, run the find. Copy that client record's primary key, and put it in a new record in the map table. Copy the master table proposal # and paste that in the map table's same entry.

That's it.

(the master table is no longer necessary But it was useful for what you wanted to build)

If you don't have the proposal numbers in the master table ... like you populated the master table with only certain elements from the proposal table ... well, maybe you'd consider rebuilding the master table?

cheers,

RobC

[Rob 7 Collins]

I'm a little unclear.

Sounds like you have a one-to-many relationship. The one-side is the proposal table (each proposal has one and only one master), and the many side is the master table (a master can have many proposals).

Do you have a record in the master table for each proposal? (I hope!)

--This might be recreating the wheel, and there are several steps involved, but they are all fairly basic. Hope this helps!

Assuming multiple records in the master table, normalized data in the tables, and enough information in the master table exists to say that any record's client fields that match any other record must both refer to the same client:

Find all records in the master table who's client-relevant fields are the same.

Write/run a script that calls itself passing the key of the current visible record in the master table, goes to the next record (don't forget to error capture and stop the self-calling!), compares the keys. If they are equal, it calls itself with the key (again). Otherwise, it creates a record in a new table, named maybe "clients" -- that auto-enters it's key and has fields where appropriate for client data. Finally it calls itself and passes the key from the current visible record in the master.

Swap the shown and ommitted records. These are clients with only 1 proposal.

Write/run a script to iterate through each record in the found set, and populate the client table -- the effect is the same result as the above script, but you don't need recursion or parameters to do it.

At this point you have a populated client's table, and unchanged and populated proposal and master tables.

Create a table that contains all the fields you want from the master table, that don't exist in the proposals table or clients table, but it has a field for the client key. It could be called the "client_proposal_map" -- at a minimum, it need only contain its own key, a field for the client table key, and a field for the proposal table field.

Show all records in the master table.

Write/run a script that iterates through each record, copy/paste each field necessary to find the client table record that matches, run the find. Copy that client record's primary key, and put it in a new record in the map table. Copy the master table proposal # and paste that in the map table's same entry.

That's it.

(the master table is no longer necessary But it was useful for what you wanted to build)

If you don't have the proposal numbers in the master table ... like you populated the master table with only certain elements from the proposal table ... well, maybe you'd consider rebuilding the master table?

cheers,

RobC

[Rob 7 Collins]

I'm a little unclear.

Sounds like you have a one-to-many relationship. The one-side is the proposal table (each proposal has one and only one master), and the many side is the master table (a master can have many proposals).

Do you have a record in the master table for each proposal? (I hope!)

--This might be recreating the wheel, and there are several steps involved, but they are all fairly basic. Hope this helps!

Assuming multiple records in the master table, normalized data in the tables, and enough information in the master table exists to say that any record's client fields that match any other record must both refer to the same client:

Find all records in the master table who's client-relevant fields are the same.

Write/run a script that calls itself passing the key of the current visible record in the master table, goes to the next record (don't forget to error capture and stop the self-calling!), compares the keys. If they are equal, it calls itself with the key (again). Otherwise, it creates a record in a new table, named maybe "clients" -- that auto-enters it's key and has fields where appropriate for client data. Finally it calls itself and passes the key from the current visible record in the master.

Swap the shown and ommitted records. These are clients with only 1 proposal.

Write/run a script to iterate through each record in the found set, and populate the client table -- the effect is the same result as the above script, but you don't need recursion or parameters to do it.

At this point you have a populated client's table, and unchanged and populated proposal and master tables.

Create a table that contains all the fields you want from the master table, that don't exist in the proposals table or clients table, but it has a field for the client key. It could be called the "client_proposal_map" -- at a minimum, it need only contain its own key, a field for the client table key, and a field for the proposal table field.

Show all records in the master table.

Write/run a script that iterates through each record, copy/paste each field necessary to find the client table record that matches, run the find. Copy that client record's primary key, and put it in a new record in the map table. Copy the master table proposal # and paste that in the map table's same entry.

That's it.

(the master table is no longer necessary But it was useful for what you wanted to build)

If you don't have the proposal numbers in the master table ... like you populated the master table with only certain elements from the proposal table ... well, maybe you'd consider rebuilding the master table?

cheers,

RobC