By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
458,193 Members | 1,691 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 458,193 IT Pros & Developers. It's quick & easy.

SQL subquery problem - bookings database

P: n/a
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
Jul 19 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
I think you can change your WHERE .. statement to GROUP BY o.camp_number

Dan Evans wrote:
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

Jul 19 '05 #2

P: n/a
I think you can change your WHERE .. statement to GROUP BY o.camp_number

Dan Evans wrote:
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

Jul 19 '05 #3

P: n/a
I think you can change your WHERE .. statement to GROUP BY o.camp_number

Dan Evans wrote:
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

Jul 19 '05 #4

P: n/a

"Dan Evans" <dd*****@hotmail.com> wrote in message
news:40***********************@mercury.nildram.net ...

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.


news://comp.database.ms-access
Jul 19 '05 #5

P: n/a

"Dan Evans" <dd*****@hotmail.com> wrote in message
news:40***********************@mercury.nildram.net ...

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.


news://comp.database.ms-access
Jul 19 '05 #6

P: n/a

"Dan Evans" <dd*****@hotmail.com> wrote in message
news:40***********************@mercury.nildram.net ...

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.


news://comp.database.ms-access
Jul 19 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.