Solved

Update lots of previous records 2/3

Posted on 2014-02-08
13
327 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 39

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
 
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

744 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now