Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jul 2001
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Can this even be done? Macro to sort query?

    Fellow Access Geniuses,
    I've got a small problem. We've designed this matrix database ... in a form you enter data such as policy type, begin date, end date, among others... Then we run a query that pulls up only those records (duh a normal query) .. here's what Management wants done.
    You see this query is ran on a monthly basis. Then the manager who runs it pastes the results into excell, plays with it, (don't ask me why he uses Excel... he likes it) finds out what he needs to know, and then he runs an append query in a DIFFERENT database taking the excel file and importing it into the new db.
    The problem we are running in to is that the query we run on the first database (policy type, begindate, enddate, etc) pulls up ALL the records from forever's past. Since this is ran monthly.. In July I'll see records A1-A12.
    During the Month in between the query's running, multiple users will enter new records into the a database that the query links to.
    When I run it again in August I see A1-A13, A13 being a newly added record (bear in mind that there are thousands of records, not just a simple 13). Management doesn't want to see A1-A12. They only want A13 to show up when they run the August query.
    So to put it simply, they don't want to see records that they have previously seen in this query. In July they want to see records that hadn't been displayed from the June running. In August they want to see records that weren't shown in July... and so on. Or they want the NEW records to be highlighted somehow, or made to stand out.
    It baffles me and I'm not sure that this can be done without writing some sort of module (which I don't have a clue how to do). They want me to run a query and then tell that query to sort their First results by whether or not the records have been displayed before in the query.
    There isn't a date field that I can sort by, unfortunately. Or I'd have this done by now. And I can't go back and enter a date field because all of those previous thousands of records don't have date fields (some people just don't plan ahead - doh!).
    So tell me folks, what can I do. Thanks ;-)

    -Josh
    Wasting more of your valuable time...

  2. #2
    Star Lounger
    Join Date
    Feb 2001
    Location
    Wirral, Merseyside, Merseyside, England
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can this even be done? Macro to sort query?

    Is there is any other key field, say Policy Number, ID etc.. that may run in sequence? you could then log the last number extracted and request only numbers greater than this one.

    Or

    Don't see a problem with adding a date field for future records defaulting to todays date on data entry. You'll have a problem with historical data but not going forward.

    or

    I am reading between the lines that there is some problem with uniqueness of records for the manager. Are there any unique indexes on the database that the manager imports the data to? If there were it wouldn't matter that data is duplicated from the export as it would be handled on the import to the new database.

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Can this even be done? Macro to sort query?

    Teach them to use Microsoft Query from Excel. You could set up a spreadsheet with a link to the query and show them how to do their analysis from there easier than you could muck around in Access trying to satisfy them, and they wouldn't have to paste anything except possibly between spreadsheets.
    Charlotte

  4. #4
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can this even be done? Macro to sort query?

    You could use the "top values" according to date, & put in your criteria using NOW. Can't quite remember how I used it before. But it's cretainly doable.

  5. #5
    New Lounger
    Join Date
    Aug 2001
    Location
    Tucson, Arizona
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can this even be done? Macro to sort query?

    Try adding a Yes/No field to act as a "printed" flag. Default the field to No, and update it to Yes after you run your query for management (use VB or a macro to run your normal query, then an update query). Only pull those records for management where the print field is No.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •