Page 2 of 2 FirstFirst 12
Results 16 to 21 of 21

Need to concatenate one field in new table

  1. #16
    MadTom's Avatar
    MadTom is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Location
    CT and VT
    Posts
    116

    More info. needed

    Hi John, Maybe you didn't understand what I wanted. Attached is a table (BinsWhatIWant) in the database, this is what I need the Query to output. This is just some of the data in my Bins table as a sample. Each part has a location (Bin #). My Bins Data is in tblBinsA.

    This is what I want my Labels to look like below. Each Bin has a number of compartments from 1-X and I will print a label to go on the front of Bin. As I get the Query right I should have the fields to do this. I have many more Tags and Bin types in this database and will be adding more, but fields will be the same as they are now. I didn't say it was easy!
    Many thanks for your patience, I owe you an ouzo!
    Tom in CT

    Labels
    Bin 1
    Resistor 1

    1-24
    Ω Blk

    # Comps 1
    Bin 2
    Resistor 2

    1-24
    Ω Blk

    # Comps 1
    Bin 3
    Resistor 3

    25-99
    Ω Blk

    # Comps 1
    Bin 4
    Resistor 4

    25-99
    Ω Blk

    # Comps 1
    Bin 5
    Resistor 5

    100-999
    Ω Brn

    # Comps 1
    Bin 6
    Resistor 6

    100-999
    Ω Brn

    # Comps 1
    Bin 7
    Resistor 7

    1K-99K
    Ω Red

    # Comps 1
    Bin 8
    Resistor 8

    1K-99K
    Ω Red

    # Comps 1
    Bin 27
    Diode 27

    LED 2W

    # Comps 1
    Bin 28
    Diode 28

    LED 3W 4W

    # Comps 1
    Bin 31-32
    Capacitor 1-2

    10pf-99pf

    # Comps 2
    Bin 33-34
    Capacitor 3-4

    100pf-999pf

    # Comps 2
    Bin 35-36
    Capacitor 5-6

    .001uf-.009uf

    # Comps 2
    Bin 37-38
    Capacitor 7-8

    .01uf-.09uf



    # Comps 2
    Bin 39-40
    Capacitor 9-10

    .1uf-1uf

    # Comps 2
    Bin 41-42
    Capacitor 11-12

    Misc

    # Comps 2
    Bin 71-74
    IC 1-4

    7400, 7401, 7402, 7403

    # Comps 4
    Bin 75-78
    IC 5-8

    7404, 7405, 7406, 7407

    # Comps 4
    Bin 79-82
    IC 9-12
    7408, 7409, 7410

    # Comps 4
    Bin 83-86
    IC 13-16
    7411, 7412,74109

    # Comps 4
    Bin 87-90
    IC 17-20

    7414, 7417, 7420, 7422

    # Comps 4
    Bin 91-94
    IC 21-24

    7426, 7427, 7428, 7430

    # Comps 4
    Bin 95-98
    IC 25-28
    7432, 7436,7437-38, 7442

    # Comps 4
    Bin 99-102
    IC 29-32

    7447, 2631?, 7451, 7472

    # Comps 4
    Attached Files Attached Files

  2. #17
    accesstos's Avatar
    accesstos is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    163
    Tom, οbviously, something escapes me!...
    Τell me please, by what rule you are grouping the ICs set 71-74?
    What is the common value in this set;

  3. #18
    MadTom's Avatar
    MadTom is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Location
    CT and VT
    Posts
    116
    Maybe what we need is to make a new field , P_Type & Bin_Num & Bin_NumComps, and group on that field?
    71-74 are grouped on 4 compartments and then Bin_Tags would show 7400, 7401, 7402, 7403 for that group and go on to next Bin_ID.
    The number of compartments are all in the same Bin, but all have a different Bin_ID
    so 71-74 IC with 4 compartments are all in one Bin with the Bin Number IC 1 to 4. (maybe I should one number for that Bin?)
    I the case for 31-32 Capacitor, all are I the same Bin (1-2) with two compartments, with one Tag 10pf-99pf
    Maybe I need to do it more in VBA?
    Thanks,
    Tom

  4. #19
    accesstos's Avatar
    accesstos is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    163
    Hi Tom!

    I think that I did something.

    I created a table from qryBins, and with the assistance of Excel, I added a field (Comp_ID) that identify the compartments and helps in grouping of bins and I think that the results are those that you desire.

    Have a look into the attachment.

    Cheers,
    John
    Attached Files Attached Files

  5. #20
    MadTom's Avatar
    MadTom is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Location
    CT and VT
    Posts
    116
    Yes I believe you did it! My first tests looks good, I'm going to test some more later.
    What is the formula for getting data in
    (Comp_ID) field? Looks like x=x+1 if Bin_CompNum is >1
    I'm going to go over your Query to see how it works so I understand it more.
    Thanks so much for your help! Tom


  6. #21
    accesstos's Avatar
    accesstos is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    163
    To the contrary, x=x+1 if Bin_CompNum=1 Else x=x

    Click image for larger version. 

Name:	xl.JPG 
Views:	8 
Size:	68.1 KB 
ID:	39428

    Now, I have to wait for my ouzo.

    Cheers,
    John

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

Similar Threads

  1. Replies: 14
    Last Post: 07-15-2019, 07:43 PM
  2. Replies: 2
    Last Post: 06-12-2019, 05:57 PM
  3. Concatenate 2 field with brackets
    By jj1 in forum Access
    Replies: 4
    Last Post: 02-13-2016, 06:40 PM
  4. Replies: 17
    Last Post: 03-16-2014, 10:52 PM
  5. Replies: 3
    Last Post: 08-16-2011, 02:20 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
  •  
Tech Forums: Microsoft Office Forums