CODEWITHSUNDEEP
sqlms-accessvbams-access-2010
Pablo
Pablo

Reputation: 93

Count in Access VBA

I am trying to insert count stats into a table as one record by using the following code in VBA. But for some reason, when I go to execute it it says the Number of query values and destination fields are not the same and I am confused as to why this is happening. Any help would be greatly appreciated.

strCount = "INSERT INTO MarketSegmentTotals([State Medicaid], [Commercial], [HIX], [MMP], [CMS Part D (CY " & intYear & ")], [CMS Part D (CY " & (intYear + 1) & ")] ) " & _
"SELECT A.cnt, B.cnt, C.cnt, D.cnt, E.cnt, F.cnt" & _
"FROM ( " & _
    "SELECT COUNT([FORMULARY ID]) as cnt " & _
    "FROM ImportMetricsIDs " & _
    "WHERE [Market Segment]= 'State Medicaid' " & _
") AS A " & _
", ( " & _
    "SELECT COUNT([FORMULARY ID]) as cnt " & _
    "FROM ImportMetricsIDs " & _
    "WHERE [Market Segment]= 'Commercial' " & _
") as B " & _
", ( " & _
    "SELECT COUNT([FORMULARY ID]) as cnt " & _
    "FROM ImportMetricsIDs " & _
    "WHERE [Market Segment]= 'HIX' " & _
") AS C " & _
", ( " & _
    "SELECT COUNT([FORMULARY ID]) as cnt " & _
    "FROM ImportMetricsIDs " & _
    "WHERE [Market Segment]= 'MMP' " & _
") AS D "

strCount2 = strCount & _
", ( " & _
    "SELECT COUNT([FORMULARY ID]) as cnt " & _
    "FROM ImportMetricsIDs " & _
    "WHERE [Market Segment]= 'CMS Part D (CY ' & (intYear) & ')' " & _
") AS E " & _
", ( " & _
    "SELECT COUNT([FORMULARY ID]) as cnt " & _
    "FROM ImportMetricsIDs " & _
    "WHERE [Market Segment]= 'CMS Part D (CY ' & (intYear + 1) & ')' " & _
") AS F "


db.Execute strCreate
db.Execute strCount2

Upvotes: 0

Views: 313

Answers (1)

KevenDenen
KevenDenen

Reputation: 1726

It looks like you are missing a space in your query:

"SELECT A.cnt, B.cnt, C.cnt, D.cnt, E.cnt, F.cnt" & _
"FROM ( " & _

should be either 

"SELECT A.cnt, B.cnt, C.cnt, D.cnt, E.cnt, F.cnt " & _
"FROM ( " & _

or

"SELECT A.cnt, B.cnt, C.cnt, D.cnt, E.cnt, F.cnt" & _
" FROM ( " & _

I prefer the first one with the space at the end of the first line as it maintains the line-up of the select and from.

Upvotes: 1

Related Questions