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.
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.
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.
@pnorsworthy
.
Went one step further. To demo the info you are looking for using my sample, added a query and then a report (simple) based upon that query:
ATVs.odb (17.7 KB)
Wow, Thanks for going to al the trouble. But I think my issues goes deeper than That. I need to enter the Unit number into the form, have it auto populate the Unit Number, VIN, and the description fields. The I will type in or pick from a kist box what goes into the rest of the fields. I have attached what I’ve been working on. it may give you a better Idea. Please note that there are some funky Forms and query’s but you will get the idea
Ratslinger
July 24
@pnorsworthy
.
Went one step further. To demo the info you are looking for using my sample, added a query and then a report (simple) based upon that query:
ATVs.odb (17.7 KB)
TCA Parts Inventory.odb (111 KB)
No, a quick look at your sample confirms what I’ve stated all along. The Unit number is automatically placed on the form (and thus in the record) in my sample. Along with that, and because of it, You do not need the VIN or descriptions fields. These are available any time with a query as I showed in my last sample. It is the feature of relational databases. Coupling records in one table to records in another.
.
Your method has unnecessary duplication of data and not taking advantage of relational databases.
.
Sorry I cannot be of further help because I firmly believe you are taking a wrong direction. Will it work? Yes with a lot of piecemeal parts and the potential for lots of headaches. As to one glaring, for work orders the primary key field is Unit Number & VIN. With a second work order for that unit you will get a duplicate key error. Example: Unit 6 with VIN 3JB1BAX46NK001234. I would think you would have more than one work order for this unit in its’ lifetime. It also appears Unit Number can be a duplicate. Don’t know reason for that but if true my sample would need just simple modification.
Feeling kind of stupid just now. I was thinking, for some reason, that the data base would make a new table for me. That would make the data base redundant. Over thinking the issue. I realize now that joining the two tables together will still store the information I need. Thanks for your help
Ratslinger
July 25
But I think my issues goes deeper than That. I need to enter the Unit number into the form, have it auto populate the Unit Number, VIN, and the description fields.
No, a quick look at your sample confirms what I’ve stated all along. The Unit number is automatically placed on the form (and thus in the record) in my sample. Along with that, and because of it, You do not need the VIN or descriptions fields. These are available any time with a query as I showed in my last sample. It is the feature of relational databases. Coupling records in one table to records in another.
.
Your method has unnecessary duplication of data and not taking advantage of relational databases.
.
Sorry I cannot be of further help because I firmly believe you are taking a wrong direction. Will it work? Yes with a lot of piecemeal parts and the potential for lots of headaches.