June 15, 2015 at 4:09 am
Hi all
I've created a table which will hold staffing data (name, grade, etc.) and any shifts that are going to be entered. I've got some test data in the table.
The base table looks like this:-
CREATE TABLE [dbo].[tbl_ForecastShifts_New](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Ward] [nvarchar](255) NULL,
[StaffBand] [real] NULL,
[StaffMemberName] [nvarchar](255) NULL,
[Date] [datetime] NULL,
[ShiftType] [int] NULL,
[ShiftStart] [datetime] NULL,
[ShiftEnd] [datetime] NULL,
[TrainingStart] [datetime] NULL,
[TrainingEnd] [datetime] NULL,
[Contactable] [bit] NULL,
[In_Charge] [bit] NULL,
CONSTRAINT [PK_tbl_ForecastShifts_New] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
I've attached a spreadsheet with the test data from the table and the required layout on the second tab.
I've created a view which displays the data as I want it and I need to attach this to an MS Access front-end for the users to input/edit the data in the table with the use of a Access form.
The view I've created looks like this:-
CREATE view [dbo].[vw_Forecast_Staffing] with schemabinding
as
select
s.Ward
,s.StaffBand
,s.StaffName
,d1.Date1
,d1.ShiftType1
,d1.ShiftStart1
,d1.ShiftEnd1
,d1.TrainingStart1
,d1.TrainingEnd1
,d2.Date2
,d2.ShiftType2
,d2.ShiftStart2
,d2.ShiftEnd2
,d2.TrainingStart2
,d2.TrainingEnd2
,d3.Date3
,d3.ShiftType3
,d3.ShiftStart3
,d3.ShiftEnd3
,d3.TrainingStart3
,d3.TrainingEnd3
,d4.Date4
,d4.ShiftType4
,d4.ShiftStart4
,d4.ShiftEnd4
,d4.TrainingStart4
,d4.TrainingEnd4
,d5.Date5
,d5.ShiftType5
,d5.ShiftStart5
,d5.ShiftEnd5
,d5.TrainingStart5
,d5.TrainingEnd5
,d6.Date6
,d6.ShiftType6
,d6.ShiftStart6
,d6.ShiftEnd6
,d6.TrainingStart6
,d6.TrainingEnd6
,d7.Date7
,d7.ShiftType7
,d7.ShiftStart7
,d7.ShiftEnd7
,d7.TrainingStart7
,d7.TrainingEnd7
from
[dbo].[tbl_Staff] s
left join (select
Ward
,StaffBand
,StaffMemberName
,[Date] as Date1
,ShiftType as ShiftType1
,ShiftStart as ShiftStart1
,ShiftEnd as ShiftEnd1
,TrainingStart as TrainingStart1
,TrainingEnd as TrainingEnd1
from
[dbo].[tbl_ForecastShifts_New]
) d1
on s.Ward=d1.Ward
and s.StaffBand=d1.StaffBand
and s.StaffName=d1.StaffMemberName
left join (select
Ward
,StaffBand
,StaffMemberName
,[Date] as Date2
,ShiftType as ShiftType2
,ShiftStart as ShiftStart2
,ShiftEnd as ShiftEnd2
,TrainingStart as TrainingStart2
,TrainingEnd as TrainingEnd2
from
[dbo].[tbl_ForecastShifts_New]
) d2
on s.Ward=d2.Ward
and s.StaffBand=d2.StaffBand
and s.StaffName=d2.StaffMemberName
and d2.Date2=d1.Date1+1
left join (select
Ward
,StaffBand
,StaffMemberName
,[Date] as Date3
,ShiftType as ShiftType3
,ShiftStart as ShiftStart3
,ShiftEnd as ShiftEnd3
,TrainingStart as TrainingStart3
,TrainingEnd as TrainingEnd3
from
[dbo].[tbl_ForecastShifts_New]
) d3
on s.Ward=d3.Ward
and s.StaffBand=d3.StaffBand
and s.StaffName=d3.StaffMemberName
and d3.Date3=d1.Date1+2
left join (select
Ward
,StaffBand
,StaffMemberName
,[Date] as Date4
,ShiftType as ShiftType4
,ShiftStart as ShiftStart4
,ShiftEnd as ShiftEnd4
,TrainingStart as TrainingStart4
,TrainingEnd as TrainingEnd4
from
[dbo].[tbl_ForecastShifts_New]
) d4
on s.Ward=d4.Ward
and s.StaffBand=d4.StaffBand
and s.StaffName=d4.StaffMemberName
and d4.Date4=d1.Date1+3
left join (select
Ward
,StaffBand
,StaffMemberName
,[Date] as Date5
,ShiftType as ShiftType5
,ShiftStart as ShiftStart5
,ShiftEnd as ShiftEnd5
,TrainingStart as TrainingStart5
,TrainingEnd as TrainingEnd5
from
[dbo].[tbl_ForecastShifts_New]
) d5
on s.Ward=d5.Ward
and s.StaffBand=d5.StaffBand
and s.StaffName=d5.StaffMemberName
and d5.Date5=d1.Date1+4
left join (select
Ward
,StaffBand
,StaffMemberName
,[Date] as Date6
,ShiftType as ShiftType6
,ShiftStart as ShiftStart6
,ShiftEnd as ShiftEnd6
,TrainingStart as TrainingStart6
,TrainingEnd as TrainingEnd6
from
[dbo].[tbl_ForecastShifts_New]
) d6
on s.Ward=d6.Ward
and s.StaffBand=d6.StaffBand
and s.StaffName=d6.StaffMemberName
and d6.Date6=d1.Date1+5
left join (select
Ward
,StaffBand
,StaffMemberName
,[Date] as Date7
,ShiftType as ShiftType7
,ShiftStart as ShiftStart7
,ShiftEnd as ShiftEnd7
,TrainingStart as TrainingStart7
,TrainingEnd as TrainingEnd7
from
[dbo].[tbl_ForecastShifts_New]
) d7
on s.Ward=d7.Ward
and s.StaffBand=d7.StaffBand
and s.StaffName=d7.StaffMemberName
and d7.Date7=d1.Date1+6
It displays all the staff members and any shifts they've been given for a 7-day period with day 1 begin supplied by the user.
The user will also supply the ward they are interested in viewing.
When I attached the view to Access it becomes read-only as it doesn't have any indexes on it.
I can't create a clustered index on the view as there are derived tables.
Can someone point me in the right direction please?
I think there's going to be a better way of doing what I want but I simply can't see it. Any help would be greatly appreciated.
June 15, 2015 at 1:57 pm
Let's start with the fact that the view does nothing but duplicate the new table's data 6 times but uses no GROUP BY or PIVOT to bring multiple rows into a single row or transform rows to columns, so the big question is, what were you trying to accomplish with that view? Six copies of the same data isn't going to accomplish much.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 16, 2015 at 1:46 am
The view takes a date given by the user and displays the data for that day. It then moves on a day and displays the data for for that day.
What you end up with is the layout in the second tab of the attached spreadsheet (that stops at 2 days but the view does actually run for 7 continuous days).
What I need to do is somehow bring that into Access for use on the form as read/write (it will only attach as read-only as the view isn't indexed).
If anyone has any other ways of doing what I want, I'm always willing to learn.
June 16, 2015 at 5:14 am
richardmgreen1 (6/16/2015)
The view takes a date given by the user and displays the data for that day. It then moves on a day and displays the data for for that day.What you end up with is the layout in the second tab of the attached spreadsheet (that stops at 2 days but the view does actually run for 7 continuous days).
What I need to do is somehow bring that into Access for use on the form as read/write (it will only attach as read-only as the view isn't indexed).
If anyone has any other ways of doing what I want, I'm always willing to learn.
I took another look at your view and found that it does use date fields to control the 6 different joins, but I think the basic problem is that you are tying multiple records together and I can't recall ever seeing a view be indexable under those circumstances... but I am NOT sure if that's the case and I'll hope someone with more knowledge on that topic can offer more information. Given what you have, however, there's clearly an effort-intensive way to get what you want to occur, but it's going to require a lot of fancy footwork on your part to develop the necessary ADO code in VBA to do the table updates directly. One question I have is whether a CLUSTERED index is necessary on the view. Might a non-clustered index do? I wish I had more answers for you, but I do know that Access is going to need an index.
Jeff Moden? Gail Shaw? Lynn Pettis? Grant Fritchey? Any of you have more information? Would using a NONCLUSTERED index on the table allow a CLUSTERED index to be on the view?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply