Statistics

[James]

Hello,
What I would like to do is have a table with several fields, eg, date, Mr.
Mrs. etc. Then every month I want the database to run a query to find out how
many Mr's there are and input the number into a record with the date and
number of mrs etc.
So then i can export the data into excel and have a graph or pie chart or
something to show the split.
I also want to use it to show the number joined or total members etc.

Any suggestions how this can be done?

Many thanks in advance. James.

 

[Niklas Östrergren]

Well you could allways start a public function each time the application is
started up. Let the function first of all check a boolean value, which you
store in a table, to see if the function have been run at this mounth or
not. And if not use DAO.Recordsets to count number of Mr. and Mrs. and with
help of DAO.Recordset add a new record in your table adding the result.

This answere is, offcourse, verry theretical and general to it nature but on
the other hand is you Q so to. If you need more detailed/specifyed help
please try to break this problem down and ask more spcific q.

What you want to do is possible and I´ll try to help you out as much as I
can.

// Niklas

 

[James]

Hi Thanks for your reply.

At the moment I go through and manually type in the data into Excell for how
many members we have and percentage Mr/Mrs etc, by month. This way I can make
a graph showing membership numbers over time. I was wondering if it is
possible to get Access to do it automaticaly, saving me doing it. Then when I
want the data I run a macro and it loads it into Excel and brings up a pie
chart or line graph.

 

[Niklas Östrergren]

Yes it´s possible! At the verry beginning I thought that you wanted your
application to automaticly export the data at a given time each month. Ant
that is sure possible to do.

But if you can settle with a much simpler way where you, or anyone else,
press a command button each time you need the data exported to Excell. Then
you could allways use:

DoCmd.TransferSpreadsheet(TransferType, SpreadsheetType, TableName,
FileName, HasFieldNames, Range, UseOA)

How to use this is quit simple. Read about TRANSFERESPREADSHEET in help file
of access.

An example is:

DoCmd.TransfereSpreadSheet(acExport,,"qryAllMr","C:\My Document\AllMr.xls")


Where "qryAllMr" is the name of your query in Access where all Mr is
selected. This is the data which will be exported to Excell. And "C:\My
Document\AllMr.xls" is the path and filename where to export the data and
which filename to save the Excell document.

You could put this code in a commandbuttons click_event and each time you
click the button a new Excellfile will be created at the specifyed path with
fresh data from your db. If you allready have a file with the same name and
in the same folder it will be overwritten so if you want to save the data
each mounth rename the file. There is ways to prevent this but this is the
simplest way you could do.

If you want you could have all Excellfiles created and saved by just
clicking one button. Just create several allmost simular lines of code in
click event of the same command button.

I hope this gives you some help. If you need more help please let me know
and I´ll try to help you out.

// Niklas

 

[Niklas Östergren]

I read more about Transferespreadsheet and I was not completly correct. If
you try to overwrite an allready existing Excell file, by using
DoCmd.SpreadSheet the old file will still contain the old data. So you first
have to remove or rename the existing file.

There are ways to solve this but I don´t have the knowledge to help you out
with that unless I have my books infront of me.

// Niklas