Copy Field Name from One Table to an Other

[access2010]

=== Question One ===
From the table = Investments01_tbl =
Could I please receive a suggestion on how to copy the data in the field
= Stock_Name = to the table = Investments01_Appendix = Stock_Name_A = if the = Symbol_Stock = are the same?

=== Question Two ===
From the table = Investments01_tbl =
Could I please receive a suggestion on how to copy the data in the field
= Symbol_Stock = to the table = Investments01_Appendix =
if the = Symbol_Stock_A = is blank based on using the same
= Investmentl_ID = and = InvestmentID_A = number.

Then I presume, that I can run the first query to populate the missing Stock Names in the = Investments01_Appendix = table.

Your assistance is appreciated
Nicole

 

[plog]

Question 1--don't.

Question 2--don't.

In a relational database data doesn't get copied hither and yon, instead you relate it. JOIN it in a query and reference the query.

I'm sure that doesn't help with the big picture issue you are facing, so please tell us what the big picture is? Why must you have 2 copies of data? What's the end goal of doing this?

 

[gemma-the-husky]

For stocks, I would probably use the stock ticker abbrevation as a PK

So in the Stocks Table you have the Stock Ticker Code and the Name, and other info.
In the Investments table, you just have the Stock Ticker Code - the name of the field in each table doesn't have to be identical, as well as purchase or sale details.

In a query you join these two tables on the two fields, and now all the details from the stocks table are available for the investment.
You could use an autonumber instead of the stock ticker code, but when you have an industry agreed standard it's one case where it's not a bad idea to use the standard. Same with US state abbreviations.

 

[access2010]

Question 1--don't.

Question 2--don't.

In a relational database data doesn't get copied hither and yon, instead you relate it. JOIN it in a query and reference the query.

I'm sure that doesn't help with the big picture issue you are facing, so please tell us what the big picture is? Why must you have 2 copies of data? What's the end goal of doing this?

Thank you for your comments, which I agree with.
We have SO VERY MANY duplicate records over the 18 years that we have been using this database, that we have decided to clean it up by creating a new table with = No Duplicates = allowed for the Stock Name.
But to do this we have to start with a clean table and this is why we are looking for suggestions on how to create the working table
= Investments01_Appendix =
I am sorry for causing confusion with our request.
Nicole

 

[access2010]

For stocks, I would probably use the stock ticker abbrevation as a PK

So in the Stocks Table you have the Stock Ticker Code and the Name, and other info.
In the Investments table, you just have the Stock Ticker Code - the name of the field in each table doesn't have to be identical, as well as purchase or sale details.

In a query you join these two tables on the two fields, and now all the details from the stocks table are available for the investment.
You could use an autonumber instead of the stock ticker code, but when you have an industry agreed standard it's one case where it's not a bad idea to use the standard. Same with US state abbreviations.

Thank you, gemma-the-husky for your suggestion. Once we have a proper working table, then we can look at changing the field names.
Nicole

 

[plog]

We have SO VERY MANY duplicate records over the 18 years that we have been using this database, that we have decided to clean it up by creating a new table with = No Duplicates = allowed for the Stock Name.

On any technical forum its best to give an explanation of the ultimate aim. Your initial post was about an issue you faced while trying a method to achieve the above aim that wasn't the best. Now, knowing the ultimate aim I can help you with an entirely new method:

1. Create a query to find all unique Name/Symbol permutations:

SELECT Investments01_tbl.Symbol_Stock, Investments01_tbl.Stock_Name
FROM Investments01_tbl
GROUP BY Investments01_tbl.Symbol_Stock, Investments01_tbl.Stock_Name;

PAste the above into a new query and name it 'sub1.

2. Create a query that will make a table to hold all the unique Stock Names:

SELECT sub1.Stock_Name, Min(sub1.Symbol_Stock) AS Stock_Symbol INTO Stocks
FROM sub1
GROUP BY sub1.Stock_Name
HAVING (((Count(sub1.Stock_Name))=1));

Paste that into a new query object and run it. It will create a table called Stocks that will hold all your unique Stock_Name values. But that query will only find unique Stock_Name values in Investments01_tbl. You must then sort through the duplicates.

3. Use the below query to generate a list of Stock_Name values with multiple Stock Symbol values:

SELECT sub1.Stock_Name, sub1.Symbol_Stock
FROM sub1 LEFT JOIN Stocks ON sub1.Stock_Name = Stocks.Stock_Name
WHERE (((Stocks.Stock_Name) Is Null))
ORDER BY sub1.Stock_Name, sub1.Symbol_Stock;

