If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
More tables vs. More records
My boss assigned me to a project. He sent an email to me that said "Put all
the Texas part numebrs into one table and all the Pennsylvania numbers into another. Once we get the expanded spreadsheet from headquarters, we will want to cross reference the two tables to make sure we didn't miss any. When you make the F&O combined table, add a column that shows what family each went to (SL20, etc)" Now, if I follow his instructions to the letter, I'll run into the issue of one part number belonging to more than one family. I'll then (I think) have to do some awkward and redundant manual entry via a form or the datasheet view of the table to make sure that Part 123 has its family field populated by all the families it belongs to. My question: Wouldn't it be easier to make a table for each family, and then concatenate when he wants to look at "the big picture"? It sounds like a good idea- I think I remember Crystal explaining this. Am I right? |
#2
|
|||
|
|||
More tables vs. More records
Hi Author (what is your name?)
"make a table for each family" no, make ONE table and add fields to the table to categorize the records .... you absolutely should not have different tables for part numbers depending on where they come from Also, especially if you are going to be importing data, add these 2 tracking fields to your tables: DateAdd, date, DefaultValue -- =Now() DateEdit, date (use the form BeforeUpdate event to fill this) "I'll run into the issue of one part number belonging to more than one family." then you would have something like this: Parts - PartID, autonumber Families - FamID, autonumber PartFamilies - PartFamID, autonumber - PartID, Long, FK to Parts - FamID, Long, FK to Families Warm Regards, Crystal remote programming and training Access Basics 8-part free tutorial that covers essentials in Access http://www.AccessMVP.com/strive4peace * (: have an awesome day * Author wrote: My boss assigned me to a project. He sent an email to me that said "Put all the Texas part numebrs into one table and all the Pennsylvania numbers into another. Once we get the expanded spreadsheet from headquarters, we will want to cross reference the two tables to make sure we didn't miss any. When you make the F&O combined table, add a column that shows what family each went to (SL20, etc)" Now, if I follow his instructions to the letter, I'll run into the issue of one part number belonging to more than one family. I'll then (I think) have to do some awkward and redundant manual entry via a form or the datasheet view of the table to make sure that Part 123 has its family field populated by all the families it belongs to. My question: Wouldn't it be easier to make a table for each family, and then concatenate when he wants to look at "the big picture"? It sounds like a good idea- I think I remember Crystal explaining this. Am I right? |
#3
|
|||
|
|||
More tables vs. More records
I agree completely with Crystal about using only ONE table, but I'm curious.
Is your boss making this specific assignment because s/he thinks s/he knows "how" to do this, or because there's something inherent in the design s/he is demanding that has use outside of the context? For example, it may be that the table design proposed is being proposed to make it easy to export the data... While this would be true for a spreadsheet, it is irrelevant for a relational database. You can use a query against a single table to extract whatever you need to have exported, and that query will "look" like it is a separate table. You might want to learn a bit more of the "why" before deciding the "how". Regards Jeff Boyce Microsoft Office/Access MVP "Author" wrote in message ... My boss assigned me to a project. He sent an email to me that said "Put all the Texas part numebrs into one table and all the Pennsylvania numbers into another. Once we get the expanded spreadsheet from headquarters, we will want to cross reference the two tables to make sure we didn't miss any. When you make the F&O combined table, add a column that shows what family each went to (SL20, etc)" Now, if I follow his instructions to the letter, I'll run into the issue of one part number belonging to more than one family. I'll then (I think) have to do some awkward and redundant manual entry via a form or the datasheet view of the table to make sure that Part 123 has its family field populated by all the families it belongs to. My question: Wouldn't it be easier to make a table for each family, and then concatenate when he wants to look at "the big picture"? It sounds like a good idea- I think I remember Crystal explaining this. Am I right? |
#4
|
|||
|
|||
More tables vs. More records
So several parts can be in one family, and each part can be in more than
one family? That is a 'many-to-many' situation. "Many" parts are joined to a family, and "many" families are joined to a part, and "many" in this case means just "possibly more than one" Access is a database management system that makes easy things easy to do. Unfortunately, many-to-many joins are not easy for Access. The main problem is that if you have a table of parts, and a table of families, and you join them to get part-family pairs or part-family sets, you will have a non-updateable query. For this reason, you may find that it is easier to do some awkward and redundant data entry, rather than trying to get a perfect database design. (david) "Author" wrote in message ... My boss assigned me to a project. He sent an email to me that said "Put all the Texas part numebrs into one table and all the Pennsylvania numbers into another. Once we get the expanded spreadsheet from headquarters, we will want to cross reference the two tables to make sure we didn't miss any. When you make the F&O combined table, add a column that shows what family each went to (SL20, etc)" Now, if I follow his instructions to the letter, I'll run into the issue of one part number belonging to more than one family. I'll then (I think) have to do some awkward and redundant manual entry via a form or the datasheet view of the table to make sure that Part 123 has its family field populated by all the families it belongs to. My question: Wouldn't it be easier to make a table for each family, and then concatenate when he wants to look at "the big picture"? It sounds like a good idea- I think I remember Crystal explaining this. Am I right? |
#5
|
|||
|
|||
More tables vs. More records
"you will have a non-updateable query" a form/subform should be used to enter data, not a query -- and then this: "easier to do some awkward and redundant data entry" is not necessary ~~~ thanks for adding your comments, David, I can tell you are good at explaining things smile Warm Regards, Crystal remote programming and training Access Basics 8-part free tutorial that covers essentials in Access http://www.AccessMVP.com/strive4peace * (: have an awesome day * david@epsomdotcomdotau wrote: So several parts can be in one family, and each part can be in more than one family? That is a 'many-to-many' situation. "Many" parts are joined to a family, and "many" families are joined to a part, and "many" in this case means just "possibly more than one" Access is a database management system that makes easy things easy to do. Unfortunately, many-to-many joins are not easy for Access. The main problem is that if you have a table of parts, and a table of families, and you join them to get part-family pairs or part-family sets, you will have a non-updateable query. For this reason, you may find that it is easier to do some awkward and redundant data entry, rather than trying to get a perfect database design. (david) "Author" wrote in message ... My boss assigned me to a project. He sent an email to me that said "Put all the Texas part numebrs into one table and all the Pennsylvania numbers into another. Once we get the expanded spreadsheet from headquarters, we will want to cross reference the two tables to make sure we didn't miss any. When you make the F&O combined table, add a column that shows what family each went to (SL20, etc)" Now, if I follow his instructions to the letter, I'll run into the issue of one part number belonging to more than one family. I'll then (I think) have to do some awkward and redundant manual entry via a form or the datasheet view of the table to make sure that Part 123 has its family field populated by all the families it belongs to. My question: Wouldn't it be easier to make a table for each family, and then concatenate when he wants to look at "the big picture"? It sounds like a good idea- I think I remember Crystal explaining this. Am I right? |
Thread Tools | |
Display Modes | |
|
|