Creating a new table based on an existing table

[zimmer9]

How would I go about creating and populating a new Table B based on existing Table A. Both tables have 3 fields. The first 2 fields would have the same values in both tables. However, the 3rd field in Table B is derived from the 3rd field in Table A. For example:

Table A has the following State fields:

State Amount Stocks

CA 100.00 3
FL 200.00 5


Table B would have the following State fields:

State, Amount, Stock Date

CA 100.00 6/30/2002
FL 200.00 6/30/2000

The fields State and Amount would have the same values in both tables. However, the Stock field in Table A represents a number of years value. To derive the Stock Date for corresponding records in Table B, I start with a fixed date of 6/30/2005 (6/30 of the Current Year) and subtract the value of the Stock field in Table A for the same state record.

For example, the first record in Table A is for California and has a Stock value of 3. To derive the Stock Date field value to place in the Table B for the California record, I would take the fixed date of 6/30/2005 minus the Stock value of 3 which yields a Stock Date of 6/30/2002.

 

[willir]

There are several ways to approach this.

One way is to create the second table. Then use an Append Query using the Query Builder to update State and Amount.

To create the Append query, first use the "Create query in design view". Add your first table. Then change the query type to an Append quer using the menu, "Query" -> "Append". When prompt with "Append to", select the target table. Then match the Field / table from the source table to the "Append to" field in second table.

Having said all this, your table design may improved a bit.
- You have duplicate data - State and Amount
- You have no apparent primary key

You may wish to review the following on database design...
Fundamentals of Relational Database Design by Paul Litwin
Download document
Read on-line (HTML)


Micro$oft's answer...
283878 - Description of the database normalization basics
304467 - ACC2000 Defining Relationships Between Tables in a Microsoft Access Database

Richard

 

[PHV]

A starting point:
UPDATE TableB B INNER JOIN TableA A
ON B.State=A.State AND B.Amount=A.Amount
SET B.[Stock Date] = DateSerial(2005-A.Stocks,6,30)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244