Save daily versions of exported data

[CW]

We log the constantly moving positions of pallets in our warehouse, using
pallet number, aisle number, row number, level number.
This data is exported at the end of every day to a Word doc (table) on which
the warehouse foreman then notes any movements during the next day. This is
then turned in to the office at the end of the day and the system is updated
and so the cycle continues.
At present the document is overwritten every time we do an export so there
is no audit trail.
I suppose somebody could manually save the Word doc with a new filename
every day but it would be much better if Access could somehow keep an
automatic daily log of the data that was exported, so that we could call up
the pallet positions for any date at any time.
Can anyone suggest how to deal with this, please?
Many thanks
CW

 

[Ken Sheridan]

Three possibilities spring to mind:

1. Include the current date, in yyyy-mm-dd format so it sorts
(Format(Date(),"yyyy-mm-dd") will return the date in this format), in the
Word filename so it doesn't overwrite the same file each day.

2. Amend the logical model of your Access database so that all of the
movements are identified by date/time if this is not already the case. You
should then be able to write a query which returns details of all movements
on a particular day or within a date range.

3. Insert dated rows to another table within the database using an 'append'
query, which I think is what you have in mind. This is risky, however, as
there is nothing then to stop the 'log' data being amended and becoming
inconsistent with the original data from which it is computed. Method 2
above, on the other hand, prevents this as the 'log' data is always computed
on the fly from the base data.

Ken Sheridan
Stafford, England

 

[MikeJohnB]

You could also save the report to a folder example below which you could adapt.

FileName = "DriveLetter:\Folder\Anysubfolder\" & MyreportName & "_" & Date()
& ".rtf"

DoCmd.OutputTo acReport, "MyReportName", "RichTextFormat(*.rtf)", FileName,
True, ""

I use this mainly to save snapshot views of invoices for Traceabilty
Reasons, I have changed the .snp extension to rtf above.

Hope this helps but I would suggest you look carefully at Ken's answer, he
knows what he is talking about.

 

[a a r o n . k e m p f]

Access shouldn't be used for data warehousing.
Move to SQL Server

 

[GenlAccess]

Access shouldn't be used for data warehousing.
Move to SQL Server

It's losing its brainpower by the minute -- it's so far along toward
babbling and rubber rooms that it can't distinguish between "data
warehousing" and using a database to support running a physical warehouse.
Somebody better call the guys in the while coats with the big nets! Contain
it before it hurts itself or somebody else.

Gen'l Access

 

[Tony Toews [MVP]]

Please ignore Aaron's response as his definition of data warehouse
bares no resemblance to a physical warehouse.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tony Toews [MVP]]

We log the constantly moving positions of pallets in our warehouse, using
pallet number, aisle number, row number, level number.
This data is exported at the end of every day to a Word doc (table) on which
the warehouse foreman then notes any movements during the next day. This is
then turned in to the office at the end of the day and the system is updated
and so the cycle continues.

In addition to Ken's response # 3 which is what I suspect you are
looking for as well, consider scanning in the foreman's marked up copy
as well. This will save having to file all those documents and would
allow for more folks to look things up.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[MikeJohnB]

Just thought, Date() wont work in its raw format due to the / in the date.
You could use some other unique identifyer field or format date as ddmmyy
with no / or use dd-mm-yy

 

[CW]

Thanks a lot, Ken. I like the second suggestion. Easy to do and I think it
would cover all your needs nicely.
Thanks again
CW

 

[CW]

Thanks for the suggestion - I will probably run with Ken's no.2 but I have
already thought of another way in which I could use your suggestion for
something else!
Thanks again
CW

 

[CW]

We are extremely happy with Access. After two years of development we have
got just about everything running perfectly as we want it. There's no way I
would consider the learning curve of moving to SQL Server now. I have posted
my issue in this group because I want to resolve it with Access, I don't want
to get into any other app. And I wouldn't consider this to be data
warehousing anyway - the file sizes are actually quite small.
Thanks anyway
CW

 

[CW]

Thanks for your suggestion Tony. I'm playing around right now with Ken's
suggestion no.2 and it is looking good. I'm just building the query to pull
out the data for any given date and I think it'll provide what we want...
Thanks anyway
CW

 

[a a r o n _ k e m p f]

well good luck with that-- It's always difficult to forecast when and
where your database will grow to a size that is unusable in Jet.
And for the record, SQL Server is _EASIER_ to use than Jet.

Queries just work in SQL Server. You can stack queries on top of
queries and things just work.
In Jet, you get the famous 'the parameter is incorrect' lol

SQL Server has all the same functionality of Jet uising Access Data
Projects-- you simply build queries-- just like you would in Jet.

the phrase 'do it nice or do it twice' jumps to mind.
Your short-sighted $12/hour Jet work is a waste of time.

 

[a a r o n _ k e m p f]

and by the way, when you have 10,000 word documents-- good luck
writing a query to say 'what was my average rate of change over the
past 10,000 day shifts'?

you can't query 10,000 word documents.
And jet doesn't handle 10,000 of anything without slowing to a crawl.

Maybe you should go to India to learn how to build a real database
before you waste your companys time any further.

Honestly-- If I had my choice between paying warehouse foreman $12/
hour or an Indian certified DBA $12/hour, I'd go with the
knowledgeable source.

DO IT NICE OR DO IT TWICE.
Learn some basic SQL or else you won't be able to maintain this
database in the future.

Do you like the idea of swimming in a sea of printouts?

Jet doesn't handle 25mb of data without crashing.
Until they fix that-- anyone using Jet should be fired and then spit
upon.