asked on

code to add data to a blank field in a table

All

Next query for my Db.......

Db is 'comprised' of two main tables

1 contains most recent staff data
2 contains information entered by user based on the staff data (information that is not captured in staff data)

I have several queries that produce information by way of queries, reports, exports etc.

I have been bothered since the beginning of this process though.

When new staff data is imported, there is no information for 'new' staff in one of the fields within the second table....and as reports are run form queries using both tables, I need to know how to add the following to an exisiting field.

eg: Joe bloggs commences work...He has no performance agreement in place....All staff must have one within 2 months of commencing work...

How do I add (Last start date plus 2 months) to the field in the second table for joe so that his supervisor gets a report with the due date on it - instead of a blank

Obviously when there is an agreement in place, a review date will be in the field. I can do a seperate query to add this date but then how do I 'meld' the two fields to one....

as always, any help appreciated.....

manthanein

perhaps what you could do is add a formula in the query something like

datefield: iif([review date] is null, [laststart] + 60, [review date])

manthanein

you may also use datadd for adding 2 months

MSAccess supports DateAdd ( interval, number, date )
now for current time....

Apply them in your select query/Update them at once.

shaz0503

ASKER

Thanks all

manthanein - by adding this to the SQL - seems to remove all records with blank SoE review date

manthanein and Gk - where and how do I add DateAdd

I have attached the code and an export of the query so you can see what I am trying to do........

In column J of the export there are blanks...this is ok...however, I need to add a date to column K where there is a blank that is 2 months from Last Start Date (column E). This will then allow me to report on those new staff and when a performance agreement is due

rgds

s

SELECT [_qrytblStatementDetails].[Department Description], [_qrytblStatementDetails].[Employee ID], [_qrytblStatementDetails].Surname, [_qrytblStatementDetails].[Given Name], [_qrytblStatementDetails].[Last Start Date], [_qrytblStatementDetails].[Employment Term Description], [_qrytblStatementDetails].[Eligibility Group], [_qrytblStatementDetails].[Supervisor Name], [_qrytblStatementDetails].[Current SoE], [_qrytblStatementDetails].[SoE on File], [_qrytblStatementDetails].[Date SoE signed], [_qrytblStatementDetails].[SoE review date], [_qrytblStatementDetails].education, [_qrytblStatementDetails].research, [_qrytblStatementDetails].service, [_qrytblStatementDetails].Comments
FROM _qrytblStatementDetails;

Open in new window

Copy-Of-Z-ActiveEmployee-tblStat.xlsx

shaz0503

ASKER

All

I have been having another look at this and wonder if the following scenario is possible

Can I import new data and somehow get a table to report new employees (would not be in the existing 'EmployeeData' table which is 'overwritten' on each new import).

Could I then add these new employees to the 'tblStatementDetails' table and add the SoE review date as - Import date plus 60 days -

The more I look at this and play with ideas the more confused and frustrated I get

S

ASKER CERTIFIED SOLUTION

manthanein

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

shaz0503

ASKER

Thanks so much

Works wonderfully....

rgds

S