Adding Columns to Tables

[KurtL]

Background info: I have created a database that consists of 22 tables that contains hundereds of electronic components seperated by type. In each table are columns for evey project so that i can determine what projects each component is used in.

Everytime there is a new project i need to add the new column to every table. Is there a way to add the column to every table in one step rather than having to do it seperately for each table? Every table set up the same, same number of columns and column titles and everything.

Thanks

 

[MStCyr]

Sounds like you need to give the application design a bit more tought

 

[Pat Hartman]

You have created a spreadsheet rather than a table. Search for posts on normalization.

Adding a new project to the database should not require ANY table modifications. A project is data. When you add data, you add a row to an existing table, you NEVER add a new column.

 

[KurtL]

I guess i need to be more specific. Ive set the database up to be used with a circuit design software's component information system. All our electronics components have 9-digit part numbers that we assign. The first 3 numbers are determine the type of component (i.e. resistor, capacitor, IC....) Well instead of having one HUGE table i created a table for each of the 3-digit categories. Every table has a yes/no column for each PCB project we have so creating BOMs and where-used queries are easy. Now every time there is a new PCB we develop i have to add that column to every table. I was wondering if there was a way that i could add these new columns to each table in one step.

I hope that clears things up a bit.

 

[neileg]

Your original explanation was quite clear enough. Your design is completely wrong. You are treating Access like a spread sheet.

You should have one table for all the components and one table for all the projects. Then you need a junction table that links the two tables in a many-to-many relationship.

If you want the user to see something that looks like a spreadsheet, you design a form or report that way. Users don't look at tables.

 

[KurtL]

