Access 97 Query Advise

[John]

Hi

I need to use a query as follows;

SELECT *
FROM (SELECT * From Table1 staff WHERE Table1.Field2 = "A") AS T1
WHERE T1.Field1 = "X"

The problem is that I get an error after first FROM saying 'Syntax Error'. I
am assuming Access 97 does not support this sort of nested query which
Access 2000 allows. Is there a way round this in Access 97?

Thanks

Regards

 

[John W. Vinson]

The problem is that I get an error after first FROM saying 'Syntax Error'. I
am assuming Access 97 does not support this sort of nested query which
Access 2000 allows. Is there a way round this in Access 97?

Yes, but it uses a truly wierd syntax:

SELECT *
FROM [SELECT * From Table1 staff WHERE Table1.Field2 = "A"]. AS T1
WHERE T1.Field1 = "X"

Note the square brackets and the period after the closing bracket.

John W. Vinson [MVP]

 

[John]

Thanks. I suspect I haven't grasped it properly. Here is my actual SQL;

SELECT Q3Str.[Staff ID], Q3Str.Name, Q3Str.Mobile, Q3Str.NormTel,
Q3Str.[E-Mail], Q3Str.Gr
FROM [SELECT Staff.[Staff ID], [Surname] & ", " & [Forename] AS Name,
Staff.Mobile, Staff.NormTel, Staff.[E-Mail], Staff.Grade AS Gr,
Staff.Forename, Staff.Surname, staff.CarOwn From staff WHERE staff.[Contact
Status] = "Current"]. AS Q3Str
WHERE Q3Str.Forename Like "*" OR Q3Str.Surname Like "*"

I am getting a "Invalid bracketing of name SELECT Staff.[Staff ID'". Any
idea what I am doing wrong?

Thanks

Regards

The problem is that I get an error after first FROM saying 'Syntax Error'.
I
am assuming Access 97 does not support this sort of nested query which
Access 2000 allows. Is there a way round this in Access 97?

Yes, but it uses a truly wierd syntax:

SELECT *
FROM [SELECT * From Table1 staff WHERE Table1.Field2 = "A"]. AS T1
WHERE T1.Field1 = "X"

Note the square brackets and the period after the closing bracket.

John W. Vinson [MVP]

 

[Douglas J. Steele]

Unfortunately, there's a bug when you've got field or table names enclosed
in square brackets inside the query, and since you appear to have a field
name with spaces in it (Contact Status), you can't avoid using them.

Save that inner query as a separate query, and just refer to it in your main
query.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks. I suspect I haven't grasped it properly. Here is my actual SQL;

SELECT Q3Str.[Staff ID], Q3Str.Name, Q3Str.Mobile, Q3Str.NormTel,
Q3Str.[E-Mail], Q3Str.Gr
FROM [SELECT Staff.[Staff ID], [Surname] & ", " & [Forename] AS Name,
Staff.Mobile, Staff.NormTel, Staff.[E-Mail], Staff.Grade AS Gr,
Staff.Forename, Staff.Surname, staff.CarOwn From staff WHERE
staff.[Contact Status] = "Current"]. AS Q3Str
WHERE Q3Str.Forename Like "*" OR Q3Str.Surname Like "*"

I am getting a "Invalid bracketing of name SELECT Staff.[Staff ID'". Any
idea what I am doing wrong?

Thanks

Regards

The problem is that I get an error after first FROM saying 'Syntax
Error'. I
am assuming Access 97 does not support this sort of nested query which
Access 2000 allows. Is there a way round this in Access 97?

Yes, but it uses a truly wierd syntax:

SELECT *
FROM [SELECT * From Table1 staff WHERE Table1.Field2 = "A"]. AS T1
WHERE T1.Field1 = "X"

Note the square brackets and the period after the closing bracket.

John W. Vinson [MVP]

 

[John]

Unfortunately it is all in code i.e. done through strings. How do I save as
query in this case?

Many Thanks

Regards

Unfortunately, there's a bug when you've got field or table names enclosed
in square brackets inside the query, and since you appear to have a field
name with spaces in it (Contact Status), you can't avoid using them.

Save that inner query as a separate query, and just refer to it in your
main query.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks. I suspect I haven't grasped it properly. Here is my actual SQL;

SELECT Q3Str.[Staff ID], Q3Str.Name, Q3Str.Mobile, Q3Str.NormTel,
Q3Str.[E-Mail], Q3Str.Gr
FROM [SELECT Staff.[Staff ID], [Surname] & ", " & [Forename] AS Name,
Staff.Mobile, Staff.NormTel, Staff.[E-Mail], Staff.Grade AS Gr,
Staff.Forename, Staff.Surname, staff.CarOwn From staff WHERE
staff.[Contact Status] = "Current"]. AS Q3Str
WHERE Q3Str.Forename Like "*" OR Q3Str.Surname Like "*"

I am getting a "Invalid bracketing of name SELECT Staff.[Staff ID'". Any
idea what I am doing wrong?

Thanks

Regards

