Trouble with a selective query

[Nick Lingenfelter]

My main table includes all sorts of pertinent information on about 520 employees, while another table contains start dates, end dates, and the reason for an employee's expected absence. I am trying to run a query that returns personnel in a single work group (1 thru 8, entered as prompted when the query is run). When I set this up, it works swimmingly.

When I try to add the Reason to my query, it returns only those entries which have an expected absence entered.

I haven't even begun to try requesting this information for a specific date yet, as I anticipate that too will be a nightmare.

Essentially, I would like to run a report that shows everyone in a work group on a given date and indicates whether or not they are available for work.

Any help is greatly appreciated.

[June7]

Show the attempted query SQL statement for analysis.

Also helpful would be an example of source data.

[RayMilhon]

I'd be willing to bet you're using an inner join between the tables instead of an outer join. However, as June7 stated without seeing your SQL it's impossible to be definitive and correct.

[Nick Lingenfelter]

Show the attempted query SQL statement for analysis.

Also helpful would be an example of source data.

I'm not sure what that is, so here is the whole database:

Duty Section.zip

[RayMilhon]

As I expected Right click on the link between Duty Section and Excused Select Join properties and change the link between those 2 tables to a left outer join where all records from Duty Section and those matching records in Excused.

[Nick Lingenfelter]

As I expected Right click on the link between Duty Section and Excused Select Join properties and change the link between those 2 tables to a left outer join where all records from Duty Section and those matching records in Excused.

Thank you! That was it.

[Nick Lingenfelter]

Okay, now that I have that done, I can't seem to limit my query to a specific date. I'd like to be able to enter a date, and have the query return a "reason" only if there is an entry on the excused table for a period inclusive of that date. Ideally, if the query does return a reason I'd like the "Muster" box to be checked.

[June7]

Try:

criteria under [Begin Date]: <=[Enter date]

criteria under [End Date]: >=[Enter date]

[RayMilhon]

Nick, I want to make sure I understand what you're needing here. You are trying to run a query that shows all personnel available to work for a specific Date Range. If that is correct then what you want under the criteria for the 2 date fields is as follows

Here's the criteria. ([Begin Date] is Null) or ([enter Date] Not between [begin Date] and [end date])

In the critera section of the query builder under [Begin Date] put is null. Add another field to the query called [end date] in the criteria section under that field on a different row than the is null put "Not between [begin date] and [end date]"

That should do it.