Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2006
    Posts
    4

    Exclamation Unanswered: Dual Inset Into Statement?

    I currently have two tables in question (tbl-A-Score & tbl-B-ScoreGroup) tbl-B-ScoreGroup contains 39 records that I run a SQL INSERT INTO statement to grab and insert the 39 records into tbl-A-Score (see below) which works fine. This is done to create all the necessary lines in a table for the user to edit. The problem is when I try and insert two pieces of data from a form (PSID, DataDate) in each of the newly created rows in the table automatically. The idea is that the database is doing the work of entering most of the data, or in a sense creating a check list that the user must fill in only two of the blanks in each row of the modified table. I have been unsuccessful in getting these two blank fields filled in for each of the 39 lines created by the statement below. What additions can I make to this statement or what UPDATE, INSERT INTO or other code statement can be made to accomplish this task. Thanks for any help you may offer.



    DoCmd.RunSQL "INSERT INTO [tbl-A-Score] ([Group], Category, Multiplier) SELECT [tbl-B-ScoreGroup].Group, [tbl-B-ScoreGroup].Category, [tbl-B-ScoreGroup].Multiplier FROM [tbl-B-ScoreGroup] WHERE ((([tbl-B-ScoreGroup].RecNo) = " & Count & ")) ORDER BY [tbl-B-ScoreGroup].RecNo;"

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Why are you just creating a copy of tbl-b? I'm confused about the logic behind your solution.

    What is the purpose of this project?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Jan 2006
    Posts
    4

    Arrow

    The logic is that the user will have to enter in 39 records otherwise. This is a pump station evaluation database that uses a decision matrix to score and evaluate the station condition. Table B contains all the groups of items to be scored. WIthout this INSERT INTO statement, when the user enters a data set for a station he/she will have to enter records for each of these 39 "groups", thus adding alot of somewhat annoying repetitive picking from combo boxes. You probably also want to know why there is a tbl-b-Score group, & the reason is this; each group has a multiplier associated with it that is used in the matrix, this multiplier will be changed from customer to customer and needs to be accessable for "non access" trained maintenance personnel. By putting it in it's own table, the user can modify multipliers via a form.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    This sounds funky as hell. Could you try explaining the project in entirety minus the database bits? I'm getting a strong impression that you could greatly benefit from a more robust backend design. This feels a little like you're using Access like it was Excel...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Ahhhh ... Template records ...

    You need to mod the qury to insert those additional columns and mod the select part of the query to return the form derived values ...
    Back to Access ... ADO is not the way to go for speed ...

  6. #6
    Join Date
    Jan 2006
    Posts
    4
    Yes, maybe that's the terminology I was looking for "template Records". the syntax going from selecting values on a table to selecting values on a form is what I'm hung up on. Can you put both in one SQL statement? & if so what would the statement look like? To respond to Teddy's statement, yes it is funky as hell & yes I am trying to make it work like excel, but that's not the problem. All of the matrix side of the DB has been figured out and functions properly. What I'm working on now is making it easier for the user to input the data.

  7. #7
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Yes you can ...

    DoCmd.RunSQL "INSERT INTO [tbl-A-Score] ([Group], Category, Multiplier, SomethingHere, SomethingHere2) SELECT [tbl-B-ScoreGroup].Group, [tbl-B-ScoreGroup].Category, [tbl-B-ScoreGroup].Multiplier, " & SomeControl.Value & " AS AliasName, " & SomeOtherControl.Value & " AS OtherAlias FROM [tbl-B-ScoreGroup] WHERE ((([tbl-B-ScoreGroup].RecNo) = " & Count & ")) ORDER BY [tbl-B-ScoreGroup].RecNo;"
    Back to Access ... ADO is not the way to go for speed ...

  8. #8
    Join Date
    Jan 2006
    Posts
    4
    Thank you, that's exactly the statement that I needed.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •