Can this even be done? - Sorting Query Results a strange way

[JPeters]

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...

 

[ejc00]

A simple option would be to simply go to the design view for your query, and in the "Criteria" part of A1-A13 field, put [Please enter Period] or whatever the data is. This will then prompt the user to enter the period they want data for.

Another option would be to do this using a form by which the user can select their criteria and have it displayed in a subform.

 

[JPeters]

The problem with that is there isn't a date field to sort by. The users aren't required to enter a date/month/period of any sort when they enter into the source database. So unfortunately I'm up a creek.

-Josh
Wasting more of your valuable time...

 

[AArt]

Create a yes/no field in the table. Immediately after Boss Man loads his spreadsheet, run an update query and set all the records to yes . Then modify your first query to only select "no" records, and always run the update query to flag those records as "pulled" after the first query is run.

 

[THoey]

How about adding a column to your table that could be filled with the date the record was entered? Or a field that was of Yes\No type that would get updated to Yes when the record has been previously queried.

Just some suggestions... Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[ejc00]

I'm sure other people on here have a solution to your problem... but here is another option. You could add an autodate field such that going forward, your management will be able to get the result they want from the new data. as for the historical data, that depends on what you want done with it. you could just give them all a date prior to the new records, so they don't show up. or you could establish the break points and fill in a date for each month for each group of records so they would filter out.

i've found the easiest way to add a lot of data to a new column is to go into Excel, where it's easy to create a lot of data at once by simply dragging the mouse and then paste appending to the table.

again... good luck...

 

[THoey]

Seems I was posting at the same time as the other two were... Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[JPeters]

Great advice guys... Adding a date field would have to be a last result .... the yes/no option would be good and bad... good because it would solve the problem, bad because I would have to go back and re-configure several other forms/tables/etc... One person over in the queries discussion recommended a Boolean... I think I should read up on this.. What do you guys think?

 

[JPeters]

OH wait... you guys are all suggesting the same thing as him.... I love this forum... thanks!

-Josh
Wasting more of your valuable time...

 

[AArt]

That's why we're here! it beat's working.
Just because you add that "boolean" yes/no field to your table, you don't have to add it to your forms. Unless you want to, of course.

 

[leobaby]

This is a good forum.

 

[willyp]

With no date field you can still get only the records for the period of time that has passed between running the reports.

first. after running the report make a copy of the table ( January ) then next month use the query wizard to to create a FIND UNMATCHED query between your January sved table and your current table. Obviously the unmatched records will be the ones for that month.

Delete your January table & save the current table again as February if you don't need to save the data for future use.

You can make these UNMATCHED queries 'make table' queries and log your data month by month for historical trending if desired.

 

[BoxHead]

Happen609, in your original post, you brought up the issue of planning ahead.

Don't be surprised if you're spending New Year's Day trying to figure out how to generate the report of "all entries last year" or "How many entries did Employee 'A' make compared to Employees 'B', 'C', & 'D'?"

The Yes/No field will answer the immediate problem, but I'd urge you to reconsider the DateEntered field. Put it on your form. Set its Visible property to 'No' and set its value to the current date with an AfterUpdate event on one of the other fields. If users are logging in, I'd capture that info in the same way. It doesn't have to be seen, but it's useful information for you to have.

You might want to find out a little more about the Manager playing with data in excel and exactly "what he needs to know". It could give you a little more notice about information they'll be asking you for down the road.


Best of luck,
BoxHead

 

[JPeters]

Yo,
Weekend has past, and now I'm back in the office working on it. New light has been shed on this project. Aparently (get this - you guys will just LOVE IT), after all that brainstorming, the data from the source field (where I'm running the query from) gets IT'S data from another source. This source is from our IT department. Aparently they update the old data each month, but not just by appending. They actually overwrite the entire old table instead of just updating new records only. So the boolean thing seems like it's out of the question until I can meet with those guys in IT (and I'm sure most of you know how tough it is to get ahold of your IT guys in a company - they're always busy!).
Also, I had this really great suggestion from a lady named Charlotte in another forum (GASP, ANOTHER FORUM? I've sinned, I repent) ;-) . She suggested that I use query language in excell that links to the database instead of having the manager use the database and copy/paste. Now I've never done this, but I'm sure I could figure it out. Maybe it would make it easier? Or maybe it will just take me in another loop so I'm back at point 1 again.
Anywho, let me know what you think. Thanks again folks, you have been awesome.

-Josh (Happen609)
Wasting more of your valuable time...