Solved

creating query with count and sum-

Posted on 2013-02-01
2
348 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 119

Accepted Solution

by:
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

by:davetough
ID: 38845098
thanks
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

919 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now