Excel/Access interaction

SarahDC · #1 (**permalink**) September 24th, 2008, 02:41 PM

I wasn't sure whether to post this under Access or Excel--hopefully the right people will find it.

I am trying to import data into Excel from an Access database. The database already contains various forms and queries, and I am not allowed to make any changes to the database.

There is one query in particular that I want to import into Excel; call it EdProfileAnchor. EdProfileAnchor looks like this:

Code:

SELECT Choose(Val(Forms!ddm.grpGeographicLevel),[district_nam],[region_nam],[Constituency],[Ward],[school_nam]) AS GroupLevel,
Choose(Val(Forms!ddm.grpGeographicLevel),gdb_school.district_num,gdb_school.region_num,gdb_school.constituency_num,gdb_school.ward_num,gdb_school.school_num) AS GroupLevelCode
FROM a_district 
INNER JOIN (
    a_Ward INNER JOIN (
        (
            (gdb_school INNER JOIN SchoolSet2 ON gdb_school.school_num = SchoolSet2.school_num) 
            INNER JOIN a_region ON gdb_school.region_num = a_region.region_num
        ) 
        INNER JOIN a_Constituency ON gdb_school.constituency_num = a_Constituency.constituency_num
    ) ON a_Ward.ward_num = gdb_school.ward_num
) ON a_district.district_num = gdb_school.district_num
GROUP BY Choose(Val(Forms!ddm.grpGeographicLevel),[district_nam],[region_nam],[Constituency],[Ward],[school_nam]),
Choose(Val(Forms!ddm.grpGeographicLevel),gdb_school.district_num,gdb_school.region_num,gdb_school.constituency_num,gdb_school.ward_num,gdb_school.school_num)
ORDER BY Choose(Val(Forms!ddm.grpGeographicLevel),gdb_school.district_num,gdb_school.region_num,gdb_school.constituency_num,gdb_school.ward_num,gdb_school.school_num);

There's some complicated nested join stuff going on here, but the key point, as far as I can tell, is that the columns GroupLevel and GroupLevelCode depend on these Choose functions. When trying to import this query into Excel, I get the message "Too few parameters. Expected 1." The parameter that it wants is, apparently, the value from Forms!ddm.grpGeographicLevel. How should I handle this? All of the information that I've found online has been about a different situation, for example, if the query had "WHERE GroupLevelCode = Val(Forms!ddm.grpGeographicLevel)" then I could pass a parameter to the query so that GroupLevelCode = 2. But in my case, I'm not trying to set the value of GroupLevelCode; I'm trying to designate which [i]column[i] (gdb_school.district_num, gdb_school.region_num, etc.) gets named "GroupLevelCode." I hope that made sense. Can anyone think of a way that this could be accomplished without making changes to the query in the database?

If there's a way to set the value of Forms!ddm.grpGeographicLevel using an Excel macro, I think that approach would help me the most. Ideally, I'd like to be able to do this without opening Access, but if it's not possible to "fake" set the value of Forms!ddm.grpGeographicLevel, I'm open to the option of setting it while Access is running.

Thank you in advance.

Need to download code?

Navigation