Showing data from query and also showing data that does NOT match

[floyd33 via AccessMonster.com]

I have a report that is pulling data based on two parameter fields (Start
Date between [Enter First Date] and [Enter Second Date] and then [Enter
Employee Name or Press OK for All]. The end user wants to be able to just
enter dates and see all employees, the ones that match the date criteria
showing the dates that match and the ones that don't match showing just their
name--which will signify that they are available during this date frame. I
have no idea where to start. My brain can't wrap around this one. Please
help!

 

[Allen Browne]

Can I suggest an alternative approach?

Provide a little unbound form with these controls:
- text box for entering the starting date;
- text box for entering the ending date;
- combo for selecting an employee;
- command button for opening the report.

You leave all the criteria out of the query. Instead, you use the Click
event procedure of the command button to open the report and filter it to
the desired records. The code in the click event can just ignore any of the
controls that don't have a value. So if the combo is blank, the result is
that it shows all employees. If the end date is blank, it shows all records
from the starting date. If that's blank too, it shows all the records.

While that does require a little programming, it's a powerful interface -
certainly worth the effort to learn.

Here's a simple example, with just the date boxes:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html
It explains 2 methods: method 1 is what you are doing; method 2 is what I'm
suggesting.

Once you have that working, you can proceed to a more involved example, to
add the employee combo too. See:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
This article includes a sample database you can download to see how it
works.

If you really want to just use the pop-up parameter boxes and not use a
form, scroll down in the last link to the paragraph titled "Using a query
instead."

 

[floyd33 via AccessMonster.com]

Thank you for your suggestion. The problem with this solution is that I
still need all my data fitting the date and employee (or just date if emp is
Null) to pull. In addition, I need a list of employees who don't fit that
date criteria to pull in a footer. Does this make sense? I fear that what I
am asking is in fact impossible.

Can I suggest an alternative approach?

Provide a little unbound form with these controls:
- text box for entering the starting date;
- text box for entering the ending date;
- combo for selecting an employee;
- command button for opening the report.

You leave all the criteria out of the query. Instead, you use the Click
event procedure of the command button to open the report and filter it to
the desired records. The code in the click event can just ignore any of the
controls that don't have a value. So if the combo is blank, the result is
that it shows all employees. If the end date is blank, it shows all records
from the starting date. If that's blank too, it shows all the records.

While that does require a little programming, it's a powerful interface -
certainly worth the effort to learn.

Here's a simple example, with just the date boxes:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html
It explains 2 methods: method 1 is what you are doing; method 2 is what I'm
suggesting.

Once you have that working, you can proceed to a more involved example, to
add the employee combo too. See:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
This article includes a sample database you can download to see how it
works.

If you really want to just use the pop-up parameter boxes and not use a
form, scroll down in the last link to the paragraph titled "Using a query
instead."

I have a report that is pulling data based on two parameter fields (Start
Date between [Enter First Date] and [Enter Second Date] and then [Enter

[quoted text clipped - 6 lines]

have no idea where to start. My brain can't wrap around this one. Please
help!

 

[Allen Browne]

Why not use a main report that shows all the employess, with a subreport
that shows the dates in the selected range?

In the subreport's query, you just use the criteria as you did before for
the starting date and ending date.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thank you for your suggestion. The problem with this solution is that I
still need all my data fitting the date and employee (or just date if emp
is
Null) to pull. In addition, I need a list of employees who don't fit that
date criteria to pull in a footer. Does this make sense? I fear that
what I
am asking is in fact impossible.

Can I suggest an alternative approach?

Provide a little unbound form with these controls:
- text box for entering the starting date;
- text box for entering the ending date;
- combo for selecting an employee;
- command button for opening the report.

You leave all the criteria out of the query. Instead, you use the Click
event procedure of the command button to open the report and filter it to
the desired records. The code in the click event can just ignore any of
the
controls that don't have a value. So if the combo is blank, the result is
that it shows all employees. If the end date is blank, it shows all
records
from the starting date. If that's blank too, it shows all the records.

While that does require a little programming, it's a powerful interface -
certainly worth the effort to learn.

Here's a simple example, with just the date boxes:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html
It explains 2 methods: method 1 is what you are doing; method 2 is what
I'm
suggesting.

Once you have that working, you can proceed to a more involved example, to
add the employee combo too. See:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
This article includes a sample database you can download to see how it
works.

If you really want to just use the pop-up parameter boxes and not use a
form, scroll down in the last link to the paragraph titled "Using a query
instead."

I have a report that is pulling data based on two parameter fields (Start
Date between [Enter First Date] and [Enter Second Date] and then [Enter

[quoted text clipped - 6 lines]

have no idea where to start. My brain can't wrap around this one.
Please
help!

 

[floyd33 via AccessMonster.com]

I'm sorry, I think I am not explaining myself clearly. I need to see those
employees that match the dates I enter and then those who don't-preferably in
the same report. Is this possible?

Why not use a main report that shows all the employess, with a subreport
that shows the dates in the selected range?

In the subreport's query, you just use the criteria as you did before for
the starting date and ending date.

Thank you for your suggestion. The problem with this solution is that I
still need all my data fitting the date and employee (or just date if emp

[quoted text clipped - 50 lines]

 

[Allen Browne]

Are you saying that you want to sort all the employees that do match
together, and then list all the ones that don't match at the end?

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I'm sorry, I think I am not explaining myself clearly. I need to see
those
employees that match the dates I enter and then those who don't-preferably
in
the same report. Is this possible?

Why not use a main report that shows all the employess, with a subreport
that shows the dates in the selected range?

In the subreport's query, you just use the criteria as you did before for
the starting date and ending date.

Thank you for your suggestion. The problem with this solution is that I
still need all my data fitting the date and employee (or just date if
emp

[quoted text clipped - 50 lines]

Please
help!

 

[floyd33 via AccessMonster.com]

Yes, exactly. Show the matches and then show the non-matches. Any ideas?

Are you saying that you want to sort all the employees that do match
together, and then list all the ones that don't match at the end?

I'm sorry, I think I am not explaining myself clearly. I need to see
those

[quoted text clipped - 14 lines]

 

[Allen Browne]

Presumably you have a table of employees (say tblEmployee), and another
table (say tblRoster, with a RosterDate field.)

You want to limit the report to dates in a range, so create a query like
this:
SELECT * FROM tblRoster
WHERE RosterDate Between [Enter First Date] and [Enter Second Date];
Save it as (say) qryRosterRange.

Now create a query using tblEmployee and qryRosterRange.
In the upper pane of the query design window, drag tblEmployee.EmployeeID,
and drop onto qryRosterRange, so there is a line joining the 2 tables.
(Access may do this automatically.)
Double-click the join line.
Access opens a dialog with 3 options.
Choose the one that says:
All records from tblEmployee, and any matches from qryRosterRange.

The query now gives you at least one record for everyone.
If they had no roster, the RosterDate field will be Null.
Type this expression into the Field row:
NoRoster: (qryRosterRange Is Null)
This field will show True (-1) if the person no roster, or False (0) if they
did.

Now in your report, use the Sorting And Grouping box to sort (Descending) on
the NoRoster field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Yes, exactly. Show the matches and then show the non-matches. Any ideas?

Are you saying that you want to sort all the employees that do match
together, and then list all the ones that don't match at the end?

I'm sorry, I think I am not explaining myself clearly. I need to see
those

[quoted text clipped - 14 lines]

Please
help!

 

[floyd33 via AccessMonster.com]

This makes sense except when I execute the second query, I get my two date
parameter boxes followed by a "qryDateRange" (my name) pop up like it doesn't
recognize the query with the table. If I press ok w/o entering data and
continue, I receive 72 records, all -1, which isn't right. Help?

Presumably you have a table of employees (say tblEmployee), and another
table (say tblRoster, with a RosterDate field.)

You want to limit the report to dates in a range, so create a query like
this:
SELECT * FROM tblRoster
WHERE RosterDate Between [Enter First Date] and [Enter Second Date];
Save it as (say) qryRosterRange.

Now create a query using tblEmployee and qryRosterRange.
In the upper pane of the query design window, drag tblEmployee.EmployeeID,
and drop onto qryRosterRange, so there is a line joining the 2 tables.
(Access may do this automatically.)
Double-click the join line.
Access opens a dialog with 3 options.
Choose the one that says:
All records from tblEmployee, and any matches from qryRosterRange.

The query now gives you at least one record for everyone.
If they had no roster, the RosterDate field will be Null.
Type this expression into the Field row:
NoRoster: (qryRosterRange Is Null)
This field will show True (-1) if the person no roster, or False (0) if they
did.

Now in your report, use the Sorting And Grouping box to sort (Descending) on
the NoRoster field.

Yes, exactly. Show the matches and then show the non-matches. Any ideas?

[quoted text clipped - 6 lines]

 

[Allen Browne]

It does have to ask for the parameters. If it is asking multiple times, the
way around that is to create a form, and put 2 text boxes on the form. Then
in your query, use this kind of thing:
WHERE RosterDate Between [Forms].[Form1].[Enter First Date]
And [Forms].[Form1].[Enter Second Date];
Now open the form and put the dates in before you run the query, and it will
read the dates from there.

Sorry: the NoRoster expression needs to have a field name. If the tblRoster
has a primary key of RosterID, you could use:
NoRoster: (qryRosterRange.RosterID Is Null)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

This makes sense except when I execute the second query, I get my two date
parameter boxes followed by a "qryDateRange" (my name) pop up like it
doesn't
recognize the query with the table. If I press ok w/o entering data and
continue, I receive 72 records, all -1, which isn't right. Help?

Presumably you have a table of employees (say tblEmployee), and another
table (say tblRoster, with a RosterDate field.)

You want to limit the report to dates in a range, so create a query like
this:
SELECT * FROM tblRoster
WHERE RosterDate Between [Enter First Date] and [Enter Second Date];
Save it as (say) qryRosterRange.

Now create a query using tblEmployee and qryRosterRange.
In the upper pane of the query design window, drag tblEmployee.EmployeeID,
and drop onto qryRosterRange, so there is a line joining the 2 tables.
(Access may do this automatically.)
Double-click the join line.
Access opens a dialog with 3 options.
Choose the one that says:
All records from tblEmployee, and any matches from qryRosterRange.

The query now gives you at least one record for everyone.
If they had no roster, the RosterDate field will be Null.
Type this expression into the Field row:
NoRoster: (qryRosterRange Is Null)
This field will show True (-1) if the person no roster, or False (0) if
they
did.

Now in your report, use the Sorting And Grouping box to sort (Descending)
on the NoRoster field.

Yes, exactly. Show the matches and then show the non-matches.

 

[floyd33 via AccessMonster.com]

Do I need to make a separate subreport for this qryRosterRange thing? I
still need quite a lot of fields to show on the report and then just names at
the end. Do I put it in the footer?

It does have to ask for the parameters. If it is asking multiple times, the
way around that is to create a form, and put 2 text boxes on the form. Then
in your query, use this kind of thing:
WHERE RosterDate Between [Forms].[Form1].[Enter First Date]
And [Forms].[Form1].[Enter Second Date];
Now open the form and put the dates in before you run the query, and it will
read the dates from there.

Sorry: the NoRoster expression needs to have a field name. If the tblRoster
has a primary key of RosterID, you could use:
NoRoster: (qryRosterRange.RosterID Is Null)

This makes sense except when I execute the second query, I get my two date
parameter boxes followed by a "qryDateRange" (my name) pop up like it

[quoted text clipped - 32 lines]

 

[Allen Browne]

If you want a separate listing at the end of your report, then a subreport
in the Report Footer section sounds like a perfectly sensible approach.

 

[floyd33 via AccessMonster.com]

I think I am having an epic fail here. I created a sub report with the
NoDate query in it and inserted it into the original show all between date
and date report linking the EmployeeID fields. I can't even get a result
because I get a million parameter boxes asking for the start end dates.
Ideas?

 

[Allen Browne]

I have no further suggestions to offer beyond the form I suggested in the
very first reply to you.

Time for me to move on to other threads.

 

[floyd33 via AccessMonster.com]

Maybe this would be more helpful. This is the query I use to show all
records for meetings between date and date. What would have to change to
show all employees not assigned to a meeting between date and date?

SELECT [qryConfirmed 2001 Meetings].*
FROM [qryConfirmed 2001 Meetings]
WHERE ((([qryConfirmed 2001 Meetings].[Meeting Planner]) Like [Enter Meeting
Planner First Name or Press OK for All]) AND (([qryConfirmed 2001 Meetings].
[Start Date]) Between [Enter First Date] And [Enter Second Date]) AND ((
[qryConfirmed 2001 Meetings].ContractStatus)="Awarded" Or ([qryConfirmed 2001
Meetings].ContractStatus)="Confirmed" Or ([qryConfirmed 2001 Meetings].
ContractStatus)="Postponed" Or ([qryConfirmed 2001 Meetings].ContractStatus)
="pending")) OR ((([qryConfirmed 2001 Meetings].[Meeting Planner]) Like
[Enter Meeting Planner First Name or Press OK for All]) AND (([qryConfirmed
2001 Meetings].ContractStatus)="Awarded" Or ([qryConfirmed 2001 Meetings].
ContractStatus)="Confirmed" Or ([qryConfirmed 2001 Meetings].ContractStatus)
="Postponed" Or ([qryConfirmed 2001 Meetings].ContractStatus)="pending") AND
((([Start Date]) Between [Enter First Date] And [Enter Second Date]) Is Null))
OR ((([qryConfirmed 2001 Meetings].[Start Date]) Between [Enter First Date]
And [Enter Second Date]) AND (([qryConfirmed 2001 Meetings].ContractStatus)
="Awarded" Or ([qryConfirmed 2001 Meetings].ContractStatus)="Confirmed" Or (
[qryConfirmed 2001 Meetings].ContractStatus)="Postponed" Or ([qryConfirmed
2001 Meetings].ContractStatus)="pending") AND ((([Meeting Planner]) Like
[Enter Meeting Planner First Name Or Press OK for All]) Is Null)) OR (((
[qryConfirmed 2001 Meetings].ContractStatus)="Awarded" Or ([qryConfirmed 2001
Meetings].ContractStatus)="Confirmed" Or ([qryConfirmed 2001 Meetings].
ContractStatus)="Postponed" Or ([qryConfirmed 2001 Meetings].ContractStatus)
="pending") AND ((([Start Date]) Between [Enter First Date] And [Enter Second
Date]) Is Null) AND ((([Meeting Planner]) Like [Enter Meeting Planner First
Name Or Press OK for All]) Is Null));

I think I am having an epic fail here. I created a sub report with the
NoDate query in it and inserted it into the original show all between date
and date report linking the EmployeeID fields. I can't even get a result
because I get a million parameter boxes asking for the start end dates.
Ideas?

If you want a separate listing at the end of your report, then a subreport
in the Report Footer section sounds like a perfectly sensible approach.

[quoted text clipped - 3 lines]