Solved

Ms Access Product Inventory Stock Movement Form Layout Suggestions Barcode Scanner

Posted on 2016-10-07
46
88 Views
Last Modified: 2016-10-11
I am in need for some food for thought. I am working on a form for Inventory Movement.

EXAMPLE:  Lets say I remove 5 of product "A" from shelf "X1" and then place 3 of product "A" on shelf "W3" and the other 2 on shelf "Z9"

What would be involved in this form?
Events
Buttons
Form Layout
Fields
Etc....

What would be the best layout to use a barcode scanner for all input.
EXAMPLE: Use the barcode scanner to Scan Shelf "X1" then Scan Product "A" 3 times (Or input 3 manually) and then Scan Shelf "W3" and so on. So the database knows after the second SHELF SCAN it is to move on to the next product?

Any suggestions or opinions!

Thank you all!
0
Comment
Question by:Dustin Stanley
  • 23
  • 19
  • 4
46 Comments
 
LVL 18
ID: 41834772
before you think about what the form looks like and how it will work, the data needs to be structured -- tables, fields, and relationships.

For now, don't worry that you will be barcoding and using a reader -- a barcode is just characters printed in another font, much like you would choose Arial or Times New Roman.
0
 

Author Comment

by:Dustin Stanley
ID: 41834980
The relationships should all be set up. Any suggestions. I do realize a barcode scanner is basically just a automatic keyboard. I just didn't know how to setup the sequence of scan, scan, scan, next record......

I was thinking maybe an excel spreadsheet would work good and then import it in access but if I could do this in access that would be best. Less steps is always best. Thank you crystal for the help.
0
 
LVL 18
ID: 41835011
you're welcome, Dustin.  What is the relevant data structure for tracking locations and moving between locations? Please post a diagram of those tables. thanks

best if the terminating character for the barcodes is TAB -- that will end the entry.  Second best is terminate with ENTER (However, I like to make that go to next record so prefer TAB which goes to next control or field depending on where entry is done).

If the scan is NOT terminated with TAB or ENTER, then you have to use the Change event to see if it is done, which can be  tricky

No reason to use Excel, that is just another step
0
 

Author Comment

by:Dustin Stanley
ID: 41835027
What is the relevant data structure for tracking locations and moving between locations?

This may be part of the answer to my question or maybe I just don't know how to ask the right questions :)

After you said that it made me think. Do I need a movement table ( like a details table about the specific movement) ?

If so how would that play out in deducting the quantity from the original location and then updating the new location quantity in my existing ProdLocations Table?

What type of events would need to be involved eg: afterupdate, beforeupdate, beforeinsert.....Etc....?

What fields would I need in the tables that are involved or in the NEW movement details table (if I need one).
Thanks!
db74.jpg
0
 
LVL 18
ID: 41835031
hi Dustin, DO you need a parts move table? What else, besides location, needs to change based on parts moving? It is a good idea to keep a record of transfers. I'll be away for awhile ...
0
 

Author Comment

by:Dustin Stanley
ID: 41835039
When moving a product From Location "A" To Location "B" I would only need to know FOR SURE:
Product SKU
Quantity
From Location "A"
To Location "B"
Movement Date

Maybe but not 100% required:
Employee ID
Specific Movement Notes
0
 
LVL 57
ID: 41835157
As Crystal said and as I mentioned in the other thread, you do need a part "movement" table.
Your thinking along the right lines as far as fields, but each record would be one move in or out of inventory.

So a part moving from one location to another in a warehouse would be two transactions; one "out" and one "in".  You can make it easy for the user and create a "location move" form that does both transaction if you want rather than forcing them to do an "out" and then an "in".  

Besides the fields you listed (SKU, Loc, Date, and Qty), you also need a flag for the direction (In or out), reason code (i.e.  Receipt in,  Issue out, cycle count, rejected goods, misc out, misc in, transaction error, etc), and possibly a document reference (when receipting in, you'd want to know from what).

As for the scanning, it is as you said; just an automated keyboard in effect.   Any input your on, you can use the scanner.  So you can design the form first, then just use a scanner.

If you want to force the user to use the scanner for some inputs, you program the gun to add a prefix and/or suffix to the scan data, then look for that in the input.   If you don't see that, then it didn't come from the gun.

Jim.
0
 

Author Comment

by:Dustin Stanley
ID: 41835355
Thank you very much that is good info I am looking for. I was thinking about the IN and OUT recording also. I was thinking it would make more sense to have an out and an IN seperated so if you need to OUT more products before you could IN the previous product then it would move quicker in the warehouse. If you have any more suggestions or ideas please share. I am going to be working on this in the next two days.
0
 
LVL 18

Accepted Solution

by:
crystal (strive4peace) - Microsoft MVP, Access earned 250 total points
ID: 41835451
ProdMoveID, AutoNumber - PK
ProdID, Number, Long Integer - FK to Products which will in turn be linked to SKUs and Conditions
LocID1 - from where, Number, Long Integer - FK to Locations
LocID2 - to where, Number, Long Integer - FK to Locations
EmpID, Number, Long Integer - FK to Employees
ReasonMID, Number, Long Integer - FK to ReasonMove with reasons for move
    -- may want a default value for this on the form
QtyMove, Number, Long Integer
dtmMove, date/time moved
IsAdjusted, yes/no -- DefaultValue=False - has inventory been adjusted?
   -- or you may want to store dtmAdjust with Null meaning it needs to be done
NoteMove, text, 50

PK = Primary Key
FK = Foreign Key

no need to store text when you have IDs :)
-- don't forget to delete default value of 0 (zero)

