VBA in Excel to query Access DB

cynx
cynx used Ask the Experts™
on
I have access database as below,
A Table called 'mydatabase' has following columns

Trunch No.
URN No.
ID No.

there are other columns, but not relevant to this question.

i need to query following in excel s/s.

Query: for each Trunch No., Group by URN Nos. that start with 'i' , count of unique ID No.

Note: URN Nos will be like, EN232, ATS-232, I777, i am interested only in those that start with I.

PS: i have programming knowledge, but new to sql.
I was able to establish connections etc. but stuck up with this query.

thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Rodney EndrigaData Analyst

Commented:
If you are familiar with the SQL View in MS Access, you can use this statement to gather the data:

Select distinct URN No., count(1) as 'Count Of URN No.' from {tablename}
where URN No. like 'i%' group by URN No.

This will give you all the instances if the URN No. and how many times it resides within your dataset.

You can use the Make Table query to create a new table with the necessary data elements that have the specific URN No.
Try this:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=889

and use this for the query:

vtSql = vtSql & " SELECT * "
vtSql = vtSql & " FROM " & AccessTableName
vtSql = vtSql & " WHERE [URN Nos] Like 'I*'" 
vtSql = vtSql & " ORDER BY [URN Nos] "
Set rs = dbs.OpenRecordset(vtSql)

Open in new window

If you do have a period in the URN Nos name (ie "URN Nos.") make sure to include it...I didn't.
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

Author

Commented:
@djrod1rep:, since i have only readonly access to DB, i cant create a view.
@sl8rz; how would the above query account for each trunch ?
i would need some nested query
Can you post a small example of your table?  Something like:

Trunch No     URN No     ID No
10                 I777         122
AB                 C523        ABC
YY                  I230         5


It doesn't need to look pretty, just give us an idea of the data (ie. do Trunch Numbers repeat, or are they unique).  If Trunch Numbers repeat then how do they relate to the other fields (or do they not relate at all)?

Author

Commented:
I have uploaded two files, Access DB, with the structure of columns required for query and the sample output i am looking for.

one observation, in above wild card * doesnt work in vba for access, wild card as % works fine.

thanks.

SampleDB.mdb
Sample-Output.xls
I think this will work.  I haven't tested it.

vtSql = vtSql & " SELECT [Trunch No], [URN No], COUNT([URN No]) "
vtSql = vtSql & " FROM " & AccessTableName 
vtSql = vtSql & " WHERE [URN Nos] Like 'I%'" 
vtSql = vtSql & " GROUP BY [Trunch No], [URN Nos] "
Set rs = dbs.OpenRecordset(vtSql)

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial