Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Update lots of previous records 2/3

Posted on 2014-02-08
13
Medium Priority
?
339 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:Eric AKA Netminder
  • 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 52

Accepted Solution

by:
Gustav Brock earned 1000 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:Eric AKA Netminder
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 52

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:Eric AKA Netminder
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:Eric AKA Netminder
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 52

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 1000 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:Eric AKA Netminder
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:Eric AKA Netminder
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:Eric AKA Netminder
ID: 39846110
That's what I figured. The data is being formatted correctly, so for this purpose, it's fine.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

963 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