Adding Data from one table to another

[odie100]

I am new to open office base. I have a very large database and I need to insert data from one table to another to keep from typing same information over and over. This is the tables I'm working with.

Table 1 (Information from this table to Table 2)
Manufacture (List Box)
Store (List Box)
Description
Brand Name
Category (List Box)
Model Number
Color (List Box)
Weight
Item Number
Temperature
Dimension
Internet Number
Definition (List Box)
Standard
Description (List box)
Cost
Tax
Shipping
Volts
Amps
Watts
BTU's

Table 2 Need to input into this table
Building Name59 (List Box)
Floor Name59 (List Box)
Beam Number59
Category59 (List Box)
Length59
Beam Height59
Flange Width59
Flange Thickness59
Weight59
Description59 (List Box) Need to select this, then insert (Store,Standard, Cost,Tax,Shipping) into(Store59,Standard59, Cost59,Tax59,Shipping59)
Standard59
Cost59
Tax59
Shipping59
Qty60
Qty61
Length62
Qty62
Category63 (List Box)
Description63 (List Box) Need to select this, then insert (Store,Standard, Cost,Tax,Shipping) into(Store63,Standard63, Cost63,Tax63,Shipping63)
Standard63
Cost63
tax63
shipping63
Category64 (List Box)
Description64 (List Box) Need to select this, then insert (Store,Standard, Cost,Tax,Shipping) into(Store64,Standard64, Cost64,Tax64,Shipping64)
Standard64
Cost64
tax64
Shipping64
Category65 (List Box)
Description65 (List Box) Need to select this, then insert (Store,Standard, Cost,Tax,Shipping) into(Store65,Standard65, Cost65,Tax65,Shipping65)
Standard65
Cost65
tax65
Shipping65

I May need to add button to do these? Or maybe a macro (Never Done that) If you can help would be nice, been working on these for a while. This last bit of information before Program is working. I Can attach What I have If necessary

[UnklDonald418]

I suspect you could use subforms to display the data from the database table underlying what you refer to as Table1 on the form displaying Table2. It would be best if you uploaded a sample so we can see what you actually have.
[Forum] How to attach a document here

[odie100]

Screen Shot of Table 1

[odie100]

I Need to Select description from table 1 and insert it into table 2, along with Category, Standard, Store Name, Cost, Tax, Shipping, This way i do not have to type them each time.

[odie100]

This Is a better view of table 2

[UnklDonald418]

The screen shots help a little but an odb file would be better so we could know the structure details of the database tables and forms.
Things can get a little confusing because the words "form" and "table" can have multiple meanings in the context of a database.
In many cases storing duplicate data in 2 database tables (as opposed to table controls on a form) can cause problems. Since the fields "Category", "Standard", "Store Name", "Cost", "Tax" and "Shipping" appear in Table1 they probably should not be duplicated in Table2.
There are some other potential issues with what looks to be the database table named Table2 displayed on the form document in your most recent screen shot. Will there always be values in Description59, Description63, Description64 and Description65? Could there be more?

From your most recent screen shot "Descption59" is a listbox. Listbox queries need a display field, in this case the "Description" field from Table1, and a bound field which should be the Primary Key value also from Table1. It is almost always best for a Primary Key to be an integer value automatically assigned by the database engine. That means the "Descption59" field in Table2 would be what is referred to as a Foreign Key and should hold an integer.
Below is how I would approach getting data from "Category", "Standard", "Store Name", "Cost", "Tax" and "Shipping" from Table1 to display on the form document based on Table2.
Open the form document that you refer to as "table 2" and using the Form Navigator add a SubForm59 that uses Table1 as its data source, Then the integer value stored in Descption59 would appear in the Link Master fields and the Primary Key field from SubForm59 in the Link Slave fields.
Move the controls Category59, Standard59, Store Name59, Cost59, Tax59 and Shipping59 onto SubForm59. Each of those controls should get their data from the matching table fields in Table 1.
With those changes, when you use the form document to look at a record, the fields Category59, Standard59, Store Name59, Cost59, Tax59 and Shipping59 should display the values from Table1 associated with the Primary Key value .
In the same manner you could add SubForm63, SubForm64 and SubForm65.

[odie100]

https://mega.nz/#!mvwRlIiY!vASPHccajQPC ... 0upEjI0m3U

This is the complete file I have to this point. I feel I understand what you are explaining to me. How ever I feel I do not need a 1 to 1 relationship within my tables. I also understand that I will/do have the ability to see within the description bar of each form/table. I will be creating a table past this point utilizing SQL to complete the math.

The point of this is to simplify the complexity of materials needed to complete a house and do the math involved in a Room by Room pricing antilogarithm.

[UnklDonald418]

After looking over your database I have a number of concerns. When I look in the Tables area of your database, it looks like you took a stab at normalizing your database tables. For instance I see a table stores with all the pertinent data about each store and the table has an auto-incrementing Primary Key so everything there looks good. But when I look in the products table at the "store" field I see a text field with the store name rather than an integer referencing Primary Key value in the stores table. Many of your tables read like spreadsheet pages rather than database tables. That results in loss of efficiency and opens the door for inconsistencies in the saved data.
For now there is one question that will dictate which way go from here. Lets say you have completed filling out the form document Fabricated Beam and have saved a record that includes a quantity of 2 X 10 X 10 lumber. Then at some later date the price of a 2 X 10 X 10 changes, so you enter the new price in the products table. If you reopen the form document Fabricated Beam and look at the record you previously entered, do you want to see the new price or the old price?
If you want to see the new price then linking the tables as I described earlier is the way to go.
If you want to keep the old price then it might be better to fill all those fields by running an SQL UPDATE command.

[odie100]

Thank you for your help, your advice is helping me see some errors in my set up. And maybe a better way to accomplish what I am trying to do.

This Is what I would like to accomplish with this database
I would like to create a product table with all the information I will need to fill in other table so I don't have to type same information over and over , plus this way information is the same each time I use it.
I created a drop-down tables for information that is the same all the time. I created a relationship Table so all information is shared. I hope this is correct?

This is the table I need to pick-up information from.
(Product Table)
Store (Drop-down Box), Category (Drop-down Box), Weight, Standard, Description, Cost, Tax,
Shipping Cost, Volts, Amps, Watts, BTU's. All of these are entry data on form, except for drop-down tables.

This is the table I need to insert data into, I will use this table to do my calculation and reports from.
(Fabricated Beams)
Store59, Category59, Weight59, Standard59, Description59 (This a drop-down Box),(This is where I select the product from the product table to insert into fabricated beam table), Cost59, Tax59, Shipping Cost59, Volts59, Amps59, Watts59, BTU's59. I will leave blank data-fields for data not use in Fabricated Beams table. This way all data inputs will be the same . I would like to do this in SQL if possible. I will use this format to fill in data for: Nail, Sealer, Glue on same table
At some point if this works I will add approx 10 more tables using this format.
As for primary keys I will look at that at a later time. I will have to work with it to see how to fix it.
Thanks for your time and energy in this process

[eremmel]

@Odie100. Take one step back and answer carefully the questions of UnklDonald418. Most volunteers (giving answers here) have for many years (semi) professional experience with Database applications. For now it looks like you take the route of an anti-pattern that you like to avoid.

@UnklDonald418, an alternative for the copy approach might be to introduce a Product Price table between Product and Fabricated Beams that contains the temporal data of a Product. Any list box referring to a Product refers in fact to the most recent Product Price entry.

[UnklDonald418]

A few observations
I guess the table named Relational Table is a nice exercise in using the Relation Design dialog, but it is of no practical use.
All the listboxes I looked at use an unusual format for the listbox queries. For instance you have a listbox to select a store with the query

Code: Select all

SELECT "StoreName", "StoreName" FROM "Store"

Normally that query would be

Code: Select all

SELECT "StoreName", "ID" FROM "Store" ORDER BY "StoreName"

Your version saves the actual store name while the normal usage saves the integer value from "Store"."ID". Your version can result in data inconsistencies. For instance if there is mistake in the string saved in "StoreName" or the store changes names, you must search all the records in your database to make the corrections. Using the "ID" value, a change to the text in "Store"."StoreName" would be the only change required.
Also note that my example has an ORDER BY clause. That results in an alphabetized list, so the user can begin typing and cursor will jump to the first matching item in the list.
The HSQL database engine doesn't do automatic garbage collection. When you have made a number of deletions it can be helpful to select Tools>SQL and execute

Code: Select all

SHUTDOWN COMPACT;

Close the database to complete the action.
When I did that to your House.odb file it went from 820 KB to 363 KB.