Replies

Yes, Hans, it works perfectly.

Thanks once again.

I apreciate the assistance and don’t have a problem with any criticisms. I am still a little confused by your description of the proper data design, but will definitely experiment with this. I guess I am so used to thinking one survey=one record with a separate field for each question.

Again, as always you are an asset and always appreciated.

Thanks Hans.

I apreciate the assistance and don’t have a problem with any criticisms. I am still a little confused by your description of the proper data design, but will definitely experiment with this. I guess I am so used to thinking one survey=one record with a separate field for each question.

Again, as always you are an asset and always appreciated.

Thanks Hans.

Yes, Hans that is correct. The problem also exists that some of the fields in the record will have a NULL value.

Thanks

Yes, Hans that is correct. The problem also exists that some of the fields in the record will have a NULL value.

Thanks

OK solved this problem too.

I just created a make table query with 2 fields, field1 and expr1, which is the sum of field2 in table1 and table2. I then ran the update query listed in the post above, except I changed it to point to table3 and included expr1

The make table is listed below

SELECT Table1.Field1, .[field2]+.[field2] AS Expr1 INTO table3
FROM Table1 INNER JOIN Table2 ON Table1.Field1 = Table2.Field1
WHERE (((Table1.Field1)=.[field1]));

and the update is listed here:

UPDATE Table1 INNER JOIN Table3 ON Table1.Field1 = Table3.Field1 SET Table1.Field2 = .[expr1]
WHERE (((Table1.Field1)=.[field1]));

I then created a macro to run both in order.

OK solved this problem too.

I just created a make table query with 2 fields, field1 and expr1, which is the sum of field2 in table1 and table2. I then ran the update query listed in the post above, except I changed it to point to table3 and included expr1

The make table is listed below

SELECT Table1.Field1, .[field2]+.[field2] AS Expr1 INTO table3
FROM Table1 INNER JOIN Table2 ON Table1.Field1 = Table2.Field1
WHERE (((Table1.Field1)=.[field1]));

and the update is listed here:

UPDATE Table1 INNER JOIN Table3 ON Table1.Field1 = Table3.Field1 SET Table1.Field2 = .[expr1]
WHERE (((Table1.Field1)=.[field1]));

I then created a macro to run both in order.

Ok, solved the first part. It was simple, I just didn’t think about it enough.

Used

UPDATE Table1 INNER JOIN Table2 ON Table1.Field1 = Table2.Field1 SET Table1.Field2 = .[field2]
WHERE ((.[field1]=.[field1]));

Now working on adding the second one to the first.

Ok, solved the first part. It was simple, I just didn’t think about it enough.

Used

UPDATE Table1 INNER JOIN Table2 ON Table1.Field1 = Table2.Field1 SET Table1.Field2 = .[field2]
WHERE ((.[field1]=.[field1]));

Now working on adding the second one to the first.

Thanks for the response.

I have removed the replication from the version I am linking to SQL Server, which originally housed both the forms and tables. The front end is now split. I was trying to avoid having to redeploy the front end when the lookup tables changed, but will look into updating the tables at startup. I assume you have all the SQL tables linked and then just run a series of Delete and Append queries to update the local lookup tables?

I will check out the link tonight as well.

Thanks.

Thanks for the response.

I have removed the replication from the version I am linking to SQL Server, which originally housed both the forms and tables. The front end is now split. I was trying to avoid having to redeploy the front end when the lookup tables changed, but will look into updating the tables at startup. I assume you have all the SQL tables linked and then just run a series of Delete and Append queries to update the local lookup tables?

I will check out the link tonight as well.

Thanks.

Thanks again.

Thanks again.

Thank you Steve, this is what I was looking for. Will this custom format remain with the workbook or is there something else I need to do to ensure that when I distribute this, the custom formatting continues to work on other computers?

Thank you Steve, this is what I was looking for. Will this custom format remain with the workbook or is there something else I need to do to ensure that when I distribute this, the custom formatting continues to work on other computers?