Relational Database - Not based on aggregating values

[ChrisDenyer]

Hi,

I've often used Pivot Tables for databases where you aggregate values, like counting up your sales figures for the month of March and relating them to each sales rep.

However, I want to create a database which keeps a record of training courses in relation to staff members, so I can see which courses are due to expire and who went on those courses at the time and will thus need to retrain.

I can create the tables, create the links between them (I'm using Excel 2013, currently without PowerPivot) and set up a Pivot Table containing those links.

However, it seems Pivot Tables always need to be aggregating something. I can ask it how many courses each staff member has done (because it's a sum), I can ask how many staff members attended each course (another sum) and how many courses are due to expire soon (another sum). But I can't get it just to list the courses.

If I were using SQL I would just have done a SELECT query, but I'm lost as to how to do it in Excel.

EDIT:
My tables, simplified, are as follows:

COURSES
CourseID, CourseName, CourseExpiry

PEOPLE
PeopleID, PersonName

LINKER
LinkID, CourseID, PeopleID

The last table stores the link between the people and the courses

 

[Jerry Sullivan]

Hi Chris and Welcome to MrExcel,

You can use an SQL Query to populate a standard Table (not a PivotTable).

This thread shows steps to use MSQuery with Excel that would apply to Excel 2013 as well.
http://www.mrexcel.com/forum/excel-questions/680325-autosort-date-after-formulas-updated.html

In that example the query was simple enough that it could be built with the dialog boxes. For queries like your's that will include Joins, you can exit out of the Wizard and then insert an SQL query by clicking the SQL button in the MS Query toolbar.

 

[ChrisDenyer]

Thanks!

I see that I can create an external file (say Database.xlsx) and put my raw tables in there for processing, then link them to another workbook (perhaps Frontend.xlsx)

Am I able to forward data to the query from a value in a cell in Frontend.xlsx?

So for instance, my users could tap a date into a cell and the table below it would use that date in the SQL query to pull up courses which expire on or after that date?

 

[ChrisDenyer]

I can't edit my above post, so sorry for bumping, but this is what I've found out so far:

Assuming your Database Tables are in the same file as the Query Table, perhaps on different sheets:

You can create yet another table, so we have Courses, People, Links and now Vars as well.

Vars has only one row, and has a column for each variable I want to pass on to the SQL Statement.

So, if I want to have a Query Table with two cells above it which determine the FROM and TO dates for the query to display, I have but to add a column to VARS for 'FROM' and 'TO' and set the values to a formula which references those cells.

The VARS table will be accessible from Microsoft Query, so we can set our SQL statement to display records between VARS.From and VARS.To.

Then we finally need a simple macro to detect when the From and To fields change and thus refresh the query.

If there's a quicker way, let me know, but that seems to do it for now.

 

[Jerry Sullivan]

I'm not able to tell from your description the method you are using to have MS Query linked to Vars parameters.

This video shows one method which can optionally be set to refresh the query anytime the value of the parameters is changed.

https://www.youtube.com/watch?v=P9cUYpXIKsU&list=UUlBYq5cbpioOFBqor6SIIBw&feature=c4-overview

Tushar Mehta has a nice set of articles that illustrate how to build queries through a series of examples.
Build Queries

 

[Fazza]

A similar example (nearly 10 years old, btw).

Daily Dose of Excel » Blog Archive » Parameters in Excel external data queries

 

[ChrisDenyer]

Hi Fazza and Jerry,
Thanks for your advice, that last link is exactly what I was after!

Sorry if my last post wasn't very clear too.

Basically, I was storing my data in Excel tables. I created a new table called Vars which had only one row but multiple columns, one column for each variable I wanted to export.

Then I set the value for that column to be a reference to a cell containing my parameter. That way, the Vars table carries whatever data was in the parameters back to MS Query for use in a query.

It's a bit of a hack compared with the built in solution, so thank you!

 

[Fazza]

The built-in solution is excellent.

For situations where different functionality is wanted, a little VBA & some creativity can do wonders. It is 'sort of' easy.