Results 1 to 12 of 12

Create sequential number in subform based on ID in main form

  1. #1
    Ginger is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Posts
    14

    Create sequential number in subform based on ID in main form

    How to create sequential number (Organism ID) in subform (Identification tab) based on Sample ID in main form (Culture Result Details)


    such as Sample ID: STC-300 ---> Organism ID: STC-300-1, STC-300-2, STC-300-3 and so on
    I have 6 types of Sample ID in the table (AR-001...,ARC-001...,MS-001..., MSC-001..., ST-001..., STC-001...)

    Click image for larger version. 

Name:	Main Form.PNG 
Views:	32 
Size:	11.2 KB 
ID:	38085

    Click image for larger version. 

Name:	SubForm.PNG 
Views:	32 
Size:	12.3 KB 
ID:	38082

    Click image for larger version. 

Name:	Relationship.PNG 
Views:	31 
Size:	18.5 KB 
ID:	38084

    Thank you

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    13,913
    Not sure if I understand. If you present data on a subform, you could have the name of the sample and a "replicate number" or similar. If you assign a number to a subform record, wouldn't/couldn't that number change with a different sort order?

    Perhaps you could describe what you have and what you need in simple terms so we understand the need in context.

    I would recommend that you do not use field names with embedded spaces. They really are a syntax issue and should be avoided. Similarly, your field name Group(Form) contains "special character(s)" in Access terms --the brackets -- and could also raise syntax issues.

    Can you post a copy of your evolving database in zip format?

  3. #3
    Ginger is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Posts
    14
    Sample ID is a format of the company that I work for. I cannot change it but I can change Organism ID (##29901, ##29902.... or ##-29901, ##-29902....).
    I created Organism ID which is primary key to associate susceptibility testing table and inventory table.
    "Replicate number" is Sample ID in identification table but was hidden.

    Thank you for your help.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,482
    Generating custom unique identifier is a common topic. Here is one https://stackoverflow.com/questions/...72420_55525189

    Alternatively, let textbox RunningSum property in report calculate this sequential number for each sample ID.
    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.

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    13,913
    I suggest you provide a clear description of what you working with and trying to achieve. You showed relationships in your post #1, but you do not have relationships in your database in post #3. The only way to know if your tables and relationships are designed to support your "business" is to have a clear understanding of your business facts.

    What is the meaning and purpose of field ID in table stIdentification? Or in any of your tables for that matter?
    You will get more focused help if readers understand your business and requirement.
    Good luck.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,482
    Linking tables on primary keys is a 1-to-1 relationship. Might as well be 1 table.

    Advise not to use spaces nor punctuation/special characters (underscore only exception) in naming convention
    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.

  7. #7
    Ginger is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Posts
    14
    I really wanted to do but it is not possible to create 1:1 relationship. I work as a researcher, there might have no pathogen or many pathogens in a specimen and each pathogen have to perform antimicrobial susceptibility testing (several drug patterns) according to the guidelines. so the 1st table is general information of specimens, the 2nd table is pathogen results and the the 3rd table is susceptibility testing results.
    Example: Growth of C.ureolyticus and Salmonella group E in STC-300 specimen after that C.ureolyticus performs susceptibility testing as CIP, TET, E, NA (drugs) and Salmonella group E does AZM, CIP, AM, SXT, TE, CRO etc.
    I know it's difficult to understand but this is what microbiologists do.

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    13,913
    Ginger,
    You are the microbiologist. You are asking a concepts/technical database question. You are receiving answers/suggestions from members who have/are working with databases. I am going to point to a few links where there has been dialog/responses to microbiological activities that involved Access databases.

    These are listed latest to oldest and will give you some idea of the database terms in these "microbiology-like" threads.
    https://www.accessforums.net/showthread.php?t=75464
    https://www.accessforums.net/showthread.php?t=64538
    https://www.accessforums.net/showthread.php?t=60035
    http://www.utteraccess.com/forum/lof.../t1996476.html

    I can assure you that table design and relationships are the heart of any useful database. As you know the usefulness of your antimicrobial susceptibility testing is dependent on the details of sample collection and testing, same with database --design and test to ensure the database supports your requirements. This isn't necessarily trivial--the devil is in the detail.

    Forum members will help as much as possible, but we need to understand the "business" - sample collection and testing - clearly in simple terms.

    Good luck with your project.

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,482
    You say "it is not possible to create 1:1 relationship" yet that is exactly what you have in your db. Linking on primary keys is 1:1.
    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.

  10. #10
    Ginger is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Posts
    14
    Actually, I asked the database design question before https://www.accessforums.net/showthread.php?t=76386
    @Orange If I am not mistaken, the threads in post#8, they were creating an antibiogram data (pathogens, antimicrobial agents and interpret columns)
    which doesn't have to inform each specimen and creating the inventory of organisms which every project in research has to preserve organism at least 20 years.

    Please recommend how to redesign a database as the following below.
    there might be up to 8 pathogens in a specimen and each pathogen performs susceptibility testing depending on nature resistance and CLSI guidelines (about 10 drugs)

    Click image for larger version. 

Name:	Capture.PNG 
Views:	21 
Size:	46.3 KB 
ID:	38118

    Thank you for you help.

  11. #11
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    13,913
    Ginger,

    Have you looked through the materials at each of the links I gave in my last post?
    In some links you will see discourse on the freezer and related storage locations. These may give you some thoughts to consider for design.
    Terms like MIC and ZoneDiameter, Interpret need to be described in clear simple terms for readers to offer focused responses. I have seen several database threads related to lab testing and evaluation of samples and replicates.

    You are showing us a "proposed report format" -and that is an output. We need to see the business facts/rules for you collection, testing, interpreting to see how the entities and relationships relate in your "microbiology business".

    To help you with design and to experience the steps involved I recommend that you work through 1 or 2 tutorials from RogersAccessLibrary mentioned in the Database Planning and Design link in my signature. Each has a description and a problem statement and a solution, and will take about 45 minutes to complete/work through. You will learn a process that you can use with your database. There are other supporting materials within that link.

    Data storage is quite different than data presentation. Access and relational database management systems are designed to work most efficiently with certain storage structures and concepts. It is important to get your data into such structures in support of your requirements.

    The draft data model here reflects many of the facts learned in the thread. It was the dialog that took ideas and identified facts within the business and requirements.

    Just as microbiology concepts and techniques can not be learned/understood without some practical experiences, so to with database concepts, design and testing.

    Perhaps you could tell us a little of yourself with respect to database and Access. It may help readers focus on specific features/issues to be addressed.

    Good luck with your project.

  12. #12
    Ginger is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Posts
    14
    The main objective of creating the access database is to collect data in statistics, such as percentage of antibiotic resistance in diarrheagenic bacteria, the prevalence of bacterial intestinal pathogens etc.
    the coordinators are in charge of collection and transportation specimens which will be submitted from cases of gastroenteritis. Scope of my work, staffs receive specimens, culture, identify pathogens and perform susceptibility testing.
    Terms of MIC and Zone Diameter are breakpoints to interpret antimicrobial susceptibility results (Susceptible, Intermediate and Resistant) according to CLSI guidelines.
    **MIC (Minimum Inhibitory Concentration) is the lowest drug concentration that inhibits microorganism growth which can be determined by E-test, Broth microdilution etc.
    **Zone diameter is a circular area around the antibiotic agents which inhibit bacteria by disk diffusion method.

    Thank you for your suggestion

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

Similar Threads

  1. Replies: 39
    Last Post: 07-28-2018, 12:27 PM
  2. Replies: 13
    Last Post: 03-02-2018, 12:04 PM
  3. Field in the main form based on a subform
    By azhar2006 in forum Forms
    Replies: 4
    Last Post: 03-08-2014, 11:24 PM
  4. Replies: 4
    Last Post: 07-27-2011, 12:42 PM
  5. Sequential number on subform
    By jpkeller55 in forum Access
    Replies: 8
    Last Post: 03-17-2011, 09:54 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
  •  
Tech Forums: Microsoft Office Forums