Excel Tool

Order Online
Our company contact information There you can find frequently asked questions and answers

Home | Products |Purchase | FAQ | Contact Us | Useful Resources

SQL Query And Summarize, Crosstab / Pivot Table (Excel, Accessl)

If you want to deal with large amounts of data in EXCEL, such as processing tens of thousands of records, it will take a lot of time, using a SQL query can effectively address this problem. SQL query speed is very fast. This command allows you to create SQL queries (For example: create crosstab / pivot table ) by clicking and arranging visual elements instead of writing SQL code even if you don't understand SQL.It can query Access, Excel using SQL and execute SQL. With the software you will spend less time.
If you want to create Two Dimensional Table (Crosstab / Pivot table). so that the result would look similar to this:

With SQL Query software you can:

1. Create Two Dimensional Table / Crosstab / Pivot Table .

2. Summarize / Count / Average / Maximum / Minimum Data By Fields.

3. Merge Multiple Excel files or Sheets.

4. Split Excel Sheet by Fields.

5. SQL INNER JOIN, LEFT JOIN, RIGHT JOIN, UNION ALL .

6. Filter the Access/Excel Data In Excel.

.....
Tool location: Add-Ins →Excel Tool Box → SQL Query: Shortcuts: (Alt + X + X + Q )


Create Two Dimensional Table (Crosstab / Pivot table).

Video Tutorial: Create Two Dimensional Table.avI

Set ProductID to (Column Fields / Pivot Column), Set Country to (Group By), Set Quantity to (Sum)

Results:

Another method of achieving


Sum Access Data By Fields. Video Tutorial: Sum Access Data By Fields.avi

Sum by 'ProductID'

Result is:

Another method of achieving


Merge Multiple Sheets Video Tutorial: Merge Multiple Sheets.avi

Merge Worksheet USA, Worksheet Spain, Worksheet Netherlands, Worksheet Germany, and so on.

Results:

Another method of achieving


Split Table by Fields Video Tutorial: Split Table by Fields.avi

Split Sheet Report by Country Fields.

Results:

Another method of achieving


Tool location: Add-Ins →Excel Tool Box → SQL Query: Shortcuts: (Alt + X + X + Q )

Merge 2 Tables by matching data 2 Tables by matching data ( Lookup / SQL RIGHT JOIN).

Video Tutorial: SQL Lookup.avi

Export 'Mode' in worksheet A to worksheet B.

Result is:

1.Another method of achieving

2.Another method of achieving

The sample table: merge 2 tables by matching data.xlsx

Example1. Result for Matched Rows from Table1, Matched Rows from Table2:

Merge Excel Tables by Matching Data

Merge Excel Tables by Matching Data

1. Click on the down arrow and select 'Associate Multiple Sheets' from the drop-down list.

2. Check the table that you want to associate.

3. Click on the down arrow and select 'Table1.Name' (The associated field) from the drop-down list.

4. Click on the down arrow and select ' = ' (Equals sign) from the drop-down list.

5. Click on the down arrow and select 'Table2.Name' (The associated field) from the drop-down list.

6. Click the 'Add' button: Set the associated field (Table1.Name = Table2.Name)

8. Clicl the 'Build SQL' button: Generates SQL code.

9. Clicl the 'Execute SQL' button

9. Results:

Example2. SQL Left Join or Left Outer Join: Result for All Rows from Table1, Matched Rows from Table2.

1. Click on the down arrow and select 'Left Join' or 'Left Outer Join' from the drop-down list.

2. Check the table that you want to associate.

3. Click on the down arrow and select 'Table1.Name' (The associated field) from the drop-down list.

4. Click on the down arrow and select ' = ' (Equals sign) from the drop-down list.

5. Click on the down arrow and select 'Table2.Name' (The associated field) from the drop-down list.

6. Click the 'Add' button: Set the associated field (Table1.Name = Table2.Name)

7. Click on the down arrow and select 'No Show' (Hidden fields that you do not want to display ) from the drop-down list, then Click the list item 'Table2.Name' and The list item will be marked 'No Show' characters automatically

7. Clicl the 'Build SQL' button: Generates SQL code.

8. Clicl the 'Execute SQL' button.

Results:

Show all fields:

Example3. SQL Right Join or Right Outer Join: Result for Matched Rows from Table1, All Rows from Table2.

1. Click on the down arrow and select 'Right Join' or 'Right Outer Join' from the drop-down list.

3. Click on the down arrow and select 'Table1.Name' (The associated field) from the drop-down list.

4. Click on the down arrow and select ' = ' (Equals sign) from the drop-down list.

5. Click on the down arrow and select 'Table2.Name' (The associated field) from the drop-down list.

6. Click the 'Add' button: Set the associated field (Table1.Name = Table2.Name)

Operation same as above

Results:


Advanced Filter ( Query A1003, A1006, A1009 ) Video Tutorial: Advanced Filter.avi

Query these ID: A1003, A1006, A1009

Result is:

Another method of achieving

Filter the ACCESS Data Video Tutorial: Filter the ACCESS Data.avi

(ProductID='A1006' And Country='USA')

Result is:

 

Copyright © 2001-2014 Excel-Tool All Rights Reserved.

Copyright Excel-Tool All rights reserved