with this information, you can then update your product locations table

you could actually use this table for receiving too -- maybe just fill LocID2, or if you have different trucks, you could put them into your locations table if you care which truck it came in on;  then you may want to also put SupplierID in there too along with other IDs you need for receiving. Not saying it should be combined, just that you may want to consider it.
0
 

Author Comment

by:Dustin Stanley
ID: 41836168
Ok I want to make sure I am understanding this clearly and there is not a debate here.

Jim says:

So a part moving from one location to another in a warehouse would be two transactions; one "out" and one "in".

Crystal Says:

LocID1 - from where, Number, Long Integer - FK to Locations
LocID2 - to where, Number, Long Integer - FK to Locations
Which shows IN and OUT in the same record....Or do I only fill out 1 or the other fields

Ok here is my tables and relations. These are NOT all my tables in my database just the tables we are working on. Please tell me what you think.
db80.jpg
0
 
LVL 18
ID: 41836213
hi Dustin,

the point is to record what is going in and what is going out -- as long as that is being done, you are good ;) ~ Not a debate, just different ways to do the same thing. Jim's method uses 2 records, mine does everything with one. I would have a dtmAdd tracking field in there too (which I didn't mention since it is in all tables for any database I build -- DefaultValue=Now ... I have code to add that if you need it). When the product is removed from Loc1, that will be the date/time for dtmAdd (date/time record was added).  Then Loc2 will be blank until they are put there and dtmMove will then get a value.  If something happens between taking them out of location1 and putting them into the new location such that the quantity doesn't match, then another record would need to be created with that difference.  In that sense, it may be better to use 2 records ... just depends on how you want to do it.  No 2 experts will usually do things the exact same way.  Nice to have different points of view and then decide for yourself.

>"LocIDs ... fill out 1 or the other fields"
fill them both

If what is moved from one location doesn't match what is being put in the other location, then adjust qty to match what is the same.  If product is being removed and NOT put somewhere else (stolen, vaprized?), then I guess Loc2 would be blank -- and likewise, if stuff is being moved to Loc2 but gets there out of thin air, then Loc1 would be blank and someone must have some magic

perhaps instead of a quantity, you just want to scan things one at a time without counting.  We give you ideas but you must analyze how it is actually done and create tables that work the best for the way your business actually happens.
0
 

Author Comment

by:Dustin Stanley
ID: 41836255
Ok I see thanks. I can see both ways being played out. I just always feel as though I may be doing something wrong that will effect the BIG picture down the road. So I ask for the best details I can get. You guys are awesome and do give good details. Thank you!

How does my relationships look?
0
 
LVL 18
ID: 41836285
Hi Dustin,

you're welcome

> "How does my relationships look?"

a few quick notes...

In ProdMovements:
1. won't LocNm From/To) already be in the Locations table? Better to use LocID than store the names again.  If you ARE then name fields more consistently because it looks like you are storing text not foreign keys.  Then set up TWO relationships to Locations.  I drag the bottom one first so join lines don't cross when the second main table is placed above the first one. Remember: on the form, these will be comboboxes so the user won't see the ID numbers.

2. what is the data type for ProdConSku ? In Products, ProductID should be an autonumber primary key. If ProdConSku is text, then it should be a unique key but, as I said before, that text should not be stored as it can be constructed. In Products, the combination of SkuID and ConditionID should be unique.

3. Oh no! Where did that attachments field come from in SKUs? Better to store files externally and in the database, you can store path\filename or just filename and put them all in the same directory.
The DocumentReference table also has an attachment field.  Those are difficult to use when it comes time to report information and convert, email -- all kinds of issues that are so much easier to handle when the files are separate from the database.

Doesn't DocumentReference need either a ProductID or SkuID?

4. In Skus, make dtmEdit and drmAdd the last 2 fields.

5. Assemblies ... what is your intention here? To store SkuID in Assemblies and then a reference to one or more child SKUs? I don't think ParentAssemblyID should be in SKUs. I don't think the relationship is right to the copy of assembly details. What does your assembly information look like on paper?

It is really important to put sample data into all the tables and test your structure.
0
 

Author Comment

by:Dustin Stanley
ID: 41836325
Crystal I feel as though this is a Confessional LOL ;)
I am trying here!
*1-2*:
OK on some of the PK I did change some of the AutoNumbers to Natural PK for testing. I am importing 1000's of products and I can't figure out how to import into the SKUs or ProdLocations  tables the info needed in certain fields and get Access to recognize the Text with the matching AutoNumber.  But that was another question I had on here that well...didn't go over to well. here is that question : https://www.experts-exchange.com/questions/28974346/MS-Access-How-to-Import-Text-Document-such-as-CSV-File-into-Table-and-get-Access-to-Accept-a-Short-Text-For-a-Autonumber-Field.html

