access make table query

I have a table that has these fields - Loc, Style, #Cases, Pallet, Plant

I need to make a query to make a new table that will assign a serial number to each case on a pallet.

So the original table would look like:

Loc   Style    #Cases    Pallet   Plant
10     AAA         3           P011    02
15     SWS        2           P033    04

The new table will look like:
Loc    Style    Pallet     SN
10      AAA     P011      0200001
10      AAA     P011      0200002
10      AAA     P011      0200003
15      SWS    P033      0400004
15      SWS    P033      0400005

I need to use the #Cases to figure out how many records for each pallet.  Then I would generate a serial number (SN) in sequential order but using the Plant number for the start of each number.

Not sure the best way to tackle this.

Any suggestions?

My goal is to use this final table to run case labels and palet label from a report (Pallet label followed by the cases on that pallet, next pallet label then cases on that pallet etc.)  That way the operator can stick the pallet label on the pallet first then the case labels to the cases.

Thanks for your help.

johnmadiganAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mbizupCommented:
You'd have to use VBA rather than a query.

Something like this:


Function CreateSNs()
      Dim rs As DAO.Recordset
      Dim rsNew as DAO.recordset
      Dim I as integer

      set rs = Currentdb.OpenRecordset("YourOriginalTable", dbopendynaset)
      Set rsNew = CurrentDB.openRecordset("YourNewTable", dbopendynaset)
      if rs.recordcount = 0 then exit function
      Do until rs.eof
           For I = 1 to rs![#cases]
                rsnew.addnew
                rsnew!Loc = rs!Loc
                rsNew!Style = rs!style
                rsNew!Pallet = rs!Pallet
                rsnew!SN = rs!Plant & Format(I, "00000")
                rsnew.update
            loop
            rs.movenext
       loop
       rs.close
       rsnew.close
       set rs = nothing
       set rsnew = nothing
End Function
                 

Open in new window

     

You'd have to adjust this to use your own table and field names.        
0
johnmadiganAuthor Commented:
When I tried to run this function I got a compile error - Loop without Do?

Do I need to create the final table before running this function or will it make a table?

0
mbizupCommented:
Sorry - typo.  My For loop should have ended with a 'Next'.

Try this:

Function CreateSNs()
      Dim rs As DAO.Recordset
      Dim rsNew as DAO.recordset
      Dim I as integer

      set rs = Currentdb.OpenRecordset("YourOriginalTable", dbopendynaset)
      Set rsNew = CurrentDB.openRecordset("YourNewTable", dbopendynaset)
      if rs.recordcount = 0 then exit function
      Do until rs.eof
           For I = 1 to rs![#cases]
                rsnew.addnew
                rsnew!Loc = rs!Loc
                rsNew!Style = rs!style
                rsNew!Pallet = rs!Pallet
                rsnew!SN = rs!Plant & Format(I, "00000")
                rsnew.update
            Next
            rs.movenext
       loop
       rs.close
       rsnew.close
       set rs = nothing
       set rsnew = nothing
End Function

Open in new window

             
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

johnmadiganAuthor Commented:
Thanks for the correction.  I ran the function and it did a great job getting the correct number of records.  The serial number has the plant ID for the first part of the number.

When it goes to a new pallet it starts over to the number 1 for the case.  I need to have the last part of the SN stay in seq. order.  So Pallet 1 will have SN's ending in 1-3 then Pallet 2 would SN's ending in 4-5. The right side of the SN would increment by 1 each time.

Thanks for your help.
0
mbizupCommented:
This should do it:

Function CreateSNs()
      Dim rs As DAO.Recordset
      Dim rsNew as DAO.recordset
      Dim I,J as integer

      set rs = Currentdb.OpenRecordset("YourOriginalTable", dbopendynaset)
      Set rsNew = CurrentDB.openRecordset("YourNewTable", dbopendynaset)
      if rs.recordcount = 0 then exit function
      J = 0
      Do until rs.eof
           For I = 1 to rs![#cases]
                J = J + 1
                rsnew.addnew
                rsnew!Loc = rs!Loc
                rsNew!Style = rs!style
                rsNew!Pallet = rs!Pallet
                rsnew!SN = rs!Plant & Format(J, "00000")
                rsnew.update
            loop
            rs.movenext
       loop
       rs.close
       rsnew.close
       set rs = nothing
       set rsnew = nothing
End Function

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mbizupCommented:
Glad that worked out.

Just as an aside, you should try to avoid field names with spaces and special characters like #cases.  Special characters often have meanings in Access that can interfere with your purpose (hence the need for the brackets).  A better name might be NumOfCases, which would cause no trouble at all.

Depending on where you are at with this database, I highly recommend changing your fieldnames accordingly - it will spare you headaches and errors in the long run.

For a  great resource for naming conventions, look up "Reddick VBA naming conventions".  These are the standards used by most of the regulars here.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.