Query help required

S

SqlDope

Hello,
I require some help with my query -- it doesn't work properly.

Quasicode:
From Table School,
Select all people in any Class where that Class Contains a person called
SMITH.
Here is my code.

SELECT DISTINCT School.Class, School.Forename, School.Surname, School.Date,
School.Age, School.year, School.ref
FROM School INNER JOIN School AS School1 ON
([School].[Class])=(School1.Class)
WHERE (([School].[surname])=("SMITH"))
ORDER BY School.Class;

Desired Result

Class FName SName Date Age Year Ref
1 John BROWN 20/2/06 14 6 1234
1 Mary SMITH 12/7/06 14 6 1345
3 David PARK 3/6/06 12 5 2134
3 George HALL 12/12/06 13 5 1235
3 Mike SMITH 25/1/06 11 5 1645

Actual Result

Class FName SName Date Age Year Ref
1 Mary SMITH 12/7/06 14 6 1345
3 Mike SMITH 25/1/06 11 5 1645

However it is only selecting people called SMITH and ignoring the other
people.
Any help much appeciated
Thanks
 
D

Douglas J. Steele

Assuming you're using Access 2000 or newer, try:

SELECT DISTINCT School.Class, School.Forename, School.Surname, School.Date,
School.Age, School.year, School.ref
FROM School INNER JOIN
(
SELECT Class FROM School
WHERE [surname]="SMITH"
) AS Subquery
ON [School].[Class]=Subquery.Class
ORDER BY School.Class;
 
S

SqlDope

Hello Douglas,
Thank You For Your Help
Will Try it out
Best Regards
Douglas J. Steele said:
Assuming you're using Access 2000 or newer, try:

SELECT DISTINCT School.Class, School.Forename, School.Surname,
School.Date,
School.Age, School.year, School.ref
FROM School INNER JOIN
(
SELECT Class FROM School
WHERE [surname]="SMITH"
) AS Subquery
ON [School].[Class]=Subquery.Class
ORDER BY School.Class;


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


SqlDope said:
Hello,
I require some help with my query -- it doesn't work properly.

Quasicode:
From Table School,
Select all people in any Class where that Class Contains a person called
SMITH.
Here is my code.

SELECT DISTINCT School.Class, School.Forename, School.Surname,
School.Date, School.Age, School.year, School.ref
FROM School INNER JOIN School AS School1 ON
([School].[Class])=(School1.Class)
WHERE (([School].[surname])=("SMITH"))
ORDER BY School.Class;

Desired Result

Class FName SName Date Age Year Ref
1 John BROWN 20/2/06 14 6 1234
1 Mary SMITH 12/7/06 14 6 1345
3 David PARK 3/6/06 12 5 2134
3 George HALL 12/12/06 13 5 1235
3 Mike SMITH 25/1/06 11 5 1645

Actual Result

Class FName SName Date Age Year Ref
1 Mary SMITH 12/7/06 14 6 1345
3 Mike SMITH 25/1/06 11 5 1645

However it is only selecting people called SMITH and ignoring the other
people.
Any help much appeciated
Thanks
Click to expand...
Click to expand...
 
S

SqlDope

Hello Douglas and All
Using MS Access 2003 Sp2
Query works fine until.....
I change the surname.
I then get an error -- 'syntax error in from clause'.
On veiwing the SQL I find that access has changed the code to:

SELECT DISTINCT school.class, school.Forename, school.Surname, school.Date,
school.Age, school.year, school.ref
FROM school INNER JOIN [SELECT class FROM school
WHERE [surname]="smith"
]. AS Subquery ON [school].[class]=Subquery.class
ORDER BY school.class;

I can change it to the correct code and it works again

SELECT DISTINCT School.Class, School.Forename, School.Surname, School.Date,
School.Age, School.year, School.ref
FROM School INNER JOIN
(
SELECT Class FROM School
WHERE [surname]="lockwood"
) AS Subquery
ON [School].[Class]=Subquery.Class
ORDER BY School.Class;

I wonder why it is being changed and the solution?
Thank for any help

SqlDope said:
Hello Douglas,
Thank You For Your Help
Will Try it out
Best Regards
Douglas J. Steele said:
Assuming you're using Access 2000 or newer, try:

