3 tables seperated need to go to same records in each

[RobotMush]

I have inherited 3 seperate records that have the same clients but different data. Is there a way to fix a hyper link field for a client in the main table to go to or bring up the equivalent client in the other table?

Or would something else be easier to do?

(Just to note:At this point I am working on getting the records of one table matched up with the records of the main table.)

Any help or suggestions would be greatly appreciated.

Thank You
RobotMush (Technical User) Self Taught

 

[deadzed]

It sounds like you "may" want to use a Union Query if your field names are the same.

Create a New Query.
In Design View select View->SQL View
In the resulting window type something like this:
(The following union query uses the UNION ALL statement to retrieve all records, including duplicates):

SELECT [CompanyName], [City]
FROM [Suppliers]

UNION ALL SELECT [CompanyName], [City]
FROM [Customers];

CompanyName and City are your field names from the tables Suppliers and Customers. This code was retrieved from the MS Access help file when I searched for Union Query

 

[RobotMush]

I have a general idea of what you are telling me. Now both tables do not match up exactly. Because these were at one time two completly seperate tables the fields have the same heading name but you might have "Smith, Jarvis S." in one and "Smith, Jrvis S." in the other. Would the union query still work?
I'll run a help check on Union Queries.

Thank you for your help
RobotMush (Technical User) Self Taught

 

[RobotMush]

I tried the union query but I'm at a lost as to what to do with the information now. How can this help me to join three tables that were created completly seperate from the other and the only fields that I can match are the Client Name and Number?

RobotMush (Technical User) Self Taught

 

[deadzed]

Hmmm...are the Client Numbers the same in each table? Are you wanting to combine all 3 old tables into one new table?
If the Client Numbers are the same in each table you can create a "Make Table Query" linking off of the Client Numbers.
If you want to leave the data in 3 seperate tables, you could create a main form based off of one table with two subforms based off of the other two tables and parent/child off of the Client Number.

 

[RobotMush]

>If you want to leave the data in 3 seperate tables, you >could create a main form based off of one table with two >subforms based off of the other two tables and >parent/child off of the Client Number.
That is what I am wanting to do. However, one table has the Client Name, and Client Number. Another table has the Client Name (with some misspellings) and the third has the Client Name mixed in with other data in the same field.
I will be able to get all three tables to join the other properly and do what you suggest. What I am wanting to do, if possible is to be able to look up the client, then choose a hyperlink or pointer to bring up one of the three tables and imediatly go to that record.

Thank you and Any suggestions?

RobotMush (Technical User) Self Taught

 

[deadzed]

If you'll be able to get all three tables to join up, then you should be able to create a query as well, that contains all of the data from all three tables. Once you have created that query, in the ClientID field you could add criteria to match the ClientID textbox on your main form like this: [Forms]![MainForm]![ClientID]

Create a seperate form and design it with the information you would like to display. For the RecordSource for this form you would choose the query that you created in the previous step.

Back on your main form, you can add a command button that will open your display form when clicked. This will open up your Client Information form and will display the record you selected because of the criteria that's in the underlying query.

Did that make any sense?

 

[RobotMush]

Did it make sense? Somewhatly. Close enough for me to believe that it is what I am wanting to do. Will print it out so that I can muddle over it while I try to follow the instructions.

Thank you for your help

RobotMush (Technical User) Self Taught

 

[FastLearnerIThink]

You have to have a unique field which you can connect through all tables. Unfortunately you don't have the client number on all tables (it would have been the simplest solution).

If names are mispelled, you will probably need to correct the misspelled names on the tables first to be able to link them using client names. And once you do, highly recommend having the client number in all three tables and using that as a main link.



-- Fast Learner, I Think

Here's something to think about. How come you never see a headline like 'Psychic Wins Lottery!'? - Jay Leno

 

[RobotMush]

Thank you FastLearner,I Think... I am having to do that now Matching Record ID with the names. Eventually I hope to have all three set up and running so that I can look up one name and be able to eather see data from he three tables or go to the tables and automaticall go to the record.

Wish me luck

RobotMush (Technical User) Self Taught