I did set all the PK text fields to UNIQUE NO DUPLICATES.
SkuID and ConditionID are Unique Indexed together as a combo.

__________________________________________________________________________________________________________
*3*:
Again I was just testing with the attachments and database sizing. And yes it bloats the database QUICKLY. I have been trying the answers you gave me from other post and trying to comprehend them completely. I also agree that all files need to be externally stored.

Doesn't DocumentReference need either a ProductID or SkuID?

Doesn't it link back to the ProductID (ProdConSku) through the table ReasonMove and over to ProdMovements???


I'm trying to understand!

__________________________________________________________________________________________________________
*4*:
DONE

__________________________________________________________________________________________________________
*5*:
Jim and Pat Helped me on this here: https://www.experts-exchange.com/questions/28974491/MS-Access-Table-Relationship-Setup-For-Keeping-Track-of-Replacement-Parts-Of-a-Product-Maybe-Many-To-Many-Relationship.html
 

By the way when you do a self link back to the same table is all the relationships supposed to be cascade update or Enforced??

Here is the whole map.
 db81.jpg
0
 

Author Comment

by:Dustin Stanley
ID: 41836329
The relationships may not be correct. I am trying to get a better understanding of what is the correct way  here.
db81.jpg
0
 
LVL 18
ID: 41836802
Hi Dustin,

Good morning

When I use "ID" in a fieldname it is because the data type is AutoNumber/Long Integer. It is confusing to have some IDs be numbers and some IDs be text. In my opinion, all the IDs should be numbers (except States and Countries since they are just 2 char and it is much easier not to have to correlate this.  Personally, I do not create relationships here, just use those tables for lookup)

