sql temp table creation for form

[Ian_]

Hi All,



This is my first post, I hope someone can help with my problem.

-- Database (access 2000) --

A work scheduling database in which the following tables exist:

tblSchedulefldHIDAutoNumberfldHMonthStartDateDate/TimefldHJIDNumberfldHMonthPercentageCompNumberfldHStaffNumber

tblScheduleDatesfldHIDNumberfldScheduleDateDate/Time

-A schedule record is for a specific month, and has information on that job for that month.
-A job may be scheduled for more then one date in a month or months.

-- Task --

To create a form by which a manager can schedule jobs on a monthly basis;

-All jobs for that month need to be viewed simultaneously
-The dates scheduled for need to be shown
-New dates need to be be able to be added and old dates deleted.

-- Solution so far --

Temporary table;

tblScheduleTempfldHIDAutoNumberfldHMonthStartDateDate/TimefldHJIDNumberfldHMonthPercentageCompNumberfldHStaffNumberfldHDay01Yes/NofldHDay02Yes/NofldHDay03Yes/NofldHDay04Yes/NofldHDay05Yes/NofldHDay06Yes/NofldHDay07Yes/NofldHDay08Yes/NofldHDay09Yes/NofldHDay10Yes/NofldHDay11Yes/NofldHDay12Yes/NofldHDay13Yes/NofldHDay14Yes/NofldHDay15Yes/NofldHDay16Yes/NofldHDay17Yes/NofldHDay18Yes/NofldHDay19Yes/NofldHDay20Yes/NofldHDay21Yes/NofldHDay22Yes/NofldHDay23Yes/NofldHDay24Yes/NofldHDay25Yes/NofldHDay26Yes/NofldHDay27Yes/NofldHDay28Yes/NofldHDay29Yes/NofldHDay30Yes/NofldHDay31Yes/No

-A form is based on this table where the year and month can be selected which would filter the records shown.
-fldHDayNN fields are represented as toggle buttons horizontally accross the record so days can easily be scheduled/unscheduled.

-- Questions --

1) What SQL would create this temporary table from my original tables?
2) What SQL would update the original tables from the temporary table?

NB. There is on average 15 scheduled jobs per month. I am not sure sure whether it is better to
-upon loading the form create the entire temporary table and upon exit update original records
-when a new month/year is selected update original tables for previous selected month, clear and update the temporary table for new month

3) Is there an alternative solution to creating a temporary table in SQL 2000?

Thanks for any help,
Ian

[Ian_]

The tables did not come out correctly, and i can not edit the post so I post them again here;

tblSchedule
fldHID AutoNumber
fldHMonthStartDate Date/Time 
fldHJID Number 
fldHMonthPercentageComp Number 
fldHStaff Number

tblScheduleDates
fldHID Number
fldScheduleDate Date/Time

tblScheduleTemp
fldHID AutoNumber
fldHMonthStartDate Date/Time 
fldHJID Number 
fldHMonthPercentageComp Number 
fldHStaff Number
fldHDay01 Yes/No
fldHDay02 Yes/No
fldHDay03 Yes/No
fldHDay04 Yes/No
fldHDay05 Yes/No
fldHDay06 Yes/No
fldHDay07 Yes/No
fldHDay08 Yes/No
fldHDay09 Yes/No
fldHDay10 Yes/No
fldHDay11 Yes/No
fldHDay12 Yes/No
fldHDay13 Yes/No
fldHDay14 Yes/No
fldHDay15 Yes/No
fldHDay16 Yes/No
fldHDay17 Yes/No
fldHDay18 Yes/No
fldHDay19 Yes/No
fldHDay20 Yes/No
fldHDay21 Yes/No
fldHDay22 Yes/No
fldHDay23 Yes/No
fldHDay24 Yes/No
fldHDay25 Yes/No
fldHDay26 Yes/No
fldHDay27 Yes/No
fldHDay28 Yes/No
fldHDay29 Yes/No
fldHDay30 Yes/No
fldHDay31 Yes/No

[Ian_]

figured this out, created 5 queries

qryClearST - clear tblScheduleTemp
(no need to show this as simple)

qryAppendST - append rows (snippet below)

 
INSERT INTO tblScheduleTemp
SELECT H1.fldHID AS fldHID, H1.fldHMonthStartDate AS fldHMonthStartDate, H1.fldHJID AS fldHJID, H1.fldHMonthPercentageComp AS fldHMonthPercentageComp, H1.fldHStaff AS fldHStaff,
(SELECT COUNT(SD1.fldHID)>0 FROM tblScheduleDates SD1 WHERE (SD1.fldHID=H1.fldHID) AND Day(SD1.fldHScheduleDate)=1) AS fldHDay01,
...
(SELECT COUNT(SD1.fldHID)>0 FROM tblScheduleDates SD1 WHERE (SD1.fldHID=H1.fldHID) AND Day(SD1.fldHScheduleDate)=31) AS fldHDay31
FROM tblSchedule AS H1
WHERE month(H1.fldHMonthStartDate)=fldMonthNum AND year(H1.fldHMonthStartDate)=fldYearNum;

qryUpdateH - update tblSchedule
(no need to show this as simple)

qryDeleteSD - delete current month/year items from tblScheduleDate
(no need to show this as simple)

qryAppendSD - append all current month/year items

 
INSERT INTO tblScheduleDates ( fldHID, fldHScheduleDate )
SELECT HID, ScheduleDate
FROM
[
SELECT ST1.fldHID as HID, ST1.fldHMonthStartDate+0 as ScheduleDate
FROM tblScheduleTemp ST1
WHERE (ST1.fldHDay01)=True
UNION ALL
SELECT ST1.fldHID, ST1.fldHMonthStartDate+1
...
UNION ALL
SELECT ST1.fldHID, ST1.fldHMonthStartDate+30
FROM tblScheduleTemp ST1
WHERE (ST1.fldHDay31)=True]. AS [%$##@_Alias
];

The items in tblScheduleDate have no ID and do not need one, so deleting then adding the items (around 15 of them) is not a problem in this case.

Appologies for my incorrect formatting previously, i understand how messy this thread looks.

Look forward to participating more in the future.
Ian