A Complex Scenario

[Scott Matheny]

Say I have a tables of records that are tests, and each
has a sponsor company and a test company. Also, there is
another single table with all the companies' information,
but both sponsor companies and test companies reside in
this table because in a different record their roles may
switch. What I want is on the records table to have a
lookup value to select a company from the table for both
the Sponsor and Test Company's fields. I have all of this
working....except....when I make a form, I need two sets
of Name, Address, and Telephone Numbers that take the
information from the Company Name in the main field and
then look up the information from the company table and
put it into the proper field. I have keys for
everything. Please offer any assistance in making this
work.

Thanks in advance,
-Scott

 

[marty]

It sounds like you might be able to use the Alias idea. I
have only read about it, but the Alias basically can
access the same table by using a "dummy" name on all but
the first link.

Good Luck.

 

[Scott]

The Alias sounds great....but...help says it can only be
used in queries

 

[Tim Ferguson]

Not very complex!

Say I have a tables of records that are tests, and each
has a sponsor company and a test company. Also, there is
another single table with all the companies' information,
but both sponsor companies and test companies reside in
this table because in a different record their roles may
switch.

Easy: just have two foreign key fields:

Records(*RecordID, SomeStuff, MoreStuff,
Sponsor(fk references Companies),
TestCo(fk references Companies), etc)

What I want is on the records table to have a
lookup value to select a company from the table for both
the Sponsor and Test Company's fields.

Please don't tell me you are thinking of LookUpFields <spit>. Just do it
the normal way: make them Long Integer fields and use the Relationships
window. Add the Records table, then the Companies table, and then the
Companies table again (it'll be called Companies_1 but don't worry about
that). Drag the Records.Sponsor field over to Companies.CompanyID and check
for RI, then do the same with Records.TestCo to the other
Companies_1.CompanyID.

I have all of this
working....except....when I make a form, I need two sets
of Name, Address, and Telephone Numbers that take the
information from the Company Name in the main field and
then look up the information from the company table and
put it into the proper field.

Easiest way is probably two simple combo boxes bound to the Sponsor and
TestCo fields. Base them both on a RowSource like this:

SELECT ALL CompanyID, FullName, AddressLineOne, PhoneNumber
FROM Companies
ORDER BY FullName ASC;

This returns four columns, so you set the ColumnCount property to four; the
BoundColumn = 1 (because it's the CompanyID value that gets sent to the
Sponsor field); the ColumnWidth="0;1.5;;1.5" (the zero hides the CompanyID
from view, because the users don't want to see that; the missing width for
Address allows it to be wide as it likes).

Easy as that. You can see more information on combo boxes in the help file:
check out the BoundColumn and ColumnWidth properties for details.

Hope it helps


Tim F

 

[Scott Matheny]

I have no SQL writing knowledge and very little
programming capabilty. I understand every thing that is
happening...but I just don't know where and how to put it
in correctly. Syntax-ugh. So if you could clarify
anything furthur for me...that would be awesome.

Thanks so much,
-Scott Matheny

 

[Scott Matheny]

I actually had the two tables (Companies_1) before I read
this, but I'm really stuck on how to distinguish between
references on my form.

Just clarifying to save you some explanation.
-Scott

 

[Scott]

Can I have a text box on that form that displays, say, the
Phone Number, row 4 of my combo box. I'm not really
wanting an entry form, but moreso a search form in which
each value (address, name, # from Companies) would display
in individual text boxes).

I suppose this all sounds very vague and
confusing...sorry

-Scott

 

[Tim Ferguson]

I have no SQL writing knowledge and very little
programming capabilty. I understand every thing that is
happening...but I just don't know where and how to put it
in correctly. Syntax-ugh. So if you could clarify
anything furthur for me...that would be awesome.

You really don't need any programming or SQL knowledge for this: it is all
doable within the Access GUI. You have picked quite a tricky real-life
scenario to model though!

I actually had the two tables (Companies_1) before I read
this, but I'm really stuck on how to distinguish between
references on my form.

There is no difference: the '_1' only exists in the Relationships window so
that it can refer to the same table several times. Otherwise it would not
be able to tell the difference between one two-field relationship and two
single-field ones.

If you really do have two identical tables (in the Database/ Tables window)
then you don't need them and you can get rid of one of them.

Can I have a text box on that form that displays, say, the
Phone Number, row 4 of my combo box. I'm not really
wanting an entry form, but moreso a search form in which
each value (address, name, # from Companies) would display
in individual text boxes).

You should try to get into the idea of One Form Equals One Process. If you
have a user that spends all their time looking up and editing contacts,
then that is one process. Once you have decided what that process is, then
doing the UI is usually pretty easy -- if you're stuck it generally means
that you don't understand where you are headed <g>. When you know what data
set you need to operate on it is not hard to create the query that the form
operates on.

No, I am not quite clear what you have in mind here: usually either a
ListBox or a SubForm is used to display information from the far end of a
one-to-many relationship. Is that what you wanted to hear?

B Wishes


Tim F