or as I suggested, you should be connecting the order directory to the InventoryLocationTable since THAT is what you are ordering.
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.
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_
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;
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;
in ConditionCodes there would also be CondCode short text,size=4 with a unique index.
well perhaps not -- the SKU would then have 2 controls on the form, one for the SKU and another for the condition
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)
What that is currently in the Products table relates directly to the parent product and what relates to the variant product?
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.
Products replaces ProductVariants (which I didn't like the name of anyway).
I see you took 'name the ID field and 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.
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.
> "where to put the main ParentSKU"
you would have a table and form for the SKU table
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. Â
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;
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.