If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Just starting to design a form (or is it a query?)
Hi, I'm just starting in Access and I have changed two existing Excel
spreadsheets into Access tables. One table has fields of ID#, assessment amount, billing method, and comment. The other table has fields of ID# (the same one), type of property, and different comment. I have established the relationship between the ID#'s on the two tables. What I'm trying to accomplish is: A user would open a form (query?), type in an ID#, and all of the pertinent data for that ID# would come up in an easily readable format, preferably not a table, but some other design. How do I get started with this? Thanks, Dino |
#2
|
|||
|
|||
Just starting to design a form (or is it a query?)
What is the relationship between the two tables? What I mean is one table
should have only one occurance and the other should have 0 to many occurances. This is called (clever name) a One to Many relationship. If there are multiple occurances of the same ID in, then your tables are not normalized, and it will be almost impossible to match them up properly. Now, assuming this is the case, what you need is a form with a sub form. the form should be based on the table that is the One side and the sub form should be based on the table that is the Many side. You can use the sub form wizard to help with this, but basically, you link the form and subform using the common ID number. So each time you move to a new record in the form, the sub form will display the matching records from the Many side table in the sub form. Hopefully, this will give you a start. Post back if you have more questions on getting this done. "Dino" wrote: Hi, I'm just starting in Access and I have changed two existing Excel spreadsheets into Access tables. One table has fields of ID#, assessment amount, billing method, and comment. The other table has fields of ID# (the same one), type of property, and different comment. I have established the relationship between the ID#'s on the two tables. What I'm trying to accomplish is: A user would open a form (query?), type in an ID#, and all of the pertinent data for that ID# would come up in an easily readable format, preferably not a table, but some other design. How do I get started with this? Thanks, Dino |
#3
|
|||
|
|||
Just starting to design a form (or is it a query?)
Hmmm....since I'm a rank beginner at Access, I'm not sure what you mean by
one to many. In the "Assessment" table, there are about 100 ID#'s (no duplicates) with applicable information. The second table just contains additional information about some of the same ID#'s from the other table, maybe about 10 of them. So not all of the ID#'s are represented in the second table. In the form I just want it to display the additional information if applicable to the ID# typed in. I'm just using these two simple tables as a start to try and get a form together, before I start bringing over more info from Excel. Hopefully this clarifies the issue? "Klatuu" wrote: What is the relationship between the two tables? What I mean is one table should have only one occurance and the other should have 0 to many occurances. This is called (clever name) a One to Many relationship. If there are multiple occurances of the same ID in, then your tables are not normalized, and it will be almost impossible to match them up properly. Now, assuming this is the case, what you need is a form with a sub form. the form should be based on the table that is the One side and the sub form should be based on the table that is the Many side. You can use the sub form wizard to help with this, but basically, you link the form and subform using the common ID number. So each time you move to a new record in the form, the sub form will display the matching records from the Many side table in the sub form. Hopefully, this will give you a start. Post back if you have more questions on getting this done. "Dino" wrote: Hi, I'm just starting in Access and I have changed two existing Excel spreadsheets into Access tables. One table has fields of ID#, assessment amount, billing method, and comment. The other table has fields of ID# (the same one), type of property, and different comment. I have established the relationship between the ID#'s on the two tables. What I'm trying to accomplish is: A user would open a form (query?), type in an ID#, and all of the pertinent data for that ID# would come up in an easily readable format, preferably not a table, but some other design. How do I get started with this? Thanks, Dino |
#4
|
|||
|
|||
Just starting to design a form (or is it a query?)
One to Many is a term to define the relationship between tables. In your
case, the Assessment table is the one side where you have ID's with no duplicates. That is as it should be. The second table is the Many side where there can be duplicates. It is also acceptable for there to No records that match an ID in the Assessment table. There should not, however, be any records in the second table that have IDs that are not in the Assessment table. Records like these are known as orphans. Aha! here is a clue to why they call it a Parent - Child relationship. One parent can have 0 or many children. A child without a parent is an orphan. You say that there may be records in Assessments that have to matching ID in the second table. That is okay, but, if it is the case that there will only be one matching ID in the second table, then maybe the two tables should be combined into one. With all that being said, my original concept of a form/subform way of viewing and editing the data is the usual way to do this. "Dino" wrote: Hmmm....since I'm a rank beginner at Access, I'm not sure what you mean by one to many. In the "Assessment" table, there are about 100 ID#'s (no duplicates) with applicable information. The second table just contains additional information about some of the same ID#'s from the other table, maybe about 10 of them. So not all of the ID#'s are represented in the second table. In the form I just want it to display the additional information if applicable to the ID# typed in. I'm just using these two simple tables as a start to try and get a form together, before I start bringing over more info from Excel. Hopefully this clarifies the issue? "Klatuu" wrote: What is the relationship between the two tables? What I mean is one table should have only one occurance and the other should have 0 to many occurances. This is called (clever name) a One to Many relationship. If there are multiple occurances of the same ID in, then your tables are not normalized, and it will be almost impossible to match them up properly. Now, assuming this is the case, what you need is a form with a sub form. the form should be based on the table that is the One side and the sub form should be based on the table that is the Many side. You can use the sub form wizard to help with this, but basically, you link the form and subform using the common ID number. So each time you move to a new record in the form, the sub form will display the matching records from the Many side table in the sub form. Hopefully, this will give you a start. Post back if you have more questions on getting this done. "Dino" wrote: Hi, I'm just starting in Access and I have changed two existing Excel spreadsheets into Access tables. One table has fields of ID#, assessment amount, billing method, and comment. The other table has fields of ID# (the same one), type of property, and different comment. I have established the relationship between the ID#'s on the two tables. What I'm trying to accomplish is: A user would open a form (query?), type in an ID#, and all of the pertinent data for that ID# would come up in an easily readable format, preferably not a table, but some other design. How do I get started with this? Thanks, Dino |
#5
|
|||
|
|||
Just starting to design a form (or is it a query?)
OK great! I've been working on it this morning, and I now have a query that
displays the correct information, but in an ugly table format. Are you saying that I don't use a query at all? Or use a form/subform to display the query results in a format that I like? I'm a newbie at this, let me go experiment with forms in Access. "Klatuu" wrote: One to Many is a term to define the relationship between tables. In your case, the Assessment table is the one side where you have ID's with no duplicates. That is as it should be. The second table is the Many side where there can be duplicates. It is also acceptable for there to No records that match an ID in the Assessment table. There should not, however, be any records in the second table that have IDs that are not in the Assessment table. Records like these are known as orphans. Aha! here is a clue to why they call it a Parent - Child relationship. One parent can have 0 or many children. A child without a parent is an orphan. You say that there may be records in Assessments that have to matching ID in the second table. That is okay, but, if it is the case that there will only be one matching ID in the second table, then maybe the two tables should be combined into one. With all that being said, my original concept of a form/subform way of viewing and editing the data is the usual way to do this. "Dino" wrote: Hmmm....since I'm a rank beginner at Access, I'm not sure what you mean by one to many. In the "Assessment" table, there are about 100 ID#'s (no duplicates) with applicable information. The second table just contains additional information about some of the same ID#'s from the other table, maybe about 10 of them. So not all of the ID#'s are represented in the second table. In the form I just want it to display the additional information if applicable to the ID# typed in. I'm just using these two simple tables as a start to try and get a form together, before I start bringing over more info from Excel. Hopefully this clarifies the issue? "Klatuu" wrote: What is the relationship between the two tables? What I mean is one table should have only one occurance and the other should have 0 to many occurances. This is called (clever name) a One to Many relationship. If there are multiple occurances of the same ID in, then your tables are not normalized, and it will be almost impossible to match them up properly. Now, assuming this is the case, what you need is a form with a sub form. the form should be based on the table that is the One side and the sub form should be based on the table that is the Many side. You can use the sub form wizard to help with this, but basically, you link the form and subform using the common ID number. So each time you move to a new record in the form, the sub form will display the matching records from the Many side table in the sub form. Hopefully, this will give you a start. Post back if you have more questions on getting this done. "Dino" wrote: Hi, I'm just starting in Access and I have changed two existing Excel spreadsheets into Access tables. One table has fields of ID#, assessment amount, billing method, and comment. The other table has fields of ID# (the same one), type of property, and different comment. I have established the relationship between the ID#'s on the two tables. What I'm trying to accomplish is: A user would open a form (query?), type in an ID#, and all of the pertinent data for that ID# would come up in an easily readable format, preferably not a table, but some other design. How do I get started with this? Thanks, Dino |
#6
|
|||
|
|||
Just starting to design a form (or is it a query?)
A Query is a view of your data. A Form is used to display the data and allow
users to manipulate that data. A query can be the record source for a form. A table can also be a record source for a form. "Dino" wrote: OK great! I've been working on it this morning, and I now have a query that displays the correct information, but in an ugly table format. Are you saying that I don't use a query at all? Or use a form/subform to display the query results in a format that I like? I'm a newbie at this, let me go experiment with forms in Access. "Klatuu" wrote: One to Many is a term to define the relationship between tables. In your case, the Assessment table is the one side where you have ID's with no duplicates. That is as it should be. The second table is the Many side where there can be duplicates. It is also acceptable for there to No records that match an ID in the Assessment table. There should not, however, be any records in the second table that have IDs that are not in the Assessment table. Records like these are known as orphans. Aha! here is a clue to why they call it a Parent - Child relationship. One parent can have 0 or many children. A child without a parent is an orphan. You say that there may be records in Assessments that have to matching ID in the second table. That is okay, but, if it is the case that there will only be one matching ID in the second table, then maybe the two tables should be combined into one. With all that being said, my original concept of a form/subform way of viewing and editing the data is the usual way to do this. "Dino" wrote: Hmmm....since I'm a rank beginner at Access, I'm not sure what you mean by one to many. In the "Assessment" table, there are about 100 ID#'s (no duplicates) with applicable information. The second table just contains additional information about some of the same ID#'s from the other table, maybe about 10 of them. So not all of the ID#'s are represented in the second table. In the form I just want it to display the additional information if applicable to the ID# typed in. I'm just using these two simple tables as a start to try and get a form together, before I start bringing over more info from Excel. Hopefully this clarifies the issue? "Klatuu" wrote: What is the relationship between the two tables? What I mean is one table should have only one occurance and the other should have 0 to many occurances. This is called (clever name) a One to Many relationship. If there are multiple occurances of the same ID in, then your tables are not normalized, and it will be almost impossible to match them up properly. Now, assuming this is the case, what you need is a form with a sub form. the form should be based on the table that is the One side and the sub form should be based on the table that is the Many side. You can use the sub form wizard to help with this, but basically, you link the form and subform using the common ID number. So each time you move to a new record in the form, the sub form will display the matching records from the Many side table in the sub form. Hopefully, this will give you a start. Post back if you have more questions on getting this done. "Dino" wrote: Hi, I'm just starting in Access and I have changed two existing Excel spreadsheets into Access tables. One table has fields of ID#, assessment amount, billing method, and comment. The other table has fields of ID# (the same one), type of property, and different comment. I have established the relationship between the ID#'s on the two tables. What I'm trying to accomplish is: A user would open a form (query?), type in an ID#, and all of the pertinent data for that ID# would come up in an easily readable format, preferably not a table, but some other design. How do I get started with this? Thanks, Dino |
#7
|
|||
|
|||
Just starting to design a form (or is it a query?)
Thanks! I played around with form design and got a format that looks good
based on the simple sample query that I used. I guess now that I know how to get started, I just need to practice with this, and experiment. I did have one question about the form when it displays the data from the query. I have the ID# at the top of the form, and I understand that I can either allow manipulation of the number or lock it. After viewing the requested data, I want to allow users to enter a new number in that field, and have the form display the new info rather than having to close the form and open a new form based on the next ID#. Thanks for your help. "Klatuu" wrote: A Query is a view of your data. A Form is used to display the data and allow users to manipulate that data. A query can be the record source for a form. A table can also be a record source for a form. "Dino" wrote: OK great! I've been working on it this morning, and I now have a query that displays the correct information, but in an ugly table format. Are you saying that I don't use a query at all? Or use a form/subform to display the query results in a format that I like? I'm a newbie at this, let me go experiment with forms in Access. "Klatuu" wrote: One to Many is a term to define the relationship between tables. In your case, the Assessment table is the one side where you have ID's with no duplicates. That is as it should be. The second table is the Many side where there can be duplicates. It is also acceptable for there to No records that match an ID in the Assessment table. There should not, however, be any records in the second table that have IDs that are not in the Assessment table. Records like these are known as orphans. Aha! here is a clue to why they call it a Parent - Child relationship. One parent can have 0 or many children. A child without a parent is an orphan. You say that there may be records in Assessments that have to matching ID in the second table. That is okay, but, if it is the case that there will only be one matching ID in the second table, then maybe the two tables should be combined into one. With all that being said, my original concept of a form/subform way of viewing and editing the data is the usual way to do this. "Dino" wrote: Hmmm....since I'm a rank beginner at Access, I'm not sure what you mean by one to many. In the "Assessment" table, there are about 100 ID#'s (no duplicates) with applicable information. The second table just contains additional information about some of the same ID#'s from the other table, maybe about 10 of them. So not all of the ID#'s are represented in the second table. In the form I just want it to display the additional information if applicable to the ID# typed in. I'm just using these two simple tables as a start to try and get a form together, before I start bringing over more info from Excel. Hopefully this clarifies the issue? "Klatuu" wrote: What is the relationship between the two tables? What I mean is one table should have only one occurance and the other should have 0 to many occurances. This is called (clever name) a One to Many relationship. If there are multiple occurances of the same ID in, then your tables are not normalized, and it will be almost impossible to match them up properly. Now, assuming this is the case, what you need is a form with a sub form. the form should be based on the table that is the One side and the sub form should be based on the table that is the Many side. You can use the sub form wizard to help with this, but basically, you link the form and subform using the common ID number. So each time you move to a new record in the form, the sub form will display the matching records from the Many side table in the sub form. Hopefully, this will give you a start. Post back if you have more questions on getting this done. "Dino" wrote: Hi, I'm just starting in Access and I have changed two existing Excel spreadsheets into Access tables. One table has fields of ID#, assessment amount, billing method, and comment. The other table has fields of ID# (the same one), type of property, and different comment. I have established the relationship between the ID#'s on the two tables. What I'm trying to accomplish is: A user would open a form (query?), type in an ID#, and all of the pertinent data for that ID# would come up in an easily readable format, preferably not a table, but some other design. How do I get started with this? Thanks, Dino |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
using a form with combo box to input criteria | Christina | Using Forms | 16 | February 3rd, 2006 08:47 PM |
Date range on reports | Mark_Milly | Setting Up & Running Reports | 10 | January 19th, 2006 03:49 PM |
Here's a shocker | Mike Labosh | General Discussion | 2 | October 26th, 2004 05:04 PM |
Display Parameter from Form on Report | sara | Setting Up & Running Reports | 10 | July 19th, 2004 04:54 PM |
surely a form with a ListBox can be used in a query? | 1.156 | Running & Setting Up Queries | 14 | June 2nd, 2004 04:54 PM |