Join to a form field without including all records (1 Viewer)

doxdanp · Feb 24, 2009

Normally when two tables are joined, rows are excluded that don't meet the join criteria. However, in this query I'm working on, the table on the left side of the join only has one row and one column, whose value references a form field. In this case, every row of the table on the right side of the join is returned, even though most of them don't meet the join criteria. (This defeats the goal, which is to filter the rows in the right-hand table based on the join.)

Is this a known bug/limitation? Is there a work-around?

You might wonder why I'm going about it this way instead of using VBA in the form, and the short answer is that I'm trying to save time/work. Since I already have a query that runs a bunch of tests and shows statistics based on values in another table by doing a left join to it, I don't want to create a new method to run the same tests on values that the user can provide, which would require me to start maintaining all of those test criteria in two separate places.

If more detail is needed, I can provide instructions for recreating the behavior I have described.

boblarson · Feb 24, 2009

Do you have it linked to the same field in the other table?

boblarson · Feb 24, 2009

Also, what is your SQL string look like?

doxdanp · Feb 25, 2009

boblarson said:
Also, what is your SQL string look like?

Here is the SQL view of the query...

Code:

SELECT frmqry_test.email, Max(message.date) AS last_message, Count(message.date) AS total_messages, Right([frmqry_test.email],Len([frmqry_test.email])-InStr([frmqry_test.email],"@")) AS [Domain], request.date AS request_date
FROM (frmqry_test LEFT JOIN request ON frmqry_test.email = request.email) LEFT JOIN message ON frmqry_test.email = message.email
GROUP BY frmqry_test.email, Right([frmqry_test.email],Len([frmqry_test.email])-InStr([frmqry_test.email],"@")), request.date;

As you can see, the tables are linked on the email field in each. That should limit the rows returned to only those where the value in the email column also exists in the frmqry_test.email column, but instead it's returning all rows.

And in case you're curious, the SQL view of the frmqry_test query is:

Code:

SELECT Forms!frm_test.txtEmail AS email
FROM dummy;

The dummy table has just one column (number) and one row (value=1). This is done to get around the "Query input must contain at least one table or query" error.

Join to a form field without including all records (1 Viewer)

doxdanp

New member

boblarson

Smeghead

boblarson

Smeghead

doxdanp

New member

Similar threads

Users who are viewing this thread