Solved

MS Access Product Variables Variations Multiple Product Conditions Table and Relationship Setup For Customer Orders and Inventory

Posted on 2016-09-24
133
112 Views
Last Modified: 2016-09-28
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.jpgdb7.jpgdb8.jpgdb9.jpgdb10.jpgdb11.jpg
0
Comment
Question by:Dustin Stanley
  • 67
  • 57
  • 8
133 Comments
 
LVL 19
ID: 41814162
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:
   ProductID_, Number, Long Integer, DefaultValue = Null (nothing -- delete 0 if Access adds it)

Open in new window

this also solves the problem of blahblahProductAvailable and the variant SKUs (I have code I can share with you to Loop and combine values from related Records).

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.
0
 

Author Comment

by:Dustin Stanley
ID: 41814171
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
0
 
LVL 19
ID: 41814177
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.
0
 

Author Comment

by:Dustin Stanley
ID: 41814181
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.
0
 
LVL 19
ID: 41814216
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.
0
 

Author Comment

by:Dustin Stanley
ID: 41814237
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
0
 
LVL 19
ID: 41814246
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
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 250 total points
ID: 41814248
All the prefixed/suffixed fields in ParentProductTable need to move to InventoryLocationTable where they will be called QuantityAvailable, SellPrice, and ProductVariantSKU

LocationName belongs in InventoryLocationTable.

ProductVariantTypesTable needs a relationship with InventoryLocationTable

OrderProductTable should point to InventoryLocationTable.InventoryPlacementID rather than ProductParentSKU

OrderProductTable should include SellPrice.  The price at the time of sale is kept with the order.

CustomerOrderTable should contain a shipping address.

ProductImagesTable should contain only a single image per record.  That way you can store as many or as few images as desired for each item.

ProductImagesTable should point to InventoryLocationTable if the images are specific to the ConditionCode.
0
 

Author Comment

by:Dustin Stanley
ID: 41814249
How does this look so far and THANK YOU for the help. It truly is a blessing!db14.jpg
0
 

Author Comment

by:Dustin Stanley
ID: 41814253
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
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41814254
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 InventoryLocationMasterListTable but if you have it, then InventoryLocation in the inventoryLocation table must be changed to LocationID and join on LocationID

When you post the picture of the schema, please expand all the tables so we can see all the columns.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41814260
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.
0
 

Author Comment

by:Dustin Stanley
ID: 41814263
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   db16.jpg
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41814265
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.
0
 
LVL 19
ID: 41814266
thanks for jumping in, Pat ~
0
 

Author Comment

by:Dustin Stanley
ID: 41814268
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
0
 

Author Comment

by:Dustin Stanley
ID: 41814271
sorry i didnt see the last post. i will update and post back
0
 

Author Comment

by:Dustin Stanley
ID: 41814275
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?

db18.jpg
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41814277
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.
0
 

Author Comment

by:Dustin Stanley
ID: 41814278
The images are related to the product specifically not the variants. The same photo goes to all the variants.
0
 

Author Comment

by:Dustin Stanley
ID: 41814285
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?
0
 
LVL 19
ID: 41814287
>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 ...
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41814289
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.
0
 

Author Comment

by:Dustin Stanley
ID: 41814290
Yes Progress is GOOD! Thank you Thank You!
0
 

Author Comment

by:Dustin Stanley
ID: 41814291
So How do I go about that?
0
 

Author Comment

by:Dustin Stanley
ID: 41814293
It just came to me. In the general area click Index no duplicate and required yes
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41814295
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
0
 

Author Comment

by:Dustin Stanley
ID: 41814305
Is this correct?
db19.jpg
0
 

Author Comment

by:Dustin Stanley
ID: 41814312
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??? I don't know exactly. The Variants stuff is what I find confusing and linking all the variants.
0
 
LVL 19
ID: 41814322
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
0
 

Author Comment

by:Dustin Stanley
ID: 41814326
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?
0
 
LVL 19
ID: 41814327
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)
0
 
LVL 19
ID: 41814328
> "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
0
 

Author Comment

by:Dustin Stanley
ID: 41814329
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
0
 
LVL 19
ID: 41814333
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
0
 

Author Comment

by:Dustin Stanley
ID: 41814338
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.jpgdb20.jpg
0
 
LVL 19
ID: 41814339
did you see my comment about stopping Access from creating all those indexes?
better to set it sooner than later ...
0
 

Author Comment

by:Dustin Stanley
ID: 41814342
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?
0
 
LVL 19
ID: 41814343
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
0
 
LVL 19
ID: 41814344
leave it blank :)
0
 

Author Comment

by:Dustin Stanley
ID: 41814345
Ok so I set the ProductID_ Do I have to make a query now?
0
 
LVL 19
ID: 41814346
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.
0
 
LVL 19
ID: 41814347
> "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
0
 
LVL 19
ID: 41814348
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_
0
 
LVL 19
ID: 41814352
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.
0
 

Author Comment

by:Dustin Stanley
ID: 41814355
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.
0
 

Author Comment

by:Dustin Stanley
ID: 41814356
I have several forms from previously tinkering around. I had a product entry form.
0
 

Author Comment

by:Dustin Stanley
ID: 41814357
How do you rename it the the alias.
0
 
LVL 19
ID: 41814359
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.
0
 
LVL 19
ID: 41814361
> "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?
0
 
LVL 19
ID: 41814363
actually, CustomerOrderTable should be Orders and OrderProductsTable should be OrderDetails (OrderProducts is ok too -- but get rid of "Table" -- they are all tables)
0
 
LVL 19
ID: 41814365
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.
0
 

Author Comment

by:Dustin Stanley
ID: 41814366
I know the difference between them (The Original and the Copy)  but I just wanted to follow direct directions and you said
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
 Ok I think I have done all that you have said so far.
db22.jpg
0
 
LVL 19
ID: 41814367
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
0
 
LVL 19
ID: 41814368
the primary key of Locations should be called LocID or LocationID (I like the shorter names)
It should not be linked to ProductID
0
 
LVL 19
ID: 41814370
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
0
 

Author Comment

by:Dustin Stanley
ID: 41814374
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
0
 
LVL 19
ID: 41814376
just call it Locations

you will run out of room for one screen of the Relationships Diagram faster with all these long names!
0
 

Author Comment

by:Dustin Stanley
ID: 41814377
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
0
 
LVL 19
ID: 41814382
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.
0
 

Author Comment

by:Dustin Stanley
ID: 41814387
db24.jpg
0
 
LVL 19
ID: 41814388
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
0
 

Author Comment

by:Dustin Stanley
ID: 41814393
Ok what data type is ProdSupID in products supposed to be because it keeps giving a error
0
 

Author Comment

by:Dustin Stanley
ID: 41814400
i got it number long integer.
0
 

Author Comment

by:Dustin Stanley
ID: 41814401
so far
db25.jpg
0
 

Author Comment

by:Dustin Stanley
ID: 41814413
db26.jpg
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 19

Accepted Solution

by:
crystal (strive4peace) - Microsoft MVP, Access earned 250 total points
ID: 41814415
move all the Supplier fields in ProductSuppliers to Suppliers (SupplierName ... SupplierEmail)

break link on ProdSupID and delete ProdSupID from Products

add ProductID to ProductSuppliers and, since there may be more than one supplier, create a field so that the main supplier is 1:
OrdrPS, integer, defaultvalue=nothing (make user fill this out)

Move Products back to Column 2 since there will most likely be something to the left that you just haven't thought of yet ... a product category or product line?

get rid of SuppliersIDtbl

it is late here ... and tomorrow I am going to be tied up. Hopefully you can finish this one your own. Or maybe Pat will jump in again

here is a short book on Access you can read:

Access Basics
http://www.AccessMVP.com/strive4peace
Free 100-page book that covers essentials in Access
0
 

Author Comment

by:Dustin Stanley
ID: 41814418
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.
0
 
LVL 19
ID: 41814420
you're welcome, Dustin -- you are doing great!
0
 

Author Comment

by:Dustin Stanley
ID: 41814423
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!
0
 

Author Comment

by:Dustin Stanley
ID: 41814825
Hello and thank you for ALL the help. I have a question about what you said.

 
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!

db27.jpgdb28.jpg
0
 
LVL 19
ID: 41814850
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!
0
 

Author Comment

by:Dustin Stanley
ID: 41814954
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:
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;

Open in new window



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;

Open in new window



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!
0
 
LVL 19
ID: 41814988
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 ...
0
 
LVL 19
ID: 41815427
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.
0
 

Author Comment

by:Dustin Stanley
ID: 41816523
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
0
 
LVL 19
ID: 41816550
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
0
 

Author Comment

by:Dustin Stanley
ID: 41816582
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
0
 
LVL 19
ID: 41816764
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
0
 

Author Comment

by:Dustin Stanley
ID: 41816807
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.
0
 
LVL 19
ID: 41816833
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
0
 

Author Comment

by:Dustin Stanley
ID: 41816866
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:
db32.jpg
0
 
LVL 19
ID: 41816885
> "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
0
 

Author Comment