You must now manually add the stock names in that query to Stocks along with the correct stock symbol for each name.

 

[Pat Hartman]

What do you want to happen to the duplicate records? How will you decide which of the duplicates to keep? If you can quantify that, then create an append query that sorts the rows so that the one you want to keep will be first, then run the append query and append the rows to the table with the proper unique index on the stock ticker. Unique rows will be appended, duplicates will be ignored.

I still don't understand the original question. An append query doesn't require matching column names so you can append fldxyz to fldabc and that isn't a problem.

I'm also not sure why you are changing the column names in the new table at this point. That will just make this harder to integrate.

 

[access2010]

On any technical forum its best to give an explanation of the ultimate aim. Your initial post was about an issue you faced while trying a method to achieve the above aim that wasn't the best. Now, knowing the ultimate aim I can help you with an entirely new method:

1. Create a query to find all unique Name/Symbol permutations:

SELECT Investments01_tbl.Symbol_Stock, Investments01_tbl.Stock_Name
FROM Investments01_tbl
GROUP BY Investments01_tbl.Symbol_Stock, Investments01_tbl.Stock_Name;

PAste the above into a new query and name it 'sub1.

2. Create a query that will make a table to hold all the unique Stock Names:

SELECT sub1.Stock_Name, Min(sub1.Symbol_Stock) AS Stock_Symbol INTO Stocks
FROM sub1
GROUP BY sub1.Stock_Name
HAVING (((Count(sub1.Stock_Name))=1));

Paste that into a new query object and run it. It will create a table called Stocks that will hold all your unique Stock_Name values. But that query will only find unique Stock_Name values in Investments01_tbl. You must then sort through the duplicates.

3. Use the below query to generate a list of Stock_Name values with multiple Stock Symbol values:

SELECT sub1.Stock_Name, sub1.Symbol_Stock
FROM sub1 LEFT JOIN Stocks ON sub1.Stock_Name = Stocks.Stock_Name
WHERE (((Stocks.Stock_Name) Is Null))
ORDER BY sub1.Stock_Name, sub1.Symbol_Stock;

You must now manually add the stock names in that query to Stocks along with the correct stock symbol for each name.

plog, Thank you for your suggestions, which W.O.R.K.
i/we appreciate your help and now our reports are printable.​

Regards
Nicole

 

[access2010]

For stocks, I would probably use the stock ticker abbrevation as a PK

So in the Stocks Table you have the Stock Ticker Code and the Name, and other info.
In the Investments table, you just have the Stock Ticker Code - the name of the field in each table doesn't have to be identical, as well as purchase or sale details.

In a query you join these two tables on the two fields, and now all the details from the stocks table are available for the investment.
You could use an autonumber instead of the stock ticker code, but when you have an industry agreed standard it's one case where it's not a bad idea to use the standard. Same with US state abbreviations.

Thank you for your assistance and appreciate

For stocks, I would probably use the stock ticker abbrevation as a PK

So in the Stocks Table you have the Stock Ticker Code and the Name, and other info.
In the Investments table, you just have the Stock Ticker Code - the name of the field in each table doesn't have to be identical, as well as purchase or sale details.

In a query you join these two tables on the two fields, and now all the details from the stocks table are available for the investment.
You could use an autonumber instead of the stock ticker code, but when you have an industry agreed standard it's one case where it's not a bad idea to use the standard. Same with US state abbreviations.

For stocks, I would probably use the stock ticker abbrevation as a PK

So in the Stocks Table you have the Stock Ticker Code and the Name, and other info.
In the Investments table, you just have the Stock Ticker Code - the name of the field in each table doesn't have to be identical, as well as purchase or sale details.

In a query you join these two tables on the two fields, and now all the details from the stocks table are available for the investment.
You could use an autonumber instead of the stock ticker code, but when you have an industry agreed standard it's one case where it's not a bad idea to use the standard. Same with US state abbreviations.

gemma-the-husky, thank you for your assistance and plog's suggestion works.​

Nicole

 

[access2010]

What do you want to happen to the duplicate records? How will you decide which of the duplicates to keep? If you can quantify that, then create an append query that sorts the rows so that the one you want to keep will be first, then run the append query and append the rows to the table with the proper unique index on the stock ticker. Unique rows will be appended, duplicates will be ignored.

I still don't understand the original question. An append query doesn't require matching column names so you can append fldxyz to fldabc and that isn't a problem.

I'm also not sure why you are changing the column names in the new table at this point. That will just make this harder to integrate.

Pat Hartman, thank you for your assistance and plog's suggestion works.
Nicole