Hi,
Can anyone help me on a little problem I am having with some SQL - in
particular on a subquery.
I am setting up a database in Access for a voluntary group which runs
voluntary youth weekend camps. The bit I am on at the moment is the booking
system for these camps.
My tables are setup as:
- people_people which contains name / address details for the volunteers.
Primary key is id field.
- camps_info which contains information on each camp. Primary key is
camp_number field.
- camps_bookings. This has a record for each booking made.
I want a query which will give me a list of each camp (ie corresponding to
each record in camps_info) and alongside that display the number of bookings
there are for each camp.
I have successfully set up a parameter query that will tell me the number of
bookings for a particular camp:
SELECT sum(camps_info.camp_number) AS num_bookings_on_this_camp
FROM camps_info INNER JOIN camps_bookings ON camps_info.camp_number =
camps_bookings.camp_number
WHERE (((camps_info.camp_number)=[What camp number?]));
However, rather than typing in each camp number, I want the query to do the
number of bookings for each camp number in the camps_info table.
I imagine that I need a sub-query for this? I have been trying various
things
(http://www.experts-exchange.com/Data...r/Q_20863705.h
tml is the most useful page I've found so far). things I have tried are:
select o.*, s.*
from camps_info o,
(
SELECT sum(camps_info.camp_number), camp_number
FROM camps_info INNER JOIN camps_bookings ON camps_info.camp_number =
camps_bookings.camp_number
) s
where o.camp_number = s.camp_number
;
...which gives an error "The specified field 'camp_number' could refer to
more than one table listed in the FROM clause of your SQL statement"
I also tried:
Select c1.camp_number, num_bookings_on_this_camp
from camps_info c1, camps_info c2, (
SELECT sum(c2.camp_number) AS num_bookings_on_this_camp
FROM c2 INNER JOIN camps_bookings ON c2.camp_number =
camps_bookings.camp_number
)
WHERE (c2.camp_number=c1.camp_number)
;
..which gives the error "The Microsoft Jet database engine cannot find the
input table of query 'c2'. Make sure it exists and that its name is spelled
correctly".
Another thing I thought of was to somehow set up a function
NumberOfBookings(CampNumber) via an Access VBA module that ran my first
query for each camp. However, I haven't had much success with this, and
logic tells me there must be a way of doing this within SQL alone.
Does anyone have any tips on the SQL statement I should be using for my
query?
Very many thanks.
--Dan Evans.
dd*****@hotmail.com
