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 |
#21
|
|||
|
|||
searching for names - multiple names per record
Thanks, Doug.
Right. Your Table2 is my 2Names. Your Table1 is my 1Main. Its fields are many, including general info (like ProjectA, ProjectB, ProjectC, dates, and other data (I expect you'll say to break dates out once I get it down. I will!). I'm also wondering about whether I need separate queries for each nameType. Do I? Otherwise, do I make a query of individual queries (ie. a query for Originator, etc.)? "Douglas J. Steele" wrote in message ... So 2Names is what you called what I referred to as Table2? What have you called the equivalent of Table1 (and what are its fields)? -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "zSplash" wrote in message ... Thanks so much, Doug. So, my SQL for the query is: SELECT [2Names].ID, [2Names].fk, [2Names].nameType, [2Names].First, [2Names].Last, [2Names].DOB FROM 2Names; TIA "Douglas J. Steele" wrote in message ... What's the SQL for your query? If you're not familiar with seeing the SQL, open the query in Design view, and then choose "SQL View" from the View menu. (It's far easier to deal with SQL than to try & walk through the graphics of the query builder!) -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "zSplash" wrote in message ... Here's yet another question: How can I return the same number of records in my form that I have in Table1? That is: Table1 has 1238 unique records. Of the queries, the one for Approver has the fewest results -- 629. When I create the form, based on separate queries for Developer, Originator, and Approver, the form only shows 629 records. What's the deal with that? I need to return all 1238 records in my form. TIA "zSplash" wrote in message ... So, it's slowly becoming clear! The light at the end of a l-o-o-n-n-n-g tunnel (probably excruciatingly long for you). Thanks, Doug. You've been the model of patience. Now, my next question: In creating forms, is it better design to use queries or tables? In this case, is it better to use Table1 or a query based on Table1 when I re-design my form? TIA "Douglas J. Steele" wrote in message ... You don't set the ControlSource property for text boxes to SQL statements. You set the RecordSource of the form to the query, and set the text box's ControlSource to the name of a field in that query. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "zSplash" wrote in message ... Okay. I've done that (and named the qTest). And I've created a form with textboxes. So, for example, to pull up the data about the first record's originator's lastname, I've put the following in the Control Source property of the textbox: SELECT [qTest]![Last] from [qTest] where [qTest]![nameType]="Originator" But, when I open the form, that textbox shows an error: Name#? What's wrong? TIA "Douglas J. Steele" wrote in message ... You join them in a query, linking the ID field in Table1 to the corresponding ID field in Table2. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "zSplash" wrote in message ... Thanks for the patience for dealing with such a dense person, Doug. So, I have Table1 and Table2 (excepting that my Table2 has a new pkID, First, Last, and DOB, in addition to your Table1 data). How do I now "connect" Table1 with Table2? TIA "Douglas J. Steele" wrote in message ... I may have confused you. If you've got Table1 and Table2 as I described them, that's essentially all you need. I'd suggested an extension of that if you had a Person table: Id Person 1 Tom Jones 2 Mary Brown 3 John Doe 4 Jill Roe 5 Mary Smith 6 John Brown Then, instead of Table2 being Id NameType Person 1 Originator Tom Jones 1 Developer Mary Brown 1 Approver John Doe 2 Originator Mary Brown 2 Developer Jill Roe 2 Approver Mary Smith 3 Originator John Brown 3 Developer John Brown 3 Approver Mary Smith it would be Id NameType Person 1 Originator 1 1 Developer 2 1 Approver 3 2 Originator 2 2 Developer 4 2 Approver 5 3 Originator 6 3 Developer 6 3 Approver 5 -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "zSplash" wrote in message ... Thanks so much, Doug. I have tried to re-do my database to meet your suggestions. I have a Table1 and Table 2, as you've outlined. Now, I need direction on how to make a third table "that resolves the intersection of the two tables". I just don't quite get the foreign key deal. TIA "Douglas J. Steele" wrote in message ... Realistically, a primary key is just an index, and any index can have up to 10 separate fields in it. Which design are you asking about "do I need a foreign key in Names table"? Are you talking about my comment at the end ("Depending on your actual requirements, you could have a Person table, so that all you store in Table2 is the PersonId."), or are you talking about Table2 in the example? Table2 must point to Table1, so yes, it must have a foreign key in it. If you're using "Names table" to refer to what I called "a Person table", then no: that table wouldn't have a foreign key in it. In essence you've got a many-to-many relationship between Table1 and the Names table. You create a third table that resolves the intersection of the two tables, and that intersection table consists of foreign keys pointing back to the other 2 tables. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "zSplash" wrote in message ... Thanks, Doug. I didn't know a primary key could be a combination of several fields -- I thought it had to be a number field? And if I use your design, do I need a foreign key in Names table to connect (somehow) to the pk in the Projects table? "Douglas J. Steele" wrote in message ... Actually, you need at least one additional field in the second table: the link back to the first table. Let's assume you currently have Table1: Id Desc Originator Developer Approver 1 Project A Tom Jones Mary Brown John Doe 2 Project B Mary Brown Jill Roe Mary Smith 3 Project C John Brown John Brown Mary Smith with Id as the Primary Key. You'd change Table1 to: Id Desc 1 Project A 2 Project B 3 Project C still with Id as the Primary Key. and Table2 would be: Id NameType Person 1 Originator Tom Jones 1 Developer Mary Brown 1 Approver John Doe 2 Originator Mary Brown 2 Developer Jill Roe 2 Approver Mary Smith 3 Originator John Brown 3 Developer John Brown 3 Approver Mary Smith with the combination of Id and NameType as the Primary Key. (If you can have more than NameType for a particular item, you'd need more for the PK) Depending on your actual requirements, you could have a Person table, so that all you store in Table2 is the PersonId. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "zSplash" wrote in message ... Thanks, Doug, but I just don't get it. If I have a single table, with a col for Nametype, a col for First, and a col for Last, how can I ever have more than one name per record? st. "Douglas J. Steele" wrote in message ... No, I don't think you should have a separate table for each name type. Have a single name table with an additional column of NameType. That'll make queries like "Let me know all records that John Brown is involved with", "Let me know those records for which Mary Smith was the Approver" and "Let me know all records where the same person was the Developer and the Originator" much, much simpler. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "zSplash" wrote in message ... Thanks, Doug, for responding. By "create a second table linked to that first table with one row for each name" that I should have individual tables for each nameType? That is leave the mainTable with the common information, and then create a table for nameOriginator, a table for nameApprover, a table nameDeveloper, etc, with nameData for each of those nameTypes? "Douglas J. Steele" wrote in message ... You'd better explain your precise need, but in general, you wouldn't put multiple names on a single record. Typically when you have multiple names on a single record, it means you've got field names like "Originator", "Approver", "Developer" etc. That's not a good idea: you're hiding data in the field names. Instead, you should keep the common information in the one table, and create a second table linked to that first table with one row for each name. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "zSplash" wrote in message ... My database needs to track several names (first and last) for each record. My initial database had all the names (first/last) in the main table. In an earlier request for help searching for lastnames, someone suggested that I put all the names in a separate Names table with a nameType comboBox. Now that I've modified my database to do that, I see that with that design I can only have one name per record. (To enter names, I select nameType, and then enter first/last names for that nameType. I have no way of entering/adding the first/lastnames for the other nameTypes. Each record has 5 or 6 first/last names to track. If they are all in one Names table, as suggested, I can only hold one nameType in each record. Can someone please suggest another way to solve my problem? TIA |
#22
|
|||
|
|||
searching for names - multiple names per record
Depending on what your aim is, yes, you might require multiple queries.
A basic starting point, given Table1 Id Desc 1 Project A 2 Project B 3 Project C and Table2 Id NameType Person 1 Originator Tom Jones 1 Developer Mary Brown 1 Approver John Doe 2 Originator Mary Brown 2 Developer Jill Roe 2 Approver Mary Smith 3 Originator John Brown 3 Developer John Brown 3 Approver Mary Smith would be something like: SELECT Table1.Id, Table1.Desc, Table2.NameType, Table2.Person FROM Table1 LEFT JOIN Table2 ON Table1.Id = Table2.Id -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "zSplash" wrote in message ... Thanks, Doug. Right. Your Table2 is my 2Names. Your Table1 is my 1Main. Its fields are many, including general info (like ProjectA, ProjectB, ProjectC, dates, and other data (I expect you'll say to break dates out once I get it down. I will!). I'm also wondering about whether I need separate queries for each nameType. Do I? Otherwise, do I make a query of individual queries (ie. a query for Originator, etc.)? "Douglas J. Steele" wrote in message ... So 2Names is what you called what I referred to as Table2? What have you called the equivalent of Table1 (and what are its fields)? -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "zSplash" wrote in message ... Thanks so much, Doug. So, my SQL for the query is: SELECT [2Names].ID, [2Names].fk, [2Names].nameType, [2Names].First, [2Names].Last, [2Names].DOB FROM 2Names; TIA "Douglas J. Steele" wrote in message ... What's the SQL for your query? If you're not familiar with seeing the SQL, open the query in Design view, and then choose "SQL View" from the View menu. (It's far easier to deal with SQL than to try & walk through the graphics of the query builder!) -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "zSplash" wrote in message ... Here's yet another question: How can I return the same number of records in my form that I have in Table1? That is: Table1 has 1238 unique records. Of the queries, the one for Approver has the fewest results -- 629. When I create the form, based on separate queries for Developer, Originator, and Approver, the form only shows 629 records. What's the deal with that? I need to return all 1238 records in my form. TIA "zSplash" wrote in message ... So, it's slowly becoming clear! The light at the end of a l-o-o-n-n-n-g tunnel (probably excruciatingly long for you). Thanks, Doug. You've been the model of patience. Now, my next question: In creating forms, is it better design to use queries or tables? In this case, is it better to use Table1 or a query based on Table1 when I re-design my form? TIA "Douglas J. Steele" wrote in message ... You don't set the ControlSource property for text boxes to SQL statements. You set the RecordSource of the form to the query, and set the text box's ControlSource to the name of a field in that query. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "zSplash" wrote in message ... Okay. I've done that (and named the qTest). And I've created a form with textboxes. So, for example, to pull up the data about the first record's originator's lastname, I've put the following in the Control Source property of the textbox: SELECT [qTest]![Last] from [qTest] where [qTest]![nameType]="Originator" But, when I open the form, that textbox shows an error: Name#? What's wrong? TIA "Douglas J. Steele" wrote in message ... You join them in a query, linking the ID field in Table1 to the corresponding ID field in Table2. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "zSplash" wrote in message ... Thanks for the patience for dealing with such a dense person, Doug. So, I have Table1 and Table2 (excepting that my Table2 has a new pkID, First, Last, and DOB, in addition to your Table1 data). How do I now "connect" Table1 with Table2? TIA "Douglas J. Steele" wrote in message ... I may have confused you. If you've got Table1 and Table2 as I described them, that's essentially all you need. I'd suggested an extension of that if you had a Person table: Id Person 1 Tom Jones 2 Mary Brown 3 John Doe 4 Jill Roe 5 Mary Smith 6 John Brown Then, instead of Table2 being Id NameType Person 1 Originator Tom Jones 1 Developer Mary Brown 1 Approver John Doe 2 Originator Mary Brown 2 Developer Jill Roe 2 Approver Mary Smith 3 Originator John Brown 3 Developer John Brown 3 Approver Mary Smith it would be Id NameType Person 1 Originator 1 1 Developer 2 1 Approver 3 2 Originator 2 2 Developer 4 2 Approver 5 3 Originator 6 3 Developer 6 3 Approver 5 -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "zSplash" wrote in message ... Thanks so much, Doug. I have tried to re-do my database to meet your suggestions. I have a Table1 and Table 2, as you've outlined. Now, I need direction on how to make a third table "that resolves the intersection of the two tables". I just don't quite get the foreign key deal. TIA "Douglas J. Steele" wrote in message ... Realistically, a primary key is just an index, and any index can have up to 10 separate fields in it. Which design are you asking about "do I need a foreign key in Names table"? Are you talking about my comment at the end ("Depending on your actual requirements, you could have a Person table, so that all you store in Table2 is the PersonId."), or are you talking about Table2 in the example? Table2 must point to Table1, so yes, it must have a foreign key in it. If you're using "Names table" to refer to what I called "a Person table", then no: that table wouldn't have a foreign key in it. In essence you've got a many-to-many relationship between Table1 and the Names table. You create a third table that resolves the intersection of the two tables, and that intersection table consists of foreign keys pointing back to the other 2 tables. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "zSplash" wrote in message ... Thanks, Doug. I didn't know a primary key could be a combination of several fields -- I thought it had to be a number field? And if I use your design, do I need a foreign key in Names table to connect (somehow) to the pk in the Projects table? "Douglas J. Steele" wrote in message ... Actually, you need at least one additional field in the second table: the link back to the first table. Let's assume you currently have Table1: Id Desc Originator Developer Approver 1 Project A Tom Jones Mary Brown John Doe 2 Project B Mary Brown Jill Roe Mary Smith 3 Project C John Brown John Brown Mary Smith with Id as the Primary Key. You'd change Table1 to: Id Desc 1 Project A 2 Project B 3 Project C still with Id as the Primary Key. and Table2 would be: Id NameType Person 1 Originator Tom Jones 1 Developer Mary Brown 1 Approver John Doe 2 Originator Mary Brown 2 Developer Jill Roe 2 Approver Mary Smith 3 Originator John Brown 3 Developer John Brown 3 Approver Mary Smith with the combination of Id and NameType as the Primary Key. (If you can have more than NameType for a particular item, you'd need more for the PK) Depending on your actual requirements, you could have a Person table, so that all you store in Table2 is the PersonId. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "zSplash" wrote in message ... Thanks, Doug, but I just don't get it. If I have a single table, with a col for Nametype, a col for First, and a col for Last, how can I ever have more than one name per record? st. "Douglas J. Steele" wrote in message ... No, I don't think you should have a separate table for each name type. Have a single name table with an additional column of NameType. That'll make queries like "Let me know all records that John Brown is involved with", "Let me know those records for which Mary Smith was the Approver" and "Let me know all records where the same person was the Developer and the Originator" much, much simpler. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "zSplash" wrote in message ... Thanks, Doug, for responding. By "create a second table linked to that first table with one row for each name" that I should have individual tables for each nameType? That is leave the mainTable with the common information, and then create a table for nameOriginator, a table for nameApprover, a table nameDeveloper, etc, with nameData for each of those nameTypes? "Douglas J. Steele" wrote in message ... You'd better explain your precise need, but in general, you wouldn't put multiple names on a single record. Typically when you have multiple names on a single record, it means you've got field names like "Originator", "Approver", "Developer" etc. That's not a good idea: you're hiding data in the field names. Instead, you should keep the common information in the one table, and create a second table linked to that first table with one row for each name. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "zSplash" wrote in message ... My database needs to track several names (first and last) for each record. My initial database had all the names (first/last) in the main table. In an earlier request for help searching for lastnames, someone suggested that I put all the names in a separate Names table with a nameType comboBox. Now that I've modified my database to do that, I see that with that design I can only have one name per record. (To enter names, I select nameType, and then enter first/last names for that nameType. I have no way of entering/adding the first/lastnames for the other nameTypes. Each record has 5 or 6 first/last names to track. If they are all in one Names table, as suggested, I can only hold one nameType in each record. Can someone please suggest another way to solve my problem? TIA |
#23
|
|||
|
|||
searching for names - multiple names per record
Okay. I really appreciate the help, Doug. (I was called away from this
project, and am only now getting back to it.) My aim is to have a single form, with labels for nameType, then textboxes for nameTypeFirst, nameTypeLast, nameTypeTitle, nameTypeAssistant. If I have to create multiple queries to get this data on the form (which it seems like I'll have to do), I'm going to have 4 queries for every name type. Is there a more efficient way to do it rather than creating 4x4 queries? As it is, my queries are overwhelming -- is that normal? TIA "Douglas J. Steele" wrote in message ... Depending on what your aim is, yes, you might require multiple queries. A basic starting point, given Table1 Id Desc 1 Project A 2 Project B 3 Project C and Table2 Id NameType Person 1 Originator Tom Jones 1 Developer Mary Brown 1 Approver John Doe 2 Originator Mary Brown 2 Developer Jill Roe 2 Approver Mary Smith 3 Originator John Brown 3 Developer John Brown 3 Approver Mary Smith would be something like: SELECT Table1.Id, Table1.Desc, Table2.NameType, Table2.Person FROM Table1 LEFT JOIN Table2 ON Table1.Id = Table2.Id -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "zSplash" wrote in message ... Thanks, Doug. Right. Your Table2 is my 2Names. Your Table1 is my 1Main. Its fields are many, including general info (like ProjectA, ProjectB, ProjectC, dates, and other data (I expect you'll say to break dates out once I get it down. I will!). I'm also wondering about whether I need separate queries for each nameType. Do I? Otherwise, do I make a query of individual queries (ie. a query for Originator, etc.)? "Douglas J. Steele" wrote in message ... So 2Names is what you called what I referred to as Table2? What have you called the equivalent of Table1 (and what are its fields)? -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "zSplash" wrote in message ... Thanks so much, Doug. So, my SQL for the query is: SELECT [2Names].ID, [2Names].fk, [2Names].nameType, [2Names].First, [2Names].Last, [2Names].DOB FROM 2Names; TIA "Douglas J. Steele" wrote in message ... What's the SQL for your query? If you're not familiar with seeing the SQL, open the query in Design view, and then choose "SQL View" from the View menu. (It's far easier to deal with SQL than to try & walk through the graphics of the query builder!) -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "zSplash" wrote in message ... Here's yet another question: How can I return the same number of records in my form that I have in Table1? That is: Table1 has 1238 unique records. Of the queries, the one for Approver has the fewest results -- 629. When I create the form, based on separate queries for Developer, Originator, and Approver, the form only shows 629 records. What's the deal with that? I need to return all 1238 records in my form. TIA "zSplash" wrote in message ... So, it's slowly becoming clear! The light at the end of a l-o-o-n-n-n-g tunnel (probably excruciatingly long for you). Thanks, Doug. You've been the model of patience. Now, my next question: In creating forms, is it better design to use queries or tables? In this case, is it better to use Table1 or a query based on Table1 when I re-design my form? TIA "Douglas J. Steele" wrote in message ... You don't set the ControlSource property for text boxes to SQL statements. You set the RecordSource of the form to the query, and set the text box's ControlSource to the name of a field in that query. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "zSplash" wrote in message ... Okay. I've done that (and named the qTest). And I've created a form with textboxes. So, for example, to pull up the data about the first record's originator's lastname, I've put the following in the Control Source property of the textbox: SELECT [qTest]![Last] from [qTest] where [qTest]![nameType]="Originator" But, when I open the form, that textbox shows an error: Name#? What's wrong? TIA "Douglas J. Steele" wrote in message ... You join them in a query, linking the ID field in Table1 to the corresponding ID field in Table2. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "zSplash" wrote in message ... Thanks for the patience for dealing with such a dense person, Doug. So, I have Table1 and Table2 (excepting that my Table2 has a new pkID, First, Last, and DOB, in addition to your Table1 data). How do I now "connect" Table1 with Table2? TIA "Douglas J. Steele" wrote in message ... I may have confused you. If you've got Table1 and Table2 as I described them, that's essentially all you need. I'd suggested an extension of that if you had a Person table: Id Person 1 Tom Jones 2 Mary Brown 3 John Doe 4 Jill Roe 5 Mary Smith 6 John Brown Then, instead of Table2 being Id NameType Person 1 Originator Tom Jones 1 Developer Mary Brown 1 Approver John Doe 2 Originator Mary Brown 2 Developer Jill Roe 2 Approver Mary Smith 3 Originator John Brown 3 Developer John Brown 3 Approver Mary Smith it would be Id NameType Person 1 Originator 1 1 Developer 2 1 Approver 3 2 Originator 2 2 Developer 4 2 Approver 5 3 Originator 6 3 Developer 6 3 Approver 5 -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "zSplash" wrote in message ... Thanks so much, Doug. I have tried to re-do my database to meet your suggestions. I have a Table1 and Table 2, as you've outlined. Now, I need direction on how to make a third table "that resolves the intersection of the two tables". I just don't quite get the foreign key deal. TIA "Douglas J. Steele" wrote in message ... Realistically, a primary key is just an index, and any index can have up to 10 separate fields in it. Which design are you asking about "do I need a foreign key in Names table"? Are you talking about my comment at the end ("Depending on your actual requirements, you could have a Person table, so that all you store in Table2 is the PersonId."), or are you talking about Table2 in the example? Table2 must point to Table1, so yes, it must have a foreign key in it. If you're using "Names table" to refer to what I called "a Person table", then no: that table wouldn't have a foreign key in it. In essence you've got a many-to-many relationship between Table1 and the Names table. You create a third table that resolves the intersection of the two tables, and that intersection table consists of foreign keys pointing back to the other 2 tables. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "zSplash" wrote in message ... Thanks, Doug. I didn't know a primary key could be a combination of several fields -- I thought it had to be a number field? And if I use your design, do I need a foreign key in Names table to connect (somehow) to the pk in the Projects table? "Douglas J. Steele" wrote in message ... Actually, you need at least one additional field in the second table: the link back to the first table. Let's assume you currently have Table1: Id Desc Originator Developer Approver 1 Project A Tom Jones Mary Brown John Doe 2 Project B Mary Brown Jill Roe Mary Smith 3 Project C John Brown John Brown Mary Smith with Id as the Primary Key. You'd change Table1 to: Id Desc 1 Project A 2 Project B 3 Project C still with Id as the Primary Key. and Table2 would be: Id NameType Person 1 Originator Tom Jones 1 Developer Mary Brown 1 Approver John Doe 2 Originator Mary Brown 2 Developer Jill Roe 2 Approver Mary Smith 3 Originator John Brown 3 Developer John Brown 3 Approver Mary Smith with the combination of Id and NameType as the Primary Key. (If you can have more than NameType for a particular item, you'd need more for the PK) Depending on your actual requirements, you could have a Person table, so that all you store in Table2 is the PersonId. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "zSplash" wrote in message ... Thanks, Doug, but I just don't get it. If I have a single table, with a col for Nametype, a col for First, and a col for Last, how can I ever have more than one name per record? st. "Douglas J. Steele" wrote in message ... No, I don't think you should have a separate table for each name type. Have a single name table with an additional column of NameType. That'll make queries like "Let me know all records that John Brown is involved with", "Let me know those records for which Mary Smith was the Approver" and "Let me know all records where the same person was the Developer and the Originator" much, much simpler. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "zSplash" wrote in message ... Thanks, Doug, for responding. By "create a second table linked to that first table with one row for each name" that I should have individual tables for each nameType? That is leave the mainTable with the common information, and then create a table for nameOriginator, a table for nameApprover, a table nameDeveloper, etc, with nameData for each of those nameTypes? "Douglas J. Steele" wrote in message ... You'd better explain your precise need, but in general, you wouldn't put multiple names on a single record. Typically when you have multiple names on a single record, it means you've got field names like "Originator", "Approver", "Developer" etc. That's not a good idea: you're hiding data in the field names. Instead, you should keep the common information in the one table, and create a second table linked to that first table with one row for each name. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "zSplash" wrote in message ... My database needs to track several names (first and last) for each record. My initial database had all the names (first/last) in the main table. In an earlier request for help searching for lastnames, someone suggested that I put all the names in a separate Names table with a nameType comboBox. Now that I've modified my database to do that, I see that with that design I can only have one name per record. (To enter names, I select nameType, and then enter first/last names for that nameType. I have no way of entering/adding the first/lastnames for the other nameTypes. Each record has 5 or 6 first/last names to track. If they are all in one Names table, as suggested, I can only hold one nameType in each record. Can someone please suggest another way to solve my problem? TIA |
#24
|
|||
|
|||
searching for names - multiple names per record
I'm not sure I understand why you need multiple queries if you're having a
single form. Seems to me a single query that returns everything is what you want. You can then apply filters to the form to only show specific records if need be. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "zSplash" wrote in message ... Okay. I really appreciate the help, Doug. (I was called away from this project, and am only now getting back to it.) My aim is to have a single form, with labels for nameType, then textboxes for nameTypeFirst, nameTypeLast, nameTypeTitle, nameTypeAssistant. If I have to create multiple queries to get this data on the form (which it seems like I'll have to do), I'm going to have 4 queries for every name type. Is there a more efficient way to do it rather than creating 4x4 queries? As it is, my queries are overwhelming -- is that normal? TIA "Douglas J. Steele" wrote in message ... Depending on what your aim is, yes, you might require multiple queries. A basic starting point, given Table1 Id Desc 1 Project A 2 Project B 3 Project C and Table2 Id NameType Person 1 Originator Tom Jones 1 Developer Mary Brown 1 Approver John Doe 2 Originator Mary Brown 2 Developer Jill Roe 2 Approver Mary Smith 3 Originator John Brown 3 Developer John Brown 3 Approver Mary Smith would be something like: SELECT Table1.Id, Table1.Desc, Table2.NameType, Table2.Person FROM Table1 LEFT JOIN Table2 ON Table1.Id = Table2.Id -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "zSplash" wrote in message ... Thanks, Doug. Right. Your Table2 is my 2Names. Your Table1 is my 1Main. Its fields are many, including general info (like ProjectA, ProjectB, ProjectC, dates, and other data (I expect you'll say to break dates out once I get it down. I will!). I'm also wondering about whether I need separate queries for each nameType. Do I? Otherwise, do I make a query of individual queries (ie. a query for Originator, etc.)? "Douglas J. Steele" wrote in message ... So 2Names is what you called what I referred to as Table2? What have you called the equivalent of Table1 (and what are its fields)? -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "zSplash" wrote in message ... Thanks so much, Doug. So, my SQL for the query is: SELECT [2Names].ID, [2Names].fk, [2Names].nameType, [2Names].First, [2Names].Last, [2Names].DOB FROM 2Names; TIA "Douglas J. Steele" wrote in message ... What's the SQL for your query? If you're not familiar with seeing the SQL, open the query in Design view, and then choose "SQL View" from the View menu. (It's far easier to deal with SQL than to try & walk through the graphics of the query builder!) -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "zSplash" wrote in message ... Here's yet another question: How can I return the same number of records in my form that I have in Table1? That is: Table1 has 1238 unique records. Of the queries, the one for Approver has the fewest results -- 629. When I create the form, based on separate queries for Developer, Originator, and Approver, the form only shows 629 records. What's the deal with that? I need to return all 1238 records in my form. TIA "zSplash" wrote in message ... So, it's slowly becoming clear! The light at the end of a l-o-o-n-n-n-g tunnel (probably excruciatingly long for you). Thanks, Doug. You've been the model of patience. Now, my next question: In creating forms, is it better design to use queries or tables? In this case, is it better to use Table1 or a query based on Table1 when I re-design my form? TIA "Douglas J. Steele" wrote in message ... You don't set the ControlSource property for text boxes to SQL statements. You set the RecordSource of the form to the query, and set the text box's ControlSource to the name of a field in that query. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "zSplash" wrote in message ... Okay. I've done that (and named the qTest). And I've created a form with textboxes. So, for example, to pull up the data about the first record's originator's lastname, I've put the following in the Control Source property of the textbox: SELECT [qTest]![Last] from [qTest] where [qTest]![nameType]="Originator" But, when I open the form, that textbox shows an error: Name#? What's wrong? TIA "Douglas J. Steele" wrote in message ... You join them in a query, linking the ID field in Table1 to the corresponding ID field in Table2. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "zSplash" wrote in message ... Thanks for the patience for dealing with such a dense person, Doug. So, I have Table1 and Table2 (excepting that my Table2 has a new pkID, First, Last, and DOB, in addition to your Table1 data). How do I now "connect" Table1 with Table2? TIA "Douglas J. Steele" wrote in message ... I may have confused you. If you've got Table1 and Table2 as I described them, that's essentially all you need. I'd suggested an extension of that if you had a Person table: Id Person 1 Tom Jones 2 Mary Brown 3 John Doe 4 Jill Roe 5 Mary Smith 6 John Brown Then, instead of Table2 being Id NameType Person 1 Originator Tom Jones 1 Developer Mary Brown 1 Approver John Doe 2 Originator Mary Brown 2 Developer Jill Roe 2 Approver Mary Smith 3 Originator John Brown 3 Developer John Brown 3 Approver Mary Smith it would be Id NameType Person 1 Originator 1 1 Developer 2 1 Approver 3 2 Originator 2 2 Developer 4 2 Approver 5 3 Originator 6 3 Developer 6 3 Approver 5 -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "zSplash" wrote in message ... Thanks so much, Doug. I have tried to re-do my database to meet your suggestions. I have a Table1 and Table 2, as you've outlined. Now, I need direction on how to make a third table "that resolves the intersection of the two tables". I just don't quite get the foreign key deal. TIA "Douglas J. Steele" wrote in message ... Realistically, a primary key is just an index, and any index can have up to 10 separate fields in it. Which design are you asking about "do I need a foreign key in Names table"? Are you talking about my comment at the end ("Depending on your actual requirements, you could have a Person table, so that all you store in Table2 is the PersonId."), or are you talking about Table2 in the example? Table2 must point to Table1, so yes, it must have a foreign key in it. If you're using "Names table" to refer to what I called "a Person table", then no: that table wouldn't have a foreign key in it. In essence you've got a many-to-many relationship between Table1 and the Names table. You create a third table that resolves the intersection of the two tables, and that intersection table consists of foreign keys pointing back to the other 2 tables. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "zSplash" wrote in message ... Thanks, Doug. I didn't know a primary key could be a combination of several fields -- I thought it had to be a number field? And if I use your design, do I need a foreign key in Names table to connect (somehow) to the pk in the Projects table? "Douglas J. Steele" wrote in message ... Actually, you need at least one additional field in the second table: the link back to the first table. Let's assume you currently have Table1: Id Desc Originator Developer Approver 1 Project A Tom Jones Mary Brown John Doe 2 Project B Mary Brown Jill Roe Mary Smith 3 Project C John Brown John Brown Mary Smith with Id as the Primary Key. You'd change Table1 to: Id Desc 1 Project A 2 Project B 3 Project C still with Id as the Primary Key. and Table2 would be: Id NameType Person 1 Originator Tom Jones 1 Developer Mary Brown 1 Approver John Doe 2 Originator Mary Brown 2 Developer Jill Roe 2 Approver Mary Smith 3 Originator John Brown 3 Developer John Brown 3 Approver Mary Smith with the combination of Id and NameType as the Primary Key. (If you can have more than NameType for a particular item, you'd need more for the PK) Depending on your actual requirements, you could have a Person table, so that all you store in Table2 is the PersonId. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "zSplash" wrote in message ... Thanks, Doug, but I just don't get it. If I have a single table, with a col for Nametype, a col for First, and a col for Last, how can I ever have more than one name per record? st. "Douglas J. Steele" wrote in message ... No, I don't think you should have a separate table for each name type. Have a single name table with an additional column of NameType. That'll make queries like "Let me know all records that John Brown is involved with", "Let me know those records for which Mary Smith was the Approver" and "Let me know all records where the same person was the Developer and the Originator" much, much simpler. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "zSplash" wrote in message ... Thanks, Doug, for responding. By "create a second table linked to that first table with one row for each name" that I should have individual tables for each nameType? That is leave the mainTable with the common information, and then create a table for nameOriginator, a table for nameApprover, a table nameDeveloper, etc, with nameData for each of those nameTypes? "Douglas J. Steele" wrote in message ... You'd better explain your precise need, but in general, you wouldn't put multiple names on a single record. Typically when you have multiple names on a single record, it means you've got field names like "Originator", "Approver", "Developer" etc. That's not a good idea: you're hiding data in the field names. Instead, you should keep the common information in the one table, and create a second table linked to that first table with one row for each name. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "zSplash" wrote in message ... My database needs to track several names (first and last) for each record. My initial database had all the names (first/last) in the main table. In an earlier request for help searching for lastnames, someone suggested that I put all the names in a separate Names table with a nameType comboBox. Now that I've modified my database to do that, I see that with that design I can only have one name per record. (To enter names, I select nameType, and then enter first/last names for that nameType. I have no way of entering/adding the first/lastnames for the other nameTypes. Each record has 5 or 6 first/last names to track. If they are all in one Names table, as suggested, I can only hold one nameType in each record. Can someone please suggest another way to solve my problem? TIA |
#25
|
|||
|
|||
searching for names - multiple names per record
In my form, then, I want to return, in one place, the originator's first and
last names. I have put the textbox for the query's firstname. How do I make it so that it pulls the firstname of the originator that fits with the record, rather than the developer's firstname? I unsuccessfully tried putting =first where nametype="originator" =first if nametype="originator" as the default value, as well as the control source in a generic textbox. TIA "Douglas J. Steele" wrote in message ... I'm not sure I understand why you need multiple queries if you're having a single form. Seems to me a single query that returns everything is what you want. You can then apply filters to the form to only show specific records if need be. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "zSplash" wrote in message ... Okay. I really appreciate the help, Doug. (I was called away from this project, and am only now getting back to it.) My aim is to have a single form, with labels for nameType, then textboxes for nameTypeFirst, nameTypeLast, nameTypeTitle, nameTypeAssistant. If I have to create multiple queries to get this data on the form (which it seems like I'll have to do), I'm going to have 4 queries for every name type. Is there a more efficient way to do it rather than creating 4x4 queries? As it is, my queries are overwhelming -- is that normal? TIA "Douglas J. Steele" wrote in message ... Depending on what your aim is, yes, you might require multiple queries. A basic starting point, given Table1 Id Desc 1 Project A 2 Project B 3 Project C and Table2 Id NameType Person 1 Originator Tom Jones 1 Developer Mary Brown 1 Approver John Doe 2 Originator Mary Brown 2 Developer Jill Roe 2 Approver Mary Smith 3 Originator John Brown 3 Developer John Brown 3 Approver Mary Smith would be something like: SELECT Table1.Id, Table1.Desc, Table2.NameType, Table2.Person FROM Table1 LEFT JOIN Table2 ON Table1.Id = Table2.Id -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "zSplash" wrote in message ... Thanks, Doug. Right. Your Table2 is my 2Names. Your Table1 is my 1Main. Its fields are many, including general info (like ProjectA, ProjectB, ProjectC, dates, and other data (I expect you'll say to break dates out once I get it down. I will!). I'm also wondering about whether I need separate queries for each nameType. Do I? Otherwise, do I make a query of individual queries (ie. a query for Originator, etc.)? "Douglas J. Steele" wrote in message ... So 2Names is what you called what I referred to as Table2? What have you called the equivalent of Table1 (and what are its fields)? -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "zSplash" wrote in message ... Thanks so much, Doug. So, my SQL for the query is: SELECT [2Names].ID, [2Names].fk, [2Names].nameType, [2Names].First, [2Names].Last, [2Names].DOB FROM 2Names; TIA "Douglas J. Steele" wrote in message ... What's the SQL for your query? If you're not familiar with seeing the SQL, open the query in Design view, and then choose "SQL View" from the View menu. (It's far easier to deal with SQL than to try & walk through the graphics of the query builder!) -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "zSplash" wrote in message ... Here's yet another question: How can I return the same number of records in my form that I have in Table1? That is: Table1 has 1238 unique records. Of the queries, the one for Approver has the fewest results -- 629. When I create the form, based on separate queries for Developer, Originator, and Approver, the form only shows 629 records. What's the deal with that? I need to return all 1238 records in my form. TIA "zSplash" wrote in message ... So, it's slowly becoming clear! The light at the end of a l-o-o-n-n-n-g tunnel (probably excruciatingly long for you). Thanks, Doug. You've been the model of patience. Now, my next question: In creating forms, is it better design to use queries or tables? In this case, is it better to use Table1 or a query based on Table1 when I re-design my form? TIA "Douglas J. Steele" wrote in message ... You don't set the ControlSource property for text boxes to SQL statements. You set the RecordSource of the form to the query, and set the text box's ControlSource to the name of a field in that query. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "zSplash" wrote in message ... Okay. I've done that (and named the qTest). And I've created a form with textboxes. So, for example, to pull up the data about the first record's originator's lastname, I've put the following in the Control Source property of the textbox: SELECT [qTest]![Last] from [qTest] where [qTest]![nameType]="Originator" But, when I open the form, that textbox shows an error: Name#? What's wrong? TIA "Douglas J. Steele" wrote in message ... You join them in a query, linking the ID field in Table1 to the corresponding ID field in Table2. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "zSplash" wrote in message ... Thanks for the patience for dealing with such a dense person, Doug. So, I have Table1 and Table2 (excepting that my Table2 has a new pkID, First, Last, and DOB, in addition to your Table1 data). How do I now "connect" Table1 with Table2? TIA "Douglas J. Steele" wrote in message ... I may have confused you. If you've got Table1 and Table2 as I described them, that's essentially all you need. I'd suggested an extension of that if you had a Person table: Id Person 1 Tom Jones 2 Mary Brown 3 John Doe 4 Jill Roe 5 Mary Smith 6 John Brown Then, instead of Table2 being Id NameType Person 1 Originator Tom Jones 1 Developer Mary Brown 1 Approver John Doe 2 Originator Mary Brown 2 Developer Jill Roe 2 Approver Mary Smith 3 Originator John Brown 3 Developer John Brown 3 Approver Mary Smith it would be Id NameType Person 1 Originator 1 1 Developer 2 1 Approver 3 2 Originator 2 2 Developer 4 2 Approver 5 3 Originator 6 3 Developer 6 3 Approver 5 -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "zSplash" wrote in message ... Thanks so much, Doug. I have tried to re-do my database to meet your suggestions. I have a Table1 and Table 2, as you've outlined. Now, I need direction on how to make a third table "that resolves the intersection of the two tables". I just don't quite get the foreign key deal. TIA "Douglas J. Steele" wrote in message ... Realistically, a primary key is just an index, and any index can have up to 10 separate fields in it. Which design are you asking about "do I need a foreign key in Names table"? Are you talking about my comment at the end ("Depending on your actual requirements, you could have a Person table, so that all you store in Table2 is the PersonId."), or are you talking about Table2 in the example? Table2 must point to Table1, so yes, it must have a foreign key in it. If you're using "Names table" to refer to what I called "a Person table", then no: that table wouldn't have a foreign key in it. In essence you've got a many-to-many relationship between Table1 and the Names table. You create a third table that resolves the intersection of the two tables, and that intersection table consists of foreign keys pointing back to the other 2 tables. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "zSplash" wrote in message ... Thanks, Doug. I didn't know a primary key could be a combination of several fields -- I thought it had to be a number field? And if I use your design, do I need a foreign key in Names table to connect (somehow) to the pk in the Projects table? "Douglas J. Steele" wrote in message ... Actually, you need at least one additional field in the second table: the link back to the first table. Let's assume you currently have Table1: Id Desc Originator Developer Approver 1 Project A Tom Jones Mary Brown John Doe 2 Project B Mary Brown Jill Roe Mary Smith 3 Project C John Brown John Brown Mary Smith with Id as the Primary Key. You'd change Table1 to: Id Desc 1 Project A 2 Project B 3 Project C still with Id as the Primary Key. and Table2 would be: Id NameType Person 1 Originator Tom Jones 1 Developer Mary Brown 1 Approver John Doe 2 Originator Mary Brown 2 Developer Jill Roe 2 Approver Mary Smith 3 Originator John Brown 3 Developer John Brown 3 Approver Mary Smith with the combination of Id and NameType as the Primary Key. (If you can have more than NameType for a particular item, you'd need more for the PK) Depending on your actual requirements, you could have a Person table, so that all you store in Table2 is the PersonId. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "zSplash" wrote in message ... Thanks, Doug, but I just don't get it. If I have a single table, with a col for Nametype, a col for First, and a col for Last, how can I ever have more than one name per record? st. "Douglas J. Steele" wrote in message ... No, I don't think you should have a separate table for each name type. Have a single name table with an additional column of NameType. That'll make queries like "Let me know all records that John Brown is involved with", "Let me know those records for which Mary Smith was the Approver" and "Let me know all records where the same person was the Developer and the Originator" much, much simpler. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "zSplash" wrote in message ... Thanks, Doug, for responding. By "create a second table linked to that first table with one row for each name" that I should have individual tables for each nameType? That is leave the mainTable with the common information, and then create a table for nameOriginator, a table for nameApprover, a table nameDeveloper, etc, with nameData for each of those nameTypes? "Douglas J. Steele" wrote in message ... You'd better explain your precise need, but in general, you wouldn't put multiple names on a single record. Typically when you have multiple names on a single record, it means you've got field names like "Originator", "Approver", "Developer" etc. That's not a good idea: you're hiding data in the field names. Instead, you should keep the common information in the one table, and create a second table linked to that first table with one row for each name. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "zSplash" wrote in message ... My database needs to track several names (first and last) for each record. My initial database had all the names (first/last) in the main table. In an earlier request for help searching for lastnames, someone suggested that I put all the names in a separate Names table with a nameType comboBox. Now that I've modified my database to do that, I see that with that design I can only have one name per record. (To enter names, I select nameType, and then enter first/last names for that nameType. I have no way of entering/adding the first/lastnames for the other nameTypes. Each record has 5 or 6 first/last names to track. If they are all in one Names table, as suggested, I can only hold one nameType in each record. Can someone please suggest another way to solve my problem? TIA |
#26
|
|||
|
|||
searching for names - multiple names per record
If I put
= First if nametype="originator" for Control Source, I get "invalid syntax". How does one filter a bound textbox, I guess I need to know? TIA "zSplash" wrote in message ... In my form, then, I want to return, in one place, the originator's first and last names. I have put the textbox for the query's firstname. How do I make it so that it pulls the firstname of the originator that fits with the record, rather than the developer's firstname? I unsuccessfully tried putting =first where nametype="originator" =first if nametype="originator" as the default value, as well as the control source in a generic textbox. TIA "Douglas J. Steele" wrote in message ... I'm not sure I understand why you need multiple queries if you're having a single form. Seems to me a single query that returns everything is what you want. You can then apply filters to the form to only show specific records if need be. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "zSplash" wrote in message ... Okay. I really appreciate the help, Doug. (I was called away from this project, and am only now getting back to it.) My aim is to have a single form, with labels for nameType, then textboxes for nameTypeFirst, nameTypeLast, nameTypeTitle, nameTypeAssistant. If I have to create multiple queries to get this data on the form (which it seems like I'll have to do), I'm going to have 4 queries for every name type. Is there a more efficient way to do it rather than creating 4x4 queries? As it is, my queries are overwhelming -- is that normal? TIA "Douglas J. Steele" wrote in message ... Depending on what your aim is, yes, you might require multiple queries. A basic starting point, given Table1 Id Desc 1 Project A 2 Project B 3 Project C and Table2 Id NameType Person 1 Originator Tom Jones 1 Developer Mary Brown 1 Approver John Doe 2 Originator Mary Brown 2 Developer Jill Roe 2 Approver Mary Smith 3 Originator John Brown 3 Developer John Brown 3 Approver Mary Smith would be something like: SELECT Table1.Id, Table1.Desc, Table2.NameType, Table2.Person FROM Table1 LEFT JOIN Table2 ON Table1.Id = Table2.Id -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "zSplash" wrote in message ... Thanks, Doug. Right. Your Table2 is my 2Names. Your Table1 is my 1Main. Its fields are many, including general info (like ProjectA, ProjectB, ProjectC, dates, and other data (I expect you'll say to break dates out once I get it down. I will!). I'm also wondering about whether I need separate queries for each nameType. Do I? Otherwise, do I make a query of individual queries (ie. a query for Originator, etc.)? "Douglas J. Steele" wrote in message ... So 2Names is what you called what I referred to as Table2? What have you called the equivalent of Table1 (and what are its fields)? -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "zSplash" wrote in message ... Thanks so much, Doug. So, my SQL for the query is: SELECT [2Names].ID, [2Names].fk, [2Names].nameType, [2Names].First, [2Names].Last, [2Names].DOB FROM 2Names; TIA "Douglas J. Steele" wrote in message ... What's the SQL for your query? If you're not familiar with seeing the SQL, open the query in Design view, and then choose "SQL View" from the View menu. (It's far easier to deal with SQL than to try & walk through the graphics of the query builder!) -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "zSplash" wrote in message ... Here's yet another question: How can I return the same number of records in my form that I have in Table1? That is: Table1 has 1238 unique records. Of the queries, the one for Approver has the fewest results -- 629. When I create the form, based on separate queries for Developer, Originator, and Approver, the form only shows 629 records. What's the deal with that? I need to return all 1238 records in my form. TIA "zSplash" wrote in message ... So, it's slowly becoming clear! The light at the end of a l-o-o-n-n-n-g tunnel (probably excruciatingly long for you). Thanks, Doug. You've been the model of patience. Now, my next question: In creating forms, is it better design to use queries or tables? In this case, is it better to use Table1 or a query based on Table1 when I re-design my form? TIA "Douglas J. Steele" wrote in message ... You don't set the ControlSource property for text boxes to SQL statements. You set the RecordSource of the form to the query, and set the text box's ControlSource to the name of a field in that query. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "zSplash" wrote in message ... Okay. I've done that (and named the qTest). And I've created a form with textboxes. So, for example, to pull up the data about the first record's originator's lastname, I've put the following in the Control Source property of the textbox: SELECT [qTest]![Last] from [qTest] where [qTest]![nameType]="Originator" But, when I open the form, that textbox shows an error: Name#? What's wrong? TIA "Douglas J. Steele" wrote in message ... You join them in a query, linking the ID field in Table1 to the corresponding ID field in Table2. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "zSplash" wrote in message ... Thanks for the patience for dealing with such a dense person, Doug. So, I have Table1 and Table2 (excepting that my Table2 has a new pkID, First, Last, and DOB, in addition to your Table1 data). How do I now "connect" Table1 with Table2? TIA "Douglas J. Steele" wrote in message ... I may have confused you. If you've got Table1 and Table2 as I described them, that's essentially all you need. I'd suggested an extension of that if you had a Person table: Id Person 1 Tom Jones 2 Mary Brown 3 John Doe 4 Jill Roe 5 Mary Smith 6 John Brown Then, instead of Table2 being Id NameType Person 1 Originator Tom Jones 1 Developer Mary Brown 1 Approver John Doe 2 Originator Mary Brown 2 Developer Jill Roe 2 Approver Mary Smith 3 Originator John Brown 3 Developer John Brown 3 Approver Mary Smith it would be Id NameType Person 1 Originator 1 1 Developer 2 1 Approver 3 2 Originator 2 2 Developer 4 2 Approver 5 3 Originator 6 3 Developer 6 3 Approver 5 -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "zSplash" wrote in message ... Thanks so much, Doug. I have tried to re-do my database to meet your suggestions. I have a Table1 and Table 2, as you've outlined. Now, I need direction on how to make a third table "that resolves the intersection of the two tables". I just don't quite get the foreign key deal. TIA "Douglas J. Steele" wrote in message ... Realistically, a primary key is just an index, and any index can have up to 10 separate fields in it. Which design are you asking about "do I need a foreign key in Names table"? Are you talking about my comment at the end ("Depending on your actual requirements, you could have a Person table, so that all you store in Table2 is the PersonId."), or are you talking about Table2 in the example? Table2 must point to Table1, so yes, it must have a foreign key in it. If you're using "Names table" to refer to what I called "a Person table", then no: that table wouldn't have a foreign key in it. In essence you've got a many-to-many relationship between Table1 and the Names table. You create a third table that resolves the intersection of the two tables, and that intersection table consists of foreign keys pointing back to the other 2 tables. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "zSplash" wrote in message ... Thanks, Doug. I didn't know a primary key could be a combination of several fields -- I thought it had to be a number field? And if I use your design, do I need a foreign key in Names table to connect (somehow) to the pk in the Projects table? "Douglas J. Steele" wrote in message ... Actually, you need at least one additional field in the second table: the link back to the first table. Let's assume you currently have Table1: Id Desc Originator Developer Approver 1 Project A Tom Jones Mary Brown John Doe 2 Project B Mary Brown Jill Roe Mary Smith 3 Project C John Brown John Brown Mary Smith with Id as the Primary Key. You'd change Table1 to: Id Desc 1 Project A 2 Project B 3 Project C still with Id as the Primary Key. and Table2 would be: Id NameType Person 1 Originator Tom Jones 1 Developer Mary Brown 1 Approver John Doe 2 Originator Mary Brown 2 Developer Jill Roe 2 Approver Mary Smith 3 Originator John Brown 3 Developer John Brown 3 Approver Mary Smith with the combination of Id and NameType as the Primary Key. (If you can have more than NameType for a particular item, you'd need more for the PK) Depending on your actual requirements, you could have a Person table, so that all you store in Table2 is the PersonId. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "zSplash" wrote in message ... Thanks, Doug, but I just don't get it. If I have a single table, with a col for Nametype, a col for First, and a col for Last, how can I ever have more than one name per record? st. "Douglas J. Steele" wrote in message ... No, I don't think you should have a separate table for each name type. Have a single name table with an additional column of NameType. That'll make queries like "Let me know all records that John Brown is involved with", "Let me know those records for which Mary Smith was the Approver" and "Let me know all records where the same person was the Developer and the Originator" much, much simpler. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "zSplash" wrote in message ... Thanks, Doug, for responding. By "create a second table linked to that first table with one row for each name" that I should have individual tables for each nameType? That is leave the mainTable with the common information, and then create a table for nameOriginator, a table for nameApprover, a table nameDeveloper, etc, with nameData for each of those nameTypes? "Douglas J. Steele" wrote in message ... You'd better explain your precise need, but in general, you wouldn't put multiple names on a single record. Typically when you have multiple names on a single record, it means you've got field names like "Originator", "Approver", "Developer" etc. That's not a good idea: you're hiding data in the field names. Instead, you should keep the common information in the one table, and create a second table linked to that first table with one row for each name. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "zSplash" wrote in message ... My database needs to track several names (first and last) for each record. My initial database had all the names (first/last) in the main table. In an earlier request for help searching for lastnames, someone suggested that I put all the names in a separate Names table with a nameType comboBox. Now that I've modified my database to do that, I see that with that design I can only have one name per record. (To enter names, I select nameType, and then enter first/last names for that nameType. I have no way of entering/adding the first/lastnames for the other nameTypes. Each record has 5 or 6 first/last names to track. If they are all in one Names table, as suggested, I can only hold one nameType in each record. Can someone please suggest another way to solve my problem? TIA |
#27
|
|||
|
|||
searching for names - multiple names per record
You don't apply filters to controls (like textboxes). You apply filters to
the underlying recordset of the form. To have your form only show the names for those people whose nametype is originator, you need code like: Me.Filter = "nametype = 'originator'" Me.FilterOn = True -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "zSplash" wrote in message ... In my form, then, I want to return, in one place, the originator's first and last names. I have put the textbox for the query's firstname. How do I make it so that it pulls the firstname of the originator that fits with the record, rather than the developer's firstname? I unsuccessfully tried putting =first where nametype="originator" =first if nametype="originator" as the default value, as well as the control source in a generic textbox. TIA "Douglas J. Steele" wrote in message ... I'm not sure I understand why you need multiple queries if you're having a single form. Seems to me a single query that returns everything is what you want. You can then apply filters to the form to only show specific records if need be. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "zSplash" wrote in message ... Okay. I really appreciate the help, Doug. (I was called away from this project, and am only now getting back to it.) My aim is to have a single form, with labels for nameType, then textboxes for nameTypeFirst, nameTypeLast, nameTypeTitle, nameTypeAssistant. If I have to create multiple queries to get this data on the form (which it seems like I'll have to do), I'm going to have 4 queries for every name type. Is there a more efficient way to do it rather than creating 4x4 queries? As it is, my queries are overwhelming -- is that normal? TIA "Douglas J. Steele" wrote in message ... Depending on what your aim is, yes, you might require multiple queries. A basic starting point, given Table1 Id Desc 1 Project A 2 Project B 3 Project C and Table2 Id NameType Person 1 Originator Tom Jones 1 Developer Mary Brown 1 Approver John Doe 2 Originator Mary Brown 2 Developer Jill Roe 2 Approver Mary Smith 3 Originator John Brown 3 Developer John Brown 3 Approver Mary Smith would be something like: SELECT Table1.Id, Table1.Desc, Table2.NameType, Table2.Person FROM Table1 LEFT JOIN Table2 ON Table1.Id = Table2.Id -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "zSplash" wrote in message ... Thanks, Doug. Right. Your Table2 is my 2Names. Your Table1 is my 1Main. Its fields are many, including general info (like ProjectA, ProjectB, ProjectC, dates, and other data (I expect you'll say to break dates out once I get it down. I will!). I'm also wondering about whether I need separate queries for each nameType. Do I? Otherwise, do I make a query of individual queries (ie. a query for Originator, etc.)? "Douglas J. Steele" wrote in message ... So 2Names is what you called what I referred to as Table2? What have you called the equivalent of Table1 (and what are its fields)? -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "zSplash" wrote in message ... Thanks so much, Doug. So, my SQL for the query is: SELECT [2Names].ID, [2Names].fk, [2Names].nameType, [2Names].First, [2Names].Last, [2Names].DOB FROM 2Names; TIA "Douglas J. Steele" wrote in message ... What's the SQL for your query? If you're not familiar with seeing the SQL, open the query in Design view, and then choose "SQL View" from the View menu. (It's far easier to deal with SQL than to try & walk through the graphics of the query builder!) -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "zSplash" wrote in message ... Here's yet another question: How can I return the same number of records in my form that I have in Table1? That is: Table1 has 1238 unique records. Of the queries, the one for Approver has the fewest results -- 629. When I create the form, based on separate queries for Developer, Originator, and Approver, the form only shows 629 records. What's the deal with that? I need to return all 1238 records in my form. TIA "zSplash" wrote in message ... So, it's slowly becoming clear! The light at the end of a l-o-o-n-n-n-g tunnel (probably excruciatingly long for you). Thanks, Doug. You've been the model of patience. Now, my next question: In creating forms, is it better design to use queries or tables? In this case, is it better to use Table1 or a query based on Table1 when I re-design my form? TIA "Douglas J. Steele" wrote in message ... You don't set the ControlSource property for text boxes to SQL statements. You set the RecordSource of the form to the query, and set the text box's ControlSource to the name of a field in that query. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "zSplash" wrote in message ... Okay. I've done that (and named the qTest). And I've created a form with textboxes. So, for example, to pull up the data about the first record's originator's lastname, I've put the following in the Control Source property of the textbox: SELECT [qTest]![Last] from [qTest] where [qTest]![nameType]="Originator" But, when I open the form, that textbox shows an error: Name#? What's wrong? TIA "Douglas J. Steele" wrote in message ... You join them in a query, linking the ID field in Table1 to the corresponding ID field in Table2. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "zSplash" wrote in message ... Thanks for the patience for dealing with such a dense person, Doug. So, I have Table1 and Table2 (excepting that my Table2 has a new pkID, First, Last, and DOB, in addition to your Table1 data). How do I now "connect" Table1 with Table2? TIA "Douglas J. Steele" wrote in message ... I may have confused you. If you've got Table1 and Table2 as I described them, that's essentially all you need. I'd suggested an extension of that if you had a Person table: Id Person 1 Tom Jones 2 Mary Brown 3 John Doe 4 Jill Roe 5 Mary Smith 6 John Brown Then, instead of Table2 being Id NameType Person 1 Originator Tom Jones 1 Developer Mary Brown 1 Approver John Doe 2 Originator Mary Brown 2 Developer Jill Roe 2 Approver Mary Smith 3 Originator John Brown 3 Developer John Brown 3 Approver Mary Smith it would be Id NameType Person 1 Originator 1 1 Developer 2 1 Approver 3 2 Originator 2 2 Developer 4 2 Approver 5 3 Originator 6 3 Developer 6 3 Approver 5 -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "zSplash" wrote in message ... Thanks so much, Doug. I have tried to re-do my database to meet your suggestions. I have a Table1 and Table 2, as you've outlined. Now, I need direction on how to make a third table "that resolves the intersection of the two tables". I just don't quite get the foreign key deal. TIA "Douglas J. Steele" wrote in message ... Realistically, a primary key is just an index, and any index can have up to 10 separate fields in it. Which design are you asking about "do I need a foreign key in Names table"? Are you talking about my comment at the end ("Depending on your actual requirements, you could have a Person table, so that all you store in Table2 is the PersonId."), or are you talking about Table2 in the example? Table2 must point to Table1, so yes, it must have a foreign key in it. If you're using "Names table" to refer to what I called "a Person table", then no: that table wouldn't have a foreign key in it. In essence you've got a many-to-many relationship between Table1 and the Names table. You create a third table that resolves the intersection of the two tables, and that intersection table consists of foreign keys pointing back to the other 2 tables. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "zSplash" wrote in message ... Thanks, Doug. I didn't know a primary key could be a combination of several fields -- I thought it had to be a number field? And if I use your design, do I need a foreign key in Names table to connect (somehow) to the pk in the Projects table? "Douglas J. Steele" wrote in message ... Actually, you need at least one additional field in the second table: the link back to the first table. Let's assume you currently have Table1: Id Desc Originator Developer Approver 1 Project A Tom Jones Mary Brown John Doe 2 Project B Mary Brown Jill Roe Mary Smith 3 Project C John Brown John Brown Mary Smith with Id as the Primary Key. You'd change Table1 to: Id Desc 1 Project A 2 Project B 3 Project C still with Id as the Primary Key. and Table2 would be: Id NameType Person 1 Originator Tom Jones 1 Developer Mary Brown 1 Approver John Doe 2 Originator Mary Brown 2 Developer Jill Roe 2 Approver Mary Smith 3 Originator John Brown 3 Developer John Brown 3 Approver Mary Smith with the combination of Id and NameType as the Primary Key. (If you can have more than NameType for a particular item, you'd need more for the PK) Depending on your actual requirements, you could have a Person table, so that all you store in Table2 is the PersonId. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "zSplash" wrote in message ... Thanks, Doug, but I just don't get it. If I have a single table, with a col for Nametype, a col for First, and a col for Last, how can I ever have more than one name per record? st. "Douglas J. Steele" wrote in message ... No, I don't think you should have a separate table for each name type. Have a single name table with an additional column of NameType. That'll make queries like "Let me know all records that John Brown is involved with", "Let me know those records for which Mary Smith was the Approver" and "Let me know all records where the same person was the Developer and the Originator" much, much simpler. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "zSplash" wrote in message ... Thanks, Doug, for responding. By "create a second table linked to that first table with one row for each name" that I should have individual tables for each nameType? That is leave the mainTable with the common information, and then create a table for nameOriginator, a table for nameApprover, a table nameDeveloper, etc, with nameData for each of those nameTypes? "Douglas J. Steele" wrote in message ... You'd better explain your precise need, but in general, you wouldn't put multiple names on a single record. Typically when you have multiple names on a single record, it means you've got field names like "Originator", "Approver", "Developer" etc. That's not a good idea: you're hiding data in the field names. Instead, you should keep the common information in the one table, and create a second table linked to that first table with one row for each name. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "zSplash" wrote in message ... My database needs to track several names (first and last) for each record. My initial database had all the names (first/last) in the main table. In an earlier request for help searching for lastnames, someone suggested that I put all the names in a separate Names table with a nameType comboBox. Now that I've modified my database to do that, I see that with that design I can only have one name per record. (To enter names, I select nameType, and then enter first/last names for that nameType. I have no way of entering/adding the first/lastnames for the other nameTypes. Each record has 5 or 6 first/last names to track. If they are all in one Names table, as suggested, I can only hold one nameType in each record. Can someone please suggest another way to solve my problem? TIA |
#28
|
|||
|
|||
searching for names - multiple names per record
Okay, Doug. Thanks for that, which makes sense, but where do I put that
code? TIA "Douglas J. Steele" wrote in message ... You don't apply filters to controls (like textboxes). You apply filters to the underlying recordset of the form. To have your form only show the names for those people whose nametype is originator, you need code like: Me.Filter = "nametype = 'originator'" Me.FilterOn = True -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Douglas J. Steele" wrote in message ... I always use a query, even if the query doesn't do anything more than return the table as-is. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "zSplash" wrote in message ... So, it's slowly becoming clear! The light at the end of a l-o-o-n-n-n-g tunnel (probably excruciatingly long for you). Thanks, Doug. You've been the model of patience. Now, my next question: In creating forms, is it better design to use queries or tables? In this case, is it better to use Table1 or a query based on Table1 when I re-design my form? TIA "Douglas J. Steele" wrote in message ... You don't set the ControlSource property for text boxes to SQL statements. You set the RecordSource of the form to the query, and set the text box's ControlSource to the name of a field in that query. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "zSplash" wrote in message ... Okay. I've done that (and named the qTest). And I've created a form with textboxes. So, for example, to pull up the data about the first record's originator's lastname, I've put the following in the Control Source property of the textbox: SELECT [qTest]![Last] from [qTest] where [qTest]![nameType]="Originator" But, when I open the form, that textbox shows an error: Name#? What's wrong? TIA "Douglas J. Steele" wrote in message ... You join them in a query, linking the ID field in Table1 to the corresponding ID field in Table2. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "zSplash" wrote in message ... Thanks for the patience for dealing with such a dense person, Doug. So, I have Table1 and Table2 (excepting that my Table2 has a new pkID, First, Last, and DOB, in addition to your Table1 data). How do I now "connect" Table1 with Table2? TIA "Douglas J. Steele" wrote in message ... I may have confused you. If you've got Table1 and Table2 as I described them, that's essentially all you need. I'd suggested an extension of that if you had a Person table: Id Person 1 Tom Jones 2 Mary Brown 3 John Doe 4 Jill Roe 5 Mary Smith 6 John Brown Then, instead of Table2 being Id NameType Person 1 Originator Tom Jones 1 Developer Mary Brown 1 Approver John Doe 2 Originator Mary Brown 2 Developer Jill Roe 2 Approver Mary Smith 3 Originator John Brown 3 Developer John Brown 3 Approver Mary Smith it would be Id NameType Person 1 Originator 1 1 Developer 2 1 Approver 3 2 Originator 2 2 Developer 4 2 Approver 5 3 Originator 6 3 Developer 6 3 Approver 5 -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "zSplash" wrote in message ... Thanks so much, Doug. I have tried to re-do my database to meet your suggestions. I have a Table1 and Table 2, as you've outlined. Now, I need direction on how to make a third table "that resolves the intersection of the two tables". I just don't quite get the foreign key deal. TIA "Douglas J. Steele" wrote in message ... Realistically, a primary key is just an index, and any index can have up to 10 separate fields in it. Which design are you asking about "do I need a foreign key in Names table"? Are you talking about my comment at the end ("Depending on your actual requirements, you could have a Person table, so that all you store in Table2 is the PersonId."), or are you talking about Table2 in the example? Table2 must point to Table1, so yes, it must have a foreign key in it. If you're using "Names table" to refer to what I called "a Person table", then no: that table wouldn't have a foreign key in it. In essence you've got a many-to-many relationship between Table1 and the Names table. You create a third table that resolves the intersection of the two tables, and that intersection table consists of foreign keys pointing back to the other 2 tables. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "zSplash" wrote in message ... Thanks, Doug. I didn't know a primary key could be a combination of several fields -- I thought it had to be a number field? And if I use your design, do I need a foreign key in Names table to connect (somehow) to the pk in the Projects table? "Douglas J. Steele" wrote in message ... Actually, you need at least one additional field in the second table: the link back to the first table. Let's assume you currently have Table1: Id Desc Originator Developer Approver 1 Project A Tom Jones Mary Brown John Doe 2 Project B Mary Brown Jill Roe Mary Smith 3 Project C John Brown John Brown Mary Smith with Id as the Primary Key. You'd change Table1 to: Id Desc 1 Project A 2 Project B 3 Project C still with Id as the Primary Key. and Table2 would be: Id NameType Person 1 Originator Tom Jones 1 Developer Mary Brown 1 Approver John Doe 2 Originator Mary Brown 2 Developer Jill Roe 2 Approver Mary Smith 3 Originator John Brown 3 Developer John Brown 3 Approver Mary Smith with the combination of Id and NameType as the Primary Key. (If you can have more than NameType for a particular item, you'd need more for the PK) Depending on your actual requirements, you could have a Person table, so that all you store in Table2 is the PersonId. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "zSplash" wrote in message ... Thanks, Doug, but I just don't get it. If I have a single table, with a col for Nametype, a col for First, and a col for Last, how can I ever have more than one name per record? st. "Douglas J. Steele" wrote in message ... No, I don't think you should have a separate table for each name type. Have a single name table with an additional column of NameType. That'll make queries like "Let me know all records that John Brown is involved with", "Let me know those records for which Mary Smith was the Approver" and "Let me know all records where the same person was the Developer and the Originator" much, much simpler. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "zSplash" wrote in message ... Thanks, Doug, for responding. By "create a second table linked to that first table with one row for each name" that I should have individual tables for each nameType? That is leave the mainTable with the common information, and then create a table for nameOriginator, a table for nameApprover, a table nameDeveloper, etc, with nameData for each of those nameTypes? "Douglas J. Steele" wrote in message ... You'd better explain your precise need, but in general, you wouldn't put multiple names on a single record. Typically when you have multiple names on a single record, it means you've got field names like "Originator", "Approver", "Developer" etc. That's not a good idea: you're hiding data in the field names. Instead, you should keep the common information in the one table, and create a second table linked to that first table with one row for each name. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "zSplash" wrote in message ... My database needs to track several names (first and last) for each record. My initial database had all the names (first/last) in the main table. In an earlier request for help searching for lastnames, someone suggested that I put all the names in a separate Names table with a nameType comboBox. Now that I've modified my database to do that, I see that with that design I can only have one name per record. (To enter names, I select nameType, and then enter first/last names for that nameType. I have no way of entering/adding the first/lastnames for the other nameTypes. Each record has 5 or 6 first/last names to track. If they are all in one Names table, as suggested, I can only hold one nameType in each record. Can someone please suggest another way to solve my problem? TIA |
#29
|
|||
|
|||
searching for names - multiple names per record
At the risk of seeming trite, wherever you need it to be.
Sorry, I don't know how you're building your application. Do you want a button that they push to limit the data, do you want to let them select from a combo box or option group, or what? -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "zSplash" wrote in message ... Okay, Doug. Thanks for that, which makes sense, but where do I put that code? TIA "Douglas J. Steele" wrote in message ... You don't apply filters to controls (like textboxes). You apply filters to the underlying recordset of the form. To have your form only show the names for those people whose nametype is originator, you need code like: Me.Filter = "nametype = 'originator'" Me.FilterOn = True -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Douglas J. Steele" wrote in message ... I always use a query, even if the query doesn't do anything more than return the table as-is. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "zSplash" wrote in message ... So, it's slowly becoming clear! The light at the end of a l-o-o-n-n-n-g tunnel (probably excruciatingly long for you). Thanks, Doug. You've been the model of patience. Now, my next question: In creating forms, is it better design to use queries or tables? In this case, is it better to use Table1 or a query based on Table1 when I re-design my form? TIA "Douglas J. Steele" wrote in message ... You don't set the ControlSource property for text boxes to SQL statements. You set the RecordSource of the form to the query, and set the text box's ControlSource to the name of a field in that query. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "zSplash" wrote in message ... Okay. I've done that (and named the qTest). And I've created a form with textboxes. So, for example, to pull up the data about the first record's originator's lastname, I've put the following in the Control Source property of the textbox: SELECT [qTest]![Last] from [qTest] where [qTest]![nameType]="Originator" But, when I open the form, that textbox shows an error: Name#? What's wrong? TIA "Douglas J. Steele" wrote in message ... You join them in a query, linking the ID field in Table1 to the corresponding ID field in Table2. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "zSplash" wrote in message ... Thanks for the patience for dealing with such a dense person, Doug. So, I have Table1 and Table2 (excepting that my Table2 has a new pkID, First, Last, and DOB, in addition to your Table1 data). How do I now "connect" Table1 with Table2? TIA "Douglas J. Steele" wrote in message ... I may have confused you. If you've got Table1 and Table2 as I described them, that's essentially all you need. I'd suggested an extension of that if you had a Person table: Id Person 1 Tom Jones 2 Mary Brown 3 John Doe 4 Jill Roe 5 Mary Smith 6 John Brown Then, instead of Table2 being Id NameType Person 1 Originator Tom Jones 1 Developer Mary Brown 1 Approver John Doe 2 Originator Mary Brown 2 Developer Jill Roe 2 Approver Mary Smith 3 Originator John Brown 3 Developer John Brown 3 Approver Mary Smith it would be Id NameType Person 1 Originator 1 1 Developer 2 1 Approver 3 2 Originator 2 2 Developer 4 2 Approver 5 3 Originator 6 3 Developer 6 3 Approver 5 -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "zSplash" wrote in message ... Thanks so much, Doug. I have tried to re-do my database to meet your suggestions. I have a Table1 and Table 2, as you've outlined. Now, I need direction on how to make a third table "that resolves the intersection of the two tables". I just don't quite get the foreign key deal. TIA "Douglas J. Steele" wrote in message ... Realistically, a primary key is just an index, and any index can have up to 10 separate fields in it. Which design are you asking about "do I need a foreign key in Names table"? Are you talking about my comment at the end ("Depending on your actual requirements, you could have a Person table, so that all you store in Table2 is the PersonId."), or are you talking about Table2 in the example? Table2 must point to Table1, so yes, it must have a foreign key in it. If you're using "Names table" to refer to what I called "a Person table", then no: that table wouldn't have a foreign key in it. In essence you've got a many-to-many relationship between Table1 and the Names table. You create a third table that resolves the intersection of the two tables, and that intersection table consists of foreign keys pointing back to the other 2 tables. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "zSplash" wrote in message ... Thanks, Doug. I didn't know a primary key could be a combination of several fields -- I thought it had to be a number field? And if I use your design, do I need a foreign key in Names table to connect (somehow) to the pk in the Projects table? "Douglas J. Steele" wrote in message ... Actually, you need at least one additional field in the second table: the link back to the first table. Let's assume you currently have Table1: Id Desc Originator Developer Approver 1 Project A Tom Jones Mary Brown John Doe 2 Project B Mary Brown Jill Roe Mary Smith 3 Project C John Brown John Brown Mary Smith with Id as the Primary Key. You'd change Table1 to: Id Desc 1 Project A 2 Project B 3 Project C still with Id as the Primary Key. and Table2 would be: Id NameType Person 1 Originator Tom Jones 1 Developer Mary Brown 1 Approver John Doe 2 Originator Mary Brown 2 Developer Jill Roe 2 Approver Mary Smith 3 Originator John Brown 3 Developer John Brown 3 Approver Mary Smith with the combination of Id and NameType as the Primary Key. (If you can have more than NameType for a particular item, you'd need more for the PK) Depending on your actual requirements, you could have a Person table, so that all you store in Table2 is the PersonId. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "zSplash" wrote in message ... Thanks, Doug, but I just don't get it. If I have a single table, with a col for Nametype, a col for First, and a col for Last, how can I ever have more than one name per record? st. "Douglas J. Steele" wrote in message ... No, I don't think you should have a separate table for each name type. Have a single name table with an additional column of NameType. That'll make queries like "Let me know all records that John Brown is involved with", "Let me know those records for which Mary Smith was the Approver" and "Let me know all records where the same person was the Developer and the Originator" much, much simpler. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "zSplash" wrote in message ... Thanks, Doug, for responding. By "create a second table linked to that first table with one row for each name" that I should have individual tables for each nameType? That is leave the mainTable with the common information, and then create a table for nameOriginator, a table for nameApprover, a table nameDeveloper, etc, with nameData for each of those nameTypes? "Douglas J. Steele" wrote in message ... You'd better explain your precise need, but in general, you wouldn't put multiple names on a single record. Typically when you have multiple names on a single record, it means you've got field names like "Originator", "Approver", "Developer" etc. That's not a good idea: you're hiding data in the field names. Instead, you should keep the common information in the one table, and create a second table linked to that first table with one row for each name. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "zSplash" wrote in message ... My database needs to track several names (first and last) for each record. My initial database had all the names (first/last) in the main table. In an earlier request for help searching for lastnames, someone suggested that I put all the names in a separate Names table with a nameType comboBox. Now that I've modified my database to do that, I see that with that design I can only have one name per record. (To enter names, I select nameType, and then enter first/last names for that nameType. I have no way of entering/adding the first/lastnames for the other nameTypes. Each record has 5 or 6 first/last names to track. If they are all in one Names table, as suggested, I can only hold one nameType in each record. Can someone please suggest another way to solve my problem? TIA |
#30
|
|||
|
|||
searching for names - multiple names per record
Trite, after all this help you've given me? LOL. Evidently, I don't yet
have a grip on how I'm building my application, either! Anyway, in my form I have a label that says "Originator:". Then, I have a bound textbox for Firstname, then a bound textbox for Lastname. I want to put your code (Me.Filter = "nametype = 'originator'") somewhere so that each of these textboxes will show the firstname and last name for the originator. TIA "Douglas J. Steele" wrote in message ... At the risk of seeming trite, wherever you need it to be. Sorry, I don't know how you're building your application. Do you want a button that they push to limit the data, do you want to let them select from a combo box or option group, or what? -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "zSplash" wrote in message ... Okay, Doug. Thanks for that, which makes sense, but where do I put that code? TIA "Douglas J. Steele" wrote in message ... You don't apply filters to controls (like textboxes). You apply filters to the underlying recordset of the form. To have your form only show the names for those people whose nametype is originator, you need code like: Me.Filter = "nametype = 'originator'" Me.FilterOn = True -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Douglas J. Steele" wrote in message ... I always use a query, even if the query doesn't do anything more than return the table as-is. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "zSplash" wrote in message ... So, it's slowly becoming clear! The light at the end of a l-o-o-n-n-n-g tunnel (probably excruciatingly long for you). Thanks, Doug. You've been the model of patience. Now, my next question: In creating forms, is it better design to use queries or tables? In this case, is it better to use Table1 or a query based on Table1 when I re-design my form? TIA "Douglas J. Steele" wrote in message ... You don't set the ControlSource property for text boxes to SQL statements. You set the RecordSource of the form to the query, and set the text box's ControlSource to the name of a field in that query. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "zSplash" wrote in message ... Okay. I've done that (and named the qTest). And I've created a form with textboxes. So, for example, to pull up the data about the first record's originator's lastname, I've put the following in the Control Source property of the textbox: SELECT [qTest]![Last] from [qTest] where [qTest]![nameType]="Originator" But, when I open the form, that textbox shows an error: Name#? What's wrong? TIA "Douglas J. Steele" wrote in message ... You join them in a query, linking the ID field in Table1 to the corresponding ID field in Table2. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "zSplash" wrote in message ... Thanks for the patience for dealing with such a dense person, Doug. So, I have Table1 and Table2 (excepting that my Table2 has a new pkID, First, Last, and DOB, in addition to your Table1 data). How do I now "connect" Table1 with Table2? TIA "Douglas J. Steele" wrote in message ... I may have confused you. If you've got Table1 and Table2 as I described them, that's essentially all you need. I'd suggested an extension of that if you had a Person table: Id Person 1 Tom Jones 2 Mary Brown 3 John Doe 4 Jill Roe 5 Mary Smith 6 John Brown Then, instead of Table2 being Id NameType Person 1 Originator Tom Jones 1 Developer Mary Brown 1 Approver John Doe 2 Originator Mary Brown 2 Developer Jill Roe 2 Approver Mary Smith 3 Originator John Brown 3 Developer John Brown 3 Approver Mary Smith it would be Id NameType Person 1 Originator 1 1 Developer 2 1 Approver 3 2 Originator 2 2 Developer 4 2 Approver 5 3 Originator 6 3 Developer 6 3 Approver 5 -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "zSplash" wrote in message ... Thanks so much, Doug. I have tried to re-do my database to meet your suggestions. I have a Table1 and Table 2, as you've outlined. Now, I need direction on how to make a third table "that resolves the intersection of the two tables". I just don't quite get the foreign key deal. TIA "Douglas J. Steele" wrote in message ... Realistically, a primary key is just an index, and any index can have up to 10 separate fields in it. Which design are you asking about "do I need a foreign key in Names table"? Are you talking about my comment at the end ("Depending on your actual requirements, you could have a Person table, so that all you store in Table2 is the PersonId."), or are you talking about Table2 in the example? Table2 must point to Table1, so yes, it must have a foreign key in it. If you're using "Names table" to refer to what I called "a Person table", then no: that table wouldn't have a foreign key in it. In essence you've got a many-to-many relationship between Table1 and the Names table. You create a third table that resolves the intersection of the two tables, and that intersection table consists of foreign keys pointing back to the other 2 tables. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "zSplash" wrote in message ... Thanks, Doug. I didn't know a primary key could be a combination of several fields -- I thought it had to be a number field? And if I use your design, do I need a foreign key in Names table to connect (somehow) to the pk in the Projects table? "Douglas J. Steele" wrote in message ... Actually, you need at least one additional field in the second table: the link back to the first table. Let's assume you currently have Table1: Id Desc Originator Developer Approver 1 Project A Tom Jones Mary Brown John Doe 2 Project B Mary Brown Jill Roe Mary Smith 3 Project C John Brown John Brown Mary Smith with Id as the Primary Key. You'd change Table1 to: Id Desc 1 Project A 2 Project B 3 Project C still with Id as the Primary Key. and Table2 would be: Id NameType Person 1 Originator Tom Jones 1 Developer Mary Brown 1 Approver John Doe 2 Originator Mary Brown 2 Developer Jill Roe 2 Approver Mary Smith 3 Originator John Brown 3 Developer John Brown 3 Approver Mary Smith with the combination of Id and NameType as the Primary Key. (If you can have more than NameType for a particular item, you'd need more for the PK) Depending on your actual requirements, you could have a Person table, so that all you store in Table2 is the PersonId. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "zSplash" wrote in message ... Thanks, Doug, but I just don't get it. If I have a single table, with a col for Nametype, a col for First, and a col for Last, how can I ever have more than one name per record? st. "Douglas J. Steele" wrote in message ... No, I don't think you should have a separate table for each name type. Have a single name table with an additional column of NameType. That'll make queries like "Let me know all records that John Brown is involved with", "Let me know those records for which Mary Smith was the Approver" and "Let me know all records where the same person was the Developer and the Originator" much, much simpler. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "zSplash" wrote in message ... Thanks, Doug, for responding. By "create a second table linked to that first table with one row for each name" that I should have individual tables for each nameType? That is leave the mainTable with the common information, and then create a table for nameOriginator, a table for nameApprover, a table nameDeveloper, etc, with nameData for each of those nameTypes? "Douglas J. Steele" wrote in message ... You'd better explain your precise need, but in general, you wouldn't put multiple names on a single record. Typically when you have multiple names on a single record, it means you've got field names like "Originator", "Approver", "Developer" etc. That's not a good idea: you're hiding data in the field names. Instead, you should keep the common information in the one table, and create a second table linked to that first table with one row for each name. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "zSplash" wrote in message ... My database needs to track several names (first and last) for each record. My initial database had all the names (first/last) in the main table. In an earlier request for help searching for lastnames, someone suggested that I put all the names in a separate Names table with a nameType comboBox. Now that I've modified my database to do that, I see that with that design I can only have one name per record. (To enter names, I select nameType, and then enter first/last names for that nameType. I have no way of entering/adding the first/lastnames for the other nameTypes. Each record has 5 or 6 first/last names to track. If they are all in one Names table, as suggested, I can only hold one nameType in each record. Can someone please suggest another way to solve my problem? TIA |
Thread Tools | |
Display Modes | |
|
|