[TUT]SQL Toolbox: Using MS Excel with Multiple Queries

[Lease]

Hello,

This example will show you how to take two individual queries from the SQL Toolbox and add the results from one to the other.

The goal of this example is to know what the member's display_name is for a specific Custom Profile Field. The theory used here will work with any two queries, when each query contain an identical value, such as each query used in this example both contain the member id field.

Gathering Information: Identifying your Profile Field IDs

Three ways to find what the field_* (where * is the Custom Profile Field ID number) is for your individual Custom Profile Fields:

1a) ACP > SQL Toolbox
1b) ibf_pfields_data > Note the pf_id for the corresponding pf_title

2a) ACP > SQL Toolbox
2b) ibf_pfields_content > Note the field_* (where * is the Custom Profile Field ID number) and using the responses identify the Custom Profile Field used.

3a) ACP > Tools & Settings > Cache Control > profilefields > View Cache Contents
3b) Your prefix and id will show there; you will see the pf_id # and the corresponding info for that id such as: title, description, content.

Queries to Use:

ACP > Admin > SQL Toolbox > Scroll to the bottom and in "Run a Query" enter the following lines, where * is equal to the Custom Profile Field you you want to query:

Query A - Profile Fields and id:

SELECT member_id, field_* pf_content FROM ibf_pfields_content

Query B - id and display name:

SELECT id, members_display_name FROM ibf_members

Additional Information:
Query omitting specific member group where * equals the group number:

SELECT id, members_display_name FROM ibf_members where mgroup != *

Query omitting specific member groups where * equals the group number:

SELECT id, members_display_name FROM ibf_members where mgroup != * AND mgroup != * AND mgroup != *

Open and Save a New MS Excel Worksheet.

1) Title your cells: A1 = member_id_pf; B1 = pf_content; C1 = results; D1 - leave blank; E1 = member_id_ml; F1 = display_name

(Key: pf = profile field; ml = member list)

2) Place your Query A info (See method below) in member_id (member_id_pf) and pf_id (pf_content) data into cells A2:A... and B2:B...

FYI: "..." depends upon how many members you have; Excel has a row limit of 65,536 so if you have more than this amount of members, you'll need to split it up.

3) Place your Query B info (See method below) member_id (member_id_ml) and display_name data into cells E2:... and F2...

4) In cell C2 (results) enter*:

=LOOKUP($A$2:$A$21,$E$2:$E$21,$F$2:$F$21)

5) Press the "Enter" key and the first "result" will show. Note the result (the display_name) and their member_id_pf number and glance over to column E, find that number and on the same row in Column F, the display_name will be the one listed in the results column.

6) To carry your data down through your list simply click back into cell C2 and place your cursor into the lower right hand corner of the highlighted box; when the cursor turns into a cross-hair pointer, drag it down the column to the end of your data table. The results will automatically fill in for you.

7) If Query A contains less rows than Query B, the "results" column will show #N/A, representative of missing data.

*The formula was written to give a working example using only 21 rows. As you will have something different than this, change the number 21 in the formula to equal the number of rows you have. If you have 55305 your formula would look like this:

=LOOKUP($A$2:$A$55305,$E$2:$E$55305,$F$2:$F$55305)

Screen Shots **

Layout and Formula:

Results:

Missing Data:

Method to place data into MS Excel:
1) Highlight all of the returned data from the "Result: Manual Query" Page. (Only the data; do not include the column titles.)
2) Ctrl +C
3) With MS Excel worksheet in view, click into the first appropriate empty cell where you want the data placed.
4) Ctrl +V

Sort Final Data Using MS Excel:
1) Select column C.
2) Toolbar > Data > Sort
3) At the "Sort Warning" prompt keep the radio dial set to "Expand the selection" and click "Sort"
4) Columns A, B, and C will be highlighted and the "Sort" prompt will appear.
5) Sort by: results > Ascending > My data range has: Header row
6) Click: "OK"
7) Your sorted results will appear.
FYI: 8) If you want to sort on the pf_content, select Column B instead of Column C or if you want to sort on member_id_pf, select Column A instead of Column C.

Screen Shots **

Sort Data First Prompt:

Sort Data Second Prompt:

Sort Data results:

Sort Data pf_contents:

Lease Reviewed by Lease on 13th Jan 2008. [TUT]SQL Toolbox: Using MS Excel with Multiple Queries Hello, This example will show you how to take two individual queries from the SQL Toolbox and add the results from one to the other. The goal of this example is to know what the member's display_name is for a specific Custom Profile Field. The theory used here will work with any two queries, when each query contain an identical value, such as each query used in this example both contain the member id field. Gathering Information: Identifying your Profile Field IDs Three ways to find Rating: 5