Restrict a Union Qry

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

Guest

I am trying to put a restriction on the dates I have in a Union qry. I only
want to return records that are => the date I have listed in a form
"FrmEmployeeStatsDate.CBOStartDate" However I am not sure how to add it to
the code. Any ideas for me. Thank you

Here is the Code
The date field is DateId

SELECT "IV" As Source,DateID,QI, IVAttempts, IVSuccess, 0 As ETATTEMPTS, 0
as ETSUCCESS, 0 As
[Total Calls], 0 as [ALS Calls], 0 as[NX Calls], 0 as Miles, 0
as Score, 0 AS [SUM OF EMPLOYEE] From [EMPLOYEE IV]
UNION SELECT "ETT" As Source, DateID, QI, 0 as IVAttempts,
0 as IVSuccess, ETATTEMPTS, ETSUCCESS, 0 As
[Total Calls], 0 as [ALS Calls], 0 as[NX Calls], 0 as
Miles, 0 as score, 0 AS [SUM OF EMPLOYEE] From [EMPLOYEE INTUBATION]
UNION SELECT "CALLS" As Source,DateID, QI, 0 as IVAttempts, 0
as IVSuccess, 0 As ETATTEMPTS, 0 as ETSUCCESS, [Total Calls], [ALS Calls],
[NX Calls], 0 as Miles, 0 as
score, 0 AS [SUM OF EMPLOYEE] From [EMPLOYEE TRIP COUNT]
UNION SELECT "DRIVING" As Source,DateID, QI, 0 as IVAttempts, 0
as IVSuccess, 0 As ETATTEMPTS, 0 as ETSUCCESS, 0 As
[Total Calls], 0 as [ALS Calls], 0 as[NX Calls], Miles, score, 0 AS [SUM OF
EMPLOYEE] From [qryROADSAFETY]
UNION SELECT "CALLOUTS" AS SOURCE,DateID, QI, 0 AS IVATTEMPTS, 0 AS
IVSUCCESS, 0 AS ETATTEMPTS, 0 AS ETSUCCESS, 0 AS [TOTAL CALLS], 0 AS [ALS
CALLS], 0 AS [NX CALLS], 0 AS MILES, 0 AS SCORE, [EMPLOYEE OUT] From
[qryCALLOUT];
 
Think of each SELECT statement as a separate query and it will be easy. your
WHERE clause should go at the end of each SELECT statement. You will need a
WHERE for each SELECT.
 
Thats seems to get the qry working correctly but somehow it is not picking up
the date from the field on my form. Is this right

Where(DateID>=[FrmEmployeestatsDate].[CboStartDate])

Thank you

Klatuu said:
Think of each SELECT statement as a separate query and it will be easy. your
WHERE clause should go at the end of each SELECT statement. You will need a
WHERE for each SELECT.

Jason said:
I am trying to put a restriction on the dates I have in a Union qry. I only
want to return records that are => the date I have listed in a form
"FrmEmployeeStatsDate.CBOStartDate" However I am not sure how to add it to
the code. Any ideas for me. Thank you

Here is the Code
The date field is DateId

SELECT "IV" As Source,DateID,QI, IVAttempts, IVSuccess, 0 As ETATTEMPTS, 0
as ETSUCCESS, 0 As
[Total Calls], 0 as [ALS Calls], 0 as[NX Calls], 0 as Miles, 0
as Score, 0 AS [SUM OF EMPLOYEE] From [EMPLOYEE IV]
UNION SELECT "ETT" As Source, DateID, QI, 0 as IVAttempts,
0 as IVSuccess, ETATTEMPTS, ETSUCCESS, 0 As
[Total Calls], 0 as [ALS Calls], 0 as[NX Calls], 0 as
Miles, 0 as score, 0 AS [SUM OF EMPLOYEE] From [EMPLOYEE INTUBATION]
UNION SELECT "CALLS" As Source,DateID, QI, 0 as IVAttempts, 0
as IVSuccess, 0 As ETATTEMPTS, 0 as ETSUCCESS, [Total Calls], [ALS Calls],
[NX Calls], 0 as Miles, 0 as
score, 0 AS [SUM OF EMPLOYEE] From [EMPLOYEE TRIP COUNT]
UNION SELECT "DRIVING" As Source,DateID, QI, 0 as IVAttempts, 0
as IVSuccess, 0 As ETATTEMPTS, 0 as ETSUCCESS, 0 As
[Total Calls], 0 as [ALS Calls], 0 as[NX Calls], Miles, score, 0 AS [SUM OF
EMPLOYEE] From [qryROADSAFETY]
UNION SELECT "CALLOUTS" AS SOURCE,DateID, QI, 0 AS IVATTEMPTS, 0 AS
IVSUCCESS, 0 AS ETATTEMPTS, 0 AS ETSUCCESS, 0 AS [TOTAL CALLS], 0 AS [ALS
CALLS], 0 AS [NX CALLS], 0 AS MILES, 0 AS SCORE, [EMPLOYEE OUT] From
[qryCALLOUT];
Click to expand...
Click to expand...
 
Thank you I got it now, just add "forms" to the begining
Jason said:
Thats seems to get the qry working correctly but somehow it is not picking up
the date from the field on my form. Is this right

Where(DateID>=[FrmEmployeestatsDate].[CboStartDate])

Thank you

Klatuu said:
Think of each SELECT statement as a separate query and it will be easy. your
WHERE clause should go at the end of each SELECT statement. You will need a
WHERE for each SELECT.

Jason said:
I am trying to put a restriction on the dates I have in a Union qry. I only
want to return records that are => the date I have listed in a form
"FrmEmployeeStatsDate.CBOStartDate" However I am not sure how to add it to
the code. Any ideas for me. Thank you

Here is the Code
The date field is DateId

SELECT "IV" As Source,DateID,QI, IVAttempts, IVSuccess, 0 As ETATTEMPTS, 0
as ETSUCCESS, 0 As
[Total Calls], 0 as [ALS Calls], 0 as[NX Calls], 0 as Miles, 0
as Score, 0 AS [SUM OF EMPLOYEE] From [EMPLOYEE IV]
UNION SELECT "ETT" As Source, DateID, QI, 0 as IVAttempts,
0 as IVSuccess, ETATTEMPTS, ETSUCCESS, 0 As
[Total Calls], 0 as [ALS Calls], 0 as[NX Calls], 0 as
Miles, 0 as score, 0 AS [SUM OF EMPLOYEE] From [EMPLOYEE INTUBATION]
UNION SELECT "CALLS" As Source,DateID, QI, 0 as IVAttempts, 0
as IVSuccess, 0 As ETATTEMPTS, 0 as ETSUCCESS, [Total Calls], [ALS Calls],
[NX Calls], 0 as Miles, 0 as
score, 0 AS [SUM OF EMPLOYEE] From [EMPLOYEE TRIP COUNT]
UNION SELECT "DRIVING" As Source,DateID, QI, 0 as IVAttempts, 0
as IVSuccess, 0 As ETATTEMPTS, 0 as ETSUCCESS, 0 As
[Total Calls], 0 as [ALS Calls], 0 as[NX Calls], Miles, score, 0 AS [SUM OF
EMPLOYEE] From [qryROADSAFETY]
UNION SELECT "CALLOUTS" AS SOURCE,DateID, QI, 0 AS IVATTEMPTS, 0 AS
IVSUCCESS, 0 AS ETATTEMPTS, 0 AS ETSUCCESS, 0 AS [TOTAL CALLS], 0 AS [ALS
CALLS], 0 AS [NX CALLS], 0 AS MILES, 0 AS SCORE, [EMPLOYEE OUT] From
[qryCALLOUT];
Click to expand...
Click to expand...
Click to expand...
 
Try:
Where(DateID>=[Forms]![FrmEmployeestatsDate].[CboStartDate])
or
Where(DateID>= #[Forms]![FrmEmployeestatsDate].[CboStartDate]#)

Jason said:
Thats seems to get the qry working correctly but somehow it is not picking up
the date from the field on my form. Is this right

Where(DateID>=[FrmEmployeestatsDate].[CboStartDate])

Thank you

Klatuu said:
Think of each SELECT statement as a separate query and it will be easy. your
WHERE clause should go at the end of each SELECT statement. You will need a
WHERE for each SELECT.

Jason said:
I am trying to put a restriction on the dates I have in a Union qry. I only
want to return records that are => the date I have listed in a form
"FrmEmployeeStatsDate.CBOStartDate" However I am not sure how to add it to
the code. Any ideas for me. Thank you

Here is the Code
The date field is DateId

SELECT "IV" As Source,DateID,QI, IVAttempts, IVSuccess, 0 As ETATTEMPTS, 0
as ETSUCCESS, 0 As
[Total Calls], 0 as [ALS Calls], 0 as[NX Calls], 0 as Miles, 0
as Score, 0 AS [SUM OF EMPLOYEE] From [EMPLOYEE IV]
UNION SELECT "ETT" As Source, DateID, QI, 0 as IVAttempts,
0 as IVSuccess, ETATTEMPTS, ETSUCCESS, 0 As
[Total Calls], 0 as [ALS Calls], 0 as[NX Calls], 0 as
Miles, 0 as score, 0 AS [SUM OF EMPLOYEE] From [EMPLOYEE INTUBATION]
UNION SELECT "CALLS" As Source,DateID, QI, 0 as IVAttempts, 0
as IVSuccess, 0 As ETATTEMPTS, 0 as ETSUCCESS, [Total Calls], [ALS Calls],
[NX Calls], 0 as Miles, 0 as
score, 0 AS [SUM OF EMPLOYEE] From [EMPLOYEE TRIP COUNT]
UNION SELECT "DRIVING" As Source,DateID, QI, 0 as IVAttempts, 0
as IVSuccess, 0 As ETATTEMPTS, 0 as ETSUCCESS, 0 As
[Total Calls], 0 as [ALS Calls], 0 as[NX Calls], Miles, score, 0 AS [SUM OF
EMPLOYEE] From [qryROADSAFETY]
UNION SELECT "CALLOUTS" AS SOURCE,DateID, QI, 0 AS IVATTEMPTS, 0 AS
IVSUCCESS, 0 AS ETATTEMPTS, 0 AS ETSUCCESS, 0 AS [TOTAL CALLS], 0 AS [ALS
CALLS], 0 AS [NX CALLS], 0 AS MILES, 0 AS SCORE, [EMPLOYEE OUT] From
[qryCALLOUT];
Click to expand...
Click to expand...
Click to expand...
 
Back
Top