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
Solved

Date in a Query

Posted on 2012-03-16
4
217 Views
Last Modified: 2012-03-16
Dear Experts,

I need to be able to create a field in a query which returns the first and last dates in a range ([DateAndTime]).  So something like "'First date' to 'Last date'"

This needs to be added into a query which then forms the dataset for a count query (the count is based on [DateAndTime]). I have tried playing around with the Expression Builder, but I’m not getting very far
The query I have is below ... Can anybody help?

SELECT FFUsers_Contact_Information.DateAndTime, FFUsers_ConsultantDefs.Description, FFUsers_ConsultantDefs.entry AS FFID
FROM FFUsers_UsersPreferences INNER JOIN (FFUsers_Contact_Information INNER JOIN FFUsers_ConsultantDefs ON FFUsers_Contact_Information.createby = FFUsers_ConsultantDefs.entry) ON FFUsers_UsersPreferences.user_log_in = FFUsers_ConsultantDefs.entry
GROUP BY FFUsers_Contact_Information.DateAndTime, FFUsers_ConsultantDefs.Description, FFUsers_ConsultantDefs.entry, FFUsers_UsersPreferences.is_current
HAVING (((FFUsers_UsersPreferences.is_current)=1)); 

Open in new window


Many thanks
0
Comment
Question by:correlate
  • 2
4 Comments
 
LVL 40

Expert Comment

by:als315
ID: 37728939
Add this field (DateAndTime) twice and select min and max in group functions
0
 
LVL 77

Accepted Solution

by:
peter57r earned 500 total points
ID: 37728950
I'm not sure I understand what you are asking for.

Are you saying you want to know the values of the oldest and newest datetimes in the field DateAndTime?  

If so then it seems that you need a separate query..

Select min(Dateandtime) as FirstDate, max(dateandtime) as LastDate
from FFUsers_Contact_Information
0
 
LVL 77

Expert Comment

by:peter57r
ID: 37728954
als315
... the query is grouping by DateAndTime so the max and min values will just be the same as the grouping value.
0
 

Author Closing Comment

by:correlate
ID: 37728997
Brilliant - thank you for that, does the trick perfectly
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
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…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

808 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