Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Location
    Moorpark, CA
    Posts
    104

    Unhappy Unanswered: VBA - Insert Into help

    Hi once again -

    Can you use INSERT INTO if you want to insert two or more fields into a table that come from more than one initial table? Think of it like this:

    I have an AuditID and a VersionID I want to populate a Table AuditResults. The table containing AuditID has other fields relating just to that Audit including Version. The table containing VersionID is the many side of a relationship where VersionID is used many times. I want to take all of the Records that have the VersionID from my Audit and paste them in to the AuditResults table, but I want each record to have the AuditID from the audit I am loading. That was a little confusing! Whew! I'll try and lay it out a little below (btw - if there are any suggestions to make this better, I'd be happy to hear them as long as you are nice!)
    1st Field is the primary key
    AuditID AuditDate Auditor VersionID = Audit Table
    VersionID Version DateCreated = Version Table
    PartID PartDescription VersionID = Parts Table
    ResultID AuditID Result PartID = AuditResults Table

    Where they are linked:
    Audit/AuditID----->>>AuditResults/AuditID & AuditResults/PartID----->>>Parts/PartID
    and:
    Version/VersionID--->>>Audit/VersionID
    and:
    Version/VersionID--->>>Parts/VersionID

    Hope that makes at least a little sense. The >>> is the many side

  2. #2
    Join Date
    Feb 2005
    Location
    England.
    Posts
    232
    May I suggest a quick cheat on this that I normally use to do this type of work.

    Build a query, as you see it working. Then just go into SQL view and hack out the SQl. You will need to modify it slightly, but the base plate is set and should be all good.

    Hope it helps.

  3. #3
    Join Date
    Nov 2003
    Location
    Moorpark, CA
    Posts
    104

    Red face

    Makes sense. Thanks - Good Cheat!

    So this is what I got:
    Add Audit = "INSERT INTO Results SELECT VersionPartsQry.PartID, Audits.AuditID FROM Audits RIGHT JOIN VersionPartsQry ON Audits.VID = VersionPartsQry.VID WHERE Audits.AuditID = AuditID.Value;"
    DoCmd.RunSQL (AddAudit)
    Last edited by Jennay; 02-11-05 at 16:48.

  4. #4
    Join Date
    Feb 2005
    Location
    England.
    Posts
    232
    So all we now need to do it alter this slightly for the SQL to work inside of a module.

    I think that it should read something like this...

    Add Audit = "INSERT INTO Results (VersionPartsQry.PartID, Audits.AuditID) FROM Audits RIGHT JOIN VersionPartsQry ON Audits.VID = VersionPartsQry.VID WHERE Audits.AuditID = AuditID.Value;"

    Have to be honest that I'm a little lost with the values you're trying to get into your Results table. Not sure what you're trying to get into VersionPartsQry.PartID and Audits.AuditID as we have no 'VALUES' statement.

    Sorry I can't be of more help here, but I'm new to this and haven't {thankfully} had to paste from more than 1 table!

    Good luck and hope this has helped.

Posting Permissions

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