How to report schedule format from table

[Hat2boots]

I have a production table. In the Table tblJobAdress, I have fields named JobNumber, Address, RoughDate, TopoutDate, and TrimDate.

I want a report that has columns with the Day of the Week (Mon-Sat) Row Headers of Rough, Topout, and Trim. And the details being the address.

I currently have a report that does that, but only one address is in each row. There is a cascading effect with first address on Monday having empty cells for Tuesday through Sat. Next row Monday is empty, Address on Tuesday and cells for Wednesday through Sat are empty. this is all the way through the report.

What I am trying to achieve is to Row 1 have the first address for Monday, first address for Tuesday, first address for Wednesday, first address for Thursday, first address for Friday, first address for Saturday. Instead of Cascading down.

Sample data from table ready for current report

[zmbd]

Let's see if I understand correctly
Take the first ten rows in your image [Phase]="Rough"
In Monday the first entry is on row 10, you would like that to start in row one. Same with Tuesday, etc...

[Phase][Monday     ][Tuesday    ][Wednesday  ][....]
[Rough][5821 Park..][6816 Clay..][2844 St B..][etc...]
[Rough][           ][           ][2848 St B..][etc...]
[Rough][...]
[...]

Is that the effect that you are after?

Can you please post the SQL or method that you are using to create your current report format?

Could you also provide 4 rows of your data in [tblJobAdress] (actual or generic; however, if generic, the values should be the same type-cast) in the table format (like I have done above). It appears from your description that you do not have a primary key in [tblJobAdress] - if you do have a primary key would you indicate which field.

Please use the [CODE/] formatting found in the toolbar to format your SQL and the table will hold its tabular formatting provided you also use the [CODE/] toolbar formatting around the table rows (tip, use spaces not the [Tab] key to space your data)

[Hat2boots]

Yes this is the Effect I am trying to get.

I use MS Access for the Tables and Queries. Starts off with query changing the schedule date into Day of Week. Then I use a Crosstab Pivot Query using the Day of Week as Column Header and Address as Value. Then I Append the Data to a Temp Table from which I Report from to get a perpetual this week and next weeks schedule.

Here is the beginning data in comma delimited:

JobId,JobNumber,Phase,Scheduled,ScheduleDate,BuilderName,Address,AreaManager,SubdivisionName,Plan,Hand,SubdivisionCity,MaterialVendor,PhaseName,Crew,StartDate
017-006-0085,0170060085,1500,Scheduled,10/27/2015,NEW HOMES,1000 Any Street,CHARLES,REDNECK FARMS,3051,RIGHT,ROANOKE,0061-HIGH DOLLAR ,Trim,,02-Jun-15
017-006-0090,0170060090,1300,Scheduled,10/30/2015,NEW HOMES,2234 Any Street,CHARLES,REDNECK FARMS,3438,RIGHT,ROANOKE,0061-HIGH DOLLAR ,Topout,,08-Sep-15
017-006-0092,0170060092,1200,Scheduled,10/28/2015,NEW HOMES,3468 Any Street,CHARLES,REDNECK FARMS,2016,LEFT,ROANOKE,0061-HIGH DOLLAR ,Rough,,21-Oct-15
017-007-0097,0170070097,1300,Scheduled,10/26/2015,NEW HOMES,4702 Any Street,DUSTIN,TRIBUTE,2761,LEFT,THE COLONY,003-LESS EXPENSIVE,Topout,,19-May-15
017-007-0101,0170070101,1300,Scheduled,10/29/2015,NEW HOMES,5936 Any Street,DUSTIN,TRIBUTE,3640,LEFT,THE COLONY,003-LESS EXPENSIVE,Topout,,20-Jul-15
017-007-0104,0170070104,1200,Scheduled,10/27/2015,NEW HOMES,7170 Any Street,DUSTIN,TRIBUTE,4787,RIGHT,THE COLONY,003-LESS EXPENSIVE,Rough,,13-Oct-15
017-007-0105,0170070105,1200,Scheduled,10/27/2015,NEW HOMES,8404 Any Street,DUSTIN,TRIBUTE,4234,LEFT,THE COLONY,003-LESS EXPENSIVE,Rough,,19-Oct-15
017-007-0106,0170070106,1200,Scheduled,10/30/2015,NEW HOMES,9638 Any Street,DUSTIN,TRIBUTE,3963,LEFT,THE COLONY,003-LESS EXPENSIVE,Rough,,22-Oct-15

Here is the SQL Code.

SELECT qryBilling02.AreaManager
   , qryBilling02!ScheduleDate-DatePart("w",qryBilling02!ScheduleDate,7,3)+3 
      AS Beginning
   , qryBilling02!ScheduleDate-DatePart("w",qryBilling02!ScheduleDate,7,3)+8 
      AS Weekending
   , Format(qryBilling02!ScheduleDate,"dddd") 
      AS WeekDay
   , qryBilling02.PhaseName, qryBilling02.ScheduleDate
   , StrConv(qryBilling02!Address & " - " 
      & [qryBilling02!Plan] & Chr(13)+Chr(10) 
      & Left(qryBilling02!BuilderName,13) & " - " 
      & Mid([qryBilling02!MaterialVendor],5) 
      & Chr(13)+Chr(10) & qryBilling02!SubdivisionName
      & ", " & qryBilling02!SubdivisionCity,3) 
         AS Address
   , Date()-DatePart("w",Date(),1,3)+15 
      AS Expr1
   , qryBilling02.BillDate
   , qryBilling02.Phase 
      AS PhaseNumber
FROM qryBilling02
WHERE (((qryBilling02.PhaseName)<>" Start") 
      AND ((qryBilling02.ScheduleDate)
         >Date()-DatePart("w",Date(),1,3)-6 
         And (qryBilling02.ScheduleDate)
            <Date()-DatePart("w",Date(),1,3)+14)
      AND ((qryBilling02.BillDate) Is Null));

"From that I use a Crosstab Pivot Query"

TRANSFORM Last(AreaManagerSchedule.Address)
   AS LastOfAddress
SELECT AreaManagerSchedule.AreaManager
   , AreaManagerSchedule.PhaseName 
      AS Phase
   , AreaManagerSchedule.Address
   , AreaManagerSchedule.Weekending
   ,AreaManagerSchedule.Beginning
   ,AreaManagerSchedule.PhaseNumber
   ,AreaManagerSchedule.BuilderName
FROM AreaManagerSchedule
GROUP BY AreaManagerSchedule.AreaManager
   , AreaManagerSchedule.PhaseName
   , AreaManagerSchedule.Address
   , AreaManagerSchedule.Weekending
   , AreaManagerSchedule.Beginning
   , AreaManagerSchedule.PhaseNumber
   , AreaManagerSchedule.BuilderName
PIVOT AreaManagerSchedule.WeekDay;

"After deleting the data out my temp table, I Append to Temp Table"

INSERT INTO tblAreaManagerSchedule 
   ( AreaManager, Phase, Weekending, Beginning
      , Monday, Tuesday, Wednesday, Thursday
      , Friday, SortCode, MondayDate, TuesdayDate
      , WednesdayDate, ThursdayDate, FridayDate
      , PhaseNumber, BuilderName)
SELECT AreaManagerSchedule_Crosstab.AreaManager
   , AreaManagerSchedule_Crosstab.Phase
   , AreaManagerSchedule_Crosstab.Weekending
   , AreaManagerSchedule_Crosstab.Beginning
   , AreaManagerSchedule_Crosstab.Monday
   , AreaManagerSchedule_Crosstab.Tuesday
   , AreaManagerSchedule_Crosstab.Wednesday
   , AreaManagerSchedule_Crosstab.Thursday 
      AS Expr1
   , AreaManagerSchedule_Crosstab.Friday
   , AreaManagerSchedule_Crosstab!AreaManager
      & " Schedule Begining on " 
      & AreaManagerSchedule_Crosstab!Beginning 
      & " and Ending on  " 
      & AreaManagerSchedule_Crosstab!Weekending 
         AS SortCode
   , AreaManagerSchedule_Crosstab.Beginning 
      AS MondayDate
   , [Beginning]+1 
      AS TuedayDate
   , [Beginning]+2 
      AS WednesdayDate
   , [Beginning]+3 
      AS ThursdayDate
   , [Beginning]+4 
      AS FridayDate
   , AreaManagerSchedule_Crosstab.PhaseNumber
   , AreaManagerSchedule_Crosstab.BuilderName
FROM AreaManagerSchedule_Crosstab
GROUP BY AreaManagerSchedule_Crosstab.AreaManager
   , AreaManagerSchedule_Crosstab.Phase
   , AreaManagerSchedule_Crosstab.Weekending
   , AreaManagerSchedule_Crosstab.Beginning
   , AreaManagerSchedule_Crosstab.Monday
   , AreaManagerSchedule_Crosstab.Tuesday
   , AreaManagerSchedule_Crosstab.Wednesday
   , AreaManagerSchedule_Crosstab.Thursday
   , AreaManagerSchedule_Crosstab.Friday
   , AreaManagerSchedule_Crosstab!AreaManager 
      & " Schedule Begining on " 
      & AreaManagerSchedule_Crosstab!Beginning 
      & " and Ending on  " 
      & AreaManagerSchedule_Crosstab!Weekending
   , AreaManagerSchedule_Crosstab.Beginning
   , [Beginning]+1
   , [Beginning]+2
   , [Beginning]+3
   , [Beginning]+4
   , AreaManagerSchedule_Crosstab.PhaseNumber
   , AreaManagerSchedule_Crosstab.BuilderName;

[zmbd]

OK,
Question about your dataset, is that all in one table, or do you have that normalized?

The reset of that will take us a little-bit to wade thru. :)

[Hat2boots]

Yes the dataset is as Normalized as much as I could. I inherited the database when I took over the job. The Dataset is from a union query "qryBilling02". it was easier to send you the data from the Union Query than sending all of the tables associated.

Table Names for qryBilling01:
tblJobAddress
tblSubdivision
tblBuilder
tblCity

qryBilling02 Union Query puts the Phase (Name, Number, ScheduleDate, BillDate, Installer) in the same columns.

[zmbd]

I'm not seeing anything in particular that would help; however, admittedly, CTQ are my weakest work knowledge.

In my experience when I've tried to do a similar report, what I have found is that to get things to line up there has to be a row to column (R,C) commonality.

So to take your image, (12,1) (Monday, first entry), you want something that will then relate the (12,1) to (1,2) and the remaining entries, so that it's (1,1), (1,2)... that, so to speak, is the "captain obvious" on my part.

What I don't see in your data set is anything that would allow us to assign (12,1) to (1,1) nor (11,2) to (1,2), (9-10,3) to (1-2,3), etc... given that you are using a temporary table, there maybe something that can be done therein, such as a sequence number along the lines of the yearly one as described here modified to cycle on the weekday for the work week...http://bytes.com/topic/access/answer...increments-one

Wish I could be of more help here;

HOWEVER,

We have some real SQL wizards on this site; hopefully, one of them will stop in shortly and provide some better help...

I'll be following this, need to learn something new here too.

[Hat2boots]

I have tried several different approaches. I have no doubt that there is a solution.

1. Tried to make Five reports (Monday, Tuesday, etc.) and then combine them in in a row of reports on a "Master report".

2. Tried to make a Crosstab Pivot Query on the DataSet above.

3. Tried to reset the Primary Key back to 1 for each report to use it for One to Many.

4. Opening five copies of the table in a query (View for SQL) Joining on PhaseName.

If anyone is curious of what I am trying to achieve is to report from the Dataset image attached at the top without the cascading separation. ZMBD is on the right path.

[jforbes]

I too am intrigued with this. I have a few questions to hopefully clarify and help this along.

What is the desired end result for this report? Do you wish to show it on a Form, Print a Report, Export to Excel? Or a combination? Will the amount of items scheduled per day vary?

One reason I ask is, I don't believe that boxing the data into a grid like in your example is necessary for you unless you are then expecting to easily Export it to Excel or Show it to the User in a Datasheet. If you are planning on showing the data in Calendar type view on a Form or Report you have some other options, like creating SubReports/Forms for each Day of the Week and letting the Days them fill themselves out from the data you currently have.

If a SubForm/Reports wont work for you, then I believe you will need to resort to some code so that you can build a looping structure. I don't think working with the data in Sets the way a database likes will work for you. I think a looping structure will be needed to determine where to put a value when attempting to compress it.

For a given week, how can you determine how many Items are scheduled for each day? I'm doubtful that there will always be the same amount of items for each day, so that on a given week, you could have 5 items for Monday and 7 Items for Tuesday. The next week this situation could be reversed. The amount of scheduled items per day and the amount of rows per week would vary and attempting to pull this off in SQL will get very hairy. The only way I can think to do this is to add a RowNumber to the each Item for that Day, then use another query that puts the Days of the Week onto a single row by using the RowNumber. This would be complicated if there are varying amounts of scheduled items per day, which would probably need a temp table of RowNumbers that would allow for all situations to be met.

...At this point, it would probably be better to switch to a procedural language to loop through all the data and insert records into a temp table (or recordset in memory) as they fill up.

[Hat2boots]

The purpose of the MS Access Report for our Field Personnel to Print out the Weeks Schedule per Field Personnel. The Number of Addresses will vary greatly from Schedule to Schedule.

We currently have 13 Field Personnel using this report with each having 60-120 houses with 12-54 active scheduled houses per week per each Field Personnel.

Previously I had used A report with five SubReports (Monday, Tuesday, etc.). Still Cascaded.

Boxing the DataSet is a end result from the Data Manipulation to organize in the format that was required of me. The TempTable has more data in it than was first show in the image. I sent only the data I thought that was pertinent to my question.

You mentioned "looping structure". I am familiar MS Access but I have little experience in SQL.

The Schedule for each day/week is determined by the Field Personnel hourly. This has to be a fluid report.

MS Access restarts the AutoNumber back to 1 when the Table is recreated. I tried to figure out a loop that was Access easy to no avail.

[Rabbit]

Do a pseudo-ranking, partitioning on the phase name and weekday. Then do a crosstab with that.

The sample code below is a mockup in SQL Server, not Access. But contains the relevant logic that will need to be replicated in Access.

declare @t table(
    JobId char(12),
    JobNumber char(10),
    Phase char(4),
    Scheduled char(9),
    ScheduleDate date,
    BuilderName char(9),
    [Address] varchar(255),
    AreaManager varchar(255),
    SubdivisionName varchar(255),
    [Plan] varchar(255),
    Hand varchar(255),
    SubdivisionCity varchar(255),
    MaterialVendor varchar(255),
    PhaseName varchar(255),
    Crew varchar(255),
    StartDate date
)
 
insert into @t values ('017-006-0085','0170060085','1500','Scheduled','10/27/2015','NEW HOMES','1000 Any Street','CHARLES','REDNECK FARMS','3051','RIGHT','ROANOKE','0061-HIGH DOLLAR ','Trim','','02-Jun-15')
insert into @t values ('017-006-0090','0170060090','1300','Scheduled','10/30/2015','NEW HOMES','2234 Any Street','CHARLES','REDNECK FARMS','3438','RIGHT','ROANOKE','0061-HIGH DOLLAR ','Topout','','08-Sep-15')
insert into @t values ('017-006-0092','0170060092','1200','Scheduled','10/28/2015','NEW HOMES','3468 Any Street','CHARLES','REDNECK FARMS','2016','LEFT','ROANOKE','0061-HIGH DOLLAR ','Rough','','21-Oct-15')
insert into @t values ('017-007-0097','0170070097','1300','Scheduled','10/26/2015','NEW HOMES','4702 Any Street','DUSTIN','TRIBUTE','2761','LEFT','THE COLONY','003-LESS EXPENSIVE','Topout','','19-May-15')
insert into @t values ('017-007-0101','0170070101','1300','Scheduled','10/29/2015','NEW HOMES','5936 Any Street','DUSTIN','TRIBUTE','3640','LEFT','THE COLONY','003-LESS EXPENSIVE','Topout','','20-Jul-15')
insert into @t values ('017-007-0104','0170070104','1200','Scheduled','10/27/2015','NEW HOMES','7170 Any Street','DUSTIN','TRIBUTE','4787','RIGHT','THE COLONY','003-LESS EXPENSIVE','Rough','','13-Oct-15')
insert into @t values ('017-007-0105','0170070105','1200','Scheduled','10/27/2015','NEW HOMES','8404 Any Street','DUSTIN','TRIBUTE','4234','LEFT','THE COLONY','003-LESS EXPENSIVE','Rough','','19-Oct-15')
insert into @t values ('017-007-0106','0170070106','1200','Scheduled','10/30/2015','NEW HOMES','9638 Any Street','DUSTIN','TRIBUTE','3963','LEFT','THE COLONY','003-LESS EXPENSIVE','Rough','','22-Oct-15')
 
select
    p.PhaseName,
    p.Monday,
    p.Tuesday,
    p.Wednesday,
    p.Thursday,
    p.Friday
 
from (
    select
        t1.PhaseName,
        datename(dw, t1.ScheduleDate) as DayOfWeekName,
        t1.[Address],
        COUNT(*) as groupRowNum
 
    from 
        @t t1
 
        left join @t t2 on
            t1.PhaseName = t2.PhaseName and
            datename(dw, t1.ScheduleDate) = datename(dw, t2.ScheduleDate) and
            t1.JobId <= t2.JobId
 
    group by
        t1.PhaseName,
        datename(dw, t1.ScheduleDate),
        t1.[Address]
) t
 
pivot (
    max([Address])
    for DayOfWeekName in (
        [Monday], [Tuesday], [Wednesday], [Thursday], [Friday]
    )
) p
 
order by 
    p.PhaseName,
    p.groupRowNum

It produces the results

PhaseName    Monday    Tuesday    Wednesday    Thursday    Friday
Rough    NULL    8404 Any Street    3468 Any Street    NULL    9638 Any Street
Rough    NULL    7170 Any Street    NULL    NULL    NULL
Topout    4702 Any Street    NULL    NULL    5936 Any Street    2234 Any Street
Trim    NULL    1000 Any Street    NULL    NULL    NULL

