[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 402
  • Last Modified:

creating query with count and sum-

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
davetough
Asked:
davetough
1 Solution
 
Rey Obrero (Capricorn1)Commented:
<( 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
 
davetoughAuthor Commented:
thanks
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now