Access Help -Archiving, Query help

[Megahurtz]

I have 3 Tables TripStart, TripData, TripMoves all share the common field Driver_Run. I have a form that has TripStart as the Parent and TripMoves, TripData as subforms.

heres how it works
[TripStart] has a unique Driver_run (PrimaryKey) Number
There can be many Trips per Run [TripData] There can also be Moves [Tripmoves] associated with Driver_Runs

I can get all te records onto one form and print correctly this sheet is given to drivers who fill it out. no problems yet

BUT... I have had trouble with my queries

I need to come up with a query scheme so that the end user only works with a single record at a time. once they print a form they will click a button that will run macros that will "clean out" the above 3 tables based on then having a field I can mark. I had started down this path but need help with how to get the value in for Driver_Run (it will vary) I had planned on forcing the user to select via a combo box but had trouble formulating that into a query.

UPDATE TripData SET TripData.TripSheetPrinted = "Y"
WHERE (((TripData.Driver_Run)=1));

I have also created tables called [TripStartMaster] etc , {TripStartArchive] and plan on using these later. Master for entering the info back from the driver and Archive to Store Processed work.

Any help with my SQL or how to perform this better (variables?) would be greatly appreciated. My VBA skills are on the weak side....
Thanks
<MEGA>

 

[Garridon]

For archiving your records, you can find some information on the Microsoft web site:

http://office.microsoft.com/assistance/2002/articles/acPeriodicallyArchive.aspx

You can pick a particular criteria that's met and use it in your append query. For instance, if you have a Date Completed field, you can set it so that it will archive when the field is filled in.

But that doesn't get rid of the files in the main table. You'll need to run a companion delete query with the same criteria--but make sure you test it throughly! If even one field is off, you may end up deleting a record without making a copy of it! It can be especially problematic if you have to add or delete fields--you have to make similar adjustments to the queries and then retest it.



Linda Adams
Visit my web site for writing and Microsoft Word tips:

http://www.hackman-adams.com

Official web site for actor David Hedison:

http://www.david-hedison.com

 

[Megahurtz]

Thanks Linda
There was some decent stuff butI find the docs out there lacking in RealWorld Application.

I wrote a Macro that has PrintOut,3 Update w/&quot;Y&quot; , 3 Append & 3 deletes based on &quot;Y&quot; key of TripSheetPrint. The Goal was to make it as simple as possible.. it's a one click solution.

I will follow the same methodology in the app to Process the TripSheets, and Archive those results.

I just know there's a thousand ways to build this better...

It's been 6 months since I have even seen MS Access.. I used to be a novice.... now its back to NooB

Luckily, this is really just a stop-gap-app until the Real Programmers develop the visual Foxpro program...