Microsoft Access

If you can spare the space, use a MAKE-TABLE query to get the price snapshot into a separate table - then use it for your query/report. You could then, if I understand the question, do a 'snapshot' of your prices table every two weeks or so, building a table each time, so you can go back and run your query/report at any time, looking at any snapshot, simply by changing the record source name for the query.

When you have your Database click on the colume header a drop down menu will show up and then just click on freeze colume

you do not have to create a new table each time you do a freeze.

I got the impression the OP was talking about freezing DATA at various points in time, so after price changes, he could still go back and see what the value was 2 weeks ago or a month ago or a year ago ;

Your technique is good for "freezing" columns in the grid display on screen.

I suppose either of us could be right..

The column locks are intended to prevent more than one user updating info at the same time (as I understand it) - my goal is to create a report of scrap dollars on a family of parts from month to month, but as the base price table changes, previously created reports do not change, but maintain the price that was in the query at the time the report was created. Like I said, the only way I have found that works (without having tremendously duplicated files each month) is to work only from the printed report, or to export to Excel & functionally create a new report. Neither of which satisfies me - so I am asking CR4 experts.

//
but as the base price table changes, previously created reports do not change, but maintain the price that was in the query at the time the report was created.
//

You've almost lost me here. You have a table with parts, and prices. You run a report. Two weeks later, the price for Part #1 changes. You run the report again, and you get the "current" price for Part #1. But you want to see the price as it was two weeks ago?

I'm kinda lost here now - if you want to maintain a history of price changes, you either need to do a 'snapshot' extract as I suggested, or create a transaction table that contains the part #, the date, and the price on that date - so you can then go back and say either, "what were all the prices on or before Dec. 1st", or "What have the price points been for Part #1 since Sept 1".

If I'm totally lost here, please clarify.

Majordud,

I know that Excel has a "paste special" function built-in. With it you can copy and then paste the copied cells as data only instead of pasting them as a reference to another cell and its formulae. I don't have the experience to know if Access has a similar feature. If it does, this should solve your problem. You could automate this process by creating a macro which stores all your keystrokes and mouse actions as you do this, and then simply invoke the macro whenever you wish to do it again.

--JMM

One short Access make-table query can do this in about 4 seconds - the query can construct a table whose name is tied to the date being 'snapshotted' - thus, the same query can be used over and over and over to create weekly or bi-monthly or for that matter, hourly snapshots - the benefit to creating snapshot tables is that after the fact, if you want to look at the data, it's there - with a PDF or report, if the information you want is not in front of you, you're kinda stiffed.

In these days of ridiculously cheap magnetic storage, it's far cheaper to create data tables than spend hours of person-labor screwing around with other alternatives.

However, since our OP has not chimed in with any additional details, I have a feeling we're just peein' in the ocean here..

Another option would be to print the report to a pdf, using adobe or a free pdf producer (such as CutePDF) and then save it on a server by date or other identifying name. Then people could access the report to their heart's content. We use this method with our MySQL and PHP/HTML reports.

The comment by JaDieN seems to be the most likely approach. I can save the report literally as it existed at time of creation - I just wish there was a more direct approach available in Access.

>>I just wish there was a more direct approach available in Access.<<

There is. I just told you what it was. Trust me. If you save ONLY a printed report, I can guarantee you that 6 months down the line, someone is gonna look at the report and want more detail - but you won't have it because all you did was CutePDF a report.

Save the data. With hardly any work at all, you can have a query that writes a snapshot dataset, and another query/report pair that can point to any one of your saved datasets to re-run the 'report' at any time. And then when the boss comes to you and says, "I want to see more about this damn part number from last October", you can just whip out the October snapshot dataset and tickle his fancy any way he wants.