Creating two tables that I can reference with MicrosoftQuery

**Telperion** · 06-28-2013, 04:04 PM

I don't have Excel 2013 so unfortunately I can't easily create relational data in Excel 2010. I'm looking for a solution to a design problem. I'd like to have 2 tables which I can join with Microsoft Query and run pivot table reports.

My department processes payments, both for internal clients and external clients. My Payments table looks like:

Date | Type (internal/external) | Operation (what type of payment) | Method (internet, mail, etc) | Quantity

Additionally, I have a table for Mail Opening, which looks like:

Date | Employee | Operation | Quantity

My overlapping fields are Date and Operation. Using each table individually, I can get nice pivot table reports. What I'd like to do though is be able to not just see what operations and methods were run each day with what quantities, but also to compare that to how much mail was opened. Employee and Operation is a multi-multi relationship, so when I join by date, I end up getting incorrect numbers because of problems with the data layout. I'm open to changing my data structure, as I know the way it's set up right now isn't great, but I'm having a mental block on how to redesign it. I attached a sample workbook.

**newdoverman** · 06-28-2013, 04:55 PM

Maybe this will help.

Uploader isn't working...will try again later.

Ah ...success!

This doesn't have anything to do with MSQuerry but maybe will give you some ideas.

**Telperion** · 07-16-2013, 10:17 AM

Hm, well it's helpful in that I can see the data structure, I was just hoping to get a more automated method.

I don't mind even a combination of index/match/sumif perhaps, if that's what it'll take. I just can't think of how to structure it though. Eventually I'd like to create a user form that will allow someone to submit the data without having to touch the spreadsheet.

**newdoverman** · 07-17-2013, 12:14 PM

I created a summary sheet with these headings (a combo of the headings of the Payment and Opening sheets.

Date Month Year Fiscal Year OperationType Operation OperationMethod OperationQuantity OpenQuantity Employee Index

I created "Index" numbers in column I of Payments running from 1 to 8338 and on Opening in column H I created Index numbers from 8339 to 8370.

On the summary sheet in A2 I entered this formula and copied across and down to the bottom of the Index numbers.

Formula:

=IFERROR(IF($K2<=8339,INDEX(Payments!A$2:A$8339,MATCH('Summary Payments-Opening'!$K2,Payments!$I$2:$I$8339,0),MATCH('Summary Payments-Opening'!A$1,Payments!A$1,0)),INDEX(Opening!A$2:A$128,MATCH('Summary Payments-Opening'!$K2,Opening!$H$2:$H$128,0),MATCH('Summary Payments-Opening'!A$1,Opening!A$1,0))),"")

That filled in the summary worksheet with the data from the two tables. In order to sort this data properly, I copied all this data and pasted VALUES into a new worksheet. This allowed the data to be sorted and other-wise manipulated.

The resultant file is much too large to upload.