Update lots of previous records 2/3

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
LVL 15
Eric AKA NetminderAsked:
Who is Participating?
 
Gustav BrockConnect With a Mentor CIOCommented:
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
 
als315Commented:
Please, look at my sample in Part 1. May be it is also answer to this question
0
 
Eric AKA NetminderAuthor Commented:
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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Jim P.Commented:
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
 
Gustav BrockCIOCommented:
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
 
Eric AKA NetminderAuthor Commented:
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
 
Eric AKA NetminderAuthor Commented:
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
 
Gustav BrockCIOCommented:
> applying the format to the column property of the query didn't Work.

Then you can do it in the form.

/gustav
0
 
Jim P.Connect With a Mentor Commented:
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
 
Eric AKA NetminderAuthor Commented:
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
 
Eric AKA NetminderAuthor Commented:
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
 
Jim P.Commented:
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
 
Eric AKA NetminderAuthor Commented:
That's what I figured. The data is being formatted correctly, so for this purpose, it's fine.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.