SQL question

[nmretd]

Can someone show me some SQL code to do the following:

I have 2 tables in Access. They are as follows:

Table 1:

Name StartDate EndDate
---- --------- -------

A 01/01/00 03/01/00
B 01/01/00 03/01/00
C 31/01/00 31/01/00

Table 2:

01/01/00 02/01/00 03/01/00 ....31/01/00
-------- -------- -------- ....--------

There is no data in the above fields for Table 2 (yet).

I want the code to select the Name of the person in Table 1 and insert it into the relevant field in Table 2 so that I have something like the following:

01/01/00 02/01/00 03/01/00 ....31/01/00
-------- -------- -------- --------
A A A
B B B
C

A and B appear in the first three fields in Table 2 because in Table 1 their start and end days are 01/01/00 to 03/01/00.

Can someone please show me some code?

[netSurfer]

Roughly:
dim rcdTable as recordset, dbsData as database, strName as string, strSDate as string, strEDate as string, rcdTable2 as recordset

'I'm setting the start and end dates as strings because you will need to compare to the field names

set dbsData as Database("data1.mdb")
set rcdTable as dbsdata.OpenRecordset("Select Name,StartDate,EndDate from Table1")
do until rcdTable.eof
strName = rcdtable(0)
strSDate = cstr(rcdtable(1))
strEDate = cstr(rcdtable(2))
set rcdTable2 as dbsData.openrecordset("Select * from table2")
'need to cycle through the field names I assume to find one that matches the sdates?
for intI = 0 to rcdtable.fieldcount -1
if rcdtable2(intI).name = strsdate then
rcdtable2.addnew
rcdtable2(inti) = strSDate
rcdtable2.update
exit for
elseif rcdtable2(intI).name = strEDate then
rcdtable2.addnew
rcdtable2(intI).name = strEDate
rcdtable2.update
exit for
end if
next inti
rcdtable2.close
rcdtable.movenext
loop
rcdtable.close
dbsdata.close

This is a quick example of what I'd do, sory about the formatting

[nmretd]

Thanks netSurfer but I am having problems with the following line in your code:

for intI = 0 to rcdtable.fieldcount -1

I am getting an error message saying:

Compile error: Method or data member not found.

It doesn't like the .fieldcount !

Please help.

[netSurfer]

Sorry about that. It should be:

rcdTable.fields.count

You may have to throw it in a seperate integer and then use that in the for loop. I'm not sure but you may have to watch for the formatting of the dates in table1 versus the field name formats in table2. When you are making table2 with the dates as field names, are you doing this manually? I'm not sure whether you are doing a different table for each month or what but if you are doing it manually, I can give you code that will create the new table and fields.

Oops, I also just realized that I had table2 adding new records using the dates, it should be like this:

if rcdtable2(intI).name = strsdate then
rcdtable2.addnew
rcdtable2(inti) = strSDate >> strName
rcdtable2.update
exit for
elseif rcdtable2(intI).name = strEDate then
rcdtable2.addnew
rcdtable2(intI).name = strEDate >> StrName
rcdtable2.update
exit for
end if

replace the strSDate and strEDate with strName.

[This message has been edited by netSurfer (edited 01-14-2000).]