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

sbaud2003

Member
Local time
Tomorrow, 02:11
Joined
Apr 5, 2020
Messages
179
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

Super Moderator
Staff member
Local time
Today, 21:41
Joined
Feb 19, 2013
Messages
16,683
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

Member
Local time
Tomorrow, 02:11
Joined
Apr 5, 2020
Messages
179
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

Well-known member
Local time
Today, 22:41
Joined
Feb 7, 2020
Messages
1,991
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.
 
Last edited:

sbaud2003

Member
Local time
Tomorrow, 02:11
Joined
Apr 5, 2020
Messages
179
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

Super Moderator
Staff member
Local time
Today, 21:41
Joined
Feb 19, 2013
Messages
16,683
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.
 

Users who are viewing this thread

Top Bottom