Ms-access – 3 Simple Queries SQL Student Question, Query Errors in Microsoft Access

datems accessms-access-2016query

The First Question is: "List the name of each trip that does not start in New Hampshire (NH)."

SELECT TripName
FROM Trip
WHERE State!='NH'; /* works with State='NH' */

errror1

The Second Question is: "List the trip name, type, and maximum group size for all trips that have Susan Kiley as a guide."

SELECT TripName, MaxGrpSize
FROM Trip, TripGuides
WHERE Type=Hiking, GuideNum = 'BR01';
/* also i tried WHERE Type=Hiking AND GuideNum = 'BR01'; */

enter image description here

I Cant Filter by only type: "Hiking"

The Third Question is: List the reservation ID, customer number, customer last name, and customer first name for all trips that occur in July 2018.
I Tried This:|

SELECT DISTINCT  LastName, FirstName, CustomerNum, ReservationID
FROM Customer, Reservation
WHERE TripDate = '2018-12'; /* i tried CustomerNum.Customer */

enter image description here

If you need more tables and more information let me know.

Best Answer

the first is simple access has no !=

SELECT TripName AS Ausdr1
FROM Trip
WHERE ([State])<>'NH';

the form of the secnd is wrong and you should start to use proper JOINs a comma for tables means always a cROSS JoiN, which can be useful, but nit in this case

also strings must always be in quotes

SELECT TripName , MaxGrpSize 
FROM Trip, TripGuides
WHERE ((([Type])='Hiking') AND (([GuideNum])='BR01'));

the last us simple bith tables have the column ´CustomerNum´, so you have to tell access which one you wantg, but i guess it is a joining column.

SELECT DISTINCT  LastName, FirstName, [Customer].CustomerNum, ReservationID
FROM Customer, Reservation
WHERE TripDate = '2018-12';

what i mean by proper joining:

SELECT DISTINCT  LastName, FirstName, c.CustomerNum, ReservationID
FROM Customer C INNER JOIN Reservation r ON c.CustomerNum = r.CustomerNum
WHERE TripDate = '2018-12';

I don#t know ou complete tables , so i am only guessing

Related Question