Ms-access – How to create a CSV file which imports data from an Access Database on a Weekly Schedule

jobsms accessscheduled-taskstime

I have very little experience in database management but have come across a problem I am unable to find a solution to online. I currently have access to a database which is updated in real time with the most recent sales figures for a company. In simple terms it looks like this on 01/09/2016:

Sales
50

I wish to make a CSV report which takes the sales levels from the live database each week and inserts them as a new row in the CSV file. In order to track the report date, I had planned to add a new column named 'Report Date' with the date of report update listed for each observation taken from the live database.

The desired output would have a format similar to

Report Date     Sales
01/09/2016      50
08/09/2016      70
15/09/2016      79

It would be good to know what people think is the best method to produce this type of dataset. If there are any options that involve SQL that would be good, but open to any method that does the job.Let me know if any more information is needed!

Best Answer

If I understand your question correctly ... you are trying to store the Report Date and Sales Value in a CSV file on a weekly schedule.

Why not save this information in your database? Have a scheduled job run a query to produce your sales numbers. That scheduled job would insert the Report Date and Sales number into a database table. That way you don't have the sales numbers in an external CSV file.

When you want to check the report date you just have to run a simple query to pull back that information. Let me know if I'm missing the boat.

*** EDIT **** In your post you said "I have access to a database". I suppose I should have first asked if this was an "Access Database" or a "SQL Server Database".

Related Solutions

Ms-access – Sorting Report entries on alphanumeric combination

Your tag says MS Access, if you are using Jet/ACE in MS Access, you can sort by a user defined function.

Very roughly

SQL

SELECT t.ID, t.SortHere, SortOrder([SortHere]) AS ToSort
FROM Table t
ORDER BY SortOrder([SortHere]);

Code

Function SortOrder(ToSort)

Part1 = Left(ToSort, 1)

For i = 2 To Len(ToSort)
    If Not IsNumeric(Mid(ToSort, i, 1)) Then
        Part2 = Part2 & Mid(ToSort, 2, i - 2)
        Part3 = Mid(ToSort, i, 1)
        Part4 = Mid(ToSort, i + 1)
        Exit For
    End If
Next

If Part2 = vbNullString Then
    Part2 = Mid(ToSort, 2)
End If

SortOrder = Part1 & Format(Part2, "0000") _
          & Part3 & Format(Part4, "0000")

End Function

I am sure you can come up with a better function with a little thought.

Ms-access – Backup Access Database Daily/Weekly

(Borrowing from my earlier answer here...)

re: actually performing the backup

Backing up a native Access database is simply a matter of copying the entire database file (.mdb for Access_2003 and earlier, .accdb for Access_2007 and later). Any scripting language would work, even a simple Windows batch file that does something like

copy /Y d:\apps\databases\mydatabase.accdb z:\backups\databases\*.*

re: automatic scheduling of the backup

The Task Scheduler in Windows could take care of that for you. Once you've created your script to copy the database file(s) you can create a scheduled task to run it periodically. See the MSDN article Using the Task Scheduler (Windows) for more information.

Best Answer

Related Solutions

Ms-access – Sorting Report entries on alphanumeric combination

Ms-access – Backup Access Database Daily/Weekly

Related Question