Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2004
    Location
    London
    Posts
    64

    Unanswered: Access Query Issue

    Hi all,
    I am in the middle of a Staffing project and have managed to pretty much work it all out so far (with a little help from some of you mind). However i am in a slight dilemma. My problem is this.

    I have a Master Staff Table that holds a new row for each time an employee changes (i.e. Team Change, promotion, leavers etc).

    Each row has a start date (the date the change starts) and a change date,this represents the start date of the new record aswell as being the change date on the (now) old record.

    There is no change date on a current record.

    The problem seems simple enough on paper but i just cant figure it out.

    The user uses an Excel form for inputting the relevent change. This is then exported into an Access holding table to be checked before going into the master table. At this point i pull (from master table) all other records for that staff member and delete them from master (to avoid duplications). What i want to do is check these and then send them back into the Master table, but i want to enter the new records start date in the empty change date field in the now old record (i really hope this makes sense). By doing it this way we can pull all records without a change date into a current staff table that runs the MIS teams reporting amd leaves an audit trail of how many team changes/moves happen in a period of time.

    I would rather this did not have to be a manual process as it is only a weekly process and could potentially have 100 plus changes to make.

    I cant seem to come up with an answer, if i use an update query it will update the change date on all previous records not just the last one.

    Hope it makes sense of sorts.

    Many thanks in advance for any time taken or advice given

    Chris

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    i think you are saying that your table is something like this:

    myTable:
    myID
    myEmpName
    myStatus
    startDate
    endDate

    some records have an empty endDate... these are the current records
    some records have a filled endDate... these are the history

    to grab your current records:

    SELECT * FROM myTable WHERE endDate IS NULL;



    to change the status of "Fred":

    UPDATE myTable SET endDate = Now() WHERE ((myName = 'Fred') AND (endDate IS NULL));

    INSERT INTO myTable (myName, myStatus, startDate) VALUES ('Fred', 99, Now());

    the two values of Now() will be slightly different - if you want them identical:

    dim myDate as date
    myDate = now()
    ...and use myDate instead of Now() in the SQL

    you could equally use Date() or a literal date if you have one in the XLS


    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Sep 2004
    Location
    London
    Posts
    64

    Many Thanks

    Izy,
    Thanks for that you hit the nail on the head and that was just what i needed to know.

    Chris

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    apart from the typo (myName and myEmpName are supposed to be the same), i have another observation: in general i worry about using NULL (or any other default value) as a meaningful value... perhaps the default value is the result of an error somewhere.

    ?? consider adding another field
    isCurrent yes/no default False
    as a positive check on whether this is an active record

    the SQL becomes:

    UPDATE myTable SET endDate = Now(), isCurrent = False WHERE ((myName = 'Fred') AND (isCurrent = True));
    i.e. a history record has an actively entered endDate as "verification" and you do not depend on the default=False isCurrent

    INSERT INTO myTable (myName, myStatus, startDate, isCurrent) VALUES ('Fred', 99, Now(), True);
    i.e. a current record has an actively entered isCurrent as "verification" and you do not depend on the default=NULL endDate

    your current records are now:

    SELECT * FROM myTable WHERE isCurrent = True;

    the initial seeting for isCurrent comes from a one-time run with:
    UPDATE myTable SET isCurrent = iif(isnull(endDate, True, False));

    your error records are:
    SELECT * FROM myTable WHERE (((isCurrent = True) AND (endDate IS NOT NULL)) OR ((isCurrent = False) AND (endDate IS NULL)));

    izy
    currently using SS 2008R2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •