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.

Answer 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.

Answer 2

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