tricky APPEND

[Guest]

I have two tables.
Table1 is constant has 20 set fields and is used to store daily data.
Table2(temp) is created on a daily basis from a crosstab(pivot) query of
another query pulling data from the back-end.

Table 2 records are to be appended to Table1 on a daily basis…here is the
problem:

Table1 with its set fields will always contain any and all fields Table2
will consist of but Table2 may not always have all the fields found in
Table1. Table2 will never have more or different fields then Table1 it will
just be missing fields if there are no values during the crosstab in those
pivoted fields. The fields that are missing should just stay null in table1
and only find and insert the fields that are existing.

Help…

 

[Guest]

Table1 with its set fields will always contain any and all fields Table2
will consist of but Table2 may not always have all the fields found in
Table1. Table2 will never have more or different fields then Table1 it will
just be missing fields if there are no values during the crosstab in those
pivoted fields. The fields that are missing should just stay null in table1
and only find and insert the fields that are existing.

Do this in code. Loop over the records in table two and...

1) add a new row to table 1
2) read a row from table 2
3) loop over the fields in table 2, and extract their name and value
4) populate that field name in table 1 with that value
5) continue both loops

eg:

while not rstTable2.EOF
rstTable1.addnew

fieldcount = 1
While fieldcount < rstTable2.fields.count
key = rstTable2.fields(fieldcount).name
value = rstTable2.fields(fieldcount)
rstTable1.fields(key) = value
wend

rstTable1.update
rstTable2.movenext

wend

 

[Guest]

I did not get a question out of your post. Are you wanting to know how to
handle the field in Table1 that Table2 will not have?
If that is the case there probably will not be a problem unless the field
are set to Required. If that is the case then you can either change to not
required or build a dummy record in the source of the crosstab so they will
always appear - use a dash in text fields and zero in nemerical fields.

 

[Guest]

With a few minor tweeks your appraoch worked perfectly...Thank you