J
Jamie Pittman via AccessMonster.com
How would I make a form to control the the union query at the bottom. I
need to make a report that I can have select criteria. (Date range) that
part works. But I need to be able to select certin [Customer Names], or
all and [Whse Names] or all and variations between the two. I have tried
to put the like commands in the query but I failed, so I removed them. I
am still rather new and don't have alot of experience with Access. Any
help would be greatly appreciated...
SELECT [Employee Production Table].[Employee Name], [Employee Production
Table].Date, [Employee Production Table].TimeIn, [Employee Production Table]
..TimeOut, Format(DateDiff("n",[TimeIn],[TimeOut])\60,"0\:") & Format
(DateDiff("n",[TimeIn],[TimeOut]) Mod 60,"00") AS [Regular Total Time],
[Employee Production Table].Description, [Employee Production Table].
[Customer Name], [Employee Production Table].[Whse Name], [Allen Employee
Listing].[Regular Cost Per Hr], ([Regular Cost Per Hr]*DateDiff("n",[TimeIn]
,[TimeOut])/60) AS [Total Regular Cost], DateDiff("n",[TimeIn],[TimeOut])
/60 AS [Total Regular Hours]
FROM [Allen Employee Listing] INNER JOIN [Employee Production Table] ON
[Allen Employee Listing].[EMPLOYEE NAME] = [Employee Production Table].
[Employee Name]
WHERE ((([Employee Production Table].Date) Between [Start Date] And [End
Date])) UNION ALL SELECT [Employee Production Overtime].[Employee Name],
[Employee Production Overtime].Date, [Employee Production Overtime].[OT
TimeIn], [Employee Production Overtime].[OT TimeOut], Format(DateDiff("n",
[OT TimeIn],[OT TimeOut])\60,"0\:") & Format(DateDiff("n",[OT TimeIn],[OT
TimeOut]) Mod 60,"00") AS [OT Total Time], [Employee Production Overtime]
..Description, [Employee Production Overtime].[Customer Name], [Employee
Production Overtime].[Whse Name], [Allen Employee Listing].[Overtime Cost
Per Hr], ([Overtime Cost Per Hr]*DateDiff("n",[OT TimeIn],[OT TimeOut])/60)
AS [Total Overtime Cost], DateDiff("n",[OT TimeIn],[OT TimeOut])/60 AS
[Total Overtime Hours]
FROM [Employee Production Overtime] INNER JOIN [Allen Employee Listing] ON
[Employee Production Overtime].[Employee Name] = [Allen Employee Listing].
[EMPLOYEE NAME]
WHERE ((([Employee Production Overtime].Date) Between [Start Date] And [End
Date]));
need to make a report that I can have select criteria. (Date range) that
part works. But I need to be able to select certin [Customer Names], or
all and [Whse Names] or all and variations between the two. I have tried
to put the like commands in the query but I failed, so I removed them. I
am still rather new and don't have alot of experience with Access. Any
help would be greatly appreciated...
SELECT [Employee Production Table].[Employee Name], [Employee Production
Table].Date, [Employee Production Table].TimeIn, [Employee Production Table]
..TimeOut, Format(DateDiff("n",[TimeIn],[TimeOut])\60,"0\:") & Format
(DateDiff("n",[TimeIn],[TimeOut]) Mod 60,"00") AS [Regular Total Time],
[Employee Production Table].Description, [Employee Production Table].
[Customer Name], [Employee Production Table].[Whse Name], [Allen Employee
Listing].[Regular Cost Per Hr], ([Regular Cost Per Hr]*DateDiff("n",[TimeIn]
,[TimeOut])/60) AS [Total Regular Cost], DateDiff("n",[TimeIn],[TimeOut])
/60 AS [Total Regular Hours]
FROM [Allen Employee Listing] INNER JOIN [Employee Production Table] ON
[Allen Employee Listing].[EMPLOYEE NAME] = [Employee Production Table].
[Employee Name]
WHERE ((([Employee Production Table].Date) Between [Start Date] And [End
Date])) UNION ALL SELECT [Employee Production Overtime].[Employee Name],
[Employee Production Overtime].Date, [Employee Production Overtime].[OT
TimeIn], [Employee Production Overtime].[OT TimeOut], Format(DateDiff("n",
[OT TimeIn],[OT TimeOut])\60,"0\:") & Format(DateDiff("n",[OT TimeIn],[OT
TimeOut]) Mod 60,"00") AS [OT Total Time], [Employee Production Overtime]
..Description, [Employee Production Overtime].[Customer Name], [Employee
Production Overtime].[Whse Name], [Allen Employee Listing].[Overtime Cost
Per Hr], ([Overtime Cost Per Hr]*DateDiff("n",[OT TimeIn],[OT TimeOut])/60)
AS [Total Overtime Cost], DateDiff("n",[OT TimeIn],[OT TimeOut])/60 AS
[Total Overtime Hours]
FROM [Employee Production Overtime] INNER JOIN [Allen Employee Listing] ON
[Employee Production Overtime].[Employee Name] = [Allen Employee Listing].
[EMPLOYEE NAME]
WHERE ((([Employee Production Overtime].Date) Between [Start Date] And [End
Date]));