It is hard for me to know the data types of your information because I suspect some of your IDs are numbers and some are not (and now I see that you did say you changed some to natural keys -- my recommendation is don't do this).

I realize that you are new to Access and the concept of artificial (surrogate) keys is difficult. It is, however, MUCH more efficient and better performance than using natural keys.  Your application will have a lot of records so you should get your head around this. Best way is to start putting data in manually and looking up numeric IDs to fill out.

It is also hard when people give you conflicting advice. Many suggest natural keys (especially to beginners) because they are easier to understand.  

that said, I will make a few more comments:

why is there a relationship between DocumentReference and ReasonMove? It should be on an ID field if it is actually needed.

DocumentReference  needs PathFilename, not attachment field.  Bloating is the most obvious problem with attachment fields ... but not the only one

My personal preference is to keep FK and PK field names the same so that when you see a fieldlist, it is more apparent that the data is the same.

Assemblies: I would rename AssemblyDetailID to simply AssemblyID. If you want to nest assemblies with a self join, create AssemblyID_, long integer, and make a relationship from AssemblyID to AssemblyID_ -- the self-join relationship should not be on the SKUs in the assembly table.

Markets: why is the PK on MarketNm and not MarketID? I am assuming you are also storing text in Orders and not a number.

> "importing 1000's of products and I can't figure out how to import into the SKUs or ProdLocations  tables the info needed in certain fields and get Access to recognize the Text with the matching AutoNumber."

When importing data without the numbers to match:
1. import to a separate table (I like to start that table name with "import_")
2. Add relevant ID fields to the import table (go to the Design view of the table)
3. Append lookup text data to the tables where it needs to go
4. Update import table IDs
5. Append rest of data to tables with numeric IDs and not text

for instance, if you have location data:
1. locations should have a unique index on LocNm and an autoNumber PK, LocID. A query can be an ACTION query that does something like add or update records. Instead of a Select query, which shows information but doesn't perform any action, an Append query adds records to a table and an Update query changes records in a table. If you attempt to append data with the same location name, those records simply won't go in because of the unique index on location name -- and the rest will -- don't run the query so it fails if anything fails -- let it add whatever records are actually new.
2. Once locations are added, manually look at the records to correct data in the import table where location is actually the same but was spelled differently (Update queries can be used to do this as you manually spot differences -- a Totals query grouped on the text you are inspecting can help).  Delete those extra records in the locations table.
3. run an Update query on the import table to update records with the IDs.
4. then you can add the data to the tables where it needs to go using numeric IDs.

I realize you are trying.  You need to step back, however, and start creating data.  Test your structures the way they should be without concern for HOW you will get the data in (that also will be easier to understand once you have manually done the process). Plowing on with design before you have a firm grasp of the design already set up is a mistake.  It takes time to absorb. Print the relationships diagram and carry it with you everywhere you go -- look at it when you have any time, even just a minute waiting for a bus, or whatever.  Create data the long way -- manually. I cannot say enough about the benefits of doing this.

Jim suggested you answering questions ... spend some time each day looking. The best way to learn is to help others (and you might be corrected if you say something wrong, don't worry, we are all learning all the time). EE has a lot of categories, not just Access.  I answer math and music questions, proof and edit things that people write, and all kinds of other unrelated interesting stuff :)
0
 

Author Comment

by:Dustin Stanley
ID: 41837397
OK THANK YOU VERY MUCH!!! I was successful at making the appended Import. So with that done I changed all the Natural Keys back to Surrogates.  

Successful Query in SQL:
INSERT INTO SKUs ( ManuID, SKU, SkuNm, SkuMPN, UPC, SkuDescr )
SELECT Manufactures.ManuID, Import_SKUs.SKU, Import_SKUs.SkuNm, Import_SKUs.SkuMPN, Import_SKUs.UPC, Import_SKUs.SkuDescr
FROM Import_SKUs INNER JOIN Manufactures ON Import_SKUs.ManuID = Manufactures.ManuNm;

Open in new window


why is there a relationship between DocumentReference and ReasonMove? It should be on an ID field if it is actually needed.

I have it on ReasonMove.DocumentRef FK and DocumentReference.DocumentRefID PK (I did have it worded incorrectly if that is what you are mentioning?)

I am going to import some dummy data and test it again now. I have been testing with dummy data and have been using the Numeric IDs So I am getting more familiar with everything. And Yes I work on this with EVERY WAKING MINUTE Straight!

Here is the updated Relations:
db90.jpg
0
 
LVL 18
ID: 41837478
>"I was successful at making the appended Import"

good for you! I noticed ManuID in your SQL statement ... and you related it to the Manufactures table -- great! So you see how it is done;) Now sort by ManuID in the SKUs table so the ones that aren't filled will come to the top and change them with Update queries. When I am appending imported data, I put temporary fields into tables with the text values  until I get all the IDs filled for exactly this reason. Then append to both the ID and the temporary text field so you can link up the ones that didn't match.  If you find that there is not always a ManufID: delete all records, add text field for manufacturer, backup database then compact/repair, and do it again -- but also append text to temporary manufacturer field (put "temp" in the fieldname so you remember to delete it later).

With tables that have 2 LocIDs such as ProdMovements, obviously you would need the Locations table in the query twice ... Access won't link these up right so you will have to delete join lines and put them the way they need to be

I'm sure you feel better with all those SKUs in! How many were there?
(you should keep a note of how many records were added and changed as you are putting data in)
 Now you are also ready for this:
'~~~~~~~~~~~~~~~~~~~~~~~~~~ AddFieldToTable
Function AddFieldToTable( _
   psTablename As String _
   , psFldName As String _
   , piDataType As Integer _
   , Optional piFieldSize As Integer _
   , Optional psDefaultValue As String = "" _
   , Optional psDescription As String = "" _
   , Optional pBooSkipMsg As Boolean = True _
   , Optional pTdf As DAO.TableDef) _
   As Boolean
 'Add Field to a Table
' s4p 8-28-07 ...130901
    
   'PARAMETERS
   'psTablename --> name of table to modify structure of
   'psFldName --> name of field to create
   'piDataType --> dbText, dbLong, dbDate, etc
   'piFieldSize --> length for text fields
   'psDefaultValue --> *AN* = autonumber
   '         --> *Null* --> DefaultValue = Null
   '         --> *Now* --> DefaultValue = Now()
   '         --> otherwise whatever is specified
   
   'NEEDS Reference to
   'a Microsoft DAO Library
   ' -- OR --
   ' Microsoft Office #.0 Access Database Engine Object Library
  
   On Error GoTo Proc_Err
   
   AddFieldToTable = False
   
   Dim db As DAO.Database _
      , tdf As DAO.TableDef _
      , fld As DAO.Field
 
   'you could make this a passed parameter
   ' and open another database
   Set db = CurrentDb
   
   If psTablename = "" Then
      If pTdf Is Nothing Then Exit Function
      Set tdf = pTdf
   Else
      Set tdf = db.TableDefs(psTablename)
   End If
      
  
   With tdf
  
      Select Case piDataType
        Case dbText
            'Text
            Set fld = .CreateField(psFldName, _
              piDataType, piFieldSize)
        Case Else
            'Long Integer, Date, etc
            Set fld = .CreateField(psFldName, piDataType)
      End Select
      
      If Len(psDefaultValue) > 0 Then
         Select Case psDefaultValue
         Case "*AN*"
            'Autonumber
            fld.Attributes = dbAutoIncrField
         Case "*Null*"
            'Null for DefaultValue
            fld.DefaultValue = "Null"
         Case "*Now*"
            'Now for DefaultValue
            fld.DefaultValue = "=Now()"
         Case Else
            'Now for DefaultValue
            fld.DefaultValue = "=" & psDefaultValue
         End Select
      End If
      
      If piDataType = dbText Then
         fld.AllowZeroLength = True
         On Error Resume Next
         fld.Properties("UnicodeCompression") = True
         If Err > 0 Then
            fld.Properties.Append fld.CreateProperty("UnicodeCompression" _
               , dbBoolean, True)
         End If
         On Error GoTo Proc_Err
      End If
      
      .Fields.Append fld
      
      If Len(psDescription) > 0 Then
         On Error Resume Next
         fld.Properties("Description") = psDescription
         If Err > 0 Then
            fld.Properties.Append fld.CreateProperty("Description" _
               , dbText, psDescription)
         End If
         On Error GoTo Proc_Err
      End If
   End With
   
   db.TableDefs.Refresh
   DoEvents
  
   If Not pBooSkipMsg Then
     MsgBox "Added --> " & psFldName _
      & " to --> " & psTablename, , "Done"
   End If
   
   AddFieldToTable = True
   
Proc_Exit:
   On Error Resume Next
   Set fld = Nothing
   Set tdf = Nothing
   'if db is external and you OPENed it,
   'you will need to close it too
   Set db = Nothing
   
   Exit Function
   
Proc_Err:
   'if the field is already there, ignore error
   If Err = 3191 Then Resume Proc_Exit
   'linked table
   If Err.Number = 3057 Or Err.Number = 3211 Then Resume Proc_Exit
   MsgBox Err.Description, , _
     "ERROR " & Err.Number & "   AddFieldToTable"
 
   Resume Proc_Exit

   'if you want to single-step code to find error, CTRL-Break at MsgBox
   'then set this to be the next statement
   Resume
    
End Function
 
'~~~~~~~~~~~~~~~~~~~~~~~~~~ RunAddFieldsToTable_Tracking
Sub RunAddFieldsToTable_Tracking()
'crystal, 130420
 
   'will add the following tracking fields to every table:
   'IDadd, IDedit, dtmAdd, dtmEdit

   ' if dtmEdit already exists, the Default Value will be updated to Now()
   
   Dim db As DAO.Database
   Dim tdf As DAO.TableDef
   
   Dim iCountDone As Integer _
      , iCountChecked As Integer _
      , iCountUpdateDefaultValue As Integer _
      , i As Integer
      
   iCountUpdateDefaultValue = 0
   Set db = CurrentDb
   
   For Each tdf In db.TableDefs
      If Left(tdf.Name, 4) <> "Msys" Then
         Debug.Print
         Debug.Print tdf.Name & "...";
         iCountChecked = iCountChecked + 1
         
        ' ----------------------------------------- IDs
        ' if you do not want to add tracking fields for user
        '  then comment these lines
 
'         If AddFieldToTable("", "IDadd", dbLong _
'            , , "*Null*", "user who added record", , tdf) Then
'            iCountDone = iCountDone + 1
'            Debug.Print "  IDadd";
'         End If
'
'         If AddFieldToTable("", "IDedit" _
'            , dbLong, , "*Null*", "user who last edited record", , tdf) Then
'            iCountDone = iCountDone + 1
'            Debug.Print "  IDedit";
'         End If
         ' ----------------------------------------- Dates
         
         If AddFieldToTable("", "dtmAdd", dbDate _
            , , "*Now*", "date record was added", , tdf) Then
            iCountDone = iCountDone + 1
            Debug.Print "  " & "dtmAdd";
         End If
         
         If AddFieldToTable("", "dtmEdit", dbDate _
            , , "*Now*", "date record was last edited", , tdf) Then
            iCountDone = iCountDone + 1
            Debug.Print "  " & "dtmEdit";
         Else
            'update Default Value of the edit date field
            On Error Resume Next
            db.TableDefs(tdf.Name).Fields("dtmEdit").DefaultValue = "=Now()"
            If Err.Number = 0 Then
               iCountUpdateDefaultValue = iCountUpdateDefaultValue + 1
            End If
            On Error GoTo Proc_Err
         End If
                
      End If
NextTdf:
   Next tdf
 
   MsgBox iCountChecked & " tables checked" & vbCrLf & vbCrLf _
      & "Added " _
      & iCountDone & " tracking fields" _
      & vbCrLf & vbCrLf _
      & "dtmEdit" & " Default Value checked/updated to Now() in " _
         & iCountUpdateDefaultValue & " tables" _
      , , "Add Tracking Fields to Table Design"

Proc_Exit:
   On Error Resume Next
   'close and release object variables
   Set tdf = Nothing
   Set db = Nothing
   Exit Sub
  
Proc_Err:
   
   MsgBox Err.Description, , _
        "ERROR " & Err.Number _
        & "   RunAddFieldsToTable_Tracking"
 
   Resume Proc_Exit
   Resume

End Sub 

Open in new window

Put that code into a module.  Name it: mod_Field_AddFieldToTable
and run RunAddFieldsToTable_Tracking -- after Debug, Compile, Save, click in that procedure and press the F5 key to run it, or choose Run, Run Macro (1st choice) from the menu in the VBE (Visual Basic Editor) ... even though it is NOT a macro. In Access, macros and VBA are not the same.

In Orders, move MarketID to the top with the other key fields. Same with ProdMovements (and a few other tables ... leave it up to you to identify them) -- good to put key fields at the top (in any order to minimize join lines crossing) and tracking fields at the bottom.

Do you need Kits AND Assemblies? Does a kit have an SKU? If so, perhaps Assemblies should be called Kits and what you have now for kits should be deleted.

You are really doing well, Dustin -- and you have learned a lot.  You still have a ways to go but you will get there ;) ~

