Count records from various queries to create PieChart (1 Viewer)

[widemonk]

I am eventually wanting to create a chart on a report. Im guessing that the points on the chart are determined by the numbers returned in a query?

The Table fields include...
• Enquiry Date
• Quotation Date

I need the query first of all to do a simple sum (quotation minus enquiry) to return the turnaround time. Then I want it to count how many records return 0, how many return 1 and so on.

I also need scope in this query to filter by customer (so that from a form, I can select results only for a specific customer, or choose all customers)

My end goal is to replicate this chart. I have it working perfectly in Excel, because I can write IF functions in hidden columns etc, but im trying to convince everyone that an Access-based quotes system is sooo much better, but of course I need to extract the stats too.

Cheers,

 

[Guus2005]

What you are asking is very specific and there is no simple answer to your question.
You have to provide more and detailed information about the actual queries and tables.
Better yet a sample database.

But to give you an answer on the convincing part: Access is better equipped than Excel to work with large quantities of data. Using the structured query language (sql) results are much easier to describe. Where Excel can store 64000 records on a single sheet (depending on which version you use), Access can store millions in a single table.

HTH

 

[widemonk]

Thanks for your reply Guus.

Sample db/table in image...

List of customer quotations which, among other info I've omitted, includes both a received date (enquiry date) and a date when we responded (quote date).

There is currently no existing field to simply return the difference between these two dates as I was hoping this could be determined programatically, but if I need to add it, I will.

I have colour-coded the 'customer' field to represent the segments of the Pie Chart...
4 quotes returned same day
3 returned after 1 day
2 returned after 2 days
1 returned after 3 days

Now all i need is to get Access to generate a pie-chart similar to as shown but im really struggling with it.

Thanks.

 

[Guus2005]

You can create the piechart based on a query.
In this query you can determine the elapsed time between the two dates.

select quoteid,  datediff("d";[enquirydate];[quotedate]) as days

You could use the build in wizard to create the piechart.

Enjoy!