CODEWITHSUNDEEP
sqlms-accessinsertvba
Pablo
Pablo

Reputation: 93

Combine INSERT INTO statements

I am trying to gather some total numbers in a table based on data in other tables using SQL through VBA. I have two SQL statements that I have pasted below. These work but they insert the totals into two different records. I was wondering if there was a way to insert them into the same record? Any help would be greatly appreciated!

TotalVerified = "INSERT INTO Totals([TOTAL VERIFIED FORMULARIES]) " & _
    "SELECT COUNT([FORMULARY ID])" & _
    "FROM VerifiedFormularies"
TotalAvailableImport = "INSERT INTO Totals([TOTAL AVAILABLE FOR IMPORT]) " & _
"SELECT COUNT([FORMULARY ID]) " & _
"FROM ImportMetricsIDs"

Edit:

   totalVerified = "INSERT INTO Totals([TOTAL VERIFIED FORMULARIES], [TOTAL AVAILABLE FOR IMPORT], [TOTAL SHOULD BE IMPORTED]) " & _
"SELECT A.cnt, B.cnt, C.cnt " & _
"FROM (SELECT COUNT([FORMULARY ID]) as cnt " & _
    "FROM VerifiedFormularies " & _
") AS A " & _
", ( " & _
    "SELECT COUNT([FORMULARY ID]) as cnt " & _
    "FROM ImportMetricsIDs " & _
") as B " & _
", (SELECT COUNT([FORMULARY ID]) as cnt " & _
"WHERE [IMPORT STATUS]= ""Yes"" " & _
"FROM ShouldImportMetricsIDsTable " & _
") AS C "

Upvotes: 0

Views: 109

Answers (2)

Lennart - Slava Ukraini
Lennart - Slava Ukraini

Reputation: 7181

This may or may not work in ms-access:

INSERT INTO Totals([TOTAL VERIFIED FORMULARIES], [TOTAL AVAILABLE FOR IMPORT], [TOTAL SHOULD BE IMPORTED])
SELECT A.cnt, B.cnt, C.cnt
FROM ( 
    SELECT COUNT([FORMULARY ID]) as cnt
    FROM VerifiedFormularies
) AS A
, (
    SELECT COUNT([FORMULARY ID]) as cnt
    FROM ImportMetricsIDs
) as B
, (
    SELECT COUNT([FORMULARY ID]) as cnt
    FROM ShouldImportMetricsIDsTable 
    WHERE [IMPORT STATUS]= 'Yes'
) AS C

Upvotes: 2

Dan Bracuk
Dan Bracuk

Reputation: 20804

Did you try this approach?

insert into table
(field1, field2)
(subquery1), (subquery2)

Upvotes: 1

Related Questions