I am new to Base so please be kind. I am trying to build a form that will create a table (I’ll call it work order). The query is based on information from other table with additional information added to the “work Order table”. When I run the query, with just the one table, I’ll call it “Inventory List” I get the expected results as the query asks me to enter a unit number. However when I add another table, I’ll call it “Major Component” to the query, which has information I will pick from a drop down list The results are empty. I figure this is something simple but I lack the terminology to search the web for an answer. I appreciate any and all help with this.
Actually you used one of the important terms: JOIN is the keyword to mix tables.
You may get more information by feeding
“SQL JOIN” to your favored search engine.
.
To save this result afterwards in another table you may prepend your SQL-query with
INSERT INTO table
where table is the name of an existing table in your database. But note: This is no longer a query but a statement/command.
.
For a quick peek how this all may look check this unrelated thread Sum a Field from Table 1 Based on Matching Criteria from Table 2
Your work order is a record (row) in a table storing work orders. Base runs an INSERT statement when you add a new record (one at a time). It runs an UPDATE statement when you store a modified record. It runs a DELETE statement when you delete one or more records.
A form never creates any table nor column. The amount of tables and columns is fixed when using the database. You add, edit or delete rows when working with the database.
Hello,
.
I may be reading your question incorrectly. Are you joining two tables so part may appear in a list box? Note that there is no “drop down”, just list and combo boxes and there is a difference.
.
If so, there is no need for a query or joining the tables. The list box list can be created form the separate table.
.
Probably would help if you provided more clarity.
Let me try to explain a little better. I have 2 tables. One has several fields and several rows of records. The other table is empty. I need to add a single record (Three fields) to the empty table and then add more information to that table. I need to do this in such a way that the records will be searchable later. I hope that is clear. Thanks for your help
Sorry, I forgot one more issue. In order to get the information from the populated table I need to have an “:Enter_Unit_Number” statement to find the record I need
OK. I have 2 data bases. One has several records. The other one is empty. I need to add three fields from the populated database to the empty database. I then need to add more data. Also I need to use a “:Enter_Unit_Number” statement to find the information in the populated database.
It appears you are just copying selective data from one table to another. A confusing point is that are these tables in the same database or different databases?
.
This may be an SQL Update statement but not sure why you are using a parameter. If you know what to input for this parameter then it appears to be unnecessary.
.
Also you need to specify what database you are using. Base is not a database - just a front end to a database. Of course based upon your last comment this could be two different databases. Please specify.
1 Like
Yes the tables are in the same database. I use the parameter to find a particular record. I this case it is a fleet of ATV’s. I need Unit number (the parameter) a description, and the VIN. Once I have this loaded I add other data from dropdown lists, such as complaint, cause and correction. All of this data is kept for historical repairs and helps with future issue. The data base is ODB
Ratslinger
July 24
It appears you are just copying selective data from one table to another. A confusing point is that are these tables in the same database or different databases?
.
This may be an SQL Update statement but not sure why you are using a parameter. If you know what to input for this parameter then it appears to be unnecessary.
.
Also you need to specify what database you are using. Base is not a database - just a front end to a database. Of course based upon your last comment this could be two different databases. Please specify.
You rarely ever copy any data from one table to another. I think you want to use references.
If you create invoices in table “Invoices” referencing clients in table “clients”, all you need is to add the ID number of the client table which belongs to the right client. This way you never store the name, address, email, phone etc. of a client in anywhere other than the client’s row of the client table.
Maybe I’m trying to do something unusual? I have one table that has information stored in it. The other table is empty. I need to add some data from the populated table into the empty table and then add additional information, that will be typed in, as in a note, or added vis list boxes.
My issues is when I us a query to make the table it won’t let me use list boxes. I can make the list box but they will not be active
The join is LEFT
When I create the form from a query I am able to enter the Unit Number and the VIN auto populates. However, when I add a list box it will not show information. I am stumped and frustrated. Can you help
Villeroy
July 24
You rarely ever copy any data from one table to another. I think you want to use references.
If you create invoices in table “Invoices” referencing clients in table “clients”, all you need is to add the ID number of the client table which belongs to the right client. This way you never store the name, address, email, phone etc. of a client in anywhere other than the client’s row of the client table.
Database normalization description - Office
Describe the method to normalize the database and gives several alternatives to normalize forms. You need to master the database principles to understand them or you can follow the steps listed in the article.
Database normalization
Database normalization is the process of structuring a relational database in accordance with a series of so-called normal forms in order to reduce data redundancy and improve data integrity. It was first proposed by Edgar F. Codd as part of his relational model. Normalization entails organizing the columns (attributes) and tables (relations) of a database to ensure that their dependencies are properly enforced by database integrity constraints. It is accomplished by applying some formal rul…
List boxes belong to the user interface which consists of forms and reports. Forms can have list boxes as shown in my example document for one-to-many relations and many-to-many relations.
Hello @pnorsworthy
I know of no such database. There is a connector type - ODBC. It is a method to talk to the database being used. So still do not know what database you are using.
.
Seems more likely your design is incorrect. From the little information presented, I see a main table with the primary data of the ATV - Unit, description, VIN and possibly a bit more like purchase date.
.
Then a second table containing this “historical data”. This second table is then linked back to the main table using the key from the main table. The form would internally contain a form (ATV main) and a sub form (historical data). All automatic when set properly. This is a very common practice and not unusual at all.
.
Sample → ATVs.odb (13.2 KB)
Actually the main table would be the “WorkOrder” table. I want to use the information in the ATV Fleet list to be added, when picked, to the WorkOder table. That way when I open the form I select a “Unit Number” from the ATV Fleet list and then type in the other fields in the WorkOder. This will then give me a history of repairs to each ATV. I just can’t seem to get the ATV Fleet and the WorkOrder tables to join so I can do this.
Can you suggest anywhere I can find some training on this so I can learn how to do this?
Thanks
Ratslinger
July 24
Hello @pnorsworthy
pnorsworthy:
The data base is ODB
I know of no such database. There is a connector type - ODBC. It is a method to talk to the database being used. So still do not know what database you are using.
.
Maybe I’m trying to do something unusual?
Seems more likely your design is incorrect. From the little information presented, I see a main table with the primary data of the ATV - Unit, description, VIN and possibly a bit more like purchase date.
.
Then a second table containing this “historical data”. This second table is then linked back to the main table using the key from the main table. The form would internally contain a form (ATV main) and a sub form (historical data). All automatic when set properly. This is a very common practice and not unusual at all.
That is just what is presented. Fleet is main. History is work order.
As this thead begins “I’m new in base” i guess he thinks the extension.odb actually reveals the database. So I assume here works with “standard” HSQL-embedded.
.
To @pnorsworthy
Base can connect to different types of databases. I’m using as a example MariaDB, Sqlite, dbase, csv/tsv-Text-Tables and sometimes HSQL-embedded.
.
With HSQL-embedded the .odb holds all queries, forms and the actual data of the tables. For other types the .odb has forms and queries, but the actual data are “somewhere else” and the .odb contains only some kind of link (the connection string) where to find the tables.
1 Like
Thanks. Did not even relate to that.
.
@pnorsworthy
Is it possible you are looking the create this Fleet table I have noted based upon your WorkOrder table and then use it as I have shown?
No. The exact opposite. I am trying to put data into a table for my WorkOrder. Some of that data, Unit Number, Description, and VIN, come from the ATV Fleet table. This ATV Fleet table is built and populated with data. Nothing will be added or removed from the ATV table. The WorkOder, however will be a continuously growing data table, each time a work order is created. I just want to populate the Unit Number, Description, and VIN, so I don’t have to type it in every time. I hope that make it clearer.
Ratslinger
July 24
Wanderer:
the extension.odb actually reveals the database.
Thanks. Did not even relate to that.
.
@pnorsworthy
Is it possible you are looking the create this Fleet table I have noted based upon your WorkOrder table and then use it as I have shown?
Sorry but do not know how else to explain at this point. On my sample you select a Fleet item (main table) which then brings up all WorkOrders for that item (sub form).
.
Now you can go to the sub form and enter new work orders for that Fleet item - as many as you want. They are all linked back to the Fleet item.
.
What you describe is duplication of data and not utilizing database relations.
.
Edit:
I would think possibly new or replacement vehicles may be added. But regardless, the Fleet form can always be set to read only - no new, changes or deleted records.
.
And you state:
.
That does make the The ATV Fleet table the main table. Work orders are tied to that.
.
And that is what relational tables (as in my sample) give you - both not typing it in AND not needing to store duplicate information.