Solved How to Extract and append in another table (1 Viewer)

[sbaud2003]

Hi Sir

I habe a table MASTER in which four fields are there Course_Code, Ind_ID, Date_from and Date_To:
I want to run a query to append the recorde in table SESSION in which four field s are there: Course Code, Ind_ID and DOT (Date of Training)

How can I append recorde for each day of training . For example if Date_from is 08/Feb/24 and Date_to is 10/Feb/24. I want for each day i.. 8,9 and 10 each individual ID with course code should be added in the SESSION Table.

 

[CJ_London]

create a new table called tblCounter with a single field called Counter. Populate this field with the values 0 to 9 (a total of 10 records)

then your append query will be something like

INSERT INTO tblSession Course Code, Ind_ID, DOT
SELECT Course_Code, Ind_ID, Date_from+Counter
FROM tblMaster, tblCounter
WHERE Counter<=Date_To-Date_from

This assumes date_from and date_to are dates only, i.e. they do not include a time element

It also assumes that courses are never longer that 10 days. If they are, extend the counter table to include 10,11, 12 etc

 

[sbaud2003]

create a new table called tblCounter with a single field called Counter. Populate this field with the values 0 to 9 (a total of 10 records)

then your append query will be something like

INSERT INTO tblSession Course Code, Ind_ID, DOT
SELECT Course_Code, Ind_ID, Date_from+Counter
FROM tblMaster, tblCounter
WHERE Counter<=Date_To-Date_from

This assumes date_from and date_to are dates only, i.e. they do not include a time element

It also assumes that courses are never longer that 10 days. If they are, extend the counter table to include 10,11, 12 etc

thanks, but its not functioning

 

[ebs17]

INSERT INTO tblSession ( Course Code, Ind_ID, DOT )
SELECT Course_Code, Ind_ID, Date_from+Counter
FROM tblMaster, tblCounter
WHERE Counter<=Date_To-Date_from

Notice the difference.

 

[CJ_London]

oops - air code does it again

 

[sbaud2003]

create a new table called tblCounter with a single field called Counter. Populate this field with the values 0 to 9 (a total of 10 records)

then your append query will be something like

INSERT INTO tblSession Course Code, Ind_ID, DOT
SELECT Course_Code, Ind_ID, Date_from+Counter
FROM tblMaster, tblCounter
WHERE Counter<=Date_To-Date_from

This assumes date_from and date_to are dates only, i.e. they do not include a time element

It also assumes that courses are never longer that 10 days. If they are, extend the counter table to include 10,11, 12 etc

Thanks a ton.... Its working fine

 

[CJ_London]

Happy to help

I should point out that unless you are going to be changing the value of ind_id in tblSessions, it should not be stored in tblSession since it can easily be looked up by linking back to tblMaster on course_code. This assumes course_code is your primary key for tblMaster. If in fact the primary key is ind_id, then the comment would refer to course_code instead.

The reason is if you modify ind_Id in tblMaster you will also need to modify ind_id in tblSession.