Stock Tracking Form and DB

Question

Hi&nbsp;all,&nbsp;&nbsp;I've&nbsp;attached&nbsp;a DB/Form im compiling to do&nbsp;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 up&nbsp;against the DB&nbsp;and bring back if its Pure or mixed, but i cant seem to get it to happen for multiple lines.&nbsp;See my&nbsp;attached DB,&nbsp;any help on this would be useful, feel free to&nbsp;add&nbsp;any&nbsp;additions too, once this is live the TM_Carton_ID will be scanned in via&nbsp;a device&nbsp;and we need to tell the user if that carton is&nbsp;a pure carton or&nbsp;a mixed carton.&nbsp;any help would be great on this thanks.DB is below.&nbsp;https://drive.google.com/file/d/1UgD7rwVlKIe_0EHKlDQ2TWqhkbwyLzBx/view?usp=sharing&nbsp;

george_hepworth · Answer

Can you provide a definition of "pure carton" and "mixed carton"?&nbsp;Are all of the tables in this downloadable accdb, or is it a subset?

jamesmiller460 · Answer

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.

george_hepworth · Answer

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.

jamesmiller460 · Answer

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 🙂

george_hepworth · Answer

jamesmiller460&nbsp;&nbsp;Got it.&nbsp;In that case, this query will list all of the "Pure" and "Mixed" cartons.&nbsp;SELECT tbl_Pack_List.TM_ID, Count(tbl_Pack_List.SKU) AS CountofSkusInPack, IIf(Count([SKU])&gt;1,"Mixed","Pure") AS PackTypeFROM tbl_Pack_ListGROUP BY tbl_Pack_List.TM_IDORDER BY Count(tbl_Pack_List.SKU) DESC;&nbsp;The table named "tbl_Carton_Type" is not needed at all.

Forum Discussion

Stock Tracking Form and DB