MS Access Product Variables Variations Multiple Product Conditions Table and Relationship Setup For Customer Orders and Inventory
Ok I am very lost on this part. I have Products and each product has 4 Product Conditions. These Product Condition Codes are NSOP NSSP USOP USSP . Each Product has a parent. Basically Just the Product. Each Product has a Unique SKU Identification Code called ProductParentSKU . They are 11 length Alphanumeric String. Every Product Variant has a Unique SKU also. It is the ProductParentSKU and then NSOP NSSP USOP USSP suffixed on the end. Example Parent ProductParentSKU would be 59KNC5BWM3K and the Variants would be 59KNC5BWM3KNSOP / 59KNC5BWM3KNSSP / 59KNC5BWM3KUSOP / 59KNC5BWM3KUSSP.
I have just read for days and have tried to set my tables up the best I can with the knowledge I have collected. I have a main table for all the product called ParentProductTable . I then in that ParentProductTable table have 4 fields for each ProductVariantSKU . Like I said this is just what I have read. Wrong or right Who knows. i read that I needed to set it up like a Customer Order table with a Order Product Table. So I assumed I needed a ProductVariantTypesTable wich I made and I now have to link it back to the fields in my main table.
This is where i am lost.
1: Is this a self joining or what?
2: How do I set it up so that when a customer orders a variant that i can input the correct variant and MS Access get the correct Variant of NSOP NSSP USOP USSP?
3: Is my tables EVEN set up correctly???
I know I could just make every variant a record but that would Quadruple (4X) the amount of Information in the Database causing severe redundancy and performance problems. I have attached several photos in hoping they can help you HELP ME PLEASE! I thank you all for your time and advice!![db6.jpg]()
![db7.jpg]()
![db8.jpg]()
![db9.jpg]()
![db10.jpg]()
![db11.jpg]()
I have just read for days and have tried to set my tables up the best I can with the knowledge I have collected. I have a main table for all the product called ParentProductTable . I then in that ParentProductTable table have 4 fields for each ProductVariantSKU . Like I said this is just what I have read. Wrong or right Who knows. i read that I needed to set it up like a Customer Order table with a Order Product Table. So I assumed I needed a ProductVariantTypesTable wich I made and I now have to link it back to the fields in my main table.
This is where i am lost.
1: Is this a self joining or what?
2: How do I set it up so that when a customer orders a variant that i can input the correct variant and MS Access get the correct Variant of NSOP NSSP USOP USSP?
3: Is my tables EVEN set up correctly???
I know I could just make every variant a record but that would Quadruple (4X) the amount of Information in the Database causing severe redundancy and performance problems. I have attached several photos in hoping they can help you HELP ME PLEASE! I thank you all for your time and advice!
instead of setting up another table for parent product sku, do a self-join to the same table. The Products table should have an AutoNumber field -- let's call it ProductID. Create another field to indicate the parent product. I like to put underscore on the end of a field that is a self-join to a parent record so there would also be:
Inventory will be on the "variant" sku and able to be rolled up to the master sku when desired.
Rather than ProductImage1, ProductImage2, ..., create a related table for ProductImages with ProductID as a foreign Key.
each piece of information should be stored atomically with no repeating values.
Locations should also have LocID as an AtuoNumber and then LocID as a Long Integer foreign key in other tables. When key fields represent the same piece of information in different tables, for ease of understanding, name them the same.
ProductID_, Number, Long Integer, DefaultValue = Null (nothing -- delete 0 if Access adds it)
Inventory will be on the "variant" sku and able to be rolled up to the master sku when desired.
Rather than ProductImage1, ProductImage2, ..., create a related table for ProductImages with ProductID as a foreign Key.
each piece of information should be stored atomically with no repeating values.
Locations should also have LocID as an AtuoNumber and then LocID as a Long Integer foreign key in other tables. When key fields represent the same piece of information in different tables, for ease of understanding, name them the same.
OK Thank you very much I got the Image part taken care of and that does make since. I made a one-one relationship between them. My Products Table does NOT have a autonumber field. I use the ProductParentSKU as my Natural Identifier Primary Key. The SKU is Unique to the product and not ever allowed to be duplicated or reused. So can you go into that part a little more to clarify please. I thank you very very much. This is a real head to desk head banger!![db12.jpg]()
there should be 1 product to many images. ProductID would not be the primary key in the product images table, it would be a foreign key. ID would would called ProductImageID and be an AutoNumber. Each record in the product images table would point to one image. It is best to store these images as individual files and in the database, store path and filename, or just filename if they are all in the same path.
Ok Please give me some food for thought. I have studied and everything I read said to use a a natural Primary Key vs a Surrogate Primary Key. I am not trying to get off the main question just want to understand where you are coming from so I can understand the BIG picture. As for storing Images I have to store them in a cloud for other uses of importing images into other websites and also this cuts down the database size and speeds things up. If I am correct. The way I visualized it was that (We have up to 12 photos per item) there was a field to each Image URL and the ProductParentSKU was what said this Record of 12 images goes to this Parent product????Thank you for your help and understanding.
Surrogate Primary Key is better for performance, and if you ever want to move back-end to SQL Server or to the cloud. The would be AutoNumber/Long Integer combination.
> "images -- store them in a cloud"
that is fine. Just store one URL on each record so all URLs are in the same field. Instead of 12 fields for URLs, you would have 12 records. This is better for many reasons.
Images could be linked to SKU or Parent SKU. If SKU, then parent SKU should show them all too and vice-verse.
> "images -- store them in a cloud"
that is fine. Just store one URL on each record so all URLs are in the same field. Instead of 12 fields for URLs, you would have 12 records. This is better for many reasons.
Images could be linked to SKU or Parent SKU. If SKU, then parent SKU should show them all too and vice-verse.
Ok I kinda get what you are saying the only problem I have if I am now set up right is that I can't enforce referential integrity in the relationship because they are of two different field types.![db13.jpg]()
Use AutoNumber for the Primary Key
Use Number, Long Integer, Default Value is nothing, for the Foreign Key
Don't link on LocationName.
Create an AutoNumber field in that table and use a Long Integer in related tables.
don't link one autonumber to another -- that is not a valid relationship because autonumbers will never match except by chance
Use Number, Long Integer, Default Value is nothing, for the Foreign Key
Don't link on LocationName.
Create an AutoNumber field in that table and use a Long Integer in related tables.
don't link one autonumber to another -- that is not a valid relationship because autonumbers will never match except by chance
PatHartman
ProductVariantTypesTable needs a relationship with InventoryLocationTable = I didn't know how to set that one up
All the images are the same for every condition type.
I did everything else you said. Please look here and Thank you for the help!!!
![db15.jpg]()
ProductVariantTypesTable needs a relationship with InventoryLocationTable = I didn't know how to set that one up
All the images are the same for every condition type.
I did everything else you said. Please look here and Thank you for the help!!!
I'm guessing you were posting when I was so you hadn't read my suggestions yet.
In addition to what I said,
The Relationship between OrderProductTable and ParentProductTable is incorrect. OrderLineID is an autonumber and the PK for the OrderProductTable. Change ProductParentSKU to ProductID and connect on the two ProductID fields or as I suggested, you should be connecting the order directory to the InventoryLocationTable since THAT is what you are ordering.
InventoryLocationTable also needs to replace ProductParentSKU with ProdductID and join on the ProductID fields.
I don't see any reason to have the InventoryLocationMasterLis
When you post the picture of the schema, please expand all the tables so we can see all the columns.
In addition to what I said,
The Relationship between OrderProductTable and ParentProductTable is incorrect. OrderLineID is an autonumber and the PK for the OrderProductTable. Change ProductParentSKU to ProductID and connect on the two ProductID fields or as I suggested, you should be connecting the order directory to the InventoryLocationTable since THAT is what you are ordering.
InventoryLocationTable also needs to replace ProductParentSKU with ProdductID and join on the ProductID fields.
I don't see any reason to have the InventoryLocationMasterLis
When you post the picture of the schema, please expand all the tables so we can see all the columns.
Crystal suggested switching to surrogate keys and you started but you haven't finished yet. You must replace all the natural foreign key fields with the surrogate key so you join on PK to FK.
Also, now that you are switching to surrogate keys, you MUST add unique indexes on all natural keys.
You asked about whether to use natural or surrogate keys, in general it is better to use surrogate keys because they will keep you from having to use multi-field primary keys on lower level table. However, since it makes no sense to allow duplicate values for ProductParentSKU, you must make a unique index on the field since you are no longer using it as your primary key. Same for ProductVariantSKU. Customer is more of a problem since FN/LN isn't guaranteed to be unique. For the customer table, you need to add either address or phone number and make a compound unique index. Having duplicates in the customer table won't cause the same kind of problems that duplicates in ParentProduct but the duplicates will prevent accurate reporting.
Also, now that you are switching to surrogate keys, you MUST add unique indexes on all natural keys.
You asked about whether to use natural or surrogate keys, in general it is better to use surrogate keys because they will keep you from having to use multi-field primary keys on lower level table. However, since it makes no sense to allow duplicate values for ProductParentSKU, you must make a unique index on the field since you are no longer using it as your primary key. Same for ProductVariantSKU. Customer is more of a problem since FN/LN isn't guaranteed to be unique. For the customer table, you need to add either address or phone number and make a compound unique index. Having duplicates in the customer table won't cause the same kind of problems that duplicates in ParentProduct but the duplicates will prevent accurate reporting.
or as I suggested, you should be connecting the order directory to the InventoryLocationTable since THAT is what you are ordering.
Are you saying to directly connect the OrderProductTable and the InventoryLocationTable together????
I have done everything up to your last post and did away with the InventoryMasterListTable
Replace ProductID in the OrderProductTable with InventoryPlacementID to switch the join from the parent table to the inventory table.
I didn't notice that you had the whole address mushed into CustomerAddress. It is best to split this into Addr1, Addr2, City, State, and Zip for easier formatting and reporting.
The Address in customerOrderTable is ShippingAddress, not customer address. Frequently they are the same and so when the order is entered, generally the billing address is copied from the customer table to the order table where it can be changed if the order is being shipped to a different address.
You seem to have given ConditionCode a surrogate key. That means that you MUST replace ConditionCode in ALL the other tables with the surrogate key field named ProductVariantCodeID.
I didn't notice that you had the whole address mushed into CustomerAddress. It is best to split this into Addr1, Addr2, City, State, and Zip for easier formatting and reporting.
The Address in customerOrderTable is ShippingAddress, not customer address. Frequently they are the same and so when the order is entered, generally the billing address is copied from the customer table to the order table where it can be changed if the order is being shipped to a different address.
You seem to have given ConditionCode a surrogate key. That means that you MUST replace ConditionCode in ALL the other tables with the surrogate key field named ProductVariantCodeID.
Did I do this right in the InventoryLocationTable by adding the Field OrderLineID and then doing the lookupwizard to the OrderProductTable so that it link PK to FK
and how would I add unique indexes to my ProductParentSKU? Thank you very very much!
![db17.jpg]()
and how would I add unique indexes to my ProductParentSKU? Thank you very very much!
You seem to have given ConditionCode a surrogate key. That means that you MUST replace ConditionCode in ALL the other tables with the surrogate key field named ProductVariantCodeID.
I don't see it? Where is it at?
The ProductVariantTypesTable seems to define ConditionCode. You need a unique index on ConditionCode in that table and you need to replace "ConditionCode" everyplace it exists with ProductVariantCodeID.
You also never said whether the images relate directly to the product or to the VariantSKU.
You also never said whether the images relate directly to the product or to the VariantSKU.
The images are related to the product specifically not the variants. The same photo goes to all the variants.
In the last photo is my Customer Table set up correctly. you stated I needed to add the address or phone number. So would I use CustomerID and CusomerAddress1 as the unique Primary Key?
>lookupwizard
Don't use the lookup wizard in desktop databases
I'll let Pat continue to help since I am currently working on something else. You are making progress!
Pat -- I suggested a self-join for variant SKU and parent SKU. Then image could relate to either ...
Don't use the lookup wizard in desktop databases
I'll let Pat continue to help since I am currently working on something else. You are making progress!
Pat -- I suggested a self-join for variant SKU and parent SKU. Then image could relate to either ...
CustomerID is an autonumber so it is unique. Combining it with another field won't make it more unique. The purpose of unique indexes on natural keys is to prevent duplicates. These indexes will never include the autonumber PK because the autonumber is itself unique and is not natural at all. If phone number is required, it could serve as the unique index on the customer table.
To make a unique index on a single field such as ProductParentSKU, open the table in design view and click on the field you want to index. Set the Indexed property to Yes (no duplicates0
To create a compound unique index, open the index dialog. Type a name for the index in the Index Name column. Choose the first field from the dropdown in Field Name. et the Unique property to Yes. On the next line, leave the Index name blank to indicate that this field belongs to the previous index and choose the second field. You can have up to 10 fields in a unique index or primary key.![UniqueIDX.JPG]()
To create a compound unique index, open the index dialog. Type a name for the index in the Index Name column. Choose the first field from the dropdown in Field Name. et the Unique property to Yes. On the next line, leave the Index name blank to indicate that this field belongs to the previous index and choose the second field. You can have up to 10 fields in a unique index or primary key.
The ConditionCode in ProductVariantTypeTable is just my VariantName Code. So I am a little lost there. It just holds four records. NSOP NSSP USOP USSP I may not even need the ProductVariantTypesTable??
the "variant" sku is a child sku to the parent ... so they can be rolled up and enumerated. I suggested creating [ProductID_] in the Products (sku) table to identify the parent SKU for a child ("variant") SKU. No need for another table.
In looking at your image, I see you are calling this ProductParentSKU and have it as short text. Firstly, it needs to link to the Primary Key field and therefore, needs to be Long Integer with no Default Value (delete what Access puts there).
Putting _ after a PK fieldname is my personal convention for identifying a parent
In looking at your image, I see you are calling this ProductParentSKU and have it as short text. Firstly, it needs to link to the Primary Key field and therefore, needs to be Long Integer with no Default Value (delete what Access puts there).
Putting _ after a PK fieldname is my personal convention for identifying a parent
I am sorry but that is a little confusing. So Remove The ProductVariantTypesTable? If you could put that in dummy form for me please. That code you showed at the beginning. How would I use that. In a query?
Access creates a LOT of indexes that you don't need (or want). To get it to stop doing that:
File, Options, Object Designers --> DELETE EVERYTHING listed in the box for AutoIndex on Import/Create (ID, Num, Code, etc)
File, Options, Object Designers --> DELETE EVERYTHING listed in the box for AutoIndex on Import/Create (ID, Num, Code, etc)
> "remove The ProductVariantTypesTable? "
I would say Yes
A SKU is a SKU. Some SKUs can roll up to others. The information you track about each is the same, isn't it?
In a query, you can add the SAME table more than one time ... 2 times in this case
I would say Yes
A SKU is a SKU. Some SKUs can roll up to others. The information you track about each is the same, isn't it?
In a query, you can add the SAME table more than one time ... 2 times in this case
Ok I deleted the content in the options and deleted the ProductVariantsTypesTable.
The SKUs are all very similar for the variants. Parent SKU is 11 alphanumeric String and the Children Variants SKUs are the 11 alphanumeric String with NSOP NSSP USOP USSP Suffixed on the end
Example
Parent SKU: BBZ3VPH4DMC
NSOP SKU: BBZ3VPH4DMCNSOP
NSSP SKU: BBZ3VPH4DMCNSSP
USOP SKU: BBZ3VPH4DMCUSOP
USSP SKU: BBZ3VPH4DMCUSSP
The SKUs are all very similar for the variants. Parent SKU is 11 alphanumeric String and the Children Variants SKUs are the 11 alphanumeric String with NSOP NSSP USOP USSP Suffixed on the end
Example
Parent SKU: BBZ3VPH4DMC
NSOP SKU: BBZ3VPH4DMCNSOP
NSSP SKU: BBZ3VPH4DMCNSSP
USOP SKU: BBZ3VPH4DMCUSOP
USSP SKU: BBZ3VPH4DMCUSSP
so now add:
ProductID_, Long Integer, Default Value is nothing
to the Products table
this will be to identify the parent SKU for the variants
ProductID_, Long Integer, Default Value is nothing
to the Products table
this will be to identify the parent SKU for the variants
Sorry for any ignorance or lack of sleep for the last few days as i have been at this for days and days. I appreciate this all more then anyone knows. Ok I did as you said is this correct?![db20.jpg]()
![db20.jpg]()
did you see my comment about stopping Access from creating all those indexes?
better to set it sooner than later ...
better to set it sooner than later ...
Yes I went into the options and deleted all that was in there. Was I supposed to put anything back in there or just leave it blank?
ProductID_:
good!
Now remove ProductParentSKU
I also like to put key fields at the top.
1. select row by clicking on the row selector box to the left
2. let go of the mouse
3. click on that box again and drag it up so the dark line indicating where it will go is under ProductID
good!
Now remove ProductParentSKU
I also like to put key fields at the top.
1. select row by clicking on the row selector box to the left
2. let go of the mouse
3. click on that box again and drag it up so the dark line indicating where it will go is under ProductID
adding onto comment before this
4. and then let go of the mouse
the field will move
click the Save icon (diskette) twice so it will save and save the new order when you look at the datasheet.
4. and then let go of the mouse
the field will move
click the Save icon (diskette) twice so it will save and save the new order when you look at the datasheet.
> "Do I have to make a query now?"
do you have data in there yet? Better to get at least some sample records in first
do you have data in there yet? Better to get at least some sample records in first
since you don't yet have a form for entering, look at the ProductID for a parent SKU when you are entering a variant and put that number into ProductID_
on the Relationships Diagram:
drag another copy of the Products table to the diagram ... and please rename this table so it is simply called "Products" -- no need to add all those extra words to its name
Drag from ProductID in the copy to ProductID_ in the first Products table
Enforce Referential Integrity and click OK
Then:
I like to position the parent above the real table and only show a few rows.
drag another copy of the Products table to the diagram ... and please rename this table so it is simply called "Products" -- no need to add all those extra words to its name
Drag from ProductID in the copy to ProductID_ in the first Products table
Enforce Referential Integrity and click OK
Then:
I like to position the parent above the real table and only show a few rows.
Yes I was previously using a desktop application software for a database and it was very very slow. This is why I am trying to build my own customized database. Plus I just enjoy it learning and building it (In between banging my head on the desk).... I have about 2200 records in the ParentProductsTable but no variants or customer info in the other tables.
rename ProductImagesTable to just ProductImages and scoot it to the right so the link line has a "1" at the top instead of an "11" (that happens when a related table starts to the left of where the parent table ends on the right.
Position all tables on the diagram so the "1" side of the line is on the left and the "many" side of the line is on the right ... so the diagram flows from left-right as data must be entered. This is very helpful to see the overall flow of the information.
so InventoryLocationsTable (just call this Locations) is to the left of OrderProductTable (just call this Orders).
the foreign key in Orders to link to ProductID should be CALLED ProductID since it represents the same piece of information -- rename it.
Position all tables on the diagram so the "1" side of the line is on the left and the "many" side of the line is on the right ... so the diagram flows from left-right as data must be entered. This is very helpful to see the overall flow of the information.
so InventoryLocationsTable (just call this Locations) is to the left of OrderProductTable (just call this Orders).
the foreign key in Orders to link to ProductID should be CALLED ProductID since it represents the same piece of information -- rename it.
> "I had a product entry form"
you can make a better one now :)
> "How do you rename it the the alias."
no need -- it will have a _1 on the end -- that is ok and your clue that it is just a copy of the same table ... or, what are you asking?
you can make a better one now :)
> "How do you rename it the the alias."
no need -- it will have a _1 on the end -- that is ok and your clue that it is just a copy of the same table ... or, what are you asking?
actually, CustomerOrderTable should be Orders and OrderProductsTable should be OrderDetails (OrderProducts is ok too -- but get rid of "Table" -- they are all tables)
In ProductSuppliers, is SupplierReferenceID an autonumber? If not, make that a different field and add a field called ProdSupID, Autonumber (PK) to the table. If it is rename it to ProdSupID. When a field is 2 things, I generally pick 3 or 4 characters from each word so fieldnames don't get too long.
You should have a different table for Suppliers with a SupplierID autonumber PK (primary key). ProductSuppliers should have SupplierID as a foreign key and ProductID as a foreign key.
You should have a different table for Suppliers with a SupplierID autonumber PK (primary key). ProductSuppliers should have SupplierID as a foreign key and ProductID as a foreign key.
I know the difference between them (The Original and the Copy) but I just wanted to follow direct directions and you said
![db22.jpg]()
drag another copy of the Products table to the diagram ... and please rename this table so it is simply called "Products" -- no need to add all those extra words to its nameOk I think I have done all that you have said so far.
rename ParentProductTable to Products
rename what is now Orders since I see it is order detail to OrderDetail or OrderProducts
rename CustomerOrderTable to Orders
rename CustomerTable to Customers and move it to the left of Orders
rename what is now Orders since I see it is order detail to OrderDetail or OrderProducts
rename CustomerOrderTable to Orders
rename CustomerTable to Customers and move it to the left of Orders
the primary key of Locations should be called LocID or LocationID (I like the shorter names)
It should not be linked to ProductID
It should not be linked to ProductID
how many Locations do you have?
Now that I am looking at fields in locations, I see ProductID and how much is there ... so that is actually ProductLocations with pk = ProdLocID and you need a different Locations table with LocID and LocName. Sorry if I am changing what I said before -- you know your data better than I do
Now that I am looking at fields in locations, I see ProductID and how much is there ... so that is actually ProductLocations with pk = ProdLocID and you need a different Locations table with LocID and LocName. Sorry if I am changing what I said before -- you know your data better than I do
Ok i am rounding up all the alters you requested :) I will be done soon. We have ALOT of locations. This was the reason for the LocationsMasterTable. and them locations are growing. Basically we are running out of room and adding more. but right now estimated around 200-300
just call it Locations
you will run out of room for one screen of the Relationships Diagram faster with all these long names!
you will run out of room for one screen of the Relationships Diagram faster with all these long names!
ok I have done all but link the SuppliersIDtbl to the ProductSupplier table. They are not the same field type??? If I done this correct?![db23.jpg]()
rename CustomerTable to Customers and move it to the left of Orders
Drag from ProductID in Products_1 to ProductID_ in the Products
Enforce Referential Integrity and click OK
Then:
I like to position the parent (Products_1) above the real table and only show a few rows.
Drag from ProductID in Products_1 to ProductID_ in the Products
Enforce Referential Integrity and click OK
Then:
I like to position the parent (Products_1) above the real table and only show a few rows.
rearrange fields in OrderDetails so OrderID is above ProductID so join lines don't cross
right now, Products is in the first column of the diagram. Move it and everything to the right of it to the right so Products is in the 3rd column.
ProductSuppliers will be in the second column with a link on ProdSupID to Products (rename SupplierReferenceID in Products and move it under ProductID_) . Actually though, cannot a product have more than one supplier? Perhaps this information needs to be in yet another related table.
So suppliers would be in the first column, linking to ProductSuppliers on SupplierID (which needs to be added to ProductSuppliers)
Make ALL the fieldlists narrower -- and shorten them -- so there is not extra space
right now, Products is in the first column of the diagram. Move it and everything to the right of it to the right so Products is in the 3rd column.
ProductSuppliers will be in the second column with a link on ProdSupID to Products (rename SupplierReferenceID in Products and move it under ProductID_) . Actually though, cannot a product have more than one supplier? Perhaps this information needs to be in yet another related table.
So suppliers would be in the first column, linking to ProductSuppliers on SupplierID (which needs to be added to ProductSuppliers)
Make ALL the fieldlists narrower -- and shorten them -- so there is not extra space
Ok thanks. It is late. I have church in 4 hours and I appreciate the help like you wouldn't believe. I know noone that is educated in this field and everyone just looks at me like I am speaking gibberish. Thank you all and Godbless and if pat wants to hop in and help that would be awesome. I will get back on in the next 24-48 hrs.
By the way I just have to say I looked at your link and at the bottom it says "Not all those who wander are lost" -- - J. R. R. Tolkien and I have a tshirt that quotes that and it is my favorite!
Hello and thank you for ALL the help. I have a question about what you said.
1: Am I supposed to make a NEW record line for each variant in the Products Table?
I would think No on the quadruple records???? But what do I know?...
2: Will I need a new table for the ParentProductSKUs AKA ProductIDs to hold my 11 length alphanumeric Parent SKUs and 15 length alphanumeric Children Variant SKUs with a Relationship between the ProductID in the Product Table and the SKU Table if I need one?
3: Also can you give me a run down of how the process flow works in this setup. As in Customer orders, order detail, inventory pick, ship item ETC.... Just something so I can visualize this specific system better.
4: Do I need to Link the Location Table to the LocMasterLst Table?
5: Then do I link the locations Table to Orders or OrderDetails Table or another Table?
I am more of a hands on or watching a video kinda person to make things click better for me. Once it clicks it CLICKS and I can move on. Here is the Relationships view I have now and the Products Table in Datasheet View.
Thank You!
![db27.jpg]()
![db28.jpg]()
since you don't yet have a form for entering, look at the ProductID for a parent SKU when you are entering a variant and put that number into ProductID_
1: Am I supposed to make a NEW record line for each variant in the Products Table?
I would think No on the quadruple records???? But what do I know?...
2: Will I need a new table for the ParentProductSKUs AKA ProductIDs to hold my 11 length alphanumeric Parent SKUs and 15 length alphanumeric Children Variant SKUs with a Relationship between the ProductID in the Product Table and the SKU Table if I need one?
3: Also can you give me a run down of how the process flow works in this setup. As in Customer orders, order detail, inventory pick, ship item ETC.... Just something so I can visualize this specific system better.
4: Do I need to Link the Location Table to the LocMasterLst Table?
5: Then do I link the locations Table to Orders or OrderDetails Table or another Table?
I am more of a hands on or watching a video kinda person to make things click better for me. Once it clicks it CLICKS and I can move on. Here is the Relationships view I have now and the Products Table in Datasheet View.
Thank You!
1. yes
2. no -- that is the point of putting Product_ into the table as well. It will hold the ProductID for the parent sku
3. the reason I suggested you arrange the relationships so "1" is on the left and "many" on the right is to better visualize the flow of information. Here is a short video on relationships you can watch:
video Tip: Enforce Referential Integrity on Access Relationships (cc) closed-caption
http://www.youtube.com/watch?v=_zxxc9jzWEg
4. I did not notice when I told you to rename Locations that it should actually be ProdLocations with ProdLoc autonumber primary key.
ProdLocations should have a field, LocID, that is a foreign key (long integer, no default value) that links to LocID in what should be the Locations table that is now called, I guess, LocMasterLst
5. OrderDetails will probably need a link to ProdLocations
the more you can abbreviate, Prod for Product, Loc for Location, etc, the less space your tables will need on the relationships diagram.
I am not going to be able to help today because I am working on another project. I will look in on what you've done tonight though. It could be late -- about the same time I got back on last night. Re-read the notes I made yesterday, realizing that I was also changing names from what I previously told you to call things based on seeing the information better. Rather than giving you conflicting information, since we have different styles, Pat wants me to continue to help you. So today, it would be best for you to assimilate what has been done and get better understanding. I also linked you to a short book you should read. You are doing very well!
2. no -- that is the point of putting Product_ into the table as well. It will hold the ProductID for the parent sku
3. the reason I suggested you arrange the relationships so "1" is on the left and "many" on the right is to better visualize the flow of information. Here is a short video on relationships you can watch:
video Tip: Enforce Referential Integrity on Access Relationships (cc) closed-caption
http://www.youtube.com/watch?v=_zxxc9jzWEg
4. I did not notice when I told you to rename Locations that it should actually be ProdLocations with ProdLoc autonumber primary key.
ProdLocations should have a field, LocID, that is a foreign key (long integer, no default value) that links to LocID in what should be the Locations table that is now called, I guess, LocMasterLst
5. OrderDetails will probably need a link to ProdLocations
the more you can abbreviate, Prod for Product, Loc for Location, etc, the less space your tables will need on the relationships diagram.
I am not going to be able to help today because I am working on another project. I will look in on what you've done tonight though. It could be late -- about the same time I got back on last night. Re-read the notes I made yesterday, realizing that I was also changing names from what I previously told you to call things based on seeing the information better. Rather than giving you conflicting information, since we have different styles, Pat wants me to continue to help you. So today, it would be best for you to assimilate what has been done and get better understanding. I also linked you to a short book you should read. You are doing very well!
THANK YOU FOR THE HELP! I completely understand and I am just posting back some background info to help you better understand Me as in why and what for I need this database for. Of course I need it for normal database use like tracking products, customers, and etc... But the main reason for me to attempt this (To Me) BIG obstacle of creating my very own custom database is for a few reasons.
SlimLine it for Speed
Custom Solutions For Me
Be able to Export CSV files (or other formats maybe) in certain ways
Overall just make things move more fluid like and Automize things.
#1 is for me to learn it so when the next hurdle comes along I can say "Yep I'm On It!" Problem solved.
I will share in the next few few parts below some Images of some CSV file layouts I am needing and had previously accomplished with Queries. I export out of Access into my own website and I also export out of Access into other websites.
I am not 100% customizable on our own website yet so I can only import a CSV file a certain way and other websites I have no say so on the CSV file layout.
![DB29.jpg]()
The Query coding I came up with below is obviously for the database before I met you wonderful people :) and we just overhauled and changed everything. So I will have to recode it to work now.
OUR WEBSITE:
OTHER WEBSITES:
On our website a single webpage has the ParentProductInfo including title description and so on. Then we have a table with the condition code variant code and the pricing and quantity. Other websites say that every individual product condition has to have a single webpage so I have to duplicate the items 4 times. (I believe like you are suggesting.)
This is just my personal uneducated thought!---- I have been trying to avoid duplicating records 4 or 5 times. (One for Each Condition OR One for Each Condition and the Parent)
I thought this would causing Database bloating and slow the system down and cause Redundancy as in the titles being similiar. Also When entering information as the user I would have to create them four lines of record for each and this would take ALOT of work and time.
I know there is a way to pass on info to duplicate fields (I believe this is called looping) and combining fields for the titles to have the condition code in it. You even said something about this at the beginning about looping and combining code. I just don't know anything about that yet.
Like i said this is just to help you help me and let you know the end expected results. I will take whatever help I can get and not be pushy because I understand and I need the help. :) Beggars can't be choosers as they say! I cant thank you enough! I have been using the old database software and it is costing me to much time and frustration. Sometimes it even deletes stuff on me after I exit.(the current Software I have NOT Access). So this is HUGE and very important to me. God Bless!
SlimLine it for Speed
Custom Solutions For Me
Be able to Export CSV files (or other formats maybe) in certain ways
Overall just make things move more fluid like and Automize things.
#1 is for me to learn it so when the next hurdle comes along I can say "Yep I'm On It!" Problem solved.
I will share in the next few few parts below some Images of some CSV file layouts I am needing and had previously accomplished with Queries. I export out of Access into my own website and I also export out of Access into other websites.
I am not 100% customizable on our own website yet so I can only import a CSV file a certain way and other websites I have no say so on the CSV file layout.
The Query coding I came up with below is obviously for the database before I met you wonderful people :) and we just overhauled and changed everything. So I will have to recode it to work now.
OUR WEBSITE:
SELECT ParentProductTable.ProductParentSKU, [ProductParentSKU] & "NSOP" AS ProductVariantSKU, ParentProductTable.ProductNameTitle, ParentProductTable.Manufacture, ParentProductTable.MPN, ParentProductTable.UPC, ParentProductTable.ProductDescription, ParentProductTable.NSOPQuantityAvailable, ParentProductTable.NSOPSellPrice, ParentProductTable.ProductImageURL1, ParentProductTable.ProductImageURL2, ParentProductTable.ProductImageURL3, ParentProductTable.ProductImageURL4, ParentProductTable.ProductImageURL5, ParentProductTable.ProductImageURL6, ParentProductTable.ProductImageURL7, ParentProductTable.ProductImageURL8, ParentProductTable.ProductImageURL9, ParentProductTable.ProductImageURL10, ParentProductTable.ProductImageURL11, ParentProductTable.ProductImageURL12
FROM ParentProductTable
UNION
SELECT ParentProductTable.ProductParentSKU, [ProductParentSKU] & "NSSP" AS ProductVariantSKU, ParentProductTable.ProductNameTitle, ParentProductTable.Manufacture, ParentProductTable.MPN, ParentProductTable.UPC, ParentProductTable.ProductDescription, ParentProductTable.NSSPQuantityAvailable, ParentProductTable.NSSPSellPrice, ParentProductTable.ProductImageURL1, ParentProductTable.ProductImageURL2, ParentProductTable.ProductImageURL3, ParentProductTable.ProductImageURL4, ParentProductTable.ProductImageURL5, ParentProductTable.ProductImageURL6, ParentProductTable.ProductImageURL7, ParentProductTable.ProductImageURL8, ParentProductTable.ProductImageURL9, ParentProductTable.ProductImageURL10, ParentProductTable.ProductImageURL11, ParentProductTable.ProductImageURL12
FROM ParentProductTable
UNION
SELECT ParentProductTable.ProductParentSKU, [ProductParentSKU] & "USOP" AS ProductVariantSKU, ParentProductTable.ProductNameTitle, ParentProductTable.Manufacture, ParentProductTable.MPN, ParentProductTable.UPC, ParentProductTable.ProductDescription, ParentProductTable.USOPQuantityAvailable, ParentProductTable.USOPSellPrice, ParentProductTable.ProductImageURL1, ParentProductTable.ProductImageURL2, ParentProductTable.ProductImageURL3, ParentProductTable.ProductImageURL4, ParentProductTable.ProductImageURL5, ParentProductTable.ProductImageURL6, ParentProductTable.ProductImageURL7, ParentProductTable.ProductImageURL8, ParentProductTable.ProductImageURL9, ParentProductTable.ProductImageURL10, ParentProductTable.ProductImageURL11, ParentProductTable.ProductImageURL12
FROM ParentProductTable
UNION
SELECT ParentProductTable.ProductParentSKU, [ProductParentSKU] & "USSP" AS ProductVariantSKU, ParentProductTable.ProductNameTitle, ParentProductTable.Manufacture, ParentProductTable.MPN, ParentProductTable.UPC, ParentProductTable.ProductDescription, ParentProductTable.USSPQuantityAvailable, ParentProductTable.USSPSellPrice, ParentProductTable.ProductImageURL1, ParentProductTable.ProductImageURL2, ParentProductTable.ProductImageURL3, ParentProductTable.ProductImageURL4, ParentProductTable.ProductImageURL5, ParentProductTable.ProductImageURL6, ParentProductTable.ProductImageURL7, ParentProductTable.ProductImageURL8, ParentProductTable.ProductImageURL9, ParentProductTable.ProductImageURL10, ParentProductTable.ProductImageURL11, ParentProductTable.ProductImageURL12
FROM ParentProductTable;
OTHER WEBSITES:
SELECT "NSOP " & [ProductNameTitle] AS ProductVariantNameTitle, [ProductParentSKU] & "NSOP" AS ProductVariantSKU, ParentProductTable.Manufacture, ParentProductTable.MPN, ParentProductTable.UPC, ParentProductTable.ProductDescription, ParentProductTable.NSOPQuantityAvailable, ParentProductTable.NSOPSellPrice, ParentProductTable.ProductImageURL1, ParentProductTable.ProductImageURL2, ParentProductTable.ProductImageURL3, ParentProductTable.ProductImageURL4, ParentProductTable.ProductImageURL5, ParentProductTable.ProductImageURL6, ParentProductTable.ProductImageURL7, ParentProductTable.ProductImageURL8, ParentProductTable.ProductImageURL9, ParentProductTable.ProductImageURL10, ParentProductTable.ProductImageURL11, ParentProductTable.ProductImageURL12, ParentProductTable.ProductParentSKU
FROM ParentProductTable
UNION
SELECT "NSSP " & [ProductNameTitle] AS ProductVariantNameTitle, [ProductParentSKU] & "NSSP" AS ProductVariantSKU, ParentProductTable.Manufacture, ParentProductTable.MPN, ParentProductTable.UPC, ParentProductTable.ProductDescription, ParentProductTable.NSSPQuantityAvailable, ParentProductTable.NSSPSellPrice, ParentProductTable.ProductImageURL1, ParentProductTable.ProductImageURL2, ParentProductTable.ProductImageURL3, ParentProductTable.ProductImageURL4, ParentProductTable.ProductImageURL5, ParentProductTable.ProductImageURL6, ParentProductTable.ProductImageURL7, ParentProductTable.ProductImageURL8, ParentProductTable.ProductImageURL9, ParentProductTable.ProductImageURL10, ParentProductTable.ProductImageURL11, ParentProductTable.ProductImageURL12, ParentProductTable.ProductParentSKU
FROM ParentProductTable
UNION
SELECT "USOP " & [ProductNameTitle] AS ProductVariantNameTitle, [ProductParentSKU] & "USOP" AS ProductVariantSKU, ParentProductTable.Manufacture, ParentProductTable.MPN, ParentProductTable.UPC, ParentProductTable.ProductDescription, ParentProductTable.USOPQuantityAvailable, ParentProductTable.USOPSellPrice, ParentProductTable.ProductImageURL1, ParentProductTable.ProductImageURL2, ParentProductTable.ProductImageURL3, ParentProductTable.ProductImageURL4, ParentProductTable.ProductImageURL5, ParentProductTable.ProductImageURL6, ParentProductTable.ProductImageURL7, ParentProductTable.ProductImageURL8, ParentProductTable.ProductImageURL9, ParentProductTable.ProductImageURL10, ParentProductTable.ProductImageURL11, ParentProductTable.ProductImageURL12, ParentProductTable.ProductParentSKU
FROM ParentProductTable
UNION
SELECT "USSP " & [ProductNameTitle] AS ProductVariantNameTitle, [ProductParentSKU] & "USSP" AS ProductVariantSKU, ParentProductTable.Manufacture, ParentProductTable.MPN, ParentProductTable.UPC, ParentProductTable.ProductDescription, ParentProductTable.USSPQuantityAvailable, ParentProductTable.USSPSellPrice, ParentProductTable.ProductImageURL1, ParentProductTable.ProductImageURL2, ParentProductTable.ProductImageURL3, ParentProductTable.ProductImageURL4, ParentProductTable.ProductImageURL5, ParentProductTable.ProductImageURL6, ParentProductTable.ProductImageURL7, ParentProductTable.ProductImageURL8, ParentProductTable.ProductImageURL9, ParentProductTable.ProductImageURL10, ParentProductTable.ProductImageURL11, ParentProductTable.ProductImageURL12, ParentProductTable.ProductParentSKU
FROM ParentProductTable;
On our website a single webpage has the ParentProductInfo including title description and so on. Then we have a table with the condition code variant code and the pricing and quantity. Other websites say that every individual product condition has to have a single webpage so I have to duplicate the items 4 times. (I believe like you are suggesting.)
This is just my personal uneducated thought!---- I have been trying to avoid duplicating records 4 or 5 times. (One for Each Condition OR One for Each Condition and the Parent)
I thought this would causing Database bloating and slow the system down and cause Redundancy as in the titles being similiar. Also When entering information as the user I would have to create them four lines of record for each and this would take ALOT of work and time.
I know there is a way to pass on info to duplicate fields (I believe this is called looping) and combining fields for the titles to have the condition code in it. You even said something about this at the beginning about looping and combining code. I just don't know anything about that yet.
Like i said this is just to help you help me and let you know the end expected results. I will take whatever help I can get and not be pushy because I understand and I need the help. :) Beggars can't be choosers as they say! I cant thank you enough! I have been using the old database software and it is costing me to much time and frustration. Sometimes it even deletes stuff on me after I exit.(the current Software I have NOT Access). So this is HUGE and very important to me. God Bless!
you're welcome. I will have to look at this later -- can't get too distracted from what I am currently working on or I won't get it done today ...
Post your relationships diagram again. I will help you get your structure right in this thread. I assume you have done more since this morning. Once the relationships are good, unique indexes need to be set.
For other topics, you will need to ask another question.
For other topics, you will need to ask another question.
I have not done much as to this specific database since last. I really didn't want to mess anything up from what we did. I just studied more and watch your video and messed around with another database example to familiarize myself with things and testing. the biggest thing that is throwing me off is the record for each condition. That seems redundant. But maybe after we are done things will seem clearer when all the relationships are completed and I can see the full flow. Thanks.![db30.jpg]()
It is looking a lot better!
Size all fieldlists so all fields shows and there is not extra space on the right or below
Products (and the table above) should move to column 2 so that ProductSuppliers is in column 3, since a ProductID must be known before it can be used in that table (so a Product record needs to be created first). ProductImages, Customers, and Locations can them be placed in the same column.
ProductLocations needs LocID (which should move up, under ProductID) linked to Locations. ProductID should go to Products. OrderLineID should be deleted.
In OrderDetails, ProdLocID should move up under ProductID. It is a good idea to put key fields at the top.
Adjust fieldlists left or right so that "1" doesn't look like "11" on the diagram (ie the line between Products and ProductImages)
ProdVarSKU does not need to be in ProdLocations since it has ProductID
Make sure that no foreign keys have a default value of 0 (zero) in the table design (Access automatically does that!) -- you will need to delete this wherever you find it.
Also make sure the PK matches the tablename -- ie: if you want OrderLineID than call the table OrderLines. Details was just a suggestion.
> "the biggest thing that is throwing me off is the record for each condition"
don't know what you mean
Size all fieldlists so all fields shows and there is not extra space on the right or below
Products (and the table above) should move to column 2 so that ProductSuppliers is in column 3, since a ProductID must be known before it can be used in that table (so a Product record needs to be created first). ProductImages, Customers, and Locations can them be placed in the same column.
ProductLocations needs LocID (which should move up, under ProductID) linked to Locations. ProductID should go to Products. OrderLineID should be deleted.
In OrderDetails, ProdLocID should move up under ProductID. It is a good idea to put key fields at the top.
Adjust fieldlists left or right so that "1" doesn't look like "11" on the diagram (ie the line between Products and ProductImages)
ProdVarSKU does not need to be in ProdLocations since it has ProductID
Make sure that no foreign keys have a default value of 0 (zero) in the table design (Access automatically does that!) -- you will need to delete this wherever you find it.
Also make sure the PK matches the tablename -- ie: if you want OrderLineID than call the table OrderLines. Details was just a suggestion.
> "the biggest thing that is throwing me off is the record for each condition"
don't know what you mean
Ok all is done. I don't get you saying to create a record for each Condition of the item in the Products table. Instead of a ConditionsTable with linking, query, join or something to make one record "Yellow Bus Toy" in the Products table. This would be redundant and extra work for the user. I thought.
ie:
1: NSOP Yellow Bus Toy
2: NSSP Yellow Bus Toy
3: USOP Yellow Bus Toy
4: USSP Yellow Bus Toy
![db31.jpg]()
ie:
1: NSOP Yellow Bus Toy
2: NSSP Yellow Bus Toy
3: USOP Yellow Bus Toy
4: USSP Yellow Bus Toy
so a condition of the item includes color or something about its style? the fact that you have different SKUs means it does need its own record. It is always best to put the same type of information (such as SKU) in the same place. The product name could be blank if it is the same as the parent sku (the database could handle that happening) and then another field for what is different about it.
What kind of things might be different? color? size? what else?
_______
delete link from Locations to ProdLocations and create correct relationship on LocID
What kind of things might be different? color? size? what else?
_______
delete link from Locations to ProdLocations and create correct relationship on LocID
Ok I get what you are are saying.
Conditions:
NSOP = New Original Packaging (Brand New)
NSSP = New Our Packaging (New Repackaged)
USOP = Used Original Packaging (Used with original packaging shows signs of use.)
USSP = Used Our Packaging (Used repackaged shows signs of use.)
That is it. Not Like Shirts and hats that may have different colors and sizes.
OK THEIR ARE TWO SIDES OF ME! LOL!!! NOT REALLY JUST TWO SEPARATE THOUGHTS OF PLAN!
Let me know what you think about this.
Plan1: I think Queries would be the best thing for the conditions. I need the Tables set up Like they are supposed to and then use Queries to SELECT and Combine the information I need into a specific single Datasheet view. Which I can then use to import into our website and other peoples websites. I was thinking Tables like : ProductTable ProductVariantSKUTable ProductPriceTable ProductConditionCodeTable and then use Queries to Combine the chopped up info.....
Plan2: Setup like you are telling me with the each Record for each SKU (Which is Just ParentSKU plus NSOP NSSP USOP USSP suffixed). Then Use Queries to create a form for simplifying the process for inserting information into multiple lines of record at once.
In Plan 2 I can see some ups for history keeping and Reports later of what kind or condition of product we sell more.
My only thing is I am afraid that the database will become sluggish after 10,000 -50,000 records that are duplicated into 40,000 -200,000 and that the user would have to enter info manually into several areas multiple times. I already have 4,000 items in our current situation and if I was to transfer this into access with a record for each condition that would be 16,000 items.
Conditions:
NSOP = New Original Packaging (Brand New)
NSSP = New Our Packaging (New Repackaged)
USOP = Used Original Packaging (Used with original packaging shows signs of use.)
USSP = Used Our Packaging (Used repackaged shows signs of use.)
That is it. Not Like Shirts and hats that may have different colors and sizes.
OK THEIR ARE TWO SIDES OF ME! LOL!!! NOT REALLY JUST TWO SEPARATE THOUGHTS OF PLAN!
Let me know what you think about this.
Plan1: I think Queries would be the best thing for the conditions. I need the Tables set up Like they are supposed to and then use Queries to SELECT and Combine the information I need into a specific single Datasheet view. Which I can then use to import into our website and other peoples websites. I was thinking Tables like : ProductTable ProductVariantSKUTable ProductPriceTable ProductConditionCodeTable and then use Queries to Combine the chopped up info.....
Plan2: Setup like you are telling me with the each Record for each SKU (Which is Just ParentSKU plus NSOP NSSP USOP USSP suffixed). Then Use Queries to create a form for simplifying the process for inserting information into multiple lines of record at once.
In Plan 2 I can see some ups for history keeping and Reports later of what kind or condition of product we sell more.
My only thing is I am afraid that the database will become sluggish after 10,000 -50,000 records that are duplicated into 40,000 -200,000 and that the user would have to enter info manually into several areas multiple times. I already have 4,000 items in our current situation and if I was to transfer this into access with a record for each condition that would be 16,000 items.
ProductTable ProductVariantSKUTable ProductPriceTable ProductConditionCodeTable -->
Products, ProductVariants, Prices, ConditionCodes with CondCodeID autonumber PK then CondCodeID Long Integer foreign key would be in ProductVariants
in ConditionCodes there would also be CondCode short text,size=4 with a unique index.
> "Setup like you are telling me with the each Record for each SKU (Which is Just ParentSKU plus NSOP NSSP USOP USSP suffixed). Then Use Queries to create a form for simplifying the process for inserting information into multiple lines of record at once."
well perhaps not -- the SKU would then have 2 controls on the form, one for the SKU and another for the condition
Products, ProductVariants, Prices, ConditionCodes with CondCodeID autonumber PK then CondCodeID Long Integer foreign key would be in ProductVariants
in ConditionCodes there would also be CondCode short text,size=4 with a unique index.
> "Setup like you are telling me with the each Record for each SKU (Which is Just ParentSKU plus NSOP NSSP USOP USSP suffixed). Then Use Queries to create a form for simplifying the process for inserting information into multiple lines of record at once."
well perhaps not -- the SKU would then have 2 controls on the form, one for the SKU and another for the condition
in ConditionCodes there would also be CondCode short text,size=4 with a unique index.
CondCode I Chose Indexed Yes No duplicates...Correct?
well perhaps not -- the SKU would then have 2 controls on the form, one for the SKU and another for the condition
What do you mean by 2 controls on the form. There is only one spot. Subform Seperate Controls or coding for 2 controls into the single space for Control?
In Prices Table would make a field called Price OR 4 fields one for each price condition NSOPPrice, NSSPPrice,USOPPrice, USSPPrice.
In ConditionCodes I made 4 records NSOP NSSP USOP USSP 1 2 3 4
this is what I have so far:
> "CondCode I Chose Indexed Yes No duplicates...Correct?"
yes. To indicate a field has a unique index, I generally move it above the PK in the order so it is apparent when you look at the Relationships diagram
> "What do you mean by 2 controls on the form. There is only one spot. Subform Seperate Controls or coding for 2 controls into the single space for Control?"
didn't you make the form? Another control can be added for the condition. While you may combine it for reporting, it is actually 2 pieces of information: an sku and a condition
> "In Prices Table would make a field called Price OR 4 fields one for each price condition NSOPPrice, NSSPPrice,USOPPrice, USSPPrice."
and here is where it gets tricky and why I suggested to put the variants into the Product table ... under no circumstances should you put price in 4 places unless they are different prices (retail, cost, etc)
If you do it this way, then a variant will not have information duplicated that can be found for the parent sku.
If WILL have ConditionID.*
* "ConditionCodes" --> Conditions
perhaps "Conditions" is a better word with autonumber ConditionID as the PK.
code is an extra word that need only be used for the text field to describe the condition
when designing database structure, there is a lot of back-and-forth ... trying things to see if they look right and fit with the logic
yes. To indicate a field has a unique index, I generally move it above the PK in the order so it is apparent when you look at the Relationships diagram
> "What do you mean by 2 controls on the form. There is only one spot. Subform Seperate Controls or coding for 2 controls into the single space for Control?"
didn't you make the form? Another control can be added for the condition. While you may combine it for reporting, it is actually 2 pieces of information: an sku and a condition
> "In Prices Table would make a field called Price OR 4 fields one for each price condition NSOPPrice, NSSPPrice,USOPPrice, USSPPrice."
and here is where it gets tricky and why I suggested to put the variants into the Product table ... under no circumstances should you put price in 4 places unless they are different prices (retail, cost, etc)
If you do it this way, then a variant will not have information duplicated that can be found for the parent sku.
If WILL have ConditionID.*
* "ConditionCodes" --> Conditions
perhaps "Conditions" is a better word with autonumber ConditionID as the PK.
code is an extra word that need only be used for the text field to describe the condition
when designing database structure, there is a lot of back-and-forth ... trying things to see if they look right and fit with the logic
didn't you make the form?Yes i apoligize I was thinking Control Source not a Control like a textbox or so on...
under no circumstances should you put price in 4 places unless they are different prices (retail, cost, etc)
The prices will be different.
IE:
NSOP: 29.95
NSSP: 24.50
USOP: 17.50
USSP: 12.50
they are still prices and therefore, belong in the same field
do you ever sell a product withOUT a condition? or do they always have something specified?
do you ever sell a product withOUT a condition? or do they always have something specified?
They always have a condition of NSOP NSSP USOP USSP . That is the simplicity of our operation. Everything meets one of our standards of one of the condition code or it is scrap or trash.
What that is currently in the Products table relates directly to the parent product and what relates to the variant product?
What that is currently in the Products table relates directly to the parent product and what relates to the variant product?
I am sorry I don't get what you are talking about.
Product_ is this what you are talking about If so can you please explain how a order or such would know which variant to choose from instead of just the product itself.
there should be 2 tables -- trying to help you separate those fields then we can figure out what to call them.
You lost me! I have the Products_1 table and the Products table linked in the self join. Are you saying you are you are kinda stumped or....
I thought your parent SKU was actually a product but it turns out that it is only a product when combined with a condition -- so the 2 tables would be separate and yes, that means the relationship and ProductID_ field get deleted, in that order. Perhaps what is now Products will be SKUs with SkuID autonumber, and Products table will have SkuID foreign key, ConditionID, Price, etc
I thought your parent SKU was actually a product but it turns out that it is only a product when combined with a condition
100% Correct!
so the 2 tables would be separate and yes, that means the relationship and ProductID_ field get deleted, in that order.
So I delete the relationship between tables Products and Products_1.
Deleteded ProductID_ field from the Products Table and I can remove the Products_1 table from my relationships display correct?
Ok so I now have changed Products Table to SKUs Table and Changed the field ProductsID to SkuID. I Have added a Products Table with Fields ProductID as the PK, SkuID, ConditionID, Price.
In the other tables (ProdLocations, ProductImages, ProductSuppliers, Order Details) Field that were linked to the Products table that is now known as the SKUs Table. Do I change the Linked fields to SkusID and keep them linked or will they be linked to the new Products table field ProductsID
Move Products to the right of SKUs and create a relationship. Products replaces ProductVariants (which I didn't like the name of anyway).
ProductID has no 's' -- each record is just one product. I see you took 'name the ID field like the table' literally ... I just meant to keep the idea the same ;)
on the existing relationships to SKUs: you will need to decide when the foreign key is ProductID (in which case, it goes to Products) or is renamed to SkuID.
I don't think Prices need to be a separate table unless you have pricing structures.
I am going to be away for awhile -- do what you can on your own.
ProductID has no 's' -- each record is just one product. I see you took 'name the ID field like the table' literally ... I just meant to keep the idea the same ;)
on the existing relationships to SKUs: you will need to decide when the foreign key is ProductID (in which case, it goes to Products) or is renamed to SkuID.
I don't think Prices need to be a separate table unless you have pricing structures.
I am going to be away for awhile -- do what you can on your own.
Products replaces ProductVariants (which I didn't like the name of anyway).
Psshhh Whatever LOL...Im not creative just simplicity get it done kinda person. You can call anything whatever you like as long I understand the operation and it works! Thank you!
I see you took 'name the ID field and table' literally ... I just meant to keep the idea the same ;)
Once I follow exact directions and understand I can run from there. :)
on the existing relationships to SKUs: you will need to decide when the foreign key is ProductID (in which case, it goes to Products) or is renamed to SkuID.
I'm lost on that. What do you mean. Example?
on the existing relationships to SKUs: you will need to decide when the foreign key is ProductID (in which case, it goes to Products) or is renamed to SkuID.
Ok so say we order new ones from a supplier and used ones from somewhere else. Then I would link the SKUs Table to the ProductSuppliers Table and Rename the field.
vs
If we order all conditions from a supplier no matter the condition. I would link the Products table with the ProductSuppliers Table
Ok I will share with you a screen shot of the current software database I am using.
We are currently only able to store a product (or a single Product Condition) in one single area. Yes that is great for organization but sometimes you need to store items in 2 areas. You can see the inventory list at the bottom. There is condition, Location, Total Quantity, Available Quantity, Sell Price, Purchase Price, Restock point.
We track it like this. We print out a label and on that label is the product ParentSKU and the Condition Code. We place the label with the product item and then store it in a inventory location. The customer orders and we find out the inventory location from the PrimaryParentSKU. We take the PrimaryParentSKU and search for it in the database that we have and look at the bottom. Location ....for condition.... and then go find that inventory location. Then look for the product with the matching label.
so you do you track the location of individual variations ... in that case, it should be ProductID in the ProdLocations table.
Remember, with a ProductID, the SKU can always be looked up. I would imagine the main thing you need SKU for is rolling up for management reports
On barcoding: it is really easy to have Access print those for you too since the barcode printer is just another device and a barcode just another font.
Remember, with a ProductID, the SKU can always be looked up. I would imagine the main thing you need SKU for is rolling up for management reports
On barcoding: it is really easy to have Access print those for you too since the barcode printer is just another device and a barcode just another font.
Yes we track individual Variations. As when a customer orders they order by variation they want. I linked the ProductID from the Products Table to the ProductID in the ProdLocations Table. What about the OrderDetails Table and the ProductSuppliers Table Relationships?
I would imagine ProdSuppliers needs to link to Products as well -- this enables you to get one variation from one supplier and a different variation from another. You might also have a default supplier for the SKU in which case SkuID would be in ProdSuppliers as well.
If you put both ProductID and SkuID in the ProdSuppliers table, you would only want the user to fill out one or the other on each record.
OrderDetails : since you said you don't have products without conditions then it would also use ProductID. In comboboxes to enter, the SKU and Condition Code can be concatenated to pick from a list of ProductID
If you put both ProductID and SkuID in the ProdSuppliers table, you would only want the user to fill out one or the other on each record.
OrderDetails : since you said you don't have products without conditions then it would also use ProductID. In comboboxes to enter, the SKU and Condition Code can be concatenated to pick from a list of ProductID
Ok could you give me a break down of how about how to insert info as a whole into this database? I know it would be a multi table form to have something like what I am working with currently as seen in the last photo. So far in the tables we have set up I can enter the info of Product Name, Manufacture, MPN (Manufacture Part Number),Product Description, UPC, Image URLs as needed. But I am not understanding where to put the main ParentSKU. Are we still with the one line entry record for every condition. Sorry if I am confused by this. I just want to be on the same page.
![db36.jpg]()
> "Manufacture"
you should have a table for Manufacturers with ManufID autonumber primary key
> "how to insert info"
you would create forms for maintaining information in each of the tables
> "where to put the main ParentSKU"
you would have a table and form for the SKU table -- and you need to rename fields in there
on the Orders form, there would be a combobox for picking a ProductID, which is what would be stored. Behind the scenes, the RowSource for the combo would be a query or SQL statements based on the Product, Sku, and conditions tables.
0: ProductID
1: SKUs.Sku & "-" & Conditions.CondCode 'if you separate with dash
columns indexes start with 0.
ColumnWidths: 0;1.5 'or whatever width is best to show Sku and condition code -- cm: 0;4cm
ListWidth: 1.7 'sum of column widths + 0.2" to allow for scrollbar -- or add 0.5cm
did you read my Access Basics book? It is short (100 pages) with lots of pictures :)
you should have a table for Manufacturers with ManufID autonumber primary key
> "how to insert info"
you would create forms for maintaining information in each of the tables
> "where to put the main ParentSKU"
you would have a table and form for the SKU table -- and you need to rename fields in there
on the Orders form, there would be a combobox for picking a ProductID, which is what would be stored. Behind the scenes, the RowSource for the combo would be a query or SQL statements based on the Product, Sku, and conditions tables.
0: ProductID
1: SKUs.Sku & "-" & Conditions.CondCode 'if you separate with dash
columns indexes start with 0.
ColumnWidths: 0;1.5 'or whatever width is best to show Sku and condition code -- cm: 0;4cm
ListWidth: 1.7 'sum of column widths + 0.2" to allow for scrollbar -- or add 0.5cm
did you read my Access Basics book? It is short (100 pages) with lots of pictures :)
Ok I have the Manufactures Table set up and linked to the Products Table.
The Forms i totally get. Its just that sometimes things don't click well for me or I overthink them or something.
Is this a new table or the SKUs Table that we have already?
I did read over some of the book not all of it. I try my very best to fit alot of stuff in and thank you very much. I did watch your videos and they were really good. I usually have to see things or hands on experience to understand things. Alot of stuff reading is not enough. I thank you very much for your time and patience. And with this mess you have great patience for sure.
![db37.jpg]()
The Forms i totally get. Its just that sometimes things don't click well for me or I overthink them or something.
> "where to put the main ParentSKU"
you would have a table and form for the SKU table
Is this a new table or the SKUs Table that we have already?
I did read over some of the book not all of it. I try my very best to fit alot of stuff in and thank you very much. I did watch your videos and they were really good. I usually have to see things or hands on experience to understand things. Alot of stuff reading is not enough. I thank you very much for your time and patience. And with this mess you have great patience for sure.
thank you ~ glad you like the videos. Wish I had time to make more of them It is a pleasure to help someone who does what I advise ;)
you already have a table for SKUs
remember to keep "1" on the left and "many" on the right when you position tables on the relationships diagram -- not many people besides me will tell you to do this ... but I am an engineer so I like things to be as logical as they possibly can. You can see the data flow much better.
I see you have a field called LocNotes. Be sure this is Short Text and not Long Text as Long Text fields should be handled differently.
Now to size ... Access defaults Short Text to 255. Personally, I change the default to 50. It is important to consider how much you want to allow users to enter. For instance, you wouldn't want address lines to be that long or they would not fit on a label. For names, I allow 75 for company name and 50 for first or last name (which is still a bit long but some people have really long names).
For optimizing size: put sample data in all your tables. Make it as long as you think it will be. Download and run this:
free Analyzer for Microsoft Access
http://analyzer.codeplex.com
Look at the Deep Analysis report to see the maximum length for characters used.
you already have a table for SKUs
remember to keep "1" on the left and "many" on the right when you position tables on the relationships diagram -- not many people besides me will tell you to do this ... but I am an engineer so I like things to be as logical as they possibly can. You can see the data flow much better.
I see you have a field called LocNotes. Be sure this is Short Text and not Long Text as Long Text fields should be handled differently.
Now to size ... Access defaults Short Text to 255. Personally, I change the default to 50. It is important to consider how much you want to allow users to enter. For instance, you wouldn't want address lines to be that long or they would not fit on a label. For names, I allow 75 for company name and 50 for first or last name (which is still a bit long but some people have really long names).
For optimizing size: put sample data in all your tables. Make it as long as you think it will be. Download and run this:
free Analyzer for Microsoft Access
http://analyzer.codeplex.com
Look at the Deep Analysis report to see the maximum length for characters used.
remember to keep "1" on the left and "many" on the right when you position tables on the relationships diagram -- not many people besides me will tell you to do this ... but I am an engineer so I like things to be as logical as they possibly can. You can see the data flow much better.
Good Eye! I was all like but I did and then I seen it LOL!
I see you have a field called LocNotes. Be sure this is Short Text and not Long Text as Long Text fields should be handled differently.
Honestly don't know if I even need it. But if so it is short text.
OK you said long text is handled differently. In the SKUs The ProdDescr might be longer as it is a product description as in product specifications. Info that goes to all the conditions.
will get some data in and run that.
255 characters goes a long way :) ~ and is MUCH longer than needed in most cases.
once you have run the Analyzer, create a query with this SQL:
SELECT T.Tbl, F.Fld, F.DefVal, F.DbID
FROM a_Tbls AS T INNER JOIN a_Flds AS F ON T.TID = F.TID
WHERE ( (F.Fld Like "*ID") AND (F.DefVal="0") AND (F.DbID=DMax("DbID","a_DBs
ORDER BY T.Tbl;
It will tell you where you have forgotten to delete the Default Value of 0 (zero) in your foreign key fields for the analysis you just did.
... and Locations is still linking to the wrong field in ProdLocations
ProdLocations does not need ConditCode -- this can be known from ProductID
I see DateCreated in your SKUs table ... I have code I can give you when this is done to add tracking fields to all your tables (ie: dtmAdd for date/time added and dtmEdit for date/time edited -- dtmEdit will then be updated by forms)
In OrderDetails, rename SellPrice to PriceSell since another report the Analyzer can give you is an alphabetical list of all the fields in your database -- that way Price and PriceSell will sort next to each other. I also like to name date fields starting with dt for the same reason (OrderDate -dtOrder) ... or dtm if time is also important.
you have Quantity in both ProdLocations and OrderDetails. It is a good idea to name fields such that the only duplicated names are key fields. Perhaps Qty for OrderDetails and QtyLoc , QtyAvail in ProdLocations.
unique indexes:
just like CondCode is a unique index in Conditions, some of the other tables should also have unique indexes such as Manufacturers (ManuName --be consistent. If you are using Nm for name, use it everywhere), SKUs (you should have a field in there called SKU), Locations (LocName -- again, be consistent), Suppliers, (SuppNm), etc.
once you have run the Analyzer, create a query with this SQL:
SELECT T.Tbl, F.Fld, F.DefVal, F.DbID
FROM a_Tbls AS T INNER JOIN a_Flds AS F ON T.TID = F.TID
WHERE ( (F.Fld Like "*ID") AND (F.DefVal="0") AND (F.DbID=DMax("DbID","a_DBs
ORDER BY T.Tbl;
It will tell you where you have forgotten to delete the Default Value of 0 (zero) in your foreign key fields for the analysis you just did.
... and Locations is still linking to the wrong field in ProdLocations
ProdLocations does not need ConditCode -- this can be known from ProductID
I see DateCreated in your SKUs table ... I have code I can give you when this is done to add tracking fields to all your tables (ie: dtmAdd for date/time added and dtmEdit for date/time edited -- dtmEdit will then be updated by forms)
In OrderDetails, rename SellPrice to PriceSell since another report the Analyzer can give you is an alphabetical list of all the fields in your database -- that way Price and PriceSell will sort next to each other. I also like to name date fields starting with dt for the same reason (OrderDate -dtOrder) ... or dtm if time is also important.
you have Quantity in both ProdLocations and OrderDetails. It is a good idea to name fields such that the only duplicated names are key fields. Perhaps Qty for OrderDetails and QtyLoc , QtyAvail in ProdLocations.
unique indexes:
just like CondCode is a unique index in Conditions, some of the other tables should also have unique indexes such as Manufacturers (ManuName --be consistent. If you are using Nm for name, use it everywhere), SKUs (you should have a field in there called SKU), Locations (LocName -- again, be consistent), Suppliers, (SuppNm), etc.
Ok the Price field in Products Table didn't carry over to the OrderDetails SellPrice Field. Normal or not....
> "Price field in Products Table didn't carry over to the OrderDetails SellPrice Field. Normal or not...."
don't know what you mean
> Also I ran the analyzer? Now what?
great!
"Look at the Deep Analysis report to see the maximum length for characters used."
There are 2 numbers after Data Type (ShTxt, Lng, etc) for each text (short and long) field. The first is how long it is defined to be, the second is the maximum number of characters you actually used -- this can help with sizing. I generally round up for fields that don't have a set length for values.
You can right-click on an Access report while you are looking at it (Print Preview) and choose Export > PDF. Then you can attach that file so I can better see what you have.
re-read my last message -- I made a lot of comments there ...edited it after you probably read it. There is a lot to consider and do.
don't know what you mean
> Also I ran the analyzer? Now what?
great!
"Look at the Deep Analysis report to see the maximum length for characters used."
There are 2 numbers after Data Type (ShTxt, Lng, etc) for each text (short and long) field. The first is how long it is defined to be, the second is the maximum number of characters you actually used -- this can help with sizing. I generally round up for fields that don't have a set length for values.
You can right-click on an Access report while you are looking at it (Print Preview) and choose Export > PDF. Then you can attach that file so I can better see what you have.
re-read my last message -- I made a lot of comments there ...edited it after you probably read it. There is a lot to consider and do.
SELECT T.Tbl, F.Fld, F.DefVal, F.DbID
FROM a_Tbls AS T INNER JOIN a_Flds AS F ON T.TID = F.TID
WHERE ( (F.Fld Like "*ID") AND (F.DefVal="0") AND (F.DbID=DMax("DbID","a_DBs")) )
ORDER BY T.Tbl;
Blank report I assume I did good LOL!
When inserting info. I inserted the price in the products table but in the ordersdetail table it was blank 0.00
I couldnt find the print to PDF but anyways it is showing all kinds of fields and tables that no longer exist
> Blank report I assume I did good LOL!
awesome! By the way, the correct term is Datasheet since this is a query not a report.
>When inserting info. I inserted the price in the products table but in the ordersdetail table it was blank 0.00
as it would be. On a form, once you choose a product from a combobox, code can automatically fill the price that can then be over-ridden by the user
when you compact/repair the database, which you should only do after you have backed it up, the ~tables should go away.
> "I couldnt find the print to PDF"
right-click on a report while you are looking at it in Print Preview (the view that the Analyzer uses). From the shortcut menu, choose Export and then PDF.
you should figure out how to do this.
btw, when you open the Analyzer again, it will open to a blank record. there is a find combobox at the top of the form to pull up a previous analysis.
__________________________
I realize you prefer messages to be short with only a few instructions ... however I do not want to repeat what I wrote in the long message (starting with '255 characters goes a long way' ) before you got back on, so please go re-read it and do/consider everything.
remember when you create a Unique Index on a field to move it up in the order do it is obvious when you look at the Relationships Diagram ... this is another thing I will recommend that you probably won't hear from others. Although I did not specifically suggest this, I hope by now you are catching on to 'my' good practice (since I am the one currently helping you).
On Order date -- only use dtmOrder if you do care about time -- I see you did this (as well as other suggestions -- great!). If only order date is important for reporting, only use dt. The reason this is important is because date/time DOES also record time so if only date is important, stripping time needs to be done.
I still see "Prod" fields in the SKUs table.
In OrderDetails-- don't move OrderID, ProductID, and ProdLocID above the Pk -- keep them below. Only move fields above when they have a unique index. Again, this is just something I do to help keep things straight that I am passing on to you.
the Manufacturers table needs to be dragged (you can drag tables from Navigation Pane) back onto the diagram. If you rename tables when the diagram is not open, they disappear (the relationships do not)
awesome! By the way, the correct term is Datasheet since this is a query not a report.
>When inserting info. I inserted the price in the products table but in the ordersdetail table it was blank 0.00
as it would be. On a form, once you choose a product from a combobox, code can automatically fill the price that can then be over-ridden by the user
when you compact/repair the database, which you should only do after you have backed it up, the ~tables should go away.
> "I couldnt find the print to PDF"
right-click on a report while you are looking at it in Print Preview (the view that the Analyzer uses). From the shortcut menu, choose Export and then PDF.
you should figure out how to do this.
btw, when you open the Analyzer again, it will open to a blank record. there is a find combobox at the top of the form to pull up a previous analysis.
__________________________
I realize you prefer messages to be short with only a few instructions ... however I do not want to repeat what I wrote in the long message (starting with '255 characters goes a long way' ) before you got back on, so please go re-read it and do/consider everything.
remember when you create a Unique Index on a field to move it up in the order do it is obvious when you look at the Relationships Diagram ... this is another thing I will recommend that you probably won't hear from others. Although I did not specifically suggest this, I hope by now you are catching on to 'my' good practice (since I am the one currently helping you).
On Order date -- only use dtmOrder if you do care about time -- I see you did this (as well as other suggestions -- great!). If only order date is important for reporting, only use dt. The reason this is important is because date/time DOES also record time so if only date is important, stripping time needs to be done.
I still see "Prod" fields in the SKUs table.
In OrderDetails-- don't move OrderID, ProductID, and ProdLocID above the Pk -- keep them below. Only move fields above when they have a unique index. Again, this is just something I do to help keep things straight that I am passing on to you.
the Manufacturers table needs to be dragged (you can drag tables from Navigation Pane) back onto the diagram. If you rename tables when the diagram is not open, they disappear (the relationships do not)
Ok I thank you very very MUCH! for your help. I will be off tonight as I am still at work from 10am and it is now 2am. I have to back in here in about 6 more hours to do another 12+ hour shift so...When I get back in in the morning I will review over everything and do all of this. If there is any other suggestions please just list them right up and I will do it also before responding back to you. Again thank you!
you're welcome, Dustin
... and I see the Manufacturers table is not gone.
Still "Prod" fields in the SKUs table -- they need to be renamed or moved.
Set unique indexes and adjust order of those fields above PK.
btw, if you want to see all the indexes on a table, click the lightening bolt icon when you are looking at table design... there are probably way more indexes than you want or need. Access automatically creates indexes for you based on what you name fields. You can (and should) stop that from happening ( covered in my Access Basics book in the Normalization or Relationships chapter -- hope you read both of those thoroughly and if not, do so).
You have a few tables that collect address/email/phone/websit
... as you have probably discovered, I tend to edit my previous comment as opposed to making a new post until I can't since you respond -- so even after you write another message, refresh the page to see if I added anything else ;)
... and I see the Manufacturers table is not gone.
Still "Prod" fields in the SKUs table -- they need to be renamed or moved.
Set unique indexes and adjust order of those fields above PK.
btw, if you want to see all the indexes on a table, click the lightening bolt icon when you are looking at table design... there are probably way more indexes than you want or need. Access automatically creates indexes for you based on what you name fields. You can (and should) stop that from happening ( covered in my Access Basics book in the Normalization or Relationships chapter -- hope you read both of those thoroughly and if not, do so).
You have a few tables that collect address/email/phone/websit
... as you have probably discovered, I tend to edit my previous comment as opposed to making a new post until I can't since you respond -- so even after you write another message, refresh the page to see if I added anything else ;)
aah! you figured out how to make a report into a PDF -- this is good so you can share with others who don't have your database.
I see places where you copied fields. CustAdd1, for instance. I also see that you did not completely populate tables with sample data -- a task for tomorrow. When you fill sample data, put a value in every field including foreign keys*. Once you do this, run the Analyzer again.
*It helps to change the Document Window Options to Overlapping Windows so you can see more than one thing at a time. File > Options > Current Database > Document Window Options - Overlapping Windows
Zip should be Short Text even though its values are numbers since you won't do math on it (get an average, a sum, etc). Also some zips start with "0" and using Number for data type means you would need a format code to show this.
Nice that you have filled filed Description for some of the fields ... if you don't have any more to say, at least copy the field name to the description. This, btw, will be Status Bar Text (what is displayed in the lower left) for each control when you create forms ... so users will see it unless you change it for that control on the form. For PK fields, I generally just put the fieldname there. Realize that when you copy structure to create a new table or fields, the description should change too. (ie: ProdSuppID says Product Supplier Unique Identification Number -- this means nothing to users but, by now, this should be descriptive stuff that you don't need either -- just put fieldname there again as it is probably NOT the unique identification number used for tracking by your company for the supplier and could be misinterpreted)
I see places where you copied fields. CustAdd1, for instance. I also see that you did not completely populate tables with sample data -- a task for tomorrow. When you fill sample data, put a value in every field including foreign keys*. Once you do this, run the Analyzer again.
*It helps to change the Document Window Options to Overlapping Windows so you can see more than one thing at a time. File > Options > Current Database > Document Window Options - Overlapping Windows
Zip should be Short Text even though its values are numbers since you won't do math on it (get an average, a sum, etc). Also some zips start with "0" and using Number for data type means you would need a format code to show this.
Nice that you have filled filed Description for some of the fields ... if you don't have any more to say, at least copy the field name to the description. This, btw, will be Status Bar Text (what is displayed in the lower left) for each control when you create forms ... so users will see it unless you change it for that control on the form. For PK fields, I generally just put the fieldname there. Realize that when you copy structure to create a new table or fields, the description should change too. (ie: ProdSuppID says Product Supplier Unique Identification Number -- this means nothing to users but, by now, this should be descriptive stuff that you don't need either -- just put fieldname there again as it is probably NOT the unique identification number used for tracking by your company for the supplier and could be misinterpreted)
Should all the ID fields be indexed no duplicates in every table. Like ProductID in tables Products, OrderDetails, ProductSuppliers, ETC.....
So to the unique indexed fields only go above the primary key or all ID fields?
I do care about time because that can help clear the air if some errors occur.
I have done everything but completed the reading of the two chapters. Working on that now. Here is the Analyzer report and screen shot
![db42.jpg]()
a_r_DeepAnalysis2.pdf
So to the unique indexed fields only go above the primary key or all ID fields?
I do care about time because that can help clear the air if some errors occur.
I have done everything but completed the reading of the two chapters. Working on that now. Here is the Analyzer report and screen shot
a_r_DeepAnalysis2.pdf
When you define a field(s) as the primary key, Access automatically creates a unique index. You should NOT create a separate one. Access also creates a hidden index for the foreign key in any relationship so you don't need to create non-unique indexes on those fields either. So for example, there is a hidden index on SupplierID in ProductSuppliers table. If you actually want to see the hidden indexes, you can look in the MSys tables which are normally hidden. You can unhide the system tables using an option on the navigation pane dialog.
You DO need to create indexes on fields that are not part of a defined key or relationship but on which you want to search/filter. CompNm would be an example of a field you might choose to index.
Earlier when you posted a picture of some indexes, there were several dups that should be deleted. And the index that I recommended that you create was incorrect. The unique index you create to enforce business rules should NEVER include the table's surrogate key. That defeats the purpose and will allow you to add logical duplicates.
For example. You create a State table and someone convinces you that you should use a surrogate key so you add an autonumber ID and that is the field that will participate in all relationships. However, state code still must be unique so you would add a unique index on the state code to ensure that no one enters a second record for CT by accident. The StateID for CT is 7. If you make the unique index on StateID AND StateCD, you will be able to add a new record with an ID of 51 and a StateCD of CT because the combination of 51+CT is different from 7+CT. That is why the index to enforce the business rule that StateCD must be unique must only include StateCD.
You DO need to create indexes on fields that are not part of a defined key or relationship but on which you want to search/filter. CompNm would be an example of a field you might choose to index.
Earlier when you posted a picture of some indexes, there were several dups that should be deleted. And the index that I recommended that you create was incorrect. The unique index you create to enforce business rules should NEVER include the table's surrogate key. That defeats the purpose and will allow you to add logical duplicates.
For example. You create a State table and someone convinces you that you should use a surrogate key so you add an autonumber ID and that is the field that will participate in all relationships. However, state code still must be unique so you would add a unique index on the state code to ensure that no one enters a second record for CT by accident. The StateID for CT is 7. If you make the unique index on StateID AND StateCD, you will be able to add a new record with an ID of 51 and a StateCD of CT because the combination of 51+CT is different from 7+CT. That is why the index to enforce the business rule that StateCD must be unique must only include StateCD.
Ok Thank you. I did the customer UniqueID as the CustFNm, CustLNm, and CustEmail. So that should be good.
thanks, Pat! -- I was tied up today
On using FK for states, however, I believe it is better to store since 2 character state abbreviation (in the US anyway) since space is the same for 2 characters or Long Integer and it does help to see the natural information in an address.
Dustin, the hidden relationships that Pat mentioned are shown on the Analyzer reports -- so, as long as you create a relationships with referential integrity (a real relationship), the only indexes you usually need to additionally define will be for unique indexes. Delete the rest of the visible indexes (except PrimaryKey) since they are just clutter -- and often repeats of something else Access already creates.
Looking good!
On using FK for states, however, I believe it is better to store since 2 character state abbreviation (in the US anyway) since space is the same for 2 characters or Long Integer and it does help to see the natural information in an address.
Dustin, the hidden relationships that Pat mentioned are shown on the Analyzer reports -- so, as long as you create a relationships with referential integrity (a real relationship), the only indexes you usually need to additionally define will be for unique indexes. Delete the rest of the visible indexes (except PrimaryKey) since they are just clutter -- and often repeats of something else Access already creates.
Looking good!
Ok I did not understand how to unhide the systems table to delete the clutter. Also I will be heading out soon and I will be back at 7am. So if there is anything you want me to do and I know there is ;) please list it up and I will get it done in the morning and update you. Thank you!
> "how to unhide the systems table to delete the clutter"
you mean indexes? Click on the lightening bolt icon in table design on the Design ribbon tab -- this will open the Indexes window. You will see row(s) for indexes that are defined. There should be PrimaryKey and whatever unique indexes you have created. Other rows, at this point anyway, are duplicates of something else already defined (often PrimaryKey is duplicated and called something different). That is why I had you modify defaults to NOT create additional indexes since by default, if Access sees "ID", "Num", "Code", or "Key" in a fieldname, it will make an index.
Down the road, you may choose to add other indexes for fields that are used for searching that are not already indexed.
you mean indexes? Click on the lightening bolt icon in table design on the Design ribbon tab -- this will open the Indexes window. You will see row(s) for indexes that are defined. There should be PrimaryKey and whatever unique indexes you have created. Other rows, at this point anyway, are duplicates of something else already defined (often PrimaryKey is duplicated and called something different). That is why I had you modify defaults to NOT create additional indexes since by default, if Access sees "ID", "Num", "Code", or "Key" in a fieldname, it will make an index.
Down the road, you may choose to add other indexes for fields that are used for searching that are not already indexed.
great!
oh, and something I didn't mention that I hope you did not do : do not make that any field Required in the table design. This is best handled by forms.
Perhaps it is time to close this question and gather your thoughts for the next one ;) ~
oh, and something I didn't mention that I hope you did not do : do not make that any field Required in the table design. This is best handled by forms.
Perhaps it is time to close this question and gather your thoughts for the next one ;) ~
Dustin, I objected to this because Pat helped you too ... also pick better posts for the solution ~ thanks... and you're welcome
Thanks for using Experts Exchange.
Please provide your email to receive a free trial preview!
*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.