Tables

[Guest]

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

 

[John Vinson]

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]

 

[Guest]

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
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]

 

[John Vinson]

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]

 

[Guest]

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?

 

[John Vinson]

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]

 

[Guest]

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

 

[John Vinson]

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]