[AccessD] Help With Excel If Possible

Hi paul

Attach the two worksheets as tables in Access. Then you can deal with
them as with any other table (except delete rows which you won't).

All you need is an update query and some DSum() ...

/gustav


> I have a workbook that contains two spreadsheets, first spreadsheet is called Contract1 and has the following columns of information:
 
> Lastname, Firstname, DOW, JobNo, Code, Jobdate, Hours
> Hartland    Paul            Mon    12345   ATT    01/01/04   8
> Hartland    Paul            Tue    12345   ATT    02/01/04   8
> Hartland    Paul            Wed    12345   HOL    03/01/04   8
> Hartland    Paul            Thu    12345   ATT    04/01/04   8
> Hartland    Paul            Fri    12345   ATT    05/01/04   8
> Hartland    Paul            Sat    12345   ATT    06/01/04   8
> Hartland    Paul            Sun    12345   ATT    07/01/04   8
> Williams     Tom            Mon    12345   HOL    01/01/04   8
> Williams     Tom            Tue    12345   SICK   02/01/04   8
> Williams     Tom            Wed    12345   ATT    03/01/04   8
> Etc, etc
 
> DOW = Day Of Week (Mon, Tue etc), Code is either ATT, HOL or SICK.  Everything on the first spreadsheet is ok.

> The second spreadsheet is called AdditionalData and has the following columns of information:
 
> Lastname, Firstname, Worked, Holiday, Sick
> Hartland    Paul
> Williams     Tom
 
> And that’s about where I have got to, and this is where I need your help.  I need to scan the Contract1 spreadsheet and where the Lastname and Firstname match from the AdditionalData spreadsheet
> and say date range 01/01/04-04/01/04 I need to populate the Worked, Holiday and Sick columns with the count of either ATT, HOL or SICK  so that the result would be like this:
 
> Lastname, Firstname, Worked, Holiday, Sick
> Hartland    Paul     3       1        0
> Williams     Tom     1       1        1
 
> Thanks in advance to anyone that can help with this or point me in the right direction.
 
> Paul Hartland