If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
A Complex Scenario
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 |
#2
|
|||
|
|||
A Complex Scenario
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. -----Original Message----- 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 . |
#3
|
|||
|
|||
A Complex Scenario
The Alias sounds great....but...help says it can only be
used in queries |
#4
|
|||
|
|||
A Complex Scenario
"Scott Matheny" wrote in
: 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 |
#5
|
|||
|
|||
I'm sure this will help....but....
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 |
#6
|
|||
|
|||
more info
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 |
#7
|
|||
|
|||
Another crazy question....
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 |
#8
|
|||
|
|||
I'm sure this will help....but....
"Scott Matheny" wrote in
: 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 he 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Complex formula | Mark1ace1 | Worksheet Functions | 1 | June 6th, 2004 06:42 AM |
Scenario Summary | linda | Worksheet Functions | 1 | January 5th, 2004 01:48 PM |
Enhanced "Scenario" manager | Jack Mallinckrodt | Worksheet Functions | 1 | September 30th, 2003 10:42 AM |
Scenario enhanced. | Jack Mallinckrodt | Worksheet Functions | 2 | September 30th, 2003 10:34 AM |