Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    gregu710 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2012
    Location
    Frederick, CO (near Longmont and Boulder)
    Posts
    40

    query is re-organizing data in a concatenated string

    As mentioned in a previous post, I'm putting together a shipping database. We scan in a string of barcodes, separate out the Serial Numbers from the Barcode Data, concatenate them together as a string with asterisks as a delimiter and join them together with a date code and manufacturing line identifier. Throughout all of the data manipulations I am able to maintain the Serial Numbers in my table in the order which they were scanned in. The very last function is to concatenate the Serial Numbers and Lot information together into one long string which will then be output to a label creation software. And up to that point, the Serial Numbers are still in the correct order (based on them being linked to an autonumber field in the initial table) Upon the final concatenation, the order of the Serial Numbers is being changed, sometimes 17 out of 18 Serial Numbers will be correctly ordered with just one being randomly placed in the string out of order. I can't find anything in the code which can cause this. Any help would be greatly appreciated. Here is the code in SQL that I use for concatenating.

    INSERT INTO tempMotorPalletLabel ( PalletLabel )


    SELECT DISTINCT tempMotors_to_Warehouse.PalletNumber&"*"&tempMotor s_to_Warehouse.Count&"*"&tempMotors_to_Warehouse.P artNumber + '*' + Replace(Replace(ConcatRelated("SerialNumber","temp Motors_to_Warehouse","PalletNumber='"&PalletNumber &"'"),' ',''),',','*')
    FROM tempMotors_to_Warehouse;


    And this SQL Query transfers the concatenated string to a table that my Label Software queries for data to create the label with.

    INSERT INTO AccessMotorLabelData ( PalletNumber, [Count], PartNumber, PalletLabel )
    SELECT DISTINCT tempMotors_to_Warehouse.PalletNumber, tempMotors_to_Warehouse.[Count], tempMotors_to_Warehouse.PartNumber, [tempMotorPalletLabel].PalletLabel
    FROM tempMotors_to_Warehouse INNER JOIN tempMotorPalletLabel ON tempMotors_to_Warehouse.PalletLabel=[tempMotorPalletLabel].PalletLabel;

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Are you saying the result from ConcatRelated() is out of order? If so, the fix would be in that function. What does it look like?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Are the serial numbers always the same length? Does order really matter to the label print operation? Show sample data.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    gregu710 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2012
    Location
    Frederick, CO (near Longmont and Boulder)
    Posts
    40
    Paul, yes, the result is out of order, but only in the Serial Number part of the String. The Concatentated String is comprised of a 9-10 digit alphnumeric Pallet Number, an asterisk delimiter, a 1-2 digit Unit Count of the number of Units in the Pallet we are building, another asterisk delimiter, a 12 digit alphnumeric Part Number, another asterisk delimiter, and then from 1-18 6 digit numeric Serial Numbers each delimited with an asterisk. Here is the code that creates that string:

    INSERT INTO tempMotorPalletLabel ( PalletLabel )
    SELECT DISTINCT tempMotors_to_Warehouse.PalletNumber&"*"&tempMotor s_to_Warehouse.Count&"*"&tempMotors_to_Warehouse.P artNumber + '*' +
    Replace(Replace(ConcatRelated("SerialNumber","temp Motors_to_Warehouse","PalletNumber='"&PalletNumber &"'"),' ',''),',','*')
    FROM tempMotors_to_Warehouse;

  5. #5
    gregu710 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2012
    Location
    Frederick, CO (near Longmont and Boulder)
    Posts
    40
    Quote Originally Posted by June7 View Post
    Are the serial numbers always the same length? Does order really matter to the label print operation? Show sample data.
    Hi June, yes, the Serial Numbers are always 6 digits long, all numeric. The order does matter somewhat. The Pallets that I am labeling hold up to 18 110 lb Motors, 4-6 in each tray, stacked 3-4 trays high. IF I need to know which tray the Motor is in because we have to break the Pallet apart, then by knowing the order of the Serial Numbers I can pinpoint how much of the Pallet I have to break apart to get to that specific unit. If it weren't for that I wouldn't really care, because then it would be good enough to know which units were in the Pallet, not specifically where they are. Here is an example of a typical concatenated string. I described the various fields in my response to Pbaldy since he asked about it as well.

    29911MLa21*18*AK21030002-A*100312*100423*100418*100429*100198*100376*100373 *100443*100398*100289*100159*100364*100417*100435* 100378*100434*100407*100297


    Hmm, I see that there is a space between 100373 and 100443 in addition to the asterisk, but it looks like the Forum put that in when I scanned in one of my labels because I don't see it when I scan the same label into Notepad. We used the asterisks because we break this string apart when it's fed into our SQL Server so that we have a permanent record of which units were shipped by the Pallet Number (which tells us the ship date).

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    My question is whether the query result is out of order, in which case perhaps an ORDER BY clause is needed, or the result of that function is out of order, in which case we need to fix the function.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    gregu710 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2012
    Location
    Frederick, CO (near Longmont and Boulder)
    Posts
    40
    OK, here is a sample of the data that is used to create the concatenated string, in the form that calls up the SQL Query to create the concat string (in the attachments to this post). And also a screenshot of the Print Form where it shows the jumbled Serial Numbers. Interesting thing, I just ran through this twice, and it kept everything in order, but the third time jumbled everything. Going to try clearing ALL of my tables of data, refreshing and compacting the database and see what happens.

  8. #8
    gregu710 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2012
    Location
    Frederick, CO (near Longmont and Boulder)
    Posts
    40
    As you can see with my attachments on the other Post, I have the Serial Numbers and all associated data Ordered by the Unit Number. This number is assigned at the very first step (as far as I can tell and how I intended to design it) when a new Unit Number is assigned via Autonumber when each unit is scanned in. And in each subsequent table I continue Ordering by that Unit Number so that they always stay in the same order. That part SEEMS to be working to the very end. In fact, even after I print the label with the jumbled Serial Numbers, I can still go back into the Source table that is used to provide the data for the concat string and all of the info is still in its correct order.

  9. #9
    gregu710 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2012
    Location
    Frederick, CO (near Longmont and Boulder)
    Posts
    40
    Quote Originally Posted by June7 View Post
    Are the serial numbers always the same length? Does order really matter to the label print operation? Show sample data.
    Here is all of the Code in the Form which drives the Concat Query, the transfer of data and the opening of the Form where the Label will be created, maybe it's in there.

    Private Sub Command14_Click()
    'Used to Create New Motor Pallet
    Dim PalletID As String
    Dim PalletNr As String
    Dim ShipDate As String

    Text45.SetFocus
    PalletNrString = Text45.Text
    Text47.SetFocus
    ShipDateString = Text47.Text
    DoCmd.SetWarnings (False)
    DoCmd.RunSQL "UPDATE tempMotors_to_Warehouse SET [PalletNumber] = ('" + PalletNrString + "'),[ShipDate] = ('" + ShipDateString + "')"
    'DoCmd.ShowAllRecords
    DoCmd.SetWarnings (True)

    'Updates Count of Units on Pallet
    Dim UnitCount As String
    Text36.SetFocus
    Text36.Text = DCount("*", "tempMotors_to_Warehouse", HousingID)
    Text36.SetFocus
    UnitCountString = Text36.Text
    DoCmd.SetWarnings (False)
    DoCmd.RunSQL "UPDATE tempMotors_to_Warehouse SET [Count] = ('" + UnitCountString + "')"
    DoCmd.SetWarnings (True)
    'Updates Lot Data in MotorLotData table
    DoCmd.OpenQuery "UpdateLotData"
    'Used to Create Label Data
    DoCmd.RunSQL ("DELETE * FROM tempMotorPalletLabel")
    DoCmd.OpenQuery "MotorPalletLabelCreate"
    DoCmd.ShowAllRecords
    Dim LabelData As String
    Text65.SetFocus
    LabelDataString = Text65.Text
    DoCmd.RunSQL "UPDATE tempMotors_to_Warehouse SET [PalletLabel] = ('" + LabelDataString + "')"
    DoCmd.ShowAllRecords
    DoCmd.OpenForm "MotorLabelPrint"

    End Sub

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    What exactly is out of order? The numbers 100768, 100769 etc? If so, that appears to be coming from the function that you haven't posted.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    gregu710 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2012
    Location
    Frederick, CO (near Longmont and Boulder)
    Posts
    40
    Yes. Not sure which code to post since I've posted the ConcatRelated Query which is stringing them together. On a related note, if I run that concatrelated query once, the data is jumbled (but not in the source table), if I run it again everything is in order.

  12. #12
    gregu710 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2012
    Location
    Frederick, CO (near Longmont and Boulder)
    Posts
    40
    would it help if I uploaded a copy of the database with some sample data loaded into the Form that creates the concatrelated string and which outputs it to the Label Print form?

  13. #13
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You posted the query but not the function it's calling (ConcatRelated). It's the function that's putting those items out of order. Either the function or the db would help.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  14. #14
    gregu710 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2012
    Location
    Frederick, CO (near Longmont and Boulder)
    Posts
    40
    OK, here is a copy of the database. First I scan the Serial Numbers into the Scan Units Form, then click "Check EOL Test Data" button to run some queries on this data, which will then open the Form "Motor Pallet". This is where I assign a Pallet Number to the group of Serial Numbers, and output all of that as a concatenated string to the MotorLabelPrint form to print out my label from another Program (CodeSoft).

    The Motor Pallet Form is already loaded with 12 Serial Numbers, ordered by the order in which I scanned them into my database in the Scan Unit form.....

  15. #15
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Allen's function allows for sorting. Does changing the query to this help?

    INSERT INTO tempMotorPalletLabel ( PalletLabel )
    SELECT DISTINCT tempMotors_to_Warehouse.PalletNumber&"*"&tempMotor s_to_Warehouse.Count&"*"&tempMotors_to_Warehouse.P artNumber + '*' + Replace(Replace(ConcatRelated("SerialNumber","temp Motors_to_Warehouse","PalletNumber='"&PalletNumber &"'", "SerialNumber"),' ',''),',','*')
    FROM tempMotors_to_Warehouse;
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Formatting a concatenated field
    By kris335 in forum Access
    Replies: 14
    Last Post: 09-21-2011, 09:56 AM
  2. Replies: 2
    Last Post: 08-18-2011, 11:40 AM
  3. Formatting a Concatenated Field
    By CoachBarker in forum Queries
    Replies: 2
    Last Post: 09-23-2010, 09:12 AM
  4. Sum values in concatenated fields
    By jdrubins in forum Reports
    Replies: 4
    Last Post: 09-01-2009, 07:20 AM
  5. organizing serial numbers and quantities
    By Diomeneus in forum Access
    Replies: 0
    Last Post: 11-14-2008, 03:17 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums