Query help required

  • Thread starter Thread starter SqlDope
  • Start date Start date
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
 
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;
 
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...
 
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...
 
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

Back
Top