Make a form to control the query to produce a report.

[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]));

 

[Rob Oldfield]

I'm not sure if I'm answering the right question here, but I've always found
it easiest in that kind of situation to create the union query with no
criteria specified, and then have another query based on the union query
that I use to apply my criteria.

(...the bit that I'm not sure of is whether you're talking about applying
your criteria at all, or just how to do it on a union query. The above
basically assumes the second case. Let me know if I have that wrong.)

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]));

 

[Jamie Pittman via AccessMonster.com]

The end result is that I need to pull out the information from the data
base that has select methods on them. I want to be able to pull the
information without typing the names of the customers and warhouses. I
would like to be able to use a form that I can have three different select
critera from a drop down list. 1. Being to specify a date range. 2.
Choose a customer (or select all in cases) 3. Choose a warehouse (or
select all in cases). I am having alot of trouble doing that. If need I
can establish another query based on the union query. I appreciate any
help that you maybe able to offer. I am still trying to learn the way
Access functions. I have seen alot of sample database and alot of them use
forms from the switchboards to handle these select criteras and end with a
report

 

[Rob Oldfield]

Sorry to not come up with an immediate answer, but I need to know how
detailed to make the answer.

Have you got any of it working as yet? Just the warehouse/customer? Adding
the "All" option? Getting the date range to work?

(It will probably help if you also post the basic SQL of the query - with
hardcoded customer/warehouse/dates included - just so I can try to match
what you're already working with.)