Date Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

In my db I have a field called Date Entered Training. I want to query all
records that show everyone who has been in training for more than 12 months.
Help?
 
In my db I have a field called Date Entered Training. I want to query all
records that show everyone who has been in training for more than 12 months.
Help?
Click to expand...

You can use a Totals query to get this:

SELECT <other fields>, DateDiff("m", Min([Date Entered Training]),
Max([Date Entered Training])) AS MonthsInTraining
WHERE MonthsInTraining > 12;

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
I apologize for my lack of code knowledge, but I wasn't able to make the
string given below to work. What is a Totals Query? Sorry...

John Vinson said:
In my db I have a field called Date Entered Training. I want to query all
records that show everyone who has been in training for more than 12 months.
Help?
Click to expand...

You can use a Totals query to get this:

SELECT <other fields>, DateDiff("m", Min([Date Entered Training]),
Max([Date Entered Training])) AS MonthsInTraining
WHERE MonthsInTraining > 12;

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
Click to expand...
 
I apologize for my lack of code knowledge, but I wasn't able to make the
string given below to work.
Click to expand...

What I posted is not "code" (a term usually used for VBA programming);
it's a hint at the SQL of a Query. You can create a Query using the
query grid and use the SQL choice on the leftmost toolbar icon (or
View... SQL on the Menu) to see the SQL view of a query.

Since I do not know anything about your table names, field names other
than the one, table structure, or what you want to see in the query, I
posted a hint, not a query which will give you the answers that you
want. My idea was that you would adapt this query to your needs. If
you wish to post back with some more information, I can give you a
more detailed query.
What is a Totals Query? Sorry...
Click to expand...

Press F1 in Access. Put Totals in the Search term. Take a look at the
20 or so hits.

Or, create a Query based on your table; press the Greek Sigma icon (it
looks like a sideways M or W). You'll see a new row in the query grid
labeled "Totals". This defaults to Group By, but can be changed to
Count, Sum, Average, and so on. This type of query lets you count,
sum, etc. records in groups.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
John,

My Fields are; Last Name, Department, Supervisor, and Date Entered Training.
Would the string you provide me be entered in one of these fields? And/or in
the Total row that you explained below?

Thanks for your help and patience.
 
John,

My Fields are; Last Name, Department, Supervisor, and Date Entered Training.
Would the string you provide me be entered in one of these fields? And/or in
the Total row that you explained below?

Thanks for your help and patience.
Click to expand...

Sorry! I must have been really sleepy when I posted that SQL. It's
wrong on several counts.

SELECT <other fields>, DateDiff("m", Min([Date Entered Training]),
Max([Date Entered Training])) AS MonthsInTraining
WHERE MonthsInTraining > 12;

Create a new Query based on your table. Change it to a Totals query by
clicking the Greek Sigma icon. Select the name fields that you want to
see (any or all of the three), but don't select the [Date Entered
Training] field. In a vacant Field cell type

MonthsEnteredTraining: DateDiff("m", Min([Date Entered Training]),
Max([Date Entered Training]))

(all on one line, Agent is word-wrapping the message). On the Criteria
line under this field put
12
Click to expand...

to select only those employees who have been in training for over 12
months.

Queries are usually built in the query grid, but that's actually just
a user-friendly (more or less) tool for constructing SQL statements.
It's very hard to post a query grid readably in a message; but the
apparently cryptic text you get when you use View... SQL or click the
SQL icon on the leftmost dropdown in the query design toolbar is the
actual definition of the Query, and is the preferred manner of viewing
queries on the newsgroups.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Ok, I know I'm getting a little closer.

I figured out how Totals work and selecting field names. But..

Dumb question: What do you mean by 'vacant Field cell'? Does that mean a
vacant cell under a field like 'Last Name', below the "Criteria: and Or:"
lines? Or do you mean in a Field that is open and has no label? I'm a
little confused about where exactly to enter this string since >12 is put in
a Criteria line.

Thanks again.
John Vinson said:
John,

My Fields are; Last Name, Department, Supervisor, and Date Entered Training.
Would the string you provide me be entered in one of these fields? And/or in
the Total row that you explained below?

Thanks for your help and patience.
Click to expand...

Sorry! I must have been really sleepy when I posted that SQL. It's
wrong on several counts.

SELECT <other fields>, DateDiff("m", Min([Date Entered Training]),
Max([Date Entered Training])) AS MonthsInTraining
WHERE MonthsInTraining > 12;

Create a new Query based on your table. Change it to a Totals query by
clicking the Greek Sigma icon. Select the name fields that you want to
see (any or all of the three), but don't select the [Date Entered
Training] field. In a vacant Field cell type

MonthsEnteredTraining: DateDiff("m", Min([Date Entered Training]),
Max([Date Entered Training]))

(all on one line, Agent is word-wrapping the message). On the Criteria
line under this field put
12
Click to expand...

to select only those employees who have been in training for over 12
months.

Queries are usually built in the query grid, but that's actually just
a user-friendly (more or less) tool for constructing SQL statements.
It's very hard to post a query grid readably in a message; but the
apparently cryptic text you get when you use View... SQL or click the
SQL icon on the leftmost dropdown in the query design toolbar is the
actual definition of the Query, and is the preferred manner of viewing
queries on the newsgroups.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
Click to expand...
 
