Connect a master table to repeating fields within other database 1

[gustavvs]

I've been building a database from a series of databases to our groups ownership information in one location. I managed to combine the names of the owners into one table that I want to use as a master.

The problem is that the other tables have need for name and ID information more than once within each table (we'll call it "items" here). I've tried all sorts of tricks including using several different queries to develop a way to a) create a drop down list so users can select a pre-existing name before entering a new one, and b) populate that information in each ID field (which can have different ID #)is stored in "items" so it can be referred back to the Names table when needed.

Needless to say, without much success.

So is there a niffy trick of linking a master information table to multiple fields within another table?

Example:
Names Table:
FName LName ID# Department

Items Table:
FName LName ID#
FName2 LName2 ID#2
FName3 LName3 ID#3
Department State Misc Updated etc...

Thanks

PS: Has anyone run into TRIM() RTRIM () etc actually not trimming when used within a SQL query?

 

[KenReay]

Not sure I fully understand your question, but I THINK you need an items table more like so

tblItems
sOwner ) Prime
sItemNo ) Key
lIDNo

in this way you avoiod repeating groups, which are not a good idea

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[gustavvs]

Maybe to clarify the question: I have need to reuse names multiple times in a single table (say column 1 is first name, column 2 is last name, and column 3 is ID. Column 4 would first name, etc...). They represent owners and contacts. I would like to have it so I can do a drop down so the users can pick from a list instead of retyping the name everytime they do a new entry.

Hopefully this help clarifies it.

 

[PHV]

Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[gustavvs]

Thanks PHV but this does not help in my problem. I've tried using different relationships to try to get around my problem but none worked.

To keep Names unique I need to be able to access it from the other tables others every the user will be entering Names in every table. Because of the type of information I'm collecting, I need access to store the Names ID more than once in a table while having a drop down box available so users can select an ID and see the name. That I've been able to do. The problem is that I can't get the name to appear/update on the form so the user

I've pointed the fields to the Names table and the ID to the "Items" table. I've pointed the fields in the form all to Names. etc... I can get the table to update one or two fields but not all the fields I need.

 

[lespaul]

could you please describe the tables and the results you want a little more clearly? Perhaps you could show us some data from your tables and how you want the results of the data in the final output and which records would meet your criteria.

thank you for helping us to help you.

 

[gustavvs]

LesPaul -

Here you go:

Names Table
Last Name, First Name, Employee ID#, Department

Jones, Bob, 123456, IT
Smith, John, 987654, Accounting


ID# is a primary key

Items Table
Items ID, Desc, Status, First Name, Last Name, ID#, Alt First Name, Alt Last Name, Alt ID# (Alt is alterative contact)

123, An Item, Active, Bob, Jones, 123456, John, Smith, 987654

The idea is that the Names table will replace ANY place where first name, last name appear and instead just have the ID# be the reference back to Names. HOWEVER, I need the name to appear in the form so people who are viewing know the person is without having to do a lookup.

I do this now where I only need the a name once. I can't seem to figure out how to get it where the data reference repeats more once in a table. As you see it is not redundent information.

The first name and last name need to be separate in the Names table because it involves a Main Frame feed.

Sorry for not being clearer before.

 

[PHV]

Items query:
SELECT I.[Items ID], I.Desc, I.Status, A.[First Name], A.[Last Name], I.[ID#], B.[First Name], B.[Last Name], I.[Alt ID#]
FROM (items AS I
INNER JOIN Names AS A ON I.[ID#] = A.[Employee ID#])
LEFT JOIN Names AS B ON I.[Alt ID#] = B.[Employee ID#]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[gustavvs]

I forgot to post thanks for y'all help.