The problem is that I get an error after first FROM saying 'Syntax
Error'. I
am assuming Access 97 does not support this sort of nested query which
Access 2000 allows. Is there a way round this in Access 97?

Yes, but it uses a truly wierd syntax:

SELECT *
FROM [SELECT * From Table1 staff WHERE Table1.Field2 = "A"]. AS T1
WHERE T1.Field1 = "X"

Note the square brackets and the period after the closing bracket.

John W. Vinson [MVP]

 

[John]

I am using code like below to create queries. What changes can I make to
make it work in 97?

Thanks

Regards


Q3Str = "SELECT * " & _
"FROM [" & Q25_Str & "]. AS Q25_Str " & _
"WHERE staff.[Contact Status] = ""Current"" "

Q4Str = "SELECT Q3Str.[Staff ID], Q3Str.Name, Q3Str.Mobile, Q3Str.NormTel,
Q3Str.[E-Mail], Q3Str.Gr " & _
"FROM [" & Q3Str & "]. AS Q3Str " & _
"WHERE <some condition> "

Unfortunately, there's a bug when you've got field or table names enclosed
in square brackets inside the query, and since you appear to have a field
name with spaces in it (Contact Status), you can't avoid using them.

Save that inner query as a separate query, and just refer to it in your
main query.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks. I suspect I haven't grasped it properly. Here is my actual SQL;

SELECT Q3Str.[Staff ID], Q3Str.Name, Q3Str.Mobile, Q3Str.NormTel,
Q3Str.[E-Mail], Q3Str.Gr
FROM [SELECT Staff.[Staff ID], [Surname] & ", " & [Forename] AS Name,
Staff.Mobile, Staff.NormTel, Staff.[E-Mail], Staff.Grade AS Gr,
Staff.Forename, Staff.Surname, staff.CarOwn From staff WHERE
staff.[Contact Status] = "Current"]. AS Q3Str
WHERE Q3Str.Forename Like "*" OR Q3Str.Surname Like "*"

I am getting a "Invalid bracketing of name SELECT Staff.[Staff ID'". Any
idea what I am doing wrong?

Thanks

Regards

The problem is that I get an error after first FROM saying 'Syntax
Error'. I
am assuming Access 97 does not support this sort of nested query which
Access 2000 allows. Is there a way round this in Access 97?

Yes, but it uses a truly wierd syntax:

SELECT *
FROM [SELECT * From Table1 staff WHERE Table1.Field2 = "A"]. AS T1
WHERE T1.Field1 = "X"

Note the square brackets and the period after the closing bracket.

John W. Vinson [MVP]

 

[Douglas J. Steele]

Dim qdfIntermediate As DAO.QueryDef

Set qdfIntermediate = CurrentDb.QueryDefs("qryQ3Str")

qdfIntermediate.SQL = "SELECT * " & _
"FROM [" & Q25_Str & "]. AS Q25_Str " & _
"WHERE staff.[Contact Status] = ""Current"" "

Q4Str = "SELECT Q3Str.[Staff ID], Q3Str.Name, Q3Str.Mobile, Q3Str.NormTel,
Q3Str.[E-Mail], Q3Str.Gr " & _
"FROM qryQ3Str AS Q3Str " & _
"WHERE <some condition> "

