Exporting Lots of Data - Best Approach

[barn606]

Hello,

Right Having a knightmare, So I will Try and Be Brief.

Currently I have a Large Database (Daily Entry) i.e


Date,Code,Weight,Number,Comments

this Exports to a Lenghtly Excel System that Sorts the Data and Referances it to other sources of daily data, all working fine,

however i have now been asked to provide a standard deviation of the weight section in the daily report for a six month period (arragh).

Now the only way i can think of doing that is having the data exported for each code (Between relevant dates) onto an individual worksheet with the s.d being worked out at the bottom and referanced to a table that the excel system sheet can lookup (i.e just add another coloum to each proccess) .

So to my question how do i get the module to export every code Recorded and all the daily data for the code requested to and new sheet. for each code ?

i.e

Code Date Weight Number (Worksheet Name as Code)


and that this is done for every code in the database with out having to write or enter every code each day ino the query/module exporter (note codes Change Dailyish so big list for a macro to work down not really practical).


or am i missing something can the data be exported with the Standard deviation for the period allready worked out ? (200 + codes so doubtfull)

any help or pointers would be gratfull before i meet the hangmans noose

thanks

Barn606

 

[FLM]

You can try

Datapig Access Explosion
http://www.datapigtechnologies.com/freeware.htm

"This Utility will split your Access datasets into separate Access Tables, Excel workbooks or separate Excel tabs based on criteria you define! So you can create a separate workbook for each Employee in your dataset or each City, or State...etc. All this with one click! "

You just have to copy the form into your database and it will do all the work for you.

I use it a lot.

Frank

 

[Jibbadiah]

You could try something like the following. You create a recordset which will specify the names of the queries that you want to output... in this case we will call both the queries and the worksheets by the names of your codes. It will run through... create a temporary query for each code, output the code to a new worksheet named as the code... then delete the temporary query and move to the next code. Paste it in a module and away you go.

J.

Sub OutputToSeparateWorkSheet()

Dim dbs As Database
Dim rstCodes As Recordset
Dim qdfTemp As QueryDef
Dim QueryName As String
Dim FileName As String

Set dbs = CurrentDb
Set rstCodes = dbs.OpenRecordset("Select Code from [B]qryYourQuery[/B] where Code is not null group by Code order by Code ASC;")

With rstCodes
    .MoveFirst
    Do While Not rstCodes.EOF
    QueryName = (rstCodes ("Code"))
    FileName = "c:\temp\" & QueryName
    
    With dbs
        Set qdfTemp = .CreateQueryDef(QueryName, "SELECT * FROM [B]qryYourQuery[/B] WHERE Code = '" & QueryName & "';")
           DoCmd.TransferSpreadsheet 1, 8, QueryName, FileName, True
        .QueryDefs.Delete QueryName
    End With
    .MoveNext
    Loop

End With

rstCodes.Close
Set dbs = Nothing
Set rstCodes = Nothing

End Sub

 

[Adeptus]

There are standard deviation functions in Access: DStDev & DStDevP
They may do what you want...