 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / New Users / November 2006Tables
How can I create multiple tables by using the data from one master table? I need to pull certain items from a master table and use them in other tables but I can only do this by copying the information into another table. This seems to go against the concept of centralized database. Thanks for your help in advance >How can I create multiple tables by using the data from one master table? I >need to pull certain items from a master table and use them in other tables >but I can only do this by copying the information into another table. This >seems to go against the concept of centralized database. It does, and ordinarily you would not WANT to copy data redundantly from a master table into other tables. Typically you can create Select Queries - ususally parameter queries, using either a prompt such as [Enter search term:] or a form reference such as [Forms]![NameOfAForm]![NameOfAControl] - as criteria. You can base a Form, a Report, an Export, etc., on such a query; there is no benefit and many disadvantages to actually creating a new table. If you wish to use data from one table in conjunction with data in another table, you'ld simply create a Query joining the two tables on an appropriate linking field. What are you trying to accomplish, and why do you feel that you need to "pull certain items... and use them in other tables"??? John W. Vinson[MVP] I have information that I pull from an ODBC connection. I have been asked to collect additional data to add to the ODBC table that I have in the database. How can I build a form that adds data to a table that is already built? > >How can I create multiple tables by using the data from one master table? I > >need to pull certain items from a master table and use them in other tables [quoted text clipped - 18 lines] > > John W. Vinson[MVP] >I have information that I pull from an ODBC connection. I have been asked to >collect additional data to add to the ODBC table that I have in the database. > How can I build a form that adds data to a table that is already built? Do you have update privileges on the linked ODBC table? Do you want to add additional *RECORDS* to the existing table - which should be easy? or do you want to add new *FIELDS* to the table - which would require that you change the design of the table in your datasource? If you can't change the structure of your data source table, but need to add additional fields, you may want to create a local Table with the same Primary Key as your linked table. This would have the additional fields, and you could use a Query linking the two tables (it will be a one-to-one relationship) to display the data; you will probably need to use a Form based on the ODBC table with a Subform based on your table to enter data. John W. Vinson[MVP] Thanks for your help on this by the way.. If I am using 2 tables in a query, each having a field called customer number and both tables hold the same contents but one has more data, how can i get the other data fields to stay with the table holding less information? Example: one table may have 100 records with 5 customers and the other has 300 records with the same 5 customers. How can i pull the customer's sales information for the 100 count table without getting the extra data from the larger table if they are linked in a query? > >I have information that I pull from an ODBC connection. I have been asked to > >collect additional data to add to the ODBC table that I have in the database. [quoted text clipped - 14 lines] > > John W. Vinson[MVP] >Thanks for your help on this by the way.. >If I am using 2 tables in a query, each having a field called customer >number and both tables hold the same contents but one has more data, how can >i get the other data fields to stay with the table holding less information? That situation should NEVER ARISE in a properly designed database. A normalized set of tables will store information *once* and once only; having the same fields in two tables is a recipe for trouble. >Example: one table may have 100 records with 5 customers and the other has >300 records with the same 5 customers. How can i pull the customer's sales >information for the 100 count table without getting the extra data from the >larger table if they are linked in a query? You've lost me completely. If you want the information from the small table, open the small table. Are these two tables with the same structure? or does the 100 record table contain *different types* of information, fields which do not exist in the 300 record table? John W. Vinson[MVP] I have a table that displays various items of customer information (name, number, address). Each customer is one line item in the table with no duplicates. I have a second table that displays how often we call on customers in a given week with creates a situation where the customer info is listed multiple times in the table. Ex. Cust A is seen on Monday and Wed resulting in 2 line items for Cust A. How can i apply a data record like "type of account" from the non-duplicate list to the list with multiple enteries for the same account based the day of week. I want Cust A, which is listed 3 times on the second table to display 'restaurant' for each of the 3 line items using the first table > >Thanks for your help on this by the way.. > >If I am using 2 tables in a query, each having a field called customer [quoted text clipped - 18 lines] > > John W. Vinson[MVP] >I have a table that displays various items of customer information (name, >number, address). Each customer is one line item in the table with no >duplicates. What is this table's Primary Key (or does it have one?) Is there a unique CustomerID, such as an Autonumber? >I have a second table that displays how often we call on customers in a >given week with creates a situation where the customer info is listed >multiple times in the table. Ex. Cust A is seen on Monday and Wed resulting >in 2 line items for Cust A. That's incorrect design. Your calls table should NOT contain any customer information, other than a CustomerID (a Long Integer if the first table's primary key is an Autonumber) as a link to the customer table. >How can i apply a data record like "type of >account" from the non-duplicate list to the list with multiple enteries for >the same account based the day of week. I want Cust A, which is listed 3 >times on the second table to display 'restaurant' for each of the 3 line >items using the first table Create a Query joining the Customers table to the Calls table by the CustomerID (or appropriate linking field). You'll then have all of the information from both tables available for display. John W. Vinson[MVP] |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.