Results 1 to 10 of 10
  1. #1
    Davide is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    38

    multiple insert


    Dear all

    in a lab, there are recurrent sets of tests. For example, analysis of type A are always made of the same list of tests.
    Also, some kind of analysis are repeated often. Therefore, the user would like to set up an analysis of a known type quickly and efficiently.
    For example, let's say that analyses of type A are made of tests 18 to 21.
    Currently, each time a user performs a new type-A analysis, she must manually select the same tests 18 to 21, then add the outcomes (results).
    Ideally, she would like to select only the kind of analysis that she needs to perform, add automatically the relative tests, and then add manually the results. I attacht the current DB design. Is it correct?
    If yes, how can the operation above be performed? By means of a multiple insert into table Outcomes?
    Thanks for your advice,
    Davide
    Attached Files Attached Files

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Build a tBatch table, that hold all these tests:
    batch, Test
    A, 18
    A, 19
    ...
    A,21
    B, 1
    B,2

    then when user picks Test type A, run append query to add all the tests to the target table.

  3. #3
    Davide is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    38
    Quote Originally Posted by ranman256 View Post
    Build a tBatch table, that hold all these tests:
    batch, Test
    A, 18
    A, 19
    ...
    A,21
    B, 1
    B,2
    That is what I have done I think with table TypeTest:

    Click image for larger version. 

Name:	ER.png 
Views:	32 
Size:	33.5 KB 
ID:	47124

    TypeTest contains records like the following:

    A, 18
    A, 19
    etc.

    Quote Originally Posted by ranman256 View Post
    then when user picks Test type A, run append query to add all the tests to the target table.
    This is the missing part. I understand I have to write an SQL Insert into Outcomes from TypeTest where FK_type="A" or something like this.
    Thanks

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    An SQL statement like:

    INSERT INTO Outcomes(FK_analysis, FK_Test) SELECT [enter Analysis key], FK_test FROM TypeTest WHERE FK_Type = [enter Type FK]

    The user inputs can be references to controls on form.
    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
    Davide is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    38
    Thanks June, that is what I am trying to do.
    Still, the "where" condition is on analysis.code and AnalysisType.TypeDescr, the text field (of course I do not use the PKs as inputs)
    This is where I got stuck. This query will get the FK_analysis:
    Code:
    SELECT Analysis.PK_AnalysisFROM Analysis
    WHERE code=[Analysis code?];
    while this one will get all the FK_tests according to an analysis type:
    Code:
    SELECT TypeTest.FK_test
    FROM AnalysisType INNER JOIN TypeTest ON AnalysisType.PK_analysisType = TypeTest.FK_Type
    WHERE AnalysisType.TypeDescr=[Analysis type?]
    I really do not know how to put both into a Insert query

  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,822
    Why would you use TypeDescr field as filter criteria? Why do you want to join tables for this? What is wrong with the SQL I suggested?

    Build a form for creating record in Analysis table and use combobox for user selection of type. User makes choice based on description but code uses key. VBA in a button click could be:

    CurrentDb.Execute "INSERT INTO Outcomes(FK_analysis, FK_Test) SELECT " & Me!PK_Analysis & ", FK_test FROM TypeTest WHERE FK_Type = " & Me.cbxType

    But I might not understand purpose of Analysis table. What is the business process? Do you receive samples of something for testing? How do you document handling of samples?
    Why are there no date fields in any table?

    I built a laboratory database for tracking test results of construction materials samples. The process starts with login of sample which is tracked by SampleID. During login, the material is identified and tests selected. There are dates recorded throughout.

    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
    Davide is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    38
    Thanks June, it seems to me it works.
    Yes, the business process is similar to what you describe: first the user creates an analysis (the user will add other data beside the code, I just didnt show them); then he will add the tests to be performed. If the analysis belongs to an existing type, all the tests belonging to that type will be added automatically to the newly created analysis.
    If you look at the attached DB, I just do not want the form to go from record 1 on... may I just de-select that property?
    Thanks
    Attached Files Attached Files

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    There is no reason to have Analysis form bound to data since both controls are unbound.

    My suggestion was intended for a bound form for creating a new Analysis record and associated Outcomes records. Your form only offers selection of existing Analysis records.
    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.

  9. #9
    Davide is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    38
    Quote Originally Posted by June7 View Post
    There is no reason to have Analysis form bound to data since both controls are unbound.

    My suggestion was intended for a bound form for creating a new Analysis record and associated Outcomes records. Your form only offers selection of existing Analysis records.
    Which I think it’s ok in my case, since Analysis records have already been created. Users only have to select one and assign it a “type”. This will automatically associate to that analysis all the tests to be performed.. what do you think? Thanks a lot

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    There will never be new analyses? Maybe I still don't understand purpose of Analysis table. I was looking at as a sample login table. If that is not correct then where do you login your samples?
    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. Need to INSERT default value into multiple tables
    By skydivetom in forum Programming
    Replies: 18
    Last Post: 04-10-2021, 04:48 PM
  2. INSERT INTO multiple tables from form
    By N7925Y in forum SQL Server
    Replies: 3
    Last Post: 09-19-2016, 03:34 PM
  3. insert into multiple tables
    By vicsaccess in forum Programming
    Replies: 4
    Last Post: 01-25-2016, 07:03 PM
  4. INSERT sql using VBA multiple fields
    By Ruegen in forum Programming
    Replies: 1
    Last Post: 10-28-2014, 01:17 AM
  5. One form insert multiple records
    By Jrzy3 in forum Forms
    Replies: 12
    Last Post: 11-06-2013, 03: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
  •  
Other Forums: Microsoft Office Forums