Solved

creating query with count and sum-

Posted on 2013-02-01
337 Views
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
Question by:davetough

LVL 119

Accepted Solution

Rey Obrero 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

ID: 38845098
thanks
0

Featured Post

Suggested Solutions

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…