Need help in Sql query in vba-excel

Question

I have data in Parent sheet in excel and I need to populate the Child Sheet.

 Parent Sheet P

Name   Gender   Grades   Frequency 
John    Male       A         3
John    Male       B         1
John    Male       C         2
Jay     Male       B         6
Sarah   Female     B         5
Sarah   Female     C         1
Maria   Female     A         4
Maria   Female     D         2

There is a clear possibility that all students won't have all types of grades. For E.g Maria has two D grades which no other student has.

Child sheet (Male) M
Name    Grade A   Grade B  Grade C  Grade D 
John       3         1         2       0
Jay        0         6         0       0

Child sheet (Female) F 
Name    Grade A    Grade B  Grade C  Grade D 
Sarah      0        5         1       0
Maria      4        0         0       2

This is how I want. I have already made the sheets with the headers and unique names and Gender,so just need to fill the grades frequency data. I want to use SQL to do it. I have two things to write.

'For Male Sheet
rs.open "SELECT [P$].Frequency FROM [P$] WHERE Name like (SELECT [M$].NAME FROM [M$])

M.Cells(3,1).CopyFromRecordset rs

Will this do the job? Also Will the recordset return Grade D value as 0 in case of John or Grad A, Grade C, Grade D value 0 in case of Jay? Please advice .Thanks.

Why does it have to be done in SQL? Why not just use a pivot table? I know you said this is "how you want" it but does How matter if the results match? — xQbert
– xQbert, Commented Apr 7, 2015 at 19:21
@xQbert Because I need to do this weekly with close to million entries, I dont know to automate pivot table, but I know a bit of SQL. — Jain
– Jain, Commented Apr 7, 2015 at 19:25
Pivot will be far easier... insert pivot table, select range of data, and then make the pivot table field list look like my field list below. If you need separate worksheets for gender add report filter for gender female and repeat steps for male. but remove gender from row lables. Excel Pivot functionality VERY powerful! — xQbert
– xQbert, Commented Apr 7, 2015 at 19:31
@xQbert Thank you so much . I still need to code the Pivot table then, so I think I prefer SQL. Thanks. — Jain
– Jain, Commented Apr 7, 2015 at 19:34

xQbert · Accepted Answer · 2015-04-07 19:30:08Z

2

Example Using Pivot Table. I think SQL is overkill here since Excel has built in functionality.

enter image description here

answered Apr 7, 2015 at 19:30

xQbert

35.5k2 gold badges46 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3819867 Over a year ago

OP wants a faster horse.

Parfait · Accepted Answer · 2015-04-07 20:39:20Z

1

Consider using a relational database as opposed to a flatfile spreadsheet to query your potentially 1 million + entries. Excel does not naturally have an SQL query feature unless called on using VBA or an Add-In.

If using a PC, with MS Access (Excel's Office sibling) you could easily run a crosstab query which essentially aggregates and pivots your recordset:

TRANSFORM Sum(ParentSheet.Frequency) AS SumOfFrequency
SELECT ParentSheet.[Name]
FROM ParentSheet
GROUP BY ParentSheet.[Name]
PIVOT 'Grade ' & ParentSheet.Grades;

And add a where clause to split by genders:

MALE

TRANSFORM Sum(ParentSheet.Frequency) AS SumOfFrequency
SELECT ParentSheet.[Name]
FROM ParentSheet
WHERE ParentSheet.Gender = 'Male'
GROUP BY ParentSheet.[Name]
PIVOT 'Grade ' & ParentSheet.Grades;

FEMALE

TRANSFORM Sum(ParentSheet.Frequency) AS SumOfFrequency
SELECT ParentSheet.[Name]
FROM ParentSheet
WHERE ParentSheet.Gender = 'Female'
GROUP BY ParentSheet.[Name]
PIVOT 'Grade ' & ParentSheet.Grades;

As seen above, simply import your Parent worksheet into a database table called ParentSheet. You can even export the gender-specific queries into your child sheets as csv, txt, xml, or back into xlsx.

answered Apr 7, 2015 at 20:39

Parfait

108k19 gold badges102 silver badges138 bronze badges

3 Comments

xQbert Over a year ago

caveat: Using get external data and a self reference to a defined table in Excel, Excel can use SQL but you may not consider that "natural" You do this by Data, From Other Sources, "Data connection Wizard", "ODBC DSN" then excel file, and select the file you're working on. This assumes you have named ranges defined in the excel document which ODBC treats as... you guessed it... tables. But probably not "natural". as it uses ODBC to accomplish the task.

Parfait Over a year ago

I am not saying Excel cannot run SQL. Connecting via ODBC calls the Jet Engine (available on several Microsoft products) to run the SQL. However, MS Access comes built-in by default with the Jet SQL Engine, no ODBC needed. I just worry anytime users run Excel as a database. Scalability and efficiency becomes a challenge.

xQbert Over a year ago

Nod. We're on the same page.

Collectives™ on Stack Overflow

Need help in Sql query in vba-excel

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related