Re: Criteria & Joins in Queries

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Nikos Yannacopoulos (nyannacoREMOVETHISBIT_at_in.gr)
Date: 04/20/04 

Date: Tue, 20 Apr 2004 12:41:28 +0300

Yes, you are making sense, I think I understand what the problem is.
One workaround is to make a query on the right table and impose the criteria
for excluding expired data there, then use that query rather than the
original table in your main query (with no criteria on it). That way you
exclude only records from the right table, whereas what you've been doing
excludes records from the results of the query, so you lose some of the
records in your left table.

HTH,
Nikos

"ADodson" <anonymous@discussions.microsoft.com> wrote in message
news:509F5BAD-E928-4AD9-B2F5-46EA06C7AA7D@microsoft.com...
> Let's see if I can explain this properly:
>
> I have two tables with a join arrow going from the left table to the right
table. In these two tables, I have the connecting field (duh). It is
standard for the join to take all records from the left table and only those
from the right that match in this field.
>
> But then the plot thickens. I have criteria eliminating records from the
right table. One particular record will not show up on my report. I think
it is because there is a record that corresponds to the left table, but the
criteria eliminates it, and so the whole record doesn't show up on the final
report, not even from the left table.
>
> To top it all off, I have this being done twice in a row. Meaning, I
really have three tables connected, all with criteria eliminating expired
data and pulling data current for a certain date. I've checked my criteria,
and I've checked my dates. Everything else is in order. The very right
table is where this problem is occuring. There is still corresponding
records in the left two tables, but because there is no new record to
replace the expired one, none of it shows up.
>
> Did I make any sense?
>
> Does anybody have any suggestions on dealing with this? I'm not SQL savy,
but with decent instructions, I feel that I could survive. Thank you in
advance.

Relevant Pages

  • RE: Filtering a subform using many combo boxes
    ... SELECT AssignedPriority, AssignedPriorityDetail FROM tblAssignedPriority ... YES for all of the combo boxes.) ... I followed you instructions to the letter and every one of the criteria ... open the query "qryProjects" in design view. ...
    (microsoft.public.access.forms)
  • Re: using a form with combo box to input criteria
    ... Candia Computer Consulting - Candia NH ... When you removed the criteria, ... Then your criteria in the query would be... ... query behind your main report. ...
    (microsoft.public.access.forms)
  • Re: using a form with combo box to input criteria
    ... Candia Computer Consulting - Candia NH ... When you removed the criteria, ... Then your criteria in the query would be... ... query behind your main report. ...
    (microsoft.public.access.forms)
  • RE: Filtering a subform using many combo boxes
    ... I followed you instructions to the letter and every one of the criteria ... I described the typical properties of all my combo boxes in my original ... open the query "qryProjects" in design view. ... I have a main table named tblProjectData that contains all the data for each ...
    (microsoft.public.access.forms)
  • Re: using a form with combo box to input criteria
    ... When you removed the criteria, ... Then your criteria in the query would be... ... query behind your main report. ...
    (microsoft.public.access.forms)