Criteria & Joins in Queries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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 said:
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
Click to expand...
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
Click to expand...
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
Click to expand...
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,
Click to expand...
but with decent instructions, I feel that I could survive. Thank you in
advance.
 
Yeah! It worked. Happy Dance

Now, I just have to do it another 4 or 5 times. I'm going to have a thousand queries... but at least it accomplishes what it should. The users won't see all the queries. My only concern is that it might run slower now. Is that a glitch on Microsoft's part or do they intentionally do this for some reason?
 
Back
Top