Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 03-09-2004, 03:08 AM   #1
Palsam
Guest
 
Posts: n/a
Crosstable query returns too many records

I'm writing a work schedule application in ASP, and I am using an Access 2000 DB with the following tables:

Code:
WorkSchedule
--------------------------
| ID | Date | EmployeeID |
|  .
|  .
|  .

Employees
---------------------
| EmployeeID | Name |
|  .
|  .
|  .

Status
----------------------------------------------------
| StatusID | DateFrom | DateTo | EmployeeID | Type |
|  .
|  .
|  .
I need to retrieve a list of employees, which are not scheduled for work between the given dates
and which have no status (sick leave, maternity leave etc). The object is to see which employees are available for scheduling on the different dates.

The resulting table/recordset should contain one record per date per available employee (see example below).

Code:
AvailableEmployees
-------------------------
| Date     | EmployeeID |
-------------------------
| 01/01/01 |    2       |
| 01/01/01 |    5       |
| 01/01/01 |    7       |
| 03/01/01 |    2       |
|  .
|  .
We first disregard the statuses. The query below returns a partially correct result. On the dates where only one employee is scheduled, only the
remaining employees are returned (correct). However, on the dates where 2 or more are scheduled, ALL employees are returned (wrong).
Code:
StartDate = "01/01/04" '(mm/dd//yy)
StopDate = "02/30/04"

Query = ""
Query = Query + "SELECT WorkSchedule.Date, Employees.EmployeeID "
Query = Query + "FROM WorkSchedule, Employees "
Query = Query + "WHERE WorkSchedule.Date BETWEEN #StartDate# AND #StopDate# "
Query = Query +    "AND Employees.EmployeeID <> WorkSchedule.EmployeeID "
Query = Query + "GROUP BY WorkSchedule.Date, Employees.EmployeeID"
Including the status condition (see query below) has no affect on the result (I assume the condition is incorrect)
Code:
Query = ""
Query = Query + "SELECT WorkSchedule.Date, Employees.EmployeeID "
Query = Query + "FROM WorkSchedule, Employees "
Query = Query + "WHERE WorkSchedule.Date BETWEEN #StartDate# AND #StopDate# "
Query = Query +    "AND NOT EXISTS (SELECT * FROM Status "
Query = Query +       "WHERE Status.EmployeeID = WorkSchedule.EmployeeID "
Query = Query +       "AND (Status.DateFrom <= WorkSchedule.Date AND Status.DateFrom >= WorkSchedule.Date)) "
Query = Query +    "AND Employees.EmployeeID <> WorkSchedule.EmployeeID "
Query = Query + "GROUP BY WorkSchedule.Date, Employees.EmployeeID"
I guess I am overlooking something trivial, but I can't seem to get the query right. Suggestions, please?

Palsam

  Reply With Quote
Old 03-09-2004, 07:59 AM   #2
doran_doran
Registered User
 
Join Date: Aug 2002
Location: Houston, TX, USA
Posts: 349
Thanks: 0
Thanked 0 Times in 0 Posts
doran_doran is on a distinguished road
more info...

http://www.google.com/search?hl=en&i...=Google+Search

Good Luck
Dianna Goldsberg
doran_doran is offline   Reply With Quote
Old 03-09-2004, 11:41 AM   #3
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,109
Thanks: 13
Thanked 1,346 Times in 1,282 Posts
Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light
There are several problems with the queries. Two are:
1. The joins are not specified. Change them to inner joins. Your current query is returning a cartesian product - all rows from tblA joined to all rows from tblB - This will certainly return more rows than you expect.
2. At least one of your conditions is invalid -
Status.DateFrom <= WorkSchedule.Date AND Status.DateFrom >= WorkSchedule.Date

Status.DateFrom CANNOT be both <= WorkSchedule.Date AND >= WorkSchedule.Date at the same time unless it is = WorkSchedule.Date

A further problem is with a column name. Date is the name of a function and should NEVER be used as the name of a column. It will cause problems because of the confusion it can cause. You can minimize the potential for damage by ALWAYS delimiting it with square brackets - [Date]

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump




All times are GMT -8. The time now is 12:51 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World