Heres the problem though. OrCAD Capture CIS(component information system) displays all tables in its interface. I wanted my database set up so that from this other program, the circuit designer could open up the CIS and instead of scrolling down a list of potentially 1000s of components, he can open the table of the specific type of component making designing easier and faster. That was the original intention. Of course since i created this database we have moved on to outsourcing so no one even uses the CIS here but we still use the database to create a master parts list that is included with every design. On top of all of this, i have never taken any kind of access classes or training or anything. :-(

 

[neileg]

Heres the problem though. OrCAD Capture CIS(component information system) displays all tables in its interface. I wanted my database set up so that from this other program, the circuit designer could open up the CIS and instead of scrolling down a list of potentially 1000s of components, he can open the table of the specific type of component making designing easier and faster. That was the original intention.

It's easy to do that without splitting up the data like you have.

Of course since i created this database we have moved on to outsourcing so no one even uses the CIS here but we still use the database to create a master parts list that is included with every design.

Don't worry, you'll be insourcing again sometime soon. The fashion is changing!

On top of all of this, i have never taken any kind of access classes or training or anything. :-(

Neither have I! I've learned it all from these forums. Keep asking questions and listening to advice.

 

[Pat Hartman]

he can open the table of the specific type of component making designing easier and faster

Each part would have a type code associated with it. That will allow you to keep the parts in a single table and use a query to select only parts of a particular type.

I cannot over emphasize the importance of proper table design. If you make tables that look like spreadsheets, you really should stick with Excel. You will be very unhappy with Access.

 

[KurtL]

Problem still lays in how OrCAD access the database. It looks at the tables not queries. I agree one giant table would make life a lot easier for me while maintaining the database, but then complicates the user's end (if we ever go back to inhouse design.) :-(

Im guessing the answer to my questing is either one table or deal with my current situation huh?

Pat, what exactly is the difference between a table and a spreadsheet?

 

[neileg]

Well, if that constraint exists, and you have to live with it, there are possibilities. You still need to have one table for projects, a junction table and 22 tables for components.

And the question about tables and spreadsheets? In a spreadsheet, you store, manipulate and display the data all in one place. In a database like Access, you store data in a table, you manipulate it in a query and you display it in a form or report. Tables need to be organised so they efficiently hold the data, not so that they look right.

 

[KurtL]

How do i make a junction table?

 

[neileg]

Search in these forums for many to many or junction tables. It's well covered.

 

[KurtL]

Well search proved to be pretty worthless, then again it usually always is. Ok so i have 22 tables that all the the same properties and column names an everything for my electronic components. I now i have another table that has a list of our PCB assembly numbers. Now how do i go about junctioning them so that each component will be associated with the assembly it was used in? What i do now is i just create queries from my unioun query to only display components that have a check in the the checkbox for a particular assembly.

 

[Pat Hartman]

A junction table is used to create a many-to-many relationship. It holds the primary key of tbl1 and the primary key of tbl2 thereby linking the two records.

A basic many-to-many relationship requires three tables. In your case, only two tables will be used - they will be:
tblPart:
PartID (autonumber primary key).
AssemblyFlg (Y/N to indicate that this part may be used as an assembly)
Nomenclature
etc.

tblAssemblies: (this is the junction table - it relates one part to another. Your code needs to ensure that only parts with an AssemblyFlg = True can be chosen as the AssemblyPartID0
AssemblyPartID (primary key fld1) (foreign key to tblPart)
ChildPartID (primary key fld2) (foreign key to tblPart)

This is a self referencing relationship and allows for infinite depth. It doesn't matter if your BOM is two levels or two hundred. You only need the two tables.

If your BOM is really and truely only two levels, you might find it easier to understand a three table structure but I strongly recommend using the two table structure since it is infinitely expandable.

 

[KurtL]

Problem, we use a "smart," and by smart i really mean ascinine, numbering sytem. Therefore autonumbering wont work. Unless im totally confused by what you are referring to.

 

[KurtL]

A junction table is used to create a many-to-many relationship. It holds the primary key of tbl1 and the primary key of tbl2 thereby linking the two records.

A basic many-to-many relationship requires three tables. In your case, only two tables will be used - they will be:
tblPart:
PartID (autonumber primary key).
AssemblyFlg (Y/N to indicate that this part may be used as an assembly)
Nomenclature
etc.

tblAssemblies: (this is the junction table - it relates one part to another. Your code needs to ensure that only parts with an AssemblyFlg = True can be chosen as the AssemblyPartID0
AssemblyPartID (primary key fld1) (foreign key to tblPart)
ChildPartID (primary key fld2) (foreign key to tblPart)

This is a self referencing relationship and allows for infinite depth. It doesn't matter if your BOM is two levels or two hundred. You only need the two tables.

If your BOM is really and truely only two levels, you might find it easier to understand a three table structure but I strongly recommend using the two table structure since it is infinitely expandable.

Im still not seeing how this could work. Ive attached a PDF of one of my tables. Im thinking you guys still arent quite getting what im trying to describe. The 3-digita numbers at the end are the assembies that i have to add to each of the 22 columns every time there is a new one, the source of this issue i brought up.

 

[Pat Hartman]

Im thinking you guys still arent quite getting what im trying to describe.

But we DO get it. You are creating a spreadsheet rather than a table. Do some reading on normalization and database design. That might help you to understand what we are talking about. Rather than adding columns to all your your tables, you need a second table that contains a row for each "column" you are trying to add to the table.

 

[KurtL]

OK so i think im starting to figure out what your saying, but I'm thinking that OrCAD CIS isnt going to like it. IM pretty sure its looks at tables as spreadsheets since the program also suggests using excel if you have fewere componenets.

 

[KurtL]

So now i have 3 tables(or atleast im working to that) One table has a column for all the assembly number. Another table has the component part number and on the information that the table orginally had minus the assembly check boxes. The third table has 2 colums one where i select the component part number and the other where i select the assembly. Im im not mistaken this table is going to have A LOT of rows in it correct? If i have 500 components 15 possible assemblies i could potentialy have 7500 if every componenet was in every assembly?

I dont know about you but that seems like a bigger PITA than adding a yes/no column to every table. Or do i still have somethign wrong here?

 

[Pat Hartman]

Yes, the table may have many rows. However, your spreadsheet shows that the columns are sparse so that in fact, each assembly will have many fewer than 15 components. The real advantage with the proper structure is that NO report/form/query changes will be required when you add the 16th component. Your proposed spreadsheet requires extensive modifications whenever you need to add a new assembly or component.