Can't edit data in form

[Brian62]

I'm trying to edit Config on my form but I can't since it is written to pull from an outside table in the procedure and renaming it as ConfigF. This is the procedure I have. I need to make the configF editable. Thanks!

ALTER PROCEDURE dbo.proc_getsched
(@v_acid int = '195')
AS SELECT id,
(SELECT tailnum
FROM aircraft
WHERE id = s.acid) AS tailnum, sched, dadate,
(SELECT TOP 1 config
FROM FLYSHEET
WHERE acid = s.acid AND CONVERT(VARCHAR, flysheet.dadate, 101) = CONVERT(VARCHAR, s.dadate, 101)) AS configF, config, acid,
DATENAME(dw, dadate) AS strdate
FROM dbo.sched s
WHERE (acid = @v_acid)
ORDER BY dadate

[nosepicker]

I don't know what you mean about making the column editable, but I thought I'd clean up the SELECT statement in the proc a little bit:

SELECT DISTINCT
id,
aircraft.tailnum,
sched,
dadate,
flysheet.config AS configF,
config,
acid,
DATENAME(dw, dadate) AS strdate
FROM dbo.sched s
JOIN aircraft ON s.acid = aircraft.id
JOIN flysheet ON s.acid = flysheet.acid AND CONVERT(VARCHAR, flysheet.dadate, 101) = CONVERT(VARCHAR, s.dadate, 101)
--JOIN flysheet ON s.acid = flysheet.acid AND DATEDIFF(d, flysheet.dadate, s.dadate) = 0
WHERE acid = @v_acid
ORDER BY dadate

I added the second "JOIN flysheet ... " statement (commented out) just because I thought it might be a little faster (can try it out to be sure).
This might need some tweaking if you really need to select "top 1" from flysheet, a sign of denormalization.

[Brian62]

I tried your procedure and it is faster but couldn't get all the data to pull. It is editable though which is great and what I needed. Here is the data it is pulling.
id tailnum sched dadate config configF acid strdate
254046 3816 SP 24-Oct-05 C23FM 201 Monday
254048 3816 SP 26-Oct-05 C20FM 201 Wednesday
254049 3816 FLY 27-Oct-05 C20FM 201 Thursday
254049 3816 FLY 27-Oct-05 C20FM 201 Thursday
254050 3816 FLY 28-Oct-05 C20FM 201 Friday

Now here is how it should pull:

id tailnum sched dadate configF config acid strdate
254046 3816 SP 24-Oct-05 C23FM 201 Monday
254047 3816 WA 25-Oct-05 C23F 201 Tuesday
254048 3816 SP 26-Oct-05 C20FM 201 Wednesday
254049 3816 FLY 27-Oct-05 C20FM 201 Thursday
254050 3816 FLY 28-Oct-05 C20FM 201 Friday
254051 3816 29-Oct-05 201 Saturday
254052 3816 30-Oct-05 201 Sunday

It seems not to be pulling all the days of the week. I tried fiddling with it but always got the same data to pull as your procedure. I hope this helps. Thanks