Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

creating query with count and sum-

Posted on 2013-02-01
2
Medium Priority
?
393 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
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…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

636 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