Field showing bound column ID instead of name 1

[snoopy92211]

Ok. This is driving me insane! I have a table, BankData, that lists bank information. It's a pretty simple table, just a primary key, and a bank name.

I have several tables that use this bank name as a field.

For example, I have an Investments table that uses a bank name field(field name is Borrower). For this field, the lookup is:

Data Type: text
row soure type: table/query
Row source query: bankdata
Bound Column: 1
Column Count: 2
Column Width 0";1"

This works fine! however, when I'm creating a Form based on the investments table, and i use the 'Borrower' field, I only see the corresponding Id Number, when I really want to see the bank name.

Now, I realize that I can just make the row source for this form field bankdata, but...when I select a bankname that isn't in the investments table (Borrower field), the form is empty.

Hope this isn't convoluted.

I just want to be able to see the name of the borrower on the field and filter from there, instead of the corresponding ID number.

HELP!!

Thank You

 

[lespaul]

do you mean you are using Lookup FIELDS in the tables?

see The Evils of Lookup Fields in Tables

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[lynchg]

Write a query that uses the investment table, but also links over to the bankdata table to get the text for the bank name.

Use this query as the data source for your form and you will have the text value for the bank name in your field list.

If I am understanding you correctly the id field from the bankdata table is a foreign key in the investment table. So the investment table by itself doesn't contain the text name of banks, just the id field.

 

[snoopy92211]

Thanks Lynchg! Now, how do I do that? I tried to do a query using a join join on BankID, but that didn't work.

Here's my query, which doesn't work at all. lol

SELECT DISTINCTROW ICLoans.LENDER, BusinessUnit.BusinessUnitID, BusinessUnit.Name
FROM ICLoans INNER JOIN BusinessUnit ON ICLoans.LENDER = BusinessUnit.Name;

The primary key for the bank table is what I want to use to compare the (Borrower field). Can you please give me an example? That would help greatly.


--
And... Thanks lespaul for the input. If I'm not supposed to use lookup tables, how do I refer to input tables?

 

[lynchg]

Which table is which?

Your first post talked about Investment and BankData, now I see ICLoans and BusinessUnit. But let me try. Remember that this is probably oversimplified, you could have 50 fields in your query, I am just trying to address the link on the bankID field.

Select Investment.XXX, Investment.ZZZ, BankData.ID, BankData.BankName
FROM Investment Inner Join BankData
On Investment.BankID = BankData.ID

Then BankName will be in your field list and you can display it on your form.

 

[snoopy92211]

oops! lol. I meant to say this.

SELECT DISTINCTROW Investments.Borrower, Bankdata.BankID, Bankdata.Name
FROM Investments INNER JOIN Bankdata ON Investment.borrower = Bankdata.id;

(borrower is bankID)

 

[lespaul]

If I'm not supposed to use lookup tables,

I didn't say lookup TABLES I said lookup FIELDS. A lookup FIELD is when Access translates the relationships for you so when you look at a table that contains a foriegn key, instead of seeing the key in the table, you see the "lookup value" of the key.

 

[lynchg]

Why doesn't it work?

are these fields the same field:

Bankdata.BankID and Bankdata.id

If there is a BankID value in BankData for every Borrower value in Investments, then it will work.

BTW - it is a good idea to name a foreign key the same in all tables, it is easier to track, especially if it appears in multiple tables. How would anyone know that Borrower = BankID, and 6 months from now you will have forgotten it too, trust me.

 

[snoopy92211]

Thanks for all of the tips!