Advanced Database Report Generation 1

[lespaul]

I have a spreadsheet with information regarding several hundred people and the days and times they reported for jury duty. I need to combine this information by person and date and show all the times for each day the person was at the courthouse. Any suggestions?

Thanks! Leslie
landrews@metrocourt.state.nm.us

SELECT * FROM USERS WHERE CLUE > 0
No Rows Returned

 

[RobBroekhuis]

Simply sorting your table (key1=name, key2=date) will get you most of the way. From there, it depends on how you need to present the times etc.
Rob

 

[lespaul]

I have the following raw data:

PanelID Name ServeDate TimeType TimeIO
P01J01 Joe Blow 12/9/2002 OI 830
P01J01 Joe Blow 12/9/2002 OO 1030
P01J01 Joe Blow 12/9/2002 TI 1030
P01J01 Joe Blow 12/9/2002 TO 1230
P01J01 Joe Blow 12/13/2002 TI 830
P01J01 Joe Blow 12/13/2002 TO 1130
P01J01 Joe Blow 12/19/2002 TI 1330
P01J01 Joe Blow 12/19/2002 TO 1600

OI - Orientation IN; OO - Orientation Out; TI - Time In; TO - Time Out

down column 1 all unique names from data list
across row one Name, orientation, all dates (12/9 - 12/20)
excluding Sat & Sun

Report Presentation
Orient 12/9(12/10-12 blank) 12/13
P01J01 Joe Blow 8:30-10:30 10:30-12:30 8:30-11:30 Leslie
landrews@metrocourt.state.nm.us

SELECT * FROM USERS WHERE CLUE > 0
No Rows Returned

 

[RobBroekhuis]

For each day there can be just one set of TI/TOs? Will you set up the dates yourself ahead of time, or will the program have to figure those out from your table? Depending on the answers to these questions, the answer could be rather straightforward, or somewhat more involved.
Rob

 

[lespaul]

The program will need to figure out which days. There is only one Orientation day (OI and OO) which needs to come first. There can be multiple sets of TI/TO for each day, but not each person.

A little more information:
there are usually 6 panels with around 30 people on each. We have two court sessions that a panel may be called in for - AM and PM. More than one panel may be called for each session.
So on 12/9/2002 Panels 5 and 6 in the AM and panel 1 in the PM
on 12/10 Panel 2 in the AM
on 12/11 Panel 3 in the PM
on 12/12 Panels 4 & 5 in the AM and 6 and 1 in the PM
on 12/13 Panel 2 in the AM and Panel 3 in the PM

each time a panel is called in all the people in the panel will have a TI and TO. But some of the people within a panel could have different times

Say on 12/10 Panel 2 came in at 930. The panel was called to a courtroom and 7 people are selected for Jury Duty. The rest of the panel is dismissed for the day at 11:30, but the 7 people on Jury Duty aren't dismissed until 4:15.

Hope this helps you to help me!!

Thanks
Leslie
landrews@metrocourt.state.nm.us

SELECT * FROM USERS WHERE CLUE > 0
No Rows Returned

 

[RobBroekhuis]

I think we'll be able to help. How far have you gotten so far?
Rob

 

[lespaul]

I have a template that I call from a Delphi program that sends the raw data to a Sheet named "Data". I need to extract the unique names, fill in the dates, and fill in the times. (So not much done!!). I have tried looking at a spreadsheet that Dale helped me with earlier this year that extracts the unique names and does some DB calculations, but I'm not having any luck "stealing" his code! Leslie
landrews@metrocourt.state.nm.us

SELECT * FROM USERS WHERE CLUE > 0
No Rows Returned

 

[RobBroekhuis]

I'd start the sub by creating a new worksheet for your new table, and assigning it to an object variable:

set Sh=worksheets.add

and then re-activate the original worksheet.
Now use the worksheetfunction.min and .max to get the date range of your table. Use that to set the column headings in your new table:

icol=4
for d=datemin to datemax
if weekday(d)>1 and weekday(d)<7 then
Sh.cells(1,icol)=d
icol=icol+1
end if
next d

Now use row counters in both your old and your new sheet to step through the data, find all the dates for a particular name, and sort the times into their columns. It's a bit laborious (too much to try and write out here), but very straightforward in its logic. A couple of nested do..loop structures with good accounting for which lines you're working with should do the trick.
Give it a try, and let us know if/when you get stuck.
Rob

 

[lespaul]

Dale actually helped me get this all figured out! Once again he has done amazing things using the database functions of Excel! The raw data is transformed into a readable, usable summary!

Stars & Cudos to Dale!

Thanks! Leslie
landrews@metrocourt.state.nm.us

SELECT * FROM USERS WHERE CLUE > 0
No Rows Returned

 

[DaleWatson123321]

Leslie,

Thanks for your kind words, and having had confidence in the use of Excel's &quot;database functions&quot; as an effective method of accomplishing your objective.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca