How to report schedule format from table

**zmbd** · Oct 24 '15, 12:18 AM

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

Code:

[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** · Oct 26 '15, 02:55 PM

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:

Code:

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.

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"

Code:

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"

Code:

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** · Oct 26 '15, 05:56 PM

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** · Oct 26 '15, 07:14 PM

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 "qryBilling 02". 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** · Oct 27 '15, 01:25 PM

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** · Oct 27 '15, 03:08 PM

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** · Oct 27 '15, 03:14 PM

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** · Oct 27 '15, 04:45 PM

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** · Oct 27 '15, 04:58 PM

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.

Code:

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

Code:

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** · Oct 27 '15, 07:16 PM

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** · Oct 27 '15, 07:33 PM

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** · Oct 27 '15, 08:08 PM

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 ScheduleRanking 2:

Code:

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:

Code:

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** · Oct 27 '15, 08:45 PM

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** · Oct 27 '15, 09:03 PM

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.

How to report schedule format from table

How to report schedule format from table

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment