Add data from another spreadsheet

[Guest]

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'.

 

[Jamie Collins]

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.

--

 

[Guest]

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

Terry

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.