Automating Link Charts

Explore why spreadsheets fail for complex records by examining relational database concepts, including tables, one-to-many relationships, and edge lists that link reports, offenses, suspects, and victims for automated link charts.

Learn how placeholder queries use undefined inputs like input first name and input last name to prompt values, then build an edge list for a link chart.

Create a blank table in design view, define column names and data types to match the source, add a yes/no keep column, and save with a primary key.

Automate edge list creation by building a macro that uses end-user selections to query the people events and chart tables, applying a keep field to produce link chart table two.

Builds a multi-query workflow to assemble an edge list for a link chart from end-user selections, merges duplicate nodes, filters out self-connections, and extends to second-degree connections.

Save query three as lc table three and run it to see results; then create subsequent make-table queries, joining ids to events to build right-side tables while preventing duplication.

Aggregate two edge lists into one with calculated columns for person one and person two, then prevent duplication to enable clean copy and paste into Node Excel for linked charts.

Eliminate reverse duplicates in a linked chart by creating a calculated 'person order' that groups by the person pair and event ID, then generate an edge list.

Create macro two to run queries 2 through 8, open edge list, and output an edge list after user selection; next video covers transitioning from Microsoft Access to Node Excel.

Install node Excel, open the template, and set up edges and vertices to automate a link chart data. Visualize the network with graph, then customize labels, colors, shapes, and images.

Transition data from your macros into NodeXL and create legible social network visuals, using edge and vertex manipulation, deduplication, labeling, grouping, and simple formulas.