Using SQL to create a mathematical expression using < and =

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

Guest

I want to use SQL to create an expression where Current date - Expiration
Date = DateDiff
and DateDiff < and = 90 days
When Datediff is < and = 90 days, pull the records that validates this
expression. The records will have these fields: ID, Company, Full Name,
E-mail Address, Company Address, Course Title, Expiration Date, Current Date,
DateDiff
Also, when I pull the records that meet the < and = 90 days criteria, I
want to separate the records by Course Title. Where the course titles
correspond to: Asbestos Contractor / Supervisor Initial, Asbestos Contractor
/ Supervisor Refresher, Asbestos Project Designer Initial, Lead Project
Designer Initial, Lead Supervisor, etc. When I have the records separted, I
will connect the various tables or databases to Outlook and send an email to
the company reminding them about the expiration date for their employee.
Also, I will connect the databases to the fax machine and send the company a
fax as a reminder. Right now, I have mastered the Outlook part, but I have
not figured out how to create the SQL language for the expression and
separating out the records by course title. Can you help me with this
project?
 
The easiest way to write SQL is to create the query which you want, then
View>SQL View. Copy/paste the SQL into your code.

James
 
Dear Mr. Deckert,
I need help to write this mathematical expression with the rest of the SQL
code to complete this project. I am learning on the job to use access.
Nobody in the company knows how to use access. Your assistance will be
greatly appreciated.
 
Here is the code that I have to create the Query: EG Query 1 from EG Table 1:

SELECT [EG Table 1].[ID], [EG Table 1].[Company], [EG Table 1].[Full Name],
[EG Table 1].[E-mail Address], [EG Table 1].[Company Address], [EG Table
1].[Course Title], [EG Table 1].[Expiration Date], [EG Table 1].[Current
Date], [EG Table 1].[DateDiff]
FROM [EG Table 1];
 
Add the following to your query
SELECT Date()-[EG Table 1].[Expiration Date] AS DateDiff
FROM [EG Table 1]
WHERE [EG Table 1].[Expiration Date]<=90;
I'm not sure if this means to sort the records by 'Course Title' or to only
show each 'Course Title' one at a time.
If one at a time you'll need to put 'Asbestos Contractor / Supervisor
Initial' etc in to the criteria field. If you want them sorted then you would
sort by the 'course title' field.

Jeff said:
Here is the code that I have to create the Query: EG Query 1 from EG Table 1:

SELECT [EG Table 1].[ID], [EG Table 1].[Company], [EG Table 1].[Full Name],
[EG Table 1].[E-mail Address], [EG Table 1].[Company Address], [EG Table
1].[Course Title], [EG Table 1].[Expiration Date], [EG Table 1].[Current
Date], [EG Table 1].[DateDiff]
FROM [EG Table 1];










--
Jeffery S. Pittman


JamesDeckert said:
The easiest way to write SQL is to create the query which you want, then
View>SQL View. Copy/paste the SQL into your code.

James
Click to expand...
Click to expand...
 
Dear Mr. Deckert:
Thank you for your assistance. I will use these expressions and see what
I get. This has been a learning experience for me with on the job training
with Microsoft websites and discussion groups to assist since nobody in the
company knows SQL.

Thanks,

Jeff Pittman
--
Jeffery S. Pittman


JamesDeckert said:
Add the following to your query
SELECT Date()-[EG Table 1].[Expiration Date] AS DateDiff
FROM [EG Table 1]
WHERE [EG Table 1].[Expiration Date]<=90;
I'm not sure if this means to sort the records by 'Course Title' or to only
show each 'Course Title' one at a time.
If one at a time you'll need to put 'Asbestos Contractor / Supervisor
Initial' etc in to the criteria field. If you want them sorted then you would
sort by the 'course title' field.

Jeff said:
Here is the code that I have to create the Query: EG Query 1 from EG Table 1:

SELECT [EG Table 1].[ID], [EG Table 1].[Company], [EG Table 1].[Full Name],
[EG Table 1].[E-mail Address], [EG Table 1].[Company Address], [EG Table
1].[Course Title], [EG Table 1].[Expiration Date], [EG Table 1].[Current
Date], [EG Table 1].[DateDiff]
FROM [EG Table 1];










--
Jeffery S. Pittman


JamesDeckert said:
The easiest way to write SQL is to create the query which you want, then
View>SQL View. Copy/paste the SQL into your code.

James

:

I want to use SQL to create an expression where Current date - Expiration
Date = DateDiff
and DateDiff < and = 90 days
When Datediff is < and = 90 days, pull the records that validates this
expression. The records will have these fields: ID, Company, Full Name,
E-mail Address, Company Address, Course Title, Expiration Date, Current Date,
DateDiff
Also, when I pull the records that meet the < and = 90 days criteria, I
want to separate the records by Course Title. Where the course titles
correspond to: Asbestos Contractor / Supervisor Initial, Asbestos Contractor
/ Supervisor Refresher, Asbestos Project Designer Initial, Lead Project
Designer Initial, Lead Supervisor, etc. When I have the records separted, I
will connect the various tables or databases to Outlook and send an email to
the company reminding them about the expiration date for their employee.
Also, I will connect the databases to the fax machine and send the company a
fax as a reminder. Right now, I have mastered the Outlook part, but I have
not figured out how to create the SQL language for the expression and
separating out the records by course title. Can you help me with this
project?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top