Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Update lots of previous records 2/3

Posted on 2014-02-08
13
Medium Priority
?
338 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 51

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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 51

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 51

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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
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…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

664 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