SQL subquery problem - bookings database

  • Thread starter Thread starter Dan Evans
  • Start date Start date
D

Dan Evans

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/Databases/Microsoft_SQL_Server/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.

(e-mail address removed)
 
Dan Evans said:
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.
Click to expand...

news://comp.database.ms-access
 
Try ...

SELECT
camps_info.camp_number,
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

Group by camps_info.camp_number;



-----Original Message-----
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/Databases/Microsoft_SQL_Server/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
Click to expand...
field 'camp_number' could refer to
 
I think you can change your WHERE .. statement to GROUP BY o.camp_number



Dan said:
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/Databases/Microsoft_SQL_Server/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.

(e-mail address removed)
Click to expand...
 
Chris,

Many thanks for your suggestion. I agree that looks like a good and more
straightforward way of doing it....

HOWEVER, when I run it, I get a "Data type mismatch in criteria expression"
error. From what I can track down, this usually seems to relate to the wrong
data types being in the wrong places... so I am thinking that it is
complaining about the fact that in the first line sum() is an expression but
camps_info.camp_number a list of fields (sorry don't know the technical
term)?

.... thanks also to Gary for the 'change WHERE .. statement to GROUP BY
o.camp_number' suggestion. I'm not having much luck with that either. I've
currently got:

select o.camp_number, s.the_total
from camps_info o,
(SELECT sum(camps_info.camp_number) as the_total, camps_info.camp_number
FROM camps_info INNER JOIN camps_bookings ON
camps_info.camp_number=camps_bookings.camp_number) s
group by o.camp_number;

which is giving the error "You tried to execute a query that does not
include the specified expression 'camp number' as part of an agregate
function. If I replace the first line with:
select o.*, s.*
then I get an error "Cannot group on fields selected with * (o). [The same
error also comes up with (s) if I put it as select o.camp_number, s.*

Anyone got any other tips. I'm sure this can't be all that hard but it is
driving me crazy!

Many thanks.

--Dan Evans.
(e-mail address removed)




Chris said:
Try ...

SELECT
camps_info.camp_number,
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

Group by camps_info.camp_number;



-----Original Message-----
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/Databases/Microsoft_SQL_Server/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
Click to expand...
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.

(e-mail address removed)


.
Click to expand...
Click to expand...
 
Folks,

Just to let you know I have now solved it. It was far easier than I was
really making it:

SELECT camp_number, count(*) as counted
from camps_bookings
group by camp_number;

that's it!!!

Cheers --Dan


Dan Evans said:
Chris,

Many thanks for your suggestion. I agree that looks like a good and more
straightforward way of doing it....

HOWEVER, when I run it, I get a "Data type mismatch in criteria expression"
error. From what I can track down, this usually seems to relate to the wrong
data types being in the wrong places... so I am thinking that it is
complaining about the fact that in the first line sum() is an expression but
camps_info.camp_number a list of fields (sorry don't know the technical
term)?

... thanks also to Gary for the 'change WHERE .. statement to GROUP BY
o.camp_number' suggestion. I'm not having much luck with that either. I've
currently got:

select o.camp_number, s.the_total
from camps_info o,
(SELECT sum(camps_info.camp_number) as the_total, camps_info.camp_number
FROM camps_info INNER JOIN camps_bookings ON
camps_info.camp_number=camps_bookings.camp_number) s
group by o.camp_number;

which is giving the error "You tried to execute a query that does not
include the specified expression 'camp number' as part of an agregate
function. If I replace the first line with:
select o.*, s.*
then I get an error "Cannot group on fields selected with * (o). [The same
error also comes up with (s) if I put it as select o.camp_number, s.*

Anyone got any other tips. I'm sure this can't be all that hard but it is
driving me crazy!

Many thanks.

--Dan Evans.
(e-mail address removed)




Chris said:
Try ...

SELECT
camps_info.camp_number,
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

Group by camps_info.camp_number;



-----Original Message-----
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/Databases/Microsoft_SQL_Server/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
Click to expand...
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.

(e-mail address removed)


.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top