I have 2 tables (named 2006 and 2007) which both contain over 500,000 rows and about 25 columns. They are identical in terms of layout, but the data has simply been split by year. Each contains 23 columns of account information a date column (month only) and a £value column (most of the 23 account info columns contain numerous duplicates)
I have an excel pivot table which links to each table (2 seperate workbooks).
I also have a Query which feeds a different workbook (again via a pivot table) which takes 4 of the account info columns and the date and sums up the £value (basically giving a summary).
My problem is that I can only get the query to run from one of the tables (either 2006 or 2007).
Is there a way to get the query to look in both tables? I've had a play with the UNION function but to be honest I dont know what i'm doing and from the simply return I did get I couldnt get the value column to sum up, but rather just show a list of all the values. (see below)
SELECT [A], [B], [C], [Date], [Current YTD]
FROM [2006]
UNION SELECT [A], [B], [C], [Date], [Current YTD]
FROM [2007];
I also need to be able to use a <>"IGNORE" within the [A] line if possible (but this isn't a problem if not poss - can remove within the pivot table)
My other option - to combine the tables into one large table - wont work because excel cannot handle that many rows when updating the pivot table.
I'm sure the answer is obvious but I've had a hunt around the forums and couldn't fund the answer.