How did you learn how to do an Append query?
0
 

Author Comment

by:Dustin Stanley
ID: 41837833
OK I am getting held up on a append. I Did a import of Import_ProdLocations and for some reason I have imported 1371 records but when I run my query it only collects 1362. I have scanned over all the records and everything is correct.

INSERT INTO ProdLocations ( LocID, ProductID, SkuID, QtyLoc )
SELECT Locations.LocID, Import_ProdLocations.ProductID, Import_ProdLocations.SkuID, Import_ProdLocations.QtyLoc
FROM Locations INNER JOIN Import_ProdLocations ON Locations.LocNm = Import_ProdLocations.LocationNm;

Open in new window



It has taken me all day to split everything up between the tables and make sure they are still in sync. I have almost completed it but this import_prodlocations is not working and I have to have them all in order for them to be in sync.

I asked a question here and Pat helped me figure out how to append for the most part. I had to do some more research and experiment to figure that one out.

I currently have 2304 SKUs and 9216 Products

I have not ran the code yet until I have finished all my table imports. What is that code for anyways?

Ok Assemblies is a Parent Product and we take Parts from it called children. Which Children can have Children and so on. All these Parents, Children, and Grandchildren also have SKUs.

Kits is something we make not buy. So it is a collection of SKUs to make a product and not a specific SKU itself. If that makes sense.