Ok, I know I'm getting a little closer.

I figured out how Totals work and selecting field names. But..

Dumb question: What do you mean by 'vacant Field cell'? Does that mean a
vacant cell under a field like 'Last Name', below the "Criteria: and Or:"
lines? Or do you mean in a Field that is open and has no label? I'm a
little confused about where exactly to enter this string since >12 is put in
a Criteria line.
Click to expand...

A field that is open and has no label. Put the expression in the Field
cell, in the same row as Last Name; and put the criterion on the
Criteria line in the same column.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Ok, I think I'm almost there. When I view the query I see two fields, 'Last
Name' and 'MonthsEnteredTraining' but no records are being displayed.

Here's what I've done:

For Totals, under Last Name, I selected Goup By, under Date Entered
Training, I selected Group By, under the Field that has the DateDiff string I
selected Expression which is same under MonthsEnteredTraining field. I also
entered >12 in criteria under the DateDiff sting Field column.

Only field that is checked to be shown is Last Name. Date Entered Training
Field is not checked. Should any other field be checked, ie.,
MonthsEnteredTraining or DateDiff string?

What have I missed?

Thanks again in advance..
 
Ok, I think I'm almost there. When I view the query I see two fields, 'Last
Name' and 'MonthsEnteredTraining' but no records are being displayed.

Here's what I've done:

For Totals, under Last Name, I selected Goup By, under Date Entered
Training, I selected Group By, under the Field that has the DateDiff string I
selected Expression which is same under MonthsEnteredTraining field. I also
entered >12 in criteria under the DateDiff sting Field column.

Only field that is checked to be shown is Last Name. Date Entered Training
Field is not checked. Should any other field be checked, ie.,
MonthsEnteredTraining or DateDiff string?

What have I missed?
Click to expand...

I'm not sure. Please open the query in SQL view and copy and paste the
SQL string to a message here. I'm not sure what you mean by "I
selected Expression which is the same..." but the SQL will make it
clear.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Here's the string:

DateDiff("m",Min([Date Entered Training]),Max([Date Entered Training]))

On the row listed as Total, under each column, you have various options to
select from, ie, Group By, Sum, Avg. etc...that is where I selected either
Group By or Expression.

Thanks again.
 
Here's the string:

DateDiff("m",Min([Date Entered Training]),Max([Date Entered Training]))
Click to expand...

Sorry, I wasn't clear enough.

Open the Query in design view. Click the dropdown on the leftmost icon
in the toolbar; one of the choices will be "SQL". Choose it.

A window will open with a bunch of text. Copy and paste that entire
text string to a message here. That is the entire Query and will let
me determine what's wrong. The DateDiff expression appears correct,
but I'm guessing that you have either it or the criterion in the wrong
place - the SQL view will tell me where!

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Here you go...thanks again...

SELECT [UGT 1].[Last Name], DateDiff("m",Min([Date Entered
Training]),Max([Date Entered Training])) AS MonthsEnteredTraining
FROM [UGT 1]
GROUP BY [UGT 1].[Last Name], [UGT 1].[Date Entered Training]
HAVING (((DateDiff("m",Min([Date Entered Training]),Max([Date Entered
Training])))>12))
ORDER BY [UGT 1].[Date Entered Training], DateDiff("m",Min([Date Entered
Training]),Max([Date Entered Training]));
 
Here you go...thanks again...

SELECT [UGT 1].[Last Name], DateDiff("m",Min([Date Entered
Training]),Max([Date Entered Training])) AS MonthsEnteredTraining
FROM [UGT 1]
GROUP BY [UGT 1].[Last Name], [UGT 1].[Date Entered Training]
HAVING (((DateDiff("m",Min([Date Entered Training]),Max([Date Entered
Training])))>12))
ORDER BY [UGT 1].[Date Entered Training], DateDiff("m",Min([Date Entered
Training]),Max([Date Entered Training]));


Larry said:
In my db I have a field called Date Entered Training. I want to query all
records that show everyone who has been in training for more than 12 months.
Help?
Click to expand...
Click to expand...

Looks OK. What I'm not certain is whether the Min and Max expressions
within the DateDiff will be correctly grouped. Try using the DMin()
and DMax() functions instead - they'll be slower but should work:

SELECT DISTINCT [UGT 1].[Last Name], DateDiff("m",DMin("[Date Entered
Training]", "[UGT 1]", "[Last Name] = '" & [Last Name] &
"'"),DMax("[Date Entered Training]", "[UGT 1]", "[Last Name] = '" &
[Last Name] & "'")) AS MonthsEnteredTraining
FROM [UGT 1]
WHERE DateDiff("m",DMin("[Date Entered
Training]", "[UGT 1]", "[Last Name] = '" & [Last Name] &
"'"),DMax("[Date Entered Training]", "[UGT 1]", "[Last Name] = '" &
[Last Name] & "'")) > 12
ORDER BY DateDiff("m",DMin("[Date Entered
Training]", "[UGT 1]", "[Last Name] = '" & [Last Name] &
"'"),DMax("[Date Entered Training]", "[UGT 1]", "[Last Name] = '" &
[Last Name] & "'"));



John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Back
Top