We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

write to a table from a query

shaz0503
shaz0503 asked
on
Medium Priority
319 Views
Last Modified: 2012-05-11
Thanks for the help in my related query - This works well, however

While the query to add a date where there are blanks works wonderfully, this does not 'write' to the main table....silly me

Records are updated through a user form to tblStatementDetails.  tblStatementDetails forms part of the query where  'add a date where there are blanks ' is placed.

How, if possible can I get these dates to 'write' to the tblStatementDetails so that when a user goes to update a record, the dates that were 'added' in the query are reported.....

rgds

S

Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2009

Commented:
You haven't given enough specific information about the tables involved.  Generally speaking, if you need to update field in a table other than by just entering the value in a bound control, you can use an append or update query, or a SQL statement like this:

UPDATE tblNumericDates SET tblNumericDates.RealDate = CDate(Mid(CStr([DateAsNumber]),5,2) & "/" & Right(CStr([DateAsNumber]),2) & "/" & Left(CStr([DateAsNumber]),4));

Author

Commented:
Helen

Thanks...I will give you more detail - apologies

I have a main data table that the user updates information relating to staff. [tblStatementDetails].  I also have a data table (imported twice monthly) [EmployeeData].

I then have a query based on these two tables [Z_ActiveEmployee_tblStatmentDetails]..

Within the [Z_ActiveEmployee_tblStatementDetails] i have code to add a date where the [SoE review date] field is blank.  

I need to be able to 'write' this [SoE review date] within the [Z_ActiveEmployee_tblStatementDetails] qry to the [SoE review date] field in the [tblStatementDetails]

Hope this clarifies a bit more

rgds

S
Okay, let's clarify some terminology:
One doesn't "write" data from one table to another.  Instead, one "updates" one table from another.

So, if I understand your explanation correctly, you have a date in one table (Z_ActiveEmployee_tblStatementDetails) that you want to insert into a column in another table (tblStatementDetails).  First off, you need to figure out how to identify which row in the source table (Z_ActiveEmployee_tblStatementDetails) matches which row in the target table (tblStatementDetails).

UPDATE S
SET [SoE review date] = Z.[SoE review date] 
FROM tblStatementDetails S
INNER JOIN Z_ActiveEmployee_tblStatementDetails Z
ON S.someIDcolumn = Z.someIDcolumn;

Open in new window

Author

Commented:
Can I give some detail on the db....

The Db is used to capture and report on performance agreements.  Employee data is imported twice monthly [EmployeeData] table.

There is a table [tblStatementDetails] that is updated via a user form with information that is not available from the [EmployeeData] table (eg. dates for review, comments etc].  I have a query based on these two tables on which reports etc are based [Z_ActiveEmployee_tblStatementDetails].

To be able to update information I need to ensure that all employees are captured within the [tblStatementDetails] and the user form uses both these tables based on [EmployeeID] from the [EmployeeData] table.

One of the fields on the user form is [SoE review date].  This is 'updated' via the user form and 'updates' the relevatn record in the [tblStatementDetails].  New Employees however do not have an [SoE review date] and do not appear in reports.

In my [Z_ActiveEmployee_tblStatementDetails] qry I have code to add a date where the [SoE review date] field is blank so reports are correct.

My problem is that when a user goes to update a record, if that person is a new employee, their [SoE review date], calculated in [Z_ActiveEmployee_tblStatementDetails] qry does not appear as it does not update [tblStatementDetails].

I am sorry this is long winded but need to sort this one out if possible....

rgds

Shaz






My problem is that when a user goes to update a record, if that person is a new employee, their [SoE review date], calculated in [Z_ActiveEmployee_tblStatementDetails] qry does not appear as it does not update [tblStatementDetails].

If there is no date in the Z_ActiveEmployee_tblStatementDetails, what should the SoE review date in the tblStatementDetails table be set to?

Can you provide a simple set of example data showing the SoE review date for both the Z_ActiveEmployee_tblStatementDetails and the tblStatementDetails tables and one more line for what you really want it to be in each?  (Needless to say, I am struggling to figure out exactly what you are trying to do and from what data you are trying to do it. ;-)

Author

Commented:
Will post a 'cut down' verison of the Db today - this may help

S

Author

Commented:
All

Please see attached cut down Db -

You will see tables and queries I have mentioned above.

If you go to the [tblStatementDetails] table you will see that [EmpolyeeID] 1474030 does not have a date in the [SoE review date] field

however the code to add a date in the [SoE date field] in [Z_ActiveEmployee_tblStatementDetails] does

this impacts on when a user goes to update a record [form] and no date displays in the review date field

I hope this helps further

rgds
Database.mdb
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
8080_Diver

No... I hadn't considered moving the calculation to the form field......  If I do so, would only those where there is NO date have the calculation applied...I can not have dates that are within this field already 'calculated'

Do I simply copy the calculation and add to the field properties, and if so where??

rgds

Author

Commented:
8080_Diver

Have got this to work.... added Z_ActiveEmployee_tblStatementDetails[SoE review date] to the form field and all looks good.... will chekc over the next little while and hopefully when new data is imported next month - all will be fine.

rgds and  thanks for your help
When I opened the database to look at the Form's design, I noticed that you have some very bad coding habits.  

For one thing, you are not giving the various control opbjects on the form meaningful names (Text58 is not a meaningful name) and are, instead, leaving the default names that are assigned when you drop the control on the form.  

Another thing is that you are embedding SQL in controls and forms.  If you embed SQL in a control, then you introduce maintenance problems.  The first problem is that the SQL is hdden and, therefore, hard to find if it needs to be changed, either becuase of a change in Business Rules or because of an error in the SQL.  The second problem is that, in order to change the SQL (assuming you know where it is), you have to modify more than just the SQL . . . for instance, you have to open the form, change something, and then close the form all of which can introduce inadvertant errors in the form.

After I reviewed the SQL embedded in the form, it is apparent why you are not seeing the calculated review date in the SoE review date field (named Text58 :-/ ).  The Control Source for the field is based upon the SoE review date column of the [tblStatementDetails] table and NOT on the query you are working on.  

If you want to have the calculated value appear in the Textr58 field, then it will have to exist in the [tblStatementDetails] table.  If the calculation in the query you have been working on is based upon the Last Start Date from that same table, then why not just update the SoE review date in the [tblStatementDetails] table using that calculation?   You could even put some VBA code in the On Lost Focus event wherever the Last Start Date is entered so that it forces the SoE review date to the calculated value.

Failing that, you would need to put some VBA code in the On Got Focus event of Text58 to perform the calculation if the SoE review date column doesn't have a date in it.  The VBA code would need to see if the column is empty and then, if the column is empty, set the column to the calculated value, otherwise (i.e. if the column is not empty) not change the column's value (at that point).

By the way, did you notice that the use of Text58 was less meaningful in the above discussion than, say, txtbxSoEReviewDate? ;-)

Author

Commented:
8080_Diver

Thanks - I realise that the form is not perfect and I should use beter naming conventions....

I have now realised I jumped the gun - by doing the above (my comments) still does not update the tblStatementDetails...

With the On Got Focus what would be the appropriate code to add.....

whilst i have been 'creating' Dbs for a little while, i am still very much a VBA code novice....

thank you for you assistance thus far

rgds

S

You'll need to add an IFF statement that sets the [SoE review date] value to either the same value (if it is not empty) or to the calculated value (if it is empty).  
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.