Results 1 to 6 of 6
  1. #1
    JesKR is offline Novice
    Windows 8 Access 2016
    Join Date
    Jan 2019
    Posts
    2

    Help making a list from two fields

    Hi

    I'm new to access so bear with me...

    I have made a database where i register submissions to a laboratory. The registration form assigns a number (ID) to each submission and you enter the number of samples that were included in this submission. From these two fields i need to generate a list.

    So to give an example of what i want to do:

    A submission is ID'ed S0001, S0002 and so forth. In the first submission (S0001) i have 3 samples. in S0002 i have 2 samples. For each submission i need to generate a "list" (is this even an entity in access?) of the samples named:



    S0001-1
    S0001-2
    S0001-3

    And for the second submission:

    S0002-1
    S0002-2

    How can I make access generate these lists automatically?
    When the list have been generated, I will need to be able to print them on individual labels.

    Please, can anyone help me with this?

    Best wishes
    Jesper

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    So the record has a field with the number of samples?

    Conventional approach is VBA code that writes records to a 'temp' table (table is permanent but records are temporary). Alternately, review http://allenbrowne.com/ser-39.html

    There is a method to use hidden system table MSysObjects https://stackoverflow.com/questions/...s-access-query
    Last edited by June7; 01-22-2019 at 05:20 AM.
    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.

  3. #3
    JesKR is offline Novice
    Windows 8 Access 2016
    Join Date
    Jan 2019
    Posts
    2
    Quote Originally Posted by June7 View Post
    So the record has a field with the number of samples?

    Conventional approach is VBA code that writes records to a 'temp' table (table is permanent but records are temporary). Alternately, review http://allenbrowne.com/ser-39.html

    There is a method to use hidden system table MSysObjects https://stackoverflow.com/questions/...s-access-query
    Yes, there is a field where you enter the number of samples.

    I've looked at what you sent me and in it, I can't find anything that produces the list of sample ID's that are unique to each sample (S0001-1, S0001-2,S0001-3). Or maybe i'm not understanding it...

    But thanks for taking your time to help me.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Post 3 was moderated, I'm posting to trigger email notifications.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Jesper,
    Have a look at this and see if that is what you need.
    Cheers,
    Vlad
    Attached Files Attached Files

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    I built table with fields: SampleID (autonumber), SampleNum (text), SampleQty (number). Here is query to generate the multiple label records:

    SELECT Samples.*
    FROM Samples, (SELECT DISTINCT [Tens]+[Ones] AS Factor, 10*Abs([Deca].[id] Mod 10) AS Tens, Abs([Uno].[id] Mod 10) AS Ones FROM MSysObjects AS Uno, MSysObjects AS Deca) AS F
    WHERE F.Factor<SampleQty
    ORDER BY SampleID;

    Now build a report with that query as RecordSource. Set a group section on SampleID field. Textboxes in Detail section:
    tbxCount
    ControlSource: =1, RunningSum: OverGroup, Visible: No
    tbxSample
    ControlSource: =[SampleNum] & "-" & [tbxCount]
    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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Making a multiple choice list searchable
    By dwheatley in forum Queries
    Replies: 8
    Last Post: 12-13-2017, 03:41 AM
  2. Making my form have a menu list
    By rajikasimu in forum Forms
    Replies: 1
    Last Post: 03-15-2015, 12:38 PM
  3. Replies: 3
    Last Post: 03-05-2014, 11:20 AM
  4. Help with making a call list
    By grizer in forum Access
    Replies: 1
    Last Post: 07-06-2012, 08:16 AM
  5. Making a list on a report
    By tbroy in forum Reports
    Replies: 2
    Last Post: 04-04-2012, 10:01 AM

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