Note that qryQ3Str must already exist. Doesn't matter what it is: it'll get
updated by the code. (Sorry, I was too lazy to include the code that checks
whether it exists, and creates it if it doesn't)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I am using code like below to create queries. What changes can I make to
make it work in 97?

Thanks

Regards


Q3Str = "SELECT * " & _
"FROM [" & Q25_Str & "]. AS Q25_Str " & _
"WHERE staff.[Contact Status] = ""Current"" "

Q4Str = "SELECT Q3Str.[Staff ID], Q3Str.Name, Q3Str.Mobile,
Q3Str.NormTel, Q3Str.[E-Mail], Q3Str.Gr " & _
"FROM [" & Q3Str & "]. AS Q3Str " & _
"WHERE <some condition> "

Unfortunately, there's a bug when you've got field or table names
enclosed in square brackets inside the query, and since you appear to
have a field name with spaces in it (Contact Status), you can't avoid
using them.

Save that inner query as a separate query, and just refer to it in your
main query.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks. I suspect I haven't grasped it properly. Here is my actual SQL;

SELECT Q3Str.[Staff ID], Q3Str.Name, Q3Str.Mobile, Q3Str.NormTel,
Q3Str.[E-Mail], Q3Str.Gr
FROM [SELECT Staff.[Staff ID], [Surname] & ", " & [Forename] AS Name,
Staff.Mobile, Staff.NormTel, Staff.[E-Mail], Staff.Grade AS Gr,
Staff.Forename, Staff.Surname, staff.CarOwn From staff WHERE
staff.[Contact Status] = "Current"]. AS Q3Str
WHERE Q3Str.Forename Like "*" OR Q3Str.Surname Like "*"

I am getting a "Invalid bracketing of name SELECT Staff.[Staff ID'". Any
idea what I am doing wrong?

Thanks

Regards

The problem is that I get an error after first FROM saying 'Syntax
Error'. I
am assuming Access 97 does not support this sort of nested query which
Access 2000 allows. Is there a way round this in Access 97?

Yes, but it uses a truly wierd syntax:

SELECT *
FROM [SELECT * From Table1 staff WHERE Table1.Field2 = "A"]. AS T1
WHERE T1.Field1 = "X"

Note the square brackets and the period after the closing bracket.

John W. Vinson [MVP]

 

[George Nicholson]

Unfortunately it is all in code i.e. done through strings. How do I save as

query in this case?

You can easily change the SQL of existing queries via code by changing the
QueryDef SQL property:

Dim db as DAO.Database
Dim qdf as DAO.QueryDef
Dim Q3Str as String

Set db = CurrentDB
Set qdf = db.querydefs("mySavedQuery")
Q3Str = "SELECT * " & _
"FROM [" & Q25_Str & "]. AS Q25_Str " & _
"WHERE staff.[Contact Status] = ""Current"" "
qdf.SQL = q3str

Then you can refer to "mySavedQuery" within another query, code based or
not. Be cautious about inconsistent Field names (i.e., saving a query that
is referred to in other places with one set of field names this time, a
different set of field names next time).

HTH,

I am using code like below to create queries. What changes can I make to
make it work in 97?

Thanks

Regards


Q3Str = "SELECT * " & _
"FROM [" & Q25_Str & "]. AS Q25_Str " & _
"WHERE staff.[Contact Status] = ""Current"" "

Q4Str = "SELECT Q3Str.[Staff ID], Q3Str.Name, Q3Str.Mobile,
Q3Str.NormTel, Q3Str.[E-Mail], Q3Str.Gr " & _
"FROM [" & Q3Str & "]. AS Q3Str " & _
"WHERE <some condition> "

Unfortunately, there's a bug when you've got field or table names
enclosed in square brackets inside the query, and since you appear to
have a field name with spaces in it (Contact Status), you can't avoid
using them.

Save that inner query as a separate query, and just refer to it in your
main query.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks. I suspect I haven't grasped it properly. Here is my actual SQL;

SELECT Q3Str.[Staff ID], Q3Str.Name, Q3Str.Mobile, Q3Str.NormTel,
Q3Str.[E-Mail], Q3Str.Gr
FROM [SELECT Staff.[Staff ID], [Surname] & ", " & [Forename] AS Name,
Staff.Mobile, Staff.NormTel, Staff.[E-Mail], Staff.Grade AS Gr,
Staff.Forename, Staff.Surname, staff.CarOwn From staff WHERE
staff.[Contact Status] = "Current"]. AS Q3Str
WHERE Q3Str.Forename Like "*" OR Q3Str.Surname Like "*"

I am getting a "Invalid bracketing of name SELECT Staff.[Staff ID'". Any
idea what I am doing wrong?

Thanks

Regards

The problem is that I get an error after first FROM saying 'Syntax
Error'. I
am assuming Access 97 does not support this sort of nested query which
Access 2000 allows. Is there a way round this in Access 97?

Yes, but it uses a truly wierd syntax:

SELECT *
FROM [SELECT * From Table1 staff WHERE Table1.Field2 = "A"]. AS T1
WHERE T1.Field1 = "X"

Note the square brackets and the period after the closing bracket.

John W. Vinson [MVP]

 

[John W. Vinson]

Thanks. I suspect I haven't grasped it properly. Here is my actual SQL;

SELECT Q3Str.[Staff ID], Q3Str.Name, Q3Str.Mobile, Q3Str.NormTel,
Q3Str.[E-Mail], Q3Str.Gr
FROM [SELECT Staff.[Staff ID], [Surname] & ", " & [Forename] AS Name,
Staff.Mobile, Staff.NormTel, Staff.[E-Mail], Staff.Grade AS Gr,
Staff.Forename, Staff.Surname, staff.CarOwn From staff WHERE staff.[Contact
Status] = "Current"]. AS Q3Str
WHERE Q3Str.Forename Like "*" OR Q3Str.Surname Like "*"

I don't understand why you need a subquery, or why you're putting a criterion
of Like "*" on the name fields at all. That criterion of course returns all
records; so does just leaving out the criterion altogether! This query is
logically equivalent to

SELECT Staff.[Staff ID], [Surname] & ", " & [Forename] AS [Name],
Staff.Mobile, Staff.NormTel, Staff.[E-Mail], Staff.Grade AS Gr
From staff WHERE staff.[Contact Status] = "Current";

Even if you need criteria on the name fields, you don't need a subquery that I
can see!

Are you perhaps making this more elaborate than it needs to be???

John W. Vinson [MVP]

 

[David W. Fenton]

I don't understand why you need a subquery, or why you're putting
a criterion of Like "*" on the name fields at all. That criterion
of course returns all records;

No, it only returns records that have non-null values in those
fields.

 

[John W. Vinson]

No, it only returns records that have non-null values in those
fields.

oops! Thanks David. Good point. That may be even further from the OP's intent
however.

John W. Vinson [MVP]