microsoft access error 3306

[mel.christie]

How do I fix this query? Access will not let me do this though I can in
other databases...

SELECT *
FROM database_schemas
WHERE (database_instance, schema) not in (SELECT distinct customer,
schema FROM db_activity WHERE date = (select max(date) from
db_activity));

 

[Van T. Dinh]

Please describe the Tables [database_schemas] and [db_activity]. If you
expect these as system Tables (???), JET uses different system Tables.

Also, "date" as a Field name would give you problems sicne "Date" is a
reserved word in JET SQL (and VBA).

 

[mel.christie]

These are not system tables. They are just regular tables that I
created. The date clause had no problem working with simpler queries. I
think it is objecting to the use of 2 fields in the main where clause
because even this query fails:

Regards

Mel

Please describe the Tables [database_schemas] and [db_activity]. If you
expect these as system Tables (???), JET uses different system Tables.

Also, "date" as a Field name would give you problems sicne "Date" is a
reserved word in JET SQL (and VBA).

--
HTH
Van T. Dinh
MVP (Access)

How do I fix this query? Access will not let me do this though I can in
other databases...

SELECT *
FROM database_schemas
WHERE (database_instance, schema) not in (SELECT distinct customer,
schema FROM db_activity WHERE date = (select max(date) from
db_activity));

 

[Rick Brandt]

These are not system tables. They are just regular tables that I
created. The date clause had no problem working with simpler queries.
I think it is objecting to the use of 2 fields in the main where
clause because even this query fails:

As far as I know an IN(SELECT) clause can only return a single field.

 

[Van T. Dinh]

Does the expression

(database_instance, schema) means you want to find Records that the
combination of these Field values doesn't match any of the conbinations in
the SubQuery?

If that's the case, it won't work as JET In operator works only with a
single Field, not a combination of Fields.

I think you can use a "frustrated outer join" (more efficient) or NOT EXISTS
predicate in this case.

 

[mel.christie]

Thanks.... Yes I do want a combination

What is a frustrated outer join?

Could I concatenate the fields and use that?

 

[Douglas J. Steele]

SELECT *
FROM database_schemas
LEFT JOIN db_activity
ON database_schemas.database_instance = db_activity.customer
AND database_schemas.schema = db_activity.schema
WHERE db_activity.customer IS NULL

 

[mel.christie]

Thanks!

I know I would never have figured that one!

Regards

Mel

 

[John Spencer]

By the way you are right, you should be able to use a concatenated field in your
original sql. I would add a separator between the two to ensure the values were
not being accidentally created that might match on the two combined values but
not the two separate values.

That said, you are probably much better off with the frustrated outer join that
Douglas has posted.

SELECT *
FROM database_schemas
WHERE (database_instance & "//" & schema)
not in
(SELECT distinct customer & "//" & schema
FROM db_activity
WHERE db_activity.date =
(select max(db_activity.date) from db_activity));