In the Main table, add a field for the original part number. If you show all records first, a single Replace Field Contents operation can copy the data from the current part number field to the new "oldNumber" field in a single batch update.
he wants to do an extra table named alias that will contain only new and old numbers
Hmm, how knowledgeable about relational database design is he? If you put up a table or list view of MainTable, only put up the two part number fields on it and name the layout "alias", will that satisfy him? (Why is he telling you to build such a table?)
You can use import records to import just the old and new part numbers into such a table, you can even use the new table option to create the table when you do so.
I also will have build a table with Model Numbers this table will be related to Parts Table so we can see which parts are needed to build each model.
Typically, a given part can be used in more than one model and this requires setting up a join table to link model and part records so that a Model can be linked to many parts and a part can be used in many models.
Models::__pk_ModelID = Model_Part::_fk_ModelId
Parts::__pk_PartID = Model_Part::_fk_PartID
And the __pk_PartID should NOT be the part number, make that a different field and define this primary key field as an auto-entered serial number. Don't let your supervisor or other users see this ID number so that they can't come up with suggestions/requirements that would make you change it. (Primary keys should be unique and never change.) Put your part number fields (old and new) wherever you need them on layouts and use them for searching and sorting. Use the hidden serial number field for your basic relationships.
Note: in manufacturing plants, Model_Part is often referred to as a "Bill of Materials" or BOM.
Thank u for ur Help :-)
Now I have a parts list table, and a Model Table (where I can choose the parts needed for a particular model) but now I was wondering how can I make a report w/ this table I would like to have in this report the parts needed for a model (which I think I already figured out) but I also want to appear the part number quantity needed for a particular model and the component to which that part number belongs to example fabric, frame, etc...Where should I add those fields?
I see that you've started a new thread and I've posted an answer there. In the future, if you don't get a response as fast as you'd like, you can also post a new response to your existing thread and that "bumps" it back to the top of the recent items list.
Ok we are working on a database for our bill of materials. When I started working on this database my boss had already designed it, and I am the one entering all the information but now he wants me to improve it. I know the last time I asked for help I was working on a new database b/ they wanted to see if I could make a better one but I guess they preferred to keep the database the way they have it. Now he asked me to find out how to populate the records information.
We have these fields: Part No. > PartName> Part Description> In Use> Part Weight and > Aliases
We are changing old numbers for new ones. In the part num. field we enter the old number and all its information then we create a new num. and enter the same information. The aliases show the old number for every new number. Example.
Light Jumper Cable
Light Jumper Cable
Now he wants that when he enters the alias num. all the old record description (name, description, weight etc) to be entered automatically for the new number. Is that even possible?
I hope I explained myself
Define a self join relationship between this table and a second occurrence of it.
In Manage | Database | relationships, make a new table occurrence of YourTable by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box.
We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.
Add it to your relationships like this:
PartsTable::alias = PartsTable 2::part number
Now you can open field options for the description field and set it to copy from the description field in PartsTable 2.