asked on

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.

mbizup

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.

johnmadigan

ASKER

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?

mbizup

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

johnmadigan

ASKER

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.

ASKER CERTIFIED SOLUTION

mbizup

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mbizup

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.