Solved

creating query with count and sum-

Posted on 2013-02-01
2
356 Views
Last Modified: 2013-02-01
hello,
attached db
open and run Query1
enter code:101
enter name:jim

The query displays 1 record with count and total

question:
Is there a way to have Query2 display the same result?
( with a date field?)-

enter same code [101]  name [jim]
and from date [ 7/1/2012] to date [10/1 2012]  

thank you
DatabasePending.accdb
0
Comment
Question by:davetough
2 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 38845075
<( with a date field?)->

what date do you want to show?

this will show the Max date

SELECT tbl1.[Item Code], tbl1.TName, Count(tbl1.TName) AS TNameCount, Sum(tbl1.[Time Spent]) AS [SumOfTime Spent], Max(tbl1.TDate) AS MaxOfTDate
FROM tbl1
GROUP BY tbl1.[Item Code], tbl1.TName
HAVING (((tbl1.[Item Code])=[Enter Item Code]) AND ((tbl1.TName)=[Enter Name]) AND ((Max(tbl1.TDate)) Between [ENTER START DATE] And [ENTER END DATE]))
ORDER BY tbl1.[Item Code];


this will show the Min date

SELECT tbl1.[Item Code], tbl1.TName, Count(tbl1.TName) AS TNameCount, Sum(tbl1.[Time Spent]) AS [SumOfTime Spent], Min(tbl1.TDate) AS MinOfTDate
FROM tbl1
GROUP BY tbl1.[Item Code], tbl1.TName
HAVING (((tbl1.[Item Code])=[Enter Item Code]) AND ((tbl1.TName)=[Enter Name]) AND ((Min(tbl1.TDate)) Between [ENTER START DATE] And [ENTER END DATE]))
ORDER BY tbl1.[Item Code];
0
 

Author Closing Comment

by:davetough
ID: 38845098
thanks
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question