Rocky Smolin - Beach Access Software
bchacc at san.rr.com
Thu Jun 10 08:56:07 CDT 2004
Paul: Although structuring queries to do this would be endlessly entertaining, this is a perfect application for a little DAO code. In cases like this I find it much faster. Open a recordset to contain the outputs. Open a second recordset to get all the names (using UNIQUE). Loop through the second recordset opening a third recordset getting all the records of the name in the current record of the second recordset. Loop through the third recordset counting the HOL, ATT, and SICK records. ADDNEW a record to the first recordset with the name and total counts. When all done use TRANSFERSPREADSHEET on the created recordset to output an Excel spreadsheet and walla, done. HTH Rocky Smolin Beach Access Software http://www.e-z-mrp.com ----- Original Message ----- From: <paul.hartland at fsmail.net> To: "accessd" <accessd at databaseadvisors.com> Sent: Thursday, June 10, 2004 4:47 AM Subject: [AccessD] Help With Excel If Possible To all, 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 -- Whatever you Wanadoo: http://www.wanadoo.co.uk/time/ This email has been checked for most known viruses - find out more at: http://www.wanadoo.co.uk/help/id/7098.htm -- _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com