SELECT DISTINCT School.Class, School.Forename, School.Surname,
School.Date,
School.Age, School.year, School.ref
FROM School INNER JOIN
(
SELECT Class FROM School
WHERE [surname]="SMITH"
) AS Subquery
ON [School].[Class]=Subquery.Class
ORDER BY School.Class;


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


SqlDope said:
Hello,
I require some help with my query -- it doesn't work properly.

Quasicode:
From Table School,
Select all people in any Class where that Class Contains a person called
SMITH.
Here is my code.

SELECT DISTINCT School.Class, School.Forename, School.Surname,
School.Date, School.Age, School.year, School.ref
FROM School INNER JOIN School AS School1 ON
([School].[Class])=(School1.Class)
WHERE (([School].[surname])=("SMITH"))
ORDER BY School.Class;

Desired Result

Class FName SName Date Age Year Ref
1 John BROWN 20/2/06 14 6 1234
1 Mary SMITH 12/7/06 14 6 1345
3 David PARK 3/6/06 12 5 2134
3 George HALL 12/12/06 13 5 1235
3 Mike SMITH 25/1/06 11 5 1645

Actual Result

Class FName SName Date Age Year Ref
1 Mary SMITH 12/7/06 14 6 1345
3 Mike SMITH 25/1/06 11 5 1645

However it is only selecting people called SMITH and ignoring the other
people.
Any help much appeciated
Thanks
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

How are you changing the surname: in the SQL view, or in the Design view?
Are you saving the query before trying to rerun it?

(Note: I'm on the road at the moment, so it might be a few days before I get
back to this thread)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



SqlDope said:
Hello Douglas and All
Using MS Access 2003 Sp2
Query works fine until.....
I change the surname.
I then get an error -- 'syntax error in from clause'.
On veiwing the SQL I find that access has changed the code to:

SELECT DISTINCT school.class, school.Forename, school.Surname,
school.Date, school.Age, school.year, school.ref
FROM school INNER JOIN [SELECT class FROM school
WHERE [surname]="smith"
]. AS Subquery ON [school].[class]=Subquery.class
ORDER BY school.class;

I can change it to the correct code and it works again

SELECT DISTINCT School.Class, School.Forename, School.Surname,
School.Date,
School.Age, School.year, School.ref
FROM School INNER JOIN
(
SELECT Class FROM School
WHERE [surname]="lockwood"
) AS Subquery
ON [School].[Class]=Subquery.Class
ORDER BY School.Class;

I wonder why it is being changed and the solution?
Thank for any help

SqlDope said:
Hello Douglas,
Thank You For Your Help
Will Try it out
Best Regards
Douglas J. Steele said:
Assuming you're using Access 2000 or newer, try:

SELECT DISTINCT School.Class, School.Forename, School.Surname,
School.Date,
School.Age, School.year, School.ref
FROM School INNER JOIN
(
SELECT Class FROM School
WHERE [surname]="SMITH"
) AS Subquery
ON [School].[Class]=Subquery.Class
ORDER BY School.Class;


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hello,
I require some help with my query -- it doesn't work properly.

Quasicode:
From Table School,
Select all people in any Class where that Class Contains a person
called SMITH.
Here is my code.

SELECT DISTINCT School.Class, School.Forename, School.Surname,
School.Date, School.Age, School.year, School.ref
FROM School INNER JOIN School AS School1 ON
([School].[Class])=(School1.Class)
WHERE (([School].[surname])=("SMITH"))
ORDER BY School.Class;

Desired Result

Class FName SName Date Age Year Ref
1 John BROWN 20/2/06 14 6 1234
1 Mary SMITH 12/7/06 14 6 1345
3 David PARK 3/6/06 12 5 2134
3 George HALL 12/12/06 13 5 1235
3 Mike SMITH 25/1/06 11 5 1645

Actual Result

Class FName SName Date Age Year Ref
1 Mary SMITH 12/7/06 14 6 1345
3 Mike SMITH 25/1/06 11 5 1645

However it is only selecting people called SMITH and ignoring the other
people.
Any help much appeciated
Thanks
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Access Changes SQL and makes unusable 3
Delete unique records 2
dates inbetween 3
Get last 6 rows in list where 1 cell matches certain criteria 2
combine rows of data to one row...tough one 3
Same name - multiple data - how to combine 3
Report from Crosstab with Select Dates Possible? 2
Calculate years from multiple records 2

Top