Add data from another spreadsheet
 

I currently export data in the form of two seperate spreadsheets from two
different Access databases. The data contained in each spreadsheet is simply
two columns (costweek/year and total hours).
I would like to combine these spreadsheets by taking one of the spreadsheets
and adding a third column which contains the hours from the second
spreadsheet corresponding to the costweek/year number.
Sorry if this is the wrong discussion group for this 'problem'.

Add data from another spreadsheet
 

TerryM wrote ...

I currently export data in the form of two seperate spreadsheets from two
different Access databases. The data contained in each spreadsheet is simply
two columns (costweek/year and total hours).
I would like to combine these spreadsheets by taking one of the spreadsheets
and adding a third column which contains the hours from the second
spreadsheet corresponding to the costweek/year number.

IMHO better to combine the data *before* it gets to Excel. Jet (MS
Access) does not support the SQL-92 FULL OUTER JOIN syntax so we have
to use the UNION between a LEFT- and a RIGHT OUTER JOIN. Something
like:

SELECT
DT1.cost_date, DT1.TotalHours_DB1,
DT1.TotalHours_DB2
INTO
[Excel 8.0;Database=C:\MyWorkbook.xls].MyExcelTable
FROM (
SELECT
T1.cost_date,
T1.hours_total AS TotalHours_DB1,
T2.hours_total AS TotalHours_DB2
FROM
[Database=C:\DB1.mdb;].MyTable AS T1
LEFT JOIN [Database=C:\DB2.mdb;].MyTable AS T2
ON T1.cost_date = T2.cost_date
UNION
SELECT
T2.cost_date,
T1.hours_total AS TotalHours_DB1,
T2.hours_total AS TotalHours_DB2
FROM
[Database=C:\DB1.mdb;].MyTable AS T1
RIGHT JOIN [Database=C:\DB2.mdb;].MyTable AS T2
ON T1.cost_date = T2.cost_date
) AS DT1
ORDER BY
DT1.cost_date
;

Jamie.

--

Add data from another spreadsheet
 

Thanks I'll certainly try that. To be honest it never even crossed my mind to
do it that way. Once again thanks.

Terry

"Jamie Collins" wrote:

TerryM wrote ...

I currently export data in the form of two seperate spreadsheets from two
different Access databases. The data contained in each spreadsheet is simply
two columns (costweek/year and total hours).
I would like to combine these spreadsheets by taking one of the spreadsheets
and adding a third column which contains the hours from the second
spreadsheet corresponding to the costweek/year number.

IMHO better to combine the data *before* it gets to Excel. Jet (MS
Access) does not support the SQL-92 FULL OUTER JOIN syntax so we have
to use the UNION between a LEFT- and a RIGHT OUTER JOIN. Something
like:

SELECT
DT1.cost_date, DT1.TotalHours_DB1,
DT1.TotalHours_DB2
INTO
[Excel 8.0;Database=C:\MyWorkbook.xls].MyExcelTable
FROM (
SELECT
T1.cost_date,
T1.hours_total AS TotalHours_DB1,
T2.hours_total AS TotalHours_DB2
FROM
[Database=C:\DB1.mdb;].MyTable AS T1
LEFT JOIN [Database=C:\DB2.mdb;].MyTable AS T2
ON T1.cost_date = T2.cost_date
UNION
SELECT
T2.cost_date,
T1.hours_total AS TotalHours_DB1,
T2.hours_total AS TotalHours_DB2
FROM
[Database=C:\DB1.mdb;].MyTable AS T1
RIGHT JOIN [Database=C:\DB2.mdb;].MyTable AS T2
ON T1.cost_date = T2.cost_date
) AS DT1
ORDER BY
DT1.cost_date
;

Jamie.

--