CODEWITHSUNDEEP
sqlvbaexcelms-access
tinman
tinman

Reputation: 13

VBA or SQL to Subgroup data under two columns?

An MS Access query gives me the below output:

Query output

I am trying to sort the above data in VBA and transfer into an Excel sheet pre-formatted as below:

Output into Formatted Excel sheet

I am trying to use the instruction in the post, Sql group rows with same value, and put that value into header?, but my query gives a huge output ~30000 rows over a date range which requires a different approach to sort.

I am trying to use the below logic

  1. Extract unique serial entries from the query first
  2. Extract all unique users for that serial
  3. Use both serial and user combination further in a 2 level nested loop to extract the 'value' and 'result' from the same query into a separate table and keep appending to it until I have looped through all serials

I am unable to get the right code for this. Please help. What I have tried:

First query 

SELECT DISTINCT(serial) AS Serial Number FROM Test_Results_Tbl;

Second Query

SELECT DISTINCT(user) AS Username FROM Test_Results_Tbl where serial = var_serial;

Should I follow below approaches?

Upvotes: 1

Views: 235

Answers (1)

Brandon Pratt
Brandon Pratt

Reputation: 135

I would do several left joins from the table Test_Results_Tbl to itself and look for each of the test results with distinct records.

I quickly replicated your input table and here is the sql for the query I created.

SELECT DISTINCT test.SerialNo, test.User, test.StartDate, test_1.TestValue, test_1.Result, test_2.TestValue, test_2.Result, test_3.TestValue, test_3.Result
FROM ((test LEFT JOIN test AS test_1 ON (test.StartDate = test_1.StartDate) AND (test.User = test_1.User) AND (test.SerialNo = test_1.SerialNo)) LEFT JOIN test AS test_2 ON (test.StartDate = test_2.StartDate) AND (test.User = test_2.User) AND (test.SerialNo = test_2.SerialNo)) LEFT JOIN test AS test_3 ON (test.StartDate = test_3.StartDate) AND (test.User = test_3.User) AND (test.SerialNo = test_3.SerialNo)
WHERE (((test_1.Testname)="test1") AND ((test_2.Testname)="test2") AND ((test_3.Testname)="test3"));

And here is a picture from the query design view.

And here is a picture of the query results.

Upvotes: 1

Related Questions