queries to summarise data

[michellerobinso]

ok heres the thing, im not brilliant at dababses but ive created one for my work. i want to create a query to summarise the information

the database is to log customer queries and there is a field for the department responsible e.g warehouse, sales etc. this is a list box by the way

i want to summaries the date for example

between 30/5/06 to 14/6/06

warehouse had 12 queries that were responsible for them.

i can so the date part of it but i dont know how to do that.

any help will be much appriciated!

 

[CraigDolphin]

Being an amateur myself, here's one suggestion. Undoubtedly there are better methods around.

Assuming each customer inquiry has a unique CustomerQueryID, you could try this with two queries.

The first of these queries would have the fields CustomerQueryID, Department, and Date. You would use a criteria of 'Between #date1# AND #date2#' in the date field.

Then run another query based on the results of the first query. Here your fields would be Grouped by Department, and Count of CustomerQueryID.

I have attached an example db which uses dates taken from text boxes on a form to set the date criteria.

Depending on what you are actually trying to achieve, you might need to investigate crosstab queries to generate tables of inquiries by department for more than one time interval.

 

[EMP]

CraigDolphin,

You can directly put the selection criteria in a Totals query.

SELECT ResponsibleDepartment, Count(CustomerQueryID) AS CountOfCustomerQueryID
FROM [tblCust-Inquiries]
WHERE InquiryDate Between [Forms]![fmDatePicker]![txtStartDate] And [Forms]![fmDatePicker]![txtEndDate]
GROUP BY ResponsibleDepartment;


EMP
^

 

[CraigDolphin]

EMP,
I did mention that I'm an amateur! Thanks for the tip!

 

[michellerobinso]

thanks for the info its much apprieciated!

my only problem is that i am struggling with the actual department.

the department isnt just a txt box it is from a drop down menu.

 

[workmad3]

using a combobox isn't that much different from a text box for grabbing the value:

SELECT Count(CustomerQueryID) AS CountOfCustomerQueryID
FROM [tblCust-Inquiries]
WHERE InquiryDate Between [Forms]![fmDatePicker]![txtStartDate] And [Forms]![fmDatePicker]![txtEndDate] AND Responsible Department = [Forms]![fmDatePicker]![cmbDepartment];

Notice that as this will only find one department, there is no longer and need to show department or have the group by in the query

 

[michellerobinso]

please forgive me for sound so thick.

does the code you provided so kindly go into the expression builder in the query?

 

[michellerobinso]

please forgive me for sound so thick.

does the code you provided so kindly go into the expression builder in the query?

 

[michellerobinso]

please forgive me for sound so thick.

does the code you provided so kindly go into the expression builder in the query?

 

[CraigDolphin]

No. It is SQL code. Open your query in design view. Go to the View menu and select SQL view. Copy and paste the SQL code from here and replace the old sql of your old query with that.