Solved

Update lots of previous records 2/3

Posted on 2014-02-08
13
332 Views
Last Modified: 2014-02-09
This is part 2 of a 3-part question; the others are located at
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_28360144.html
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_28360143.html (yes, I know they're out of order)

In the attached database, which is data retrieved from an ODBC-connected database to which I have read-only access, I can import the data shown in tblOrig. There are hundreds of IDNumbers; this is filtered data for one of them. Essentially, the table from which I am drawing this information lists every IDNumber, and revisions to it are shown in the Revision field, so the unique identifier of each record in the table is IDNumber & Revision.

I'm not really interested in updating tens of thousands of records manually, what I'm interested in doing is renaming the EditDate (shown in tblOrig) to StartDate (shown in tblNew) and then running a query that looks at the StartDate from record 10085-2, subtracts one day and updates the EndDate field for record 10085-1, then goes to record 10085-3, looks at the StartDate, subtracts a day and updates 10085-2 and so on. That would leave the last revision of each record without an EndDate until whoever updates the ODBC database I can't update does another revision.

Part 1 of this question has to do with doing this automatically whenever there are new revisions added to the ODBC database. Part 3 has to do with some kind of script that would let me poll the ODBC database and get the revisions that have been added since last time I updated my database.

My Access coding skills are... rusty... so any assistance would be greatly appreciated.

Thanks,

ep
efptest01.mdb
0
Comment
Question by:ericpete
  • 6
  • 3
  • 3
  • +1
13 Comments
 
LVL 40

Expert Comment

by:als315
ID: 39845024
Please, look at my sample in Part 1. May be it is also answer to this question
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 250 total points
ID: 39845075
You neither need a tblNew or to update anything.
You can just create run this query:

SELECT
    tblOrig.IDNumber,
    tblOrig.Revision,
    tblOrig.LocationString,
    tblOrig.EditDate AS StartDate,
    Fix(
       (Select
           EditDate
       From
           tblOrig As T
       Where
           T.IDNumber = tblOrig.IDNumber
           And
           T.Revision = tblOrig.Revision + 1)) - 1 AS EndDate
FROM
    tblOrig;

/gustav
0
 
LVL 15

Author Comment

by:ericpete
ID: 39845570
Alexey,

See Part 1.

Gustav,

That looks fine except for one thing: The newly created EndDate column needs to be formatted as dd-MMM-yy (or Medium Date). I've tried the process of opening the query in the design -> datasheet view and changing the format of the new field, but it isn't taking; I'm still getting the five digit number for the date.

Thoughts?

ep
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 38

Expert Comment

by:Jim P.
ID: 39845593
No Points.
SELECT
    tblOrig.IDNumber,
    tblOrig.Revision,
    tblOrig.LocationString,
    tblOrig.EditDate AS StartDate,
    Fix(
       (Select
           Format(EditDate, "dd MMM yyyy") 
       From
           tblOrig As T
       Where
           T.IDNumber = tblOrig.IDNumber
           And
           T.Revision = tblOrig.Revision + 1)) - 1 AS EndDate
FROM
    tblOrig;

Open in new window

0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39845608
The value is correct, so you can just apply the format to either the column Format property of the query or the textbox of the form where you display it. Do the same for StartDate.

/gustav
0
 
LVL 15

Author Comment

by:ericpete
ID: 39845685
Gustav,

That's my point; applying the format to the column property of the query didn't work. StartDate is fine because (I assume) it's set by the table and inherited in the query.

Jim,

Close but no cigar. Both "dd MMM yyyy" and "dd-mmm-yy" throw #Error in the EndDate column.

===

When I use Alexey's two queries (one selects, then uses the results to update the "tblNew" table shown in his example) the data format is obviously inherited and works just fine.

In Gustav's example, changed to a CreateTable query but with no formatting, the date is saved as a Number, not a Date/Time field -- which, I suppose is okay because it's not all that difficult to change the format of the field, but it's not exactly elegant.

If I use Gustav's example WITHOUT setting it as a CreateTable query, then it works perfectly (the format is set in the design query/datasheet view).

ep
0
 
LVL 15

Author Comment

by:ericpete
ID: 39845701
This will make it a little easier.

GustavQuery1 is his original select query. GustavQuery2 is the same exact query -- no changes made EXCEPT it is converted to a MakeTable query.

JimQuery1 is a select query with the formatting clause Format(EditDate, "dd MMM yyyy") except that I've entered the correct syntax (*snark*).
efptest02.mdb
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39845908
> applying the format to the column property of the query didn't Work.

Then you can do it in the form.

/gustav
0
 
LVL 38

Assisted Solution

by:Jim P.
Jim P. earned 250 total points
ID: 39846009
Had the format statement in the wrong place.

SELECT tblOrig.IDNumber, tblOrig.Revision, tblOrig.LocationString, tblOrig.EditDate AS StartDate, Format(Fix((Select EditDate  From    tblOrig As T        Where T.IDNumber = tblOrig.IDNumber  And    T.Revision = tblOrig.Revision + 1))-1,"mm-ddd-yyyy") AS EndDate
FROM tblOrig;

Open in new window

0
 
LVL 15

Author Comment

by:ericpete
ID: 39846043
Then you can do it in the form.

Which doesn't help if you're just looking at the table.

Had the format statement in the wrong place.

Your code creates 9-Sun-14 for today, but that was easy enough to fix.
0
 
LVL 15

Author Closing Comment

by:ericpete
ID: 39846053
Don't know how this got flagged as Neglected, but I certainly don't mind the idea of handing out extra points, considering it's a weekend.

Between the two, I have this to the point where it's acceptable. I'm curious as to why the new EndDate column is flush left and not flush right like the StartDate column, but I'm not going to worry about it.

Thanks for all your help!

ep
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 39846092
EndDate column is flush left and not flush right like the StartDate column,

The format function puts out a text or string column regardless of the value of the field. In a report you would just change the justification. Or in a field on a form you can also do the input at 2/01/14 and the format will still right justify it. There are just so many date handling issues in Access that you just get used to them and work around as needed.
0
 
LVL 15

Author Comment

by:ericpete
ID: 39846110
That's what I figured. The data is being formatted correctly, so for this purpose, it's fine.
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

840 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