Forum Discussion
Stock Tracking Form and DB
Hi all, I've attached a DB/Form im compiling to do a simple intake form for stock holding, Consignment Number needs to be unique, when inputting the TM_Carton_ID on the subform i want it to look...
In this link, on the table "tble_Carton_Type" the inputted carton should look up to that table and the corresponding value out of the "type" column should be brought back.
Okay, but does "Mixed" mean that there is more than one SKU in a carton? And does "Pure" mean there is only one SKU in a carton? And are the SKUS also listed in each carton individually? If so, the field that is used to indicate "Pure" or "Mixed" is redundant because you can count the different SKUs in a carton. So, I'm trying to understand the business context, not just the fields in the tables.
- Yeah exactly, pure = only 1 sku and mixed = More than one, yes the field is there purely as a reference 🙂
Scanning stock in to putaway in the warehouse, Pure cartons will go to a different location than Mixed so need to flag what the type is each time a Carton ID is entered 🙂Got it.
In that case, this query will list all of the "Pure" and "Mixed" cartons.
SELECT tbl_Pack_List.TM_ID, Count(tbl_Pack_List.SKU) AS CountofSkusInPack, IIf(Count([SKU])>1,"Mixed","Pure") AS PackType
FROM tbl_Pack_List
GROUP BY tbl_Pack_List.TM_ID
ORDER BY Count(tbl_Pack_List.SKU) DESC;The table named "tbl_Carton_Type" is not needed at all.
George_Hepworth :
Hi mate, like i say on the form itself, once entering the TM ID i need it too look up against the table and let me know if its a PURE or Mixed Carton... and i need to get it to display on the screen , via a text box to just prompt the user what type of carton it is.