by:Dustin Stanley
ID: 41816906
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
db33.jpg
0
 
LVL 19
ID: 41816916
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?
0
 

Author Comment

by:Dustin Stanley
ID: 41816923
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.
0
 
LVL 19
ID: 41816933
What that is currently in the Products table relates directly to the parent product and what relates to the variant product?
0
 

Author Comment

by:Dustin Stanley
ID: 41816939
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.
0
 

Author Comment

by:Dustin Stanley
ID: 41816950
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.
0
 
LVL 19
ID: 41816958
there should be 2 tables -- trying to help you separate those fields then we can figure out what to call them.
0
 

Author Comment

by:Dustin Stanley
ID: 41816960
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....
0
 
LVL 19
ID: 41816968
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
0
 

Author Comment

by:Dustin Stanley
ID: 41816976
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

db34.jpg
0
 
LVL 19
ID: 41816979
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.
0
 

Author Comment

by:Dustin Stanley
ID: 41816986
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?
0
 
LVL 19
ID: 41816988
For ProdLocations, do you track inventory for SKUs or for the SKU with condition (ProductID)?
0
 

Author Comment

by:Dustin Stanley
ID: 41816991
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.

db35.jpg
0
 
LVL 19
ID: 41817070
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.
0
 

Author Comment

by:Dustin Stanley
ID: 41817116
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?
0
 
LVL 19
ID: 41817118
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
0
 

Author Comment

by:Dustin Stanley
ID: 41817123
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
0
 
LVL 19
ID: 41817131
> "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 :)
0
 

Author Comment

by:Dustin Stanley
ID: 41817136
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.

> "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.
db37.jpg
0
 
LVL 19
ID: 41817154
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.
0
 

Author Comment

by:Dustin Stanley
ID: 41817157
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.
0
 
LVL 19
ID: 41817158
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.
0
 

Author Comment

by:Dustin Stanley
ID: 41817178
Ok the Price field in Products Table didn't carry over to the OrderDetails SellPrice Field. Normal or not....
0
 

Author Comment

by:Dustin Stanley
ID: 41817184
Also I ran the analyzer? Now what?
0
 
LVL 19
ID: 41817190
> "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.
0
 

Author Comment

by:Dustin Stanley
ID: 41817198
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!
0
 

Author Comment

by:Dustin Stanley
ID: 41817204
db38.jpg
When inserting info. I inserted the price in the products table but in the ordersdetail table it was blank 0.00

db39.jpgdb40.jpg
I couldnt find the print to PDF but anyways it is showing all kinds of fields and tables that no longer exist

db41.jpg
0
 
LVL 19
ID: 41817212
> 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)
0
 

Author Comment

by:Dustin Stanley
ID: 41817242
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!
0
 
LVL 19
ID: 41817246
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/website.  In my opinion, these are redundant fields and should be put into their own places ... to be discussed later.

... 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 ;)
0
 

Author Comment

by:Dustin Stanley
ID: 41817256
0
 
LVL 19
ID: 41817260
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)
0
 

Author Comment

by:Dustin Stanley
ID: 41818342
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.jpga_r_DeepAnalysis2.pdf
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41818396
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.
0
 

Author Comment

by:Dustin Stanley
ID: 41818493
Ok Thank you. I did the customer UniqueID as the CustFNm, CustLNm, and CustEmail. So that should be good.
0
 
LVL 19
ID: 41818924
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!
0
 

Author Comment

by:Dustin Stanley
ID: 41818949
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!
0
 
LVL 19
ID: 41818974
> "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.
0
 

Author Comment

by:Dustin Stanley
ID: 41818977
Oh ok I already did that
0
 
LVL 19
ID: 41818983
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 ;) ~
0
 

Author Comment

by:Dustin Stanley
ID: 41818989
Ok thanks
0
 
LVL 19
ID: 41818997
Dustin, I objected to this because Pat helped you too ... also pick better posts for the solution ~ thanks... and you're welcome
0
 

Author Comment

by:Dustin Stanley
ID: 41818999
All new to me. I picked assisted but Idk maybe I reset it. I will try again. I'm on my mobile now .
0
 

Author Comment

by:Dustin Stanley
ID: 41819001
How do I redo it?
0
 
LVL 19
ID: 41819003
I think you can "unclose" the question via a moderator (I requested attention) and then redistribute points ( to better posts!)
0
 

Author Comment

by:Dustin Stanley
ID: 41819004
Ok
0
 

Author Closing Comment

by:Dustin Stanley
ID: 41819619
Thank you for the help! It means a lot!
0
 
LVL 19
ID: 41820059
you're welcome, Dustin ~ happy to help
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now