Create a stastical report and extract to Excel

[branbran]

Hi There,

I'm fairly new to access. I have database that I need to create a statistical report from, so titles with numbers, how many records at this status or that status, how many records of this customer type (we have categorized the customers). I almost want it to look like a dashboard. I then need to be able to export it to excel because my team only has excel, not access. Is there a way to do this in access? Do I need to use the reports function or reports wizard? I don't want to see all records, I just want to pull out how many records at a bunch of different status, so I will pull from how many occurrences of a value out of a certain field, or a combination of fields.
I am used to using formula's in excel to do this, but I'm not sure how to pull out this data and display it in access. I used to work in SQl databases along time ago but my SQL is rusty, but I have a solid understanding of how databases work, I'm just not sure what Access functions what I need to use in order to create this. Is what I've described possible?

thank you!

[ranman256]

Yes, you build queries to pull the data you want. Then make charts,reports with these.
these can be controlled on a form, to limit the date range,or the customer, etc.

[branbran]

ok thank you, so essentially I would like to look like an excel spreadsheet, 5 headings with the numbers underneath. So a heading would say "No. of applicants In progress" and the number underneath would say 30. So if I build a query to grab all applicants of a status in progress it will show me all the rows of data, how do I then build a report to have the label and the number underneath for each stat I want?? that's the part I'm getting suck on. I took a quick look at the report function and can't find where it will do a count?

[branbran]

ok I just tried to create a query using the query design, from my table named "table". I chose the "status" field. In the Criteria I entered "In Progress" and in the Total drop-down I chose "Count". I was hoping for the report to show the number of records at status "In Progress" but I get a message saying there's a data type mismatch in the criteria expression. Is there a way to modify this to get the result I am looking for?

[orange]

Excel/spreadsheet is built on an entirely different object model than Access/database.
Your excel formulas will be of little use---if you understand the arithmetic of the formula and can translate that to vba or a query, great.

Database is about table design and Normalization. It is rare to have only 1 Table in database applications.

If you have
-a set of requirements (what statistics/reports you must create)
-a current database

Please zip these and attach to your next post.

Good luck

[branbran]

Thank you for your reply. I do realize Excel and Access are completely different platforms. I'm used to using SQL with oracle and grabbing the data directly. In my work environment I'm not able to attach the access database to an SQL server, too bad for me as it would be much simpler. As such I am just having trouble learning how to use the query tools. I am also not sure if access will allow me to create a query that just shows statistics. I thought it would, but perhaps I am wrong. Unfortunately I can't zip and attach my database as there is protected information, wish I could, but I do know I need about 7 statistics. If I could use SQL to write the query it would look like this: select primary_status from main_table where primary_status =-"In progress". The next one would be: select category from main_table where category = "consigner". If I use the query design, what do I put in the query design fields to accomplish this? How do I then create a report that just shows the count or number of rows returned from these queries?

[orange]

What tool(s) are you using to go from your PC to Oracle?

You might try a query along these lines. (untested)
Select primary_status, Count(primary_status) from main_table
Group By Primary_Status
Order by Count(Primary_status) Desc.

[RuralGuy]

It is also *very* useful to avoid reserved words in naming your objects: http://www.allenbrowne.com/AppIssueBadWord.html

[branbran]

I'm not using any tools or oracle right now. I used to use them on a different project a few years ago. I just know that sql is easier for me. So I now need build a query in Access: So when you say try this Query, do you mean use the query design in access?

You might try a query along these lines. (untested)
Select primary_status, Count(primary_status) from main_table
Group By Primary_Status
Order by Count(Primary_status) Desc.

If so, I tried filling in these fields in query design:

Field: Primary Status
Table:Input Table - final
Total: Count
Sort:
Show:
Criteria: "In progress"

I get a data mismatch error, what am I doing wrong?