?
Solved

write to a table from a query

Posted on 2011-04-28
13
Medium Priority
?
300 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

0
Comment
Question by:shaz0503
  • 7
  • 5
13 Comments
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 35491553
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));
0
 

Author Comment

by:shaz0503
ID: 35508939
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
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 35513247
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

0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 

Author Comment

by:shaz0503
ID: 35694390
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






0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 35694486
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. ;-)
0
 

Author Comment

by:shaz0503
ID: 35694776
Will post a 'cut down' verison of the Db today - this may help

S
0
 

Author Comment

by:shaz0503
ID: 35695551
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
0
 
LVL 22

Accepted Solution

by:
8080_Diver earned 2000 total points
ID: 35698066
this impacts on when a user goes to update a record [form] and no date displays in the review date field

Okay, it there is no [SoE review date],  the Z_ActiveEmployee_tblStatementDetails calculates one (based upon the "last start date").  I am still not exactly sure what you're trying to accomplish, though.

Are you wanting to have the user presented with this calculated date and then be able to update the date and have that date inserted into the
[tblStatementDetails]?  If so, are you having trouble getting the date to display or to be saved into the [tblStatementDetails] table?

Have you considered moving that particular calculation to the form's field?
0
 

Author Comment

by:shaz0503
ID: 35702410
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
0
 

Author Closing Comment

by:shaz0503
ID: 35702657
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
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 35702718
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? ;-)
0
 

Author Comment

by:shaz0503
ID: 35702839
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

0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 35702927
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).  
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question