Thanks for the help.
0
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 250 total points
ID: 41838196
Sorry I've not commented, but life has been busy all of a sudden.

Just as a follow up on the to/from locations in a single transaction record:

"the point is to record what is going in and what is going out -- as long as that is being done, you are good ;) ~ Not a debate, just different ways to do the same thing. Jim's method uses 2 records, mine does everything with one. "

 Well a bit of a debate.  It's actually a denormalized design to have a to/from location in the same record and here's two ways to see that:

1. On any transaction other than a move of inventory from one location to another. one of  locations will always be NULL because you can't put a value in it ever (if a PO receipt, the "FROM" would be null)

   That means some transaction records are a different shape (as in the number of fields that can be filled in) from others.  You should not end up with that in a fully normalized design.  I always should be able to supply a value for a field.   I may not know it at the moment (that's what a NULL is for), but at some point, I should be able to fill it with a value.

2. If you ask the question "Show me all the transactions involving location XYZ".   You can't do it without going to each record and looking at both the from and the to locations of each record.

With that said, I've seen inventory/MRP systems done both ways.   With a From/To in the same record however, the logic was always more complex.  

 I've always found that your better off to have a single location in the record and have two records.

 I haven't looked at the rest of the design, but the important thing is to test it with dummy data and ask lots of questions related to fetching data and seeing if you can answer them easily or not.

  Walk through the process of receiving goods, putting it in inventory, doing various transactions on them (ie. cycle counts), making assemblies and kits (and I would not have separate tables for these), building kits, tracking them,  and then selling them.

Jim.
0
 

Author Comment

by:Dustin Stanley
ID: 41838252
As I said:

"Ok Assemblies is a Parent Product and we take Parts from it called children. Which Children can have Children and so on. All these Parents, Children, and Grandchildren also have SKUs. 

Kits is something we make not buy. So it is a collection of SKUs to make a product and not a specific SKU itself. If that makes sense."

 Wouldn't I need two separate tables? one is a SKU and the other is a product.

We don't buy a whole kit so we would not have a SKU ever for it.
0
 
LVL 18
ID: 41838272
>"What is that code for anyways?"

At the top of the code, this is explained:
   'will add the following tracking fields to every table:
   'IDadd, IDedit, dtmAdd, dtmEdit

the statements to add IDadd and IDedit are commented -- it can be run later with those lines uncommented when you want to add tracking for which user added and edited a record.  I use database properties in the FE to keep track og whose front-end it is.  Since you are now putting records in your tables, it would be good to add fields to track at least when records were added.  I add dtmEdit when I add dtmAdd.

> "That means some transaction records are a different shape (as in the number of fields that can be filled in) from others.  You should not end up with that in a fully normalized design."

that is a good point, Jim.  This, however, turned out to be a good method in the past for a PartsMove feature.  Records needing LocID2 could be shown for the scanned item and the user pick which one it was (usually the only one on the list) and then the record was updated
-- or usually when the items were loaded, it was already known where they were going and LocID2 was filled at the same time.  I agree, however, that it is not normalized since LocID is repeated -- easier to see, however, if they are balanced.  LocID1 and LocID2 both end up getting filled where there is not a problem.

>"imported 1371 records but when I run my query it only collects 1362"

those are probably the records that don't match Locations.LocNm

to see the ones that don't match:
1. right-click on the join line between Locations and Import_ProdLocations
2. edit join properties
3. choose to show all records from Import_ProdLocations,
and only those from Locations where they match
4. under Locations.LocID on the grid, use criteria --> Is Null

when appending records, you should always show all records from the import table so data can be fixed. Obviously when you do append, you will want to remove the Is Null criteria
0
 
LVL 57
ID: 41838337
<< Wouldn't I need two separate tables? one is a SKU and the other is a product.>>

No, not necessarily.   When you step back, both SKUs and Kits are the really the same thing:  Parts related to other parts in a parent/child relationship.

 But here's the deciding factor (and the way you should be approaching the database design):  

1.  List all the things you know about each "thing" your trying to describe  (attributes or facts about something).
2. Make sure you can provide a value of each of those attributes (fields) for every instance of that thing (row in a table).

For example:

ParentPart#
ChildPart#
QPA (Qty Per Assembly)

Can I do both SKU's and Kits the same way?   If that is the same list of attributes for each, and I can fill in a value for each, then they are the same type of "thing".

 One happens to be something you get, the other you sell, but if that's the only difference, then they are only  different types of parts.  So all you would need is a "type flag" in your part master.

Jim.
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 18
ID: 41838353
the reason for the Products table is because pricing is different depending on the the Condition of each SKU. As Jim said, however, Kits simply specify what SKUs are in each kit.  Kits does not have pricing information. Just like the other SKUs, Kit SKU records would be added to the Products table for each condition you sell to track its pricing.
0
 
LVL 57
ID: 41838356
Just to make that a bit clearer, say I wanted to keep track of Pens and Auto's.   So I make a table called "tblObjects", and I have this field list:

Make
Model
Year
VIN
Ink Color

First record:   Chevy,  Silverado, 2005,  177S8EDDSS713733, null
Second Record:   Montegue, Elite, 2005, NULL, Blue

Even though the auto and pen are "objects", they are not the same "thing"; I can never have a VIN number for a pen and I can never have an ink color for a auto.   So I need another table as they are two different things.

 This is a rather simplistic example, but in a nutshell, that's how you go about starting a database design.

Jim.
0
 

Author Comment

by:Dustin Stanley
ID: 41838399
Ok I will be working on this here soon thank you. but Crystal the code you gave...I have already added them fields in some tables. Will this duplicate them fields or just move on past the tables that already have them. Thanks!
0
 
LVL 18
ID: 41838417
you're welcome
it will just add tracking fields to tables that don't have them yet -- so you can run it anytime there are tables where info is needed.  The message box at the end will tell you what was added. Down the road, when you are ready to deploy to multiple users, you will want to run it again to add the user tracking fields, assuming that is how you will do it.  I assign each user a long integer and each front end knows who the user is.

After you paste the code in to a new module (Insert, Module from the menu and paste after the compiler directives at the top -- Option statement(s)), from the menu: Debug, Compile then Save. Am assuming there won't be errors.  Save the module with a name that is NOT any procedure name ... like "mod_Field_Add"

to update dtmEdit (and possibly the userIDs), a general function is called by each form BeforeUpdate event -- general function used so there is just one place to change it when you add more tracking fields.
0
 

Author Comment

by:Dustin Stanley
ID: 41838665
Ok I put the IsNull in the criteria and it says data type mismatch in criteria expression

INSERT INTO ProdLocations ( LocID, ProductID, SkuID, QtyLoc )
SELECT Locations.LocID, Import_ProdLocations.ProductID, Import_ProdLocations.SkuID, Import_ProdLocations.QtyLoc
FROM Locations RIGHT JOIN Import_ProdLocations ON Locations.LocNm = Import_ProdLocations.LocationNm
WHERE (((Locations.LocID)="IsNull"));

Open in new window


db81.jpg
0
 
LVL 18
ID: 41838669
Is Null is 2 words and does not go in quote marks

IsNull, one word, is a function that needs a parameter and would not be used in the criteria

you also want to add LocationNm to the grid
0
 

Author Comment

by:Dustin Stanley
ID: 41838727
Great I got it appended correctly! Thanks.

For a flag field what would I do exactly. For ProdMovements table I need an IN and a OUT. Would this be a separate table
IN = 1
out =2

How would this play out on a form could I use a button for each?

I am going to try and test different ways and try to set up a simple movement form.
0
 
LVL 18
ID: 41838845
great!

If you are going to record IN or OUT (which I will refer to as InOut) then you should only use one LocID.

For the "move from" action, set the Value of InOut to -1 so you can multiply that by the Qty and get a negative number. For the "move to" action, set the Value of InOut to +1, or 1, so it will be a positive number adding to the quantity. InOut would be stored as an Integer.

InOut would be in the ProdMovement table. It could be a control on the form with Visible=No.
0
 

Author Comment

by:Dustin Stanley
ID: 41838851
Ok you lost me!

What data type would InOut be in the ProdMovements Table?

For the ACTION??? What do you mean? Is that set up on the form or what?
Same for the Value- 1 +1
0
 
LVL 18
ID: 41838864
InOut would be stored as an Integer.

action is either take stuff out or put stuff in

would there be several products to move at a time? You may want to scan or fill location into unbound control at top which would be the value in each record for LocID (Visible can be No if this is the case) -- use form BeforeUpdate event to fill LocID for each record. Controls that show would be Qty and ProductID (which needs to go into the table), assuming there will also be a condition.  You have to decide if you want them to fill qty or have them do them one at a time.
0
 

Author Comment

by:Dustin Stanley
ID: 41838889
OK there most likely will be multiple items moved at the same time. Lets say for a shelf Condensing.
OUT:
5 from shelf X1
12 from shelf X2
8 from shelf W5

IN:
 25 onto shelf Z9.

action is either take stuff out or put stuff in

Well I know that LOL! But how will access know if I am IN or OUT ing a product.
I know it has something to do with the +1 -1 values you spoke of but I am unsure where to place them or how to use them.

Yes there will be a condition. That is where the ProdConSku comes into place (Product = (Condition + SKU)) and this is what I want everything to revolve around. All the items are barcoded with their proper ProdConSKu.

No I have not decided yet if I want them to be able to put in a quantity yet or not. But I know you can set certain things up to type in a quantity or every scan of a barcode with a barcode reader equals 1 count. Scan 10 items and 10 is the quantity going in or out the shelf.
0
 
LVL 18
ID: 41838912
>"All the items are barcoded with their proper ProdConSKu."
so this can be collected with a combobox -- store ProductID

you have already built a query to concatenate SKU and CondCode. A combo will have the first column be ProductID and what is actually stored (ColumnCount=2. ColumnWidths = 0;2" or something like that. Listwidth=sum(ColumnWidths) + 0.2" for scrollbar so, for instance, 2.2") so the first column won't show.

> "But how will access know if I am IN or OUT ing a product"

you need to decide if you will have them specify that on the form with an unbound control they can flip or you will make them go to the main menu and set things yourself when the form is loaded.  Perhaps you may want a different color as well as a different title for In and Out, along with different values to use before the record is updated.  There is no need to make 2 separate forms to do this but you can if that is easier for you ... and if you do make 2 forms, name them so they sort next to each other alphabetically.
0
 

Author Comment

by:Dustin Stanley
ID: 41839117
or you will make them go to the main menu and set things yourself when the form is loaded.  

Can you clarify for me. I really appreciate all the help you guys and GAL do. It really makes me think about our process. Which when I am done the process will be COMPLETELY different for the good.


I can give you a simple example. When we receive the items in they just have to be put away. I would like a simple setup for that. Receiving is called "First Floor Finish"  it then goes upstairs to be inventoried to a shelf like X1, W3, Z9, etc...
0
 
LVL 18
ID: 41839130
that is another question
0
 

Author Comment

by:Dustin Stanley
ID: 41839133
For the "move from" action, set the Value of InOut to -1 so you can multiply that by the Qty and get a negative number. For the "move to" action, set the Value of InOut to +1, or 1, so it will be a positive number adding to the quantity.

Are you saying to literally type 1 or -1 into the record field InOut in the ProdMovements Table?
0
 
LVL 18
ID: 41839134
no, code can supply the value
0
 

Author Comment

by:Dustin Stanley
ID: 41839286
Ok I see how this works I believe.

This may be completely off the wall but the multiplying is throwing me off.

In the field when "FROM" is the action set it to -1 and then I would have another field or text box in the form where the count will go of how many items was removed. I would create a calculated field "3rd" field or..... where

Quantity x -1 = -Quantity
0
 

Author Closing Comment

by:Dustin Stanley
ID: 41839324
Thank you for the help. I am going to try and absorb this info as much as possible. THANK YOU!
0
 
LVL 18
ID: 41839358
you're welcome ~ happy to help

when you multiply a number by -1, it becomes negative. If you add a negative number to a value, you are, essentially subtracting the value.

if locations are specific like shelf# then LocID would not be in an unbound control that applies to a bunch of records; it would be in a bound control that is filled every time.  If you have >1 warehouse, perhaps Locations also needs a BldgID (building)
0
 

Author Comment

by:Dustin Stanley
ID: 41839368
See that is the part I am working on now and just lost. Reading it over and over and...LOL I think it is SLOWLY absorbing!  I get the multiplying as in mathematical just not the form set up with it all. And yes there will always be a specific place it is coming from or going to.
0
 

Author Comment

by:Dustin Stanley
ID: 41839371
Just one building for now. Multiple floors though.

Do you have any forms or know where I can download a sample database with similar forms to mine so I can dissect it and understand better.
0
 
LVL 18
ID: 41839382
Sketch the form on paper and think about it while you sleep. Things will be more clear in the morning.

You should add FloorNbr to your locations table.

You are reaching a point where you need to start writing code, which is something else new for you.  

Learn VBA  
http://www.AccessMVP.com/strive4peace/VBA.htm

It is not hard to learn the basics of VBA ... it makes sense. The 3 chapters (all I wrote) posted here will not take you long to read. Chapter 1 ... maybe 20 minutes. Chapters 2 and 3 you can read for logic but not memorize -- they are there for reference and foundation.
0
 

Author Comment

by:Dustin Stanley
ID: 41839390
Sounds Good Thanks!
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
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…
Viewers will get an overview of the benefits and risks of using Bitcoin to accept payments. What Bitcoin is: Legality: Risks: Benefits: Which businesses are best suited?: Other things you should know: How to get started:

708 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

19 Experts available now in Live!

Get 1:1 Help Now