[zmbd]

Rabbit,

For those of us that are a tad SQL-Server Illiterate/Rusty. ( Me (@_@) )

Lines 1 thru 27 mimic OP's temporary table (provided that is the data set OP has in Post#3

Then Line 29 forward starts the pseudo-ranking and the CTQ.

Lines 38 thru 56 are the ranking sub-query within the outer CTQ

Am I following this correctly?


(would be nice to be able to a declare clause in Access... would make my life soooo much easier! )

[Rabbit]

Yes, that's correct. You can ignore all the SQL related to the SQL Server version of cross tab. The key part is the ranking query to number the rows by the 2 groups needed for the crosstab: PhaseName and WeekdayName. That way, if there are 3 addresses for 'Rough' on a Tuesday, it gets numbered 1, 2, 3. And if there are 4 addresses for 'Rough' on a Friday, it gets numbered 1, 2, 3, 4. This allows the crosstab to group by the PhaseName and the pseudo-rank so that all the "number 1" address are on one row.

[jforbes]

Thanks for the lesson Rabbit. I've very little experience with this, so I took this opportunity to learn.
I converted the T-SQL (which worked great in SSMS) to a couple queries. The table needed to be created by hand and I named it ScheduleRawData so that it would make sense to me later. I also added the AreaManager to learn on and to have something to link to.

The query to create the Ranking, named ScheduleRanking2:

SELECT t1.AreaManager
     , t1.PhaseName
     , datepart("w",t1.ScheduleDate) AS DayOfWeekName
     , t1.[Address], COUNT(*) AS groupRowNum
FROM ScheduleRawData AS t1 
LEFT JOIN ScheduleRawData AS t2 
ON (t1.PhaseName=t2.PhaseName) AND (datepart("w",t1.ScheduleDate)=datepart("w",t2.ScheduleDate)) AND (t1.JobId<=t2.JobId)
GROUP BY t1.AreaManager
       , t1.PhaseName
       , datepart("w",t1.ScheduleDate)
       , t1.[Address]

The Crosstab:

TRANSFORM Max(ScheduleRanking2.Address) AS MaxOfAddress
SELECT ScheduleRanking2.AreaManager
     , ScheduleRanking2.PhaseName
FROM ScheduleRanking2
GROUP BY ScheduleRanking2.AreaManager
    , ScheduleRanking2.PhaseName
    , ScheduleRanking2.groupRowNum
PIVOT ScheduleRanking2.DayOfWeekName

The Ranking is slick and it fulfills the RowNumber approach that I alluded to earlier very well.

The looping structure, if you would still need it or want it, would be written in VBA. It would open up the Raw Data with a SQL Select Statement, place the results in a RecordSet, loop trough the recordset and then logically places what records it finds into some kind of data structure that can be displayed to the user, like a Static Table in the Database, a recordset in memory, or simply some arrays. It would give you a lot of flexibility, but whether or not it is the right method for you would be your decision.

It looks like the SQL approach might be best, especially since you have some experience with it and Rabbit hooked you up. If you want to pursue the VBA looping approach, just say so.

[zmbd]

Rabbit:
The key part is the ranking query to number the rows by the 2 groups needed for the crosstab: PhaseName and WeekdayName.

Well, at least I understood what I was looking at :D

I had the WeekDayName and the PhaseName; however, I was looking at another table to related these two togeither, I had looked at your Ranking Article due to the traffic in it as of late... should have taken that as hint from the either!

As usual, learned something new from you!

@Hat2boots
Please let us know how you progress with this project - especially if you need any more help. If Rabbit's post solves the issue, please remember to select it as "Best Answer"


Thank you for asking the question!

-z

[Rabbit]

I have found that the ability to number rows have many uses outside the normal use of ranking rows. Especially when it comes to collapsing data and doing historical comparisons.

One assumption of the SQL is that JobId is unique among all the rows in the dataset.

[zmbd]

Rabbit, absolutely, I will have to keep the RQ in mind... starting to wonder how many of my designs have been over complicated because I have not used this method. So obvious now that I've seen it done - took me more time to import the dataset than to type in the SQL, I feel a tad... maybe it's the old age starting to set in? :)

The JobID is one thing that was throwing me, amongst other distractions today. I just assumed it would be unique thru the job phases.

Attached is one version of the database based on Rabbit's post.

One thing I noted, in Access, DatePart("w",[datefield]) will return the numeric weekday value. I used the Format([datefield],"ddd") to return the short name for the weekdays in the attached file. :)