Totals Query

  • Thread starter Thread starter James
  • Start date Start date
J

James

I have the following code which I was given:

Well this SQL query:

--------------------------------
SELECT maintable.source, COUNT(maintable.source) AS Number
FROM maintable
GROUP BY maintable.source;
--------------------------------

will give you the number of each source.

This query:

--------------------------------
SELECT COUNT(*) AS NumberOfRecords
FROM maintable;
--------------------------------

will give the total number of records.

Lets call them Query1 and Query2 respectively, then I
think this query:

--------------------------------
SELECT Query1.source,
Query1.Number/Query2.NumberOfRecords*100 AS Percentage
FROM Query1, Query2;
--------------------------------


Ok so whats my source in the table please could you help
with this??

Also I have to get the combo boxes somehow into this query
does anyone out there have any idea how to do this?

Also this reserved word thing... I have done the
following: "Number" and the error goes away would the code
still run or would it treat Number as something diffrent
now?

Many Thanks

James
-----Original Message-----
Hmm.
The combo boxes must be bound to an object right. So, I
Click to expand...
dunno what exactly you need to do.
As for reserved words, well that's always a huge pain in
Click to expand...
the butt. You're going to have to get rid of them somehow.
Just change it for something else I guess.
 
I think that the following may give you what you are after

SELECT Maintable.source, Count(1) AS SourceCount, 100 *
Count([1])/(Select Count(*) FROM Maintable) AS SourcePercentage
FROM Maintable
GROUP BY Maintable.source;

Try to avoid using reserved words as column names. If you
must, then place them in []

You will have to repost to this thread to explain further
about including combo boxes in this query.

Hope This Helps
Gerald Stanley MCSD
 
Hi,

The code I have was given to me by someone else on here..
Will it work?

Also in your code below it mentiones Maintable.Source ok
fine... but what would I be looking for in regards to the
source???

And where you say SourceCount I take it this mean teh
Source mentioned above?

So will I only need the one query shown below or all three
which I have and add yours in?

As for the combo box....

Well I just have three combo boxes where I need there
values in the query so it can do the percentage of the the
total records by the selections made by the user in the
three combo boxes...

Many Thanks

James
-----Original Message-----
I think that the following may give you what you are after

SELECT Maintable.source, Count(1) AS SourceCount, 100 *
Count([1])/(Select Count(*) FROM Maintable) AS SourcePercentage
FROM Maintable
GROUP BY Maintable.source;

Try to avoid using reserved words as column names. If you
must, then place them in []

You will have to repost to this thread to explain further
about including combo boxes in this query.

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
I have the following code which I was given:

Well this SQL query:

--------------------------------
SELECT maintable.source, COUNT(maintable.source) AS Number
FROM maintable
GROUP BY maintable.source;
--------------------------------

will give you the number of each source.

This query:

--------------------------------
SELECT COUNT(*) AS NumberOfRecords
FROM maintable;
--------------------------------

will give the total number of records.

Lets call them Query1 and Query2 respectively, then I
think this query:

--------------------------------
SELECT Query1.source,
Query1.Number/Query2.NumberOfRecords*100 AS Percentage
FROM Query1, Query2;
--------------------------------


Ok so whats my source in the table please could you help
with this??

Also I have to get the combo boxes somehow into this query
does anyone out there have any idea how to do this?

Also this reserved word thing... I have done the
following: "Number" and the error goes away would the code
still run or would it treat Number as something diffrent
now?

Many Thanks

James
dunno what exactly you need to do. in
the butt. You're going to have to get rid of them somehow.
Just change it for something else I guess.

.
Click to expand...
.
Click to expand...
 
-----Original Message-----
Hi,

The code I have was given to me by someone else on here..
Will it work?
Click to expand...

As long as you enclose Number in [], it should be fine. It
is bad practice to use reserved words as column names which
is why I names the Count column as SourceCount.
Also in your code below it mentiones Maintable.Source ok
fine... but what would I be looking for in regards to the
source???
Click to expand...

Maintable.Source was taken directly from you original
queries. Please remember that we cannot see your table
structures so we take it on faith that you have quoted the
column names correctly
And where you say SourceCount I take it this mean teh
Source mentioned above?
Click to expand...

That is the name I gave to the count column. You can name
it whatever you like but bear in mind the previous comment
about using reserved words as column names.
So will I only need the one query shown below or all three
which I have and add yours in?
Click to expand...

My query should replace your 3 queries.

As for the combo box....

Well I just have three combo boxes where I need there
values in the query so it can do the percentage of the the
total records by the selections made by the user in the
three combo boxes...
Click to expand...

What are they selecting - different columns from your table
or different values from the same column?
Many Thanks

James
-----Original Message-----
I think that the following may give you what you are after

SELECT Maintable.source, Count(1) AS SourceCount, 100 *
Count([1])/(Select Count(*) FROM Maintable) AS SourcePercentage
FROM Maintable
GROUP BY Maintable.source;

Try to avoid using reserved words as column names. If you
must, then place them in []

You will have to repost to this thread to explain further
about including combo boxes in this query.

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
I have the following code which I was given:

Well this SQL query:

--------------------------------
SELECT maintable.source, COUNT(maintable.source) AS Number
FROM maintable
GROUP BY maintable.source;
--------------------------------

will give you the number of each source.

This query:

--------------------------------
SELECT COUNT(*) AS NumberOfRecords
FROM maintable;
--------------------------------

will give the total number of records.

Lets call them Query1 and Query2 respectively, then I
think this query:

--------------------------------
SELECT Query1.source,
Query1.Number/Query2.NumberOfRecords*100 AS Percentage
FROM Query1, Query2;
--------------------------------


Ok so whats my source in the table please could you help
with this??

Also I have to get the combo boxes somehow into this query
does anyone out there have any idea how to do this?

Also this reserved word thing... I have done the
following: "Number" and the error goes away would the code
still run or would it treat Number as something diffrent
now?

Many Thanks

James
-----Original Message-----
Hmm.
The combo boxes must be bound to an object right. So, I
dunno what exactly you need to do.
As for reserved words, well that's always a huge pain in
the butt. You're going to have to get rid of them somehow.
Just change it for something else I guess.
.

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
What are they selecting - different columns from your table
or different values from the same column?

Diffent Colums from diffent table(s)....


Maintable.Source was taken directly from you original
queries. Please remember that we cannot see your table
structures so we take it on faith that you have quoted the
column names correctly
Click to expand...

This I know...

What is the source though what sort of thing would I be
looking for? could you give me a brief example.. of a made
up table's and there source??

That way I could get an Idea of whta I am looking for...

Many Thanks

James


-----Original Message-----
-----Original Message-----
Hi,

The code I have was given to me by someone else on here..
Will it work?
Click to expand...

As long as you enclose Number in [], it should be fine. It
is bad practice to use reserved words as column names which
is why I names the Count column as SourceCount.
Also in your code below it mentiones Maintable.Source ok
fine... but what would I be looking for in regards to the
source???
Click to expand...

Maintable.Source was taken directly from you original
queries. Please remember that we cannot see your table
structures so we take it on faith that you have quoted the
column names correctly
And where you say SourceCount I take it this mean teh
Source mentioned above?
Click to expand...

That is the name I gave to the count column. You can name
it whatever you like but bear in mind the previous comment
about using reserved words as column names.
So will I only need the one query shown below or all three
which I have and add yours in?
Click to expand...

My query should replace your 3 queries.

As for the combo box....

Well I just have three combo boxes where I need there
values in the query so it can do the percentage of the the
total records by the selections made by the user in the
three combo boxes...
Click to expand...

What are they selecting - different columns from your table
or different values from the same column?
Many Thanks

James
-----Original Message-----
I think that the following may give you what you are after

SELECT Maintable.source, Count(1) AS SourceCount, 100 *
Count([1])/(Select Count(*) FROM Maintable) AS SourcePercentage
FROM Maintable
GROUP BY Maintable.source;

Try to avoid using reserved words as column names. If you
must, then place them in []

You will have to repost to this thread to explain further
about including combo boxes in this query.

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
I have the following code which I was given:

Well this SQL query:

--------------------------------
SELECT maintable.source, COUNT(maintable.source) AS Number
FROM maintable
GROUP BY maintable.source;
--------------------------------

will give you the number of each source.

This query:

--------------------------------
SELECT COUNT(*) AS NumberOfRecords
FROM maintable;
--------------------------------

will give the total number of records.

Lets call them Query1 and Query2 respectively, then I
think this query:

--------------------------------
SELECT Query1.source,
Query1.Number/Query2.NumberOfRecords*100 AS Percentage
FROM Query1, Query2;
--------------------------------


Ok so whats my source in the table please could you help
with this??

Also I have to get the combo boxes somehow into this query
does anyone out there have any idea how to do this?

Also this reserved word thing... I have done the
following: "Number" and the error goes away would the code
still run or would it treat Number as something diffrent
now?

Many Thanks

James
-----Original Message-----
Hmm.
The combo boxes must be bound to an object right. So, I
dunno what exactly you need to do.
As for reserved words, well that's always a huge pain in
the butt. You're going to have to get rid of them somehow.
Just change it for something else I guess.
.

.

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
-----Original Message-----
What are they selecting - different columns from your table
or different values from the same column?

Diffent Colums from diffent table(s)....


Maintable.Source was taken directly from you original

This I know...

What is the source though what sort of thing would I be
looking for? could you give me a brief example.. of a made
up table's and there source??
Click to expand...

Look at the table called Maintable in Design View and it
will show you the column names. One of these should be
called source.
That way I could get an Idea of whta I am looking for...

Many Thanks

James


-----Original Message-----
-----Original Message-----
Hi,

The code I have was given to me by someone else on here..
Will it work?
Click to expand...

As long as you enclose Number in [], it should be fine. It
is bad practice to use reserved words as column names which
is why I names the Count column as SourceCount.
Also in your code below it mentiones Maintable.Source ok
fine... but what would I be looking for in regards to the
source???
Click to expand...

Maintable.Source was taken directly from you original
queries. Please remember that we cannot see your table
structures so we take it on faith that you have quoted the
column names correctly
And where you say SourceCount I take it this mean teh
Source mentioned above?
Click to expand...

That is the name I gave to the count column. You can name
it whatever you like but bear in mind the previous comment
about using reserved words as column names.
So will I only need the one query shown below or all three
which I have and add yours in?
Click to expand...

My query should replace your 3 queries.

As for the combo box....

Well I just have three combo boxes where I need there
values in the query so it can do the percentage of the the
total records by the selections made by the user in the
three combo boxes...
Click to expand...

What are they selecting - different columns from your table
or different values from the same column?
Many Thanks

James
-----Original Message-----
I think that the following may give you what you are after

SELECT Maintable.source, Count(1) AS SourceCount, 100 *
Count([1])/(Select Count(*) FROM Maintable) AS
SourcePercentage
FROM Maintable
GROUP BY Maintable.source;

Try to avoid using reserved words as column names. If you
must, then place them in []

You will have to repost to this thread to explain further
about including combo boxes in this query.

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
I have the following code which I was given:

Well this SQL query:

--------------------------------
SELECT maintable.source, COUNT(maintable.source) AS
Number
FROM maintable
GROUP BY maintable.source;
--------------------------------

will give you the number of each source.

This query:

--------------------------------
SELECT COUNT(*) AS NumberOfRecords
FROM maintable;
--------------------------------

will give the total number of records.

Lets call them Query1 and Query2 respectively, then I
think this query:

--------------------------------
SELECT Query1.source,
Query1.Number/Query2.NumberOfRecords*100 AS Percentage
FROM Query1, Query2;
--------------------------------


Ok so whats my source in the table please could you help
with this??

Also I have to get the combo boxes somehow into this
query
does anyone out there have any idea how to do this?

Also this reserved word thing... I have done the
following: "Number" and the error goes away would the
code
still run or would it treat Number as something diffrent
now?

Many Thanks

James
-----Original Message-----
Hmm.
The combo boxes must be bound to an object right. So, I
dunno what exactly you need to do.
As for reserved words, well that's always a huge pain
in
the butt. You're going to have to get rid of them
somehow.
Just change it for something else I guess.
.

.

.

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Ok I have added a Field called Source... Is there any
relevance to this field?? or could it be changed?

Also how do I get the Combo Boxes encorportated into this?

By the fact that what ever is in the combo boxes it comes
up and tells you the percentage of what is in the database
to the selections made... so for example...

There are 20% of everything in the database which are Male
and to Q2 they answered False!!

Many Thanks

James
-----Original Message-----
-----Original Message-----
What are they selecting - different columns from your table
or different values from the same column?

Diffent Colums from diffent table(s)....


Maintable.Source was taken directly from you original

This I know...

What is the source though what sort of thing would I be
looking for? could you give me a brief example.. of a made
up table's and there source??
Click to expand...

Look at the table called Maintable in Design View and it
will show you the column names. One of these should be
called source.
That way I could get an Idea of whta I am looking for...

Many Thanks

James


-----Original Message-----

-----Original Message-----
Hi,

The code I have was given to me by someone else on here..
Will it work?

As long as you enclose Number in [], it should be
Click to expand...
fine.
It
is bad practice to use reserved words as column names which
is why I names the Count column as SourceCount.


Also in your code below it mentiones Maintable.Source ok
fine... but what would I be looking for in regards to the
source???

Maintable.Source was taken directly from you original
queries. Please remember that we cannot see your table
structures so we take it on faith that you have quoted the
column names correctly


And where you say SourceCount I take it this mean teh
Source mentioned above?

That is the name I gave to the count column. You can name
it whatever you like but bear in mind the previous comment
about using reserved words as column names.


So will I only need the one query shown below or all three
which I have and add yours in?

My query should replace your 3 queries.



As for the combo box....

Well I just have three combo boxes where I need there
values in the query so it can do the percentage of the the
total records by the selections made by the user in the
three combo boxes...


What are they selecting - different columns from your table
or different values from the same column?

Many Thanks

James
-----Original Message-----
I think that the following may give you what you are after

SELECT Maintable.source, Count(1) AS SourceCount, 100 *
Count([1])/(Select Count(*) FROM Maintable) AS
SourcePercentage
FROM Maintable
GROUP BY Maintable.source;

Try to avoid using reserved words as column names.
Click to expand...
If
you
must, then place them in []

You will have to repost to this thread to explain further
about including combo boxes in this query.

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
I have the following code which I was given:

Well this SQL query:

--------------------------------
SELECT maintable.source, COUNT(maintable.source) AS
Number
FROM maintable
GROUP BY maintable.source;
--------------------------------

will give you the number of each source.

This query:

--------------------------------
SELECT COUNT(*) AS NumberOfRecords
FROM maintable;
--------------------------------

will give the total number of records.

Lets call them Query1 and Query2 respectively, then I
think this query:

--------------------------------
SELECT Query1.source,
Query1.Number/Query2.NumberOfRecords*100 AS Percentage
FROM Query1, Query2;
--------------------------------


Ok so whats my source in the table please could you help
with this??

Also I have to get the combo boxes somehow into this
query
does anyone out there have any idea how to do this?

Also this reserved word thing... I have done the
following: "Number" and the error goes away would the
code
still run or would it treat Number as something diffrent
now?

Many Thanks

James
-----Original Message-----
Hmm.
The combo boxes must be bound to an object right.
Click to expand...
So,
I
dunno what exactly you need to do.
As for reserved words, well that's always a huge pain
in
the butt. You're going to have to get rid of them
somehow.
Just change it for something else I guess.
.

.

.

.

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
I am getting the impression that the original SQL given to
you by your colleague were for example only and had nothing
to do with your database. That being the case, you should
remove the column Source from your table but post to this
thread all the column names that it does have.

Thanks
Gerald Stanley MCSD
-----Original Message-----
Ok I have added a Field called Source... Is there any
relevance to this field?? or could it be changed?

Also how do I get the Combo Boxes encorportated into this?

By the fact that what ever is in the combo boxes it comes
up and tells you the percentage of what is in the database
to the selections made... so for example...

There are 20% of everything in the database which are Male
and to Q2 they answered False!!

Many Thanks

James
-----Original Message-----
-----Original Message-----
What are they selecting - different columns from your table
or different values from the same column?

Diffent Colums from diffent table(s)....


Maintable.Source was taken directly from you original
queries. Please remember that we cannot see your table
structures so we take it on faith that you have quoted the
column names correctly

This I know...

What is the source though what sort of thing would I be
looking for? could you give me a brief example.. of a made
up table's and there source??
Click to expand...

Look at the table called Maintable in Design View and it
will show you the column names. One of these should be
called source.
That way I could get an Idea of whta I am looking for...

Many Thanks

James



-----Original Message-----

-----Original Message-----
Hi,

The code I have was given to me by someone else on
here..
Will it work?

As long as you enclose Number in [], it should be fine.
It
is bad practice to use reserved words as column names
which
is why I names the Count column as SourceCount.


Also in your code below it mentiones Maintable.Source ok
fine... but what would I be looking for in regards to
the
source???

Maintable.Source was taken directly from you original
queries. Please remember that we cannot see your table
structures so we take it on faith that you have quoted the
column names correctly


And where you say SourceCount I take it this mean teh
Source mentioned above?

That is the name I gave to the count column. You can name
it whatever you like but bear in mind the previous comment
about using reserved words as column names.


So will I only need the one query shown below or all
three
which I have and add yours in?

My query should replace your 3 queries.



As for the combo box....

Well I just have three combo boxes where I need there
values in the query so it can do the percentage of the
the
total records by the selections made by the user in the
three combo boxes...


What are they selecting - different columns from your
table
or different values from the same column?

Many Thanks

James
-----Original Message-----
I think that the following may give you what you are
after

SELECT Maintable.source, Count(1) AS SourceCount, 100 *
Count([1])/(Select Count(*) FROM Maintable) AS
SourcePercentage
FROM Maintable
GROUP BY Maintable.source;

Try to avoid using reserved words as column names. If
you
must, then place them in []

You will have to repost to this thread to explain
further
about including combo boxes in this query.

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
I have the following code which I was given:

Well this SQL query:

--------------------------------
SELECT maintable.source, COUNT(maintable.source) AS
Number
FROM maintable
GROUP BY maintable.source;
--------------------------------

will give you the number of each source.

This query:

--------------------------------
SELECT COUNT(*) AS NumberOfRecords
FROM maintable;
--------------------------------

will give the total number of records.

Lets call them Query1 and Query2 respectively, then I
think this query:

--------------------------------
SELECT Query1.source,
Query1.Number/Query2.NumberOfRecords*100 AS Percentage
FROM Query1, Query2;
--------------------------------


Ok so whats my source in the table please could you
help
with this??

Also I have to get the combo boxes somehow into this
query
does anyone out there have any idea how to do this?

Also this reserved word thing... I have done the
following: "Number" and the error goes away would the
code
still run or would it treat Number as something
diffrent
now?

Many Thanks

James
-----Original Message-----
Hmm.
The combo boxes must be bound to an object right. So,
I
dunno what exactly you need to do.
As for reserved words, well that's always a huge pain
in
the butt. You're going to have to get rid of them
somehow.
Just change it for something else I guess.
.

.

.

.

.

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Yes it was sorry for not mentioning it...

Well it has 4 tables...

tblGender

GenderID (AutoNumber)
Gender (Text)

tblQuestion

QuestionID (AutoNumber)
Question (Text) Just has question numbers in this from Q01
to Q25

tblAnswer

AnswerID (AutoNumber)
Answer (Text) Just True or False

tblMain

RespondantID (AutoNumber)
Gender (Text)
Question (Text)
Answer (Text)

I have some relationships which are the ID's from the
tblGender,tblAnswer and tblQuestion to the relevant fields
in the tblMain

Does this help?

Many Thanks

James
-----Original Message-----
I am getting the impression that the original SQL given to
you by your colleague were for example only and had nothing
to do with your database. That being the case, you should
remove the column Source from your table but post to this
thread all the column names that it does have.

Thanks
Gerald Stanley MCSD
-----Original Message-----
Ok I have added a Field called Source... Is there any
relevance to this field?? or could it be changed?

Also how do I get the Combo Boxes encorportated into this?

By the fact that what ever is in the combo boxes it comes
up and tells you the percentage of what is in the database
to the selections made... so for example...

There are 20% of everything in the database which are Male
and to Q2 they answered False!!

Many Thanks

James
-----Original Message-----

-----Original Message-----
What are they selecting - different columns from your table
or different values from the same column?

Diffent Colums from diffent table(s)....


Maintable.Source was taken directly from you original
queries. Please remember that we cannot see your table
structures so we take it on faith that you have
Click to expand...
quoted
the
column names correctly

This I know...

What is the source though what sort of thing would I be
looking for? could you give me a brief example.. of a made
up table's and there source??

Look at the table called Maintable in Design View and it
will show you the column names. One of these should be
called source.


That way I could get an Idea of whta I am looking for...

Many Thanks

James



-----Original Message-----

-----Original Message-----
Hi,

The code I have was given to me by someone else on
here..
Will it work?

As long as you enclose Number in [], it should be fine.
It
is bad practice to use reserved words as column names
which
is why I names the Count column as SourceCount.


Also in your code below it mentiones
Click to expand...
Maintable.Source
ok
fine... but what would I be looking for in regards to
the
source???

Maintable.Source was taken directly from you original
queries. Please remember that we cannot see your table
structures so we take it on faith that you have
Click to expand...
quoted
the
column names correctly


And where you say SourceCount I take it this mean teh
Source mentioned above?

That is the name I gave to the count column. You can name
it whatever you like but bear in mind the previous comment
about using reserved words as column names.


So will I only need the one query shown below or all
three
which I have and add yours in?

My query should replace your 3 queries.



As for the combo box....

Well I just have three combo boxes where I need there
values in the query so it can do the percentage of the
the
total records by the selections made by the user in the
three combo boxes...


What are they selecting - different columns from your
table
or different values from the same column?

Many Thanks

James
-----Original Message-----
I think that the following may give you what you are
after

SELECT Maintable.source, Count(1) AS SourceCount,
Click to expand...
100
*
Count([1])/(Select Count(*) FROM Maintable) AS
SourcePercentage
FROM Maintable
GROUP BY Maintable.source;

Try to avoid using reserved words as column names. If
you
must, then place them in []

You will have to repost to this thread to explain
further
about including combo boxes in this query.

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
I have the following code which I was given:

Well this SQL query:

--------------------------------
SELECT maintable.source, COUNT(maintable.source) AS
Number
FROM maintable
GROUP BY maintable.source;
--------------------------------

will give you the number of each source.

This query:

--------------------------------
SELECT COUNT(*) AS NumberOfRecords
FROM maintable;
--------------------------------

will give the total number of records.

Lets call them Query1 and Query2 respectively,
Click to expand...
then
I
think this query:

--------------------------------
SELECT Query1.source,
Query1.Number/Query2.NumberOfRecords*100 AS Percentage
FROM Query1, Query2;
--------------------------------


Ok so whats my source in the table please could you
help
with this??

Also I have to get the combo boxes somehow into this
query
does anyone out there have any idea how to do this?

Also this reserved word thing... I have done the
following: "Number" and the error goes away would the
code
still run or would it treat Number as something
diffrent
now?

Many Thanks

James
-----Original Message-----
Hmm.
The combo boxes must be bound to an object right. So,
I
dunno what exactly you need to do.
As for reserved words, well that's always a huge pain
in
the butt. You're going to have to get rid of them
somehow.
Just change it for something else I guess.
.

.

.

.

.

.

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
The query to give the number of rows in tblMain that match
the 3 selections in the comboBoxes cboGender, cboQuestion
and cboAnswer on form frmMain and express that number as a
percentage of all the rows in tblMain will be

SELECT Count(1) AS ReturnCount, 100 * Count(1)/(Select
Count(*) FROM tblMain) AS ReturnPercentage
FROM tblMain
WHERE gender= Forms!frmMain!cboGender AND question =
Forms!frmMain!cboQuestion AND answer = Forms!frmMain!cboAnswer

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
Yes it was sorry for not mentioning it...

Well it has 4 tables...

tblGender

GenderID (AutoNumber)
Gender (Text)

tblQuestion

QuestionID (AutoNumber)
Question (Text) Just has question numbers in this from Q01
to Q25

tblAnswer

AnswerID (AutoNumber)
Answer (Text) Just True or False

tblMain

RespondantID (AutoNumber)
Gender (Text)
Question (Text)
Answer (Text)

I have some relationships which are the ID's from the
tblGender,tblAnswer and tblQuestion to the relevant fields
in the tblMain

Does this help?

Many Thanks

James
-----Original Message-----
I am getting the impression that the original SQL given to
you by your colleague were for example only and had nothing
to do with your database. That being the case, you should
remove the column Source from your table but post to this
thread all the column names that it does have.

Thanks
Gerald Stanley MCSD
-----Original Message-----
Ok I have added a Field called Source... Is there any
relevance to this field?? or could it be changed?

Also how do I get the Combo Boxes encorportated into this?

By the fact that what ever is in the combo boxes it comes
up and tells you the percentage of what is in the database
to the selections made... so for example...

There are 20% of everything in the database which are Male
and to Q2 they answered False!!

Many Thanks

James

-----Original Message-----

-----Original Message-----
What are they selecting - different columns from your
table
or different values from the same column?

Diffent Colums from diffent table(s)....


Maintable.Source was taken directly from you original
queries. Please remember that we cannot see your table
structures so we take it on faith that you have quoted
the
column names correctly

This I know...

What is the source though what sort of thing would I be
looking for? could you give me a brief example.. of a
made
up table's and there source??

Look at the table called Maintable in Design View and it
will show you the column names. One of these should be
called source.


That way I could get an Idea of whta I am looking for...

Many Thanks

James



-----Original Message-----

-----Original Message-----
Hi,

The code I have was given to me by someone else on
here..
Will it work?

As long as you enclose Number in [], it should be
fine.
It
is bad practice to use reserved words as column names
which
is why I names the Count column as SourceCount.


Also in your code below it mentiones Maintable.Source
ok
fine... but what would I be looking for in regards to
the
source???

Maintable.Source was taken directly from you original
queries. Please remember that we cannot see your table
structures so we take it on faith that you have quoted
the
column names correctly


And where you say SourceCount I take it this mean teh
Source mentioned above?

That is the name I gave to the count column. You can
name
it whatever you like but bear in mind the previous
comment
about using reserved words as column names.


So will I only need the one query shown below or all
three
which I have and add yours in?

My query should replace your 3 queries.



As for the combo box....

Well I just have three combo boxes where I need there
values in the query so it can do the percentage of the
the
total records by the selections made by the user in
the
three combo boxes...


What are they selecting - different columns from your
table
or different values from the same column?

Many Thanks

James
-----Original Message-----
I think that the following may give you what you are
after

SELECT Maintable.source, Count(1) AS SourceCount, 100
*
Count([1])/(Select Count(*) FROM Maintable) AS
SourcePercentage
FROM Maintable
GROUP BY Maintable.source;

Try to avoid using reserved words as column names.
If
you
must, then place them in []

You will have to repost to this thread to explain
further
about including combo boxes in this query.

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
I have the following code which I was given:

Well this SQL query:

--------------------------------
SELECT maintable.source, COUNT(maintable.source) AS
Number
FROM maintable
GROUP BY maintable.source;
--------------------------------

will give you the number of each source.

This query:

--------------------------------
SELECT COUNT(*) AS NumberOfRecords
FROM maintable;
--------------------------------

will give the total number of records.

Lets call them Query1 and Query2 respectively, then
I
think this query:

--------------------------------
SELECT Query1.source,
Query1.Number/Query2.NumberOfRecords*100 AS
Percentage
FROM Query1, Query2;
--------------------------------


Ok so whats my source in the table please could you
help
with this??

Also I have to get the combo boxes somehow into this
query
does anyone out there have any idea how to do this?

Also this reserved word thing... I have done the
following: "Number" and the error goes away would
the
code
still run or would it treat Number as something
diffrent
now?

Many Thanks

James
-----Original Message-----
Hmm.
The combo boxes must be bound to an object right.
So,
I
dunno what exactly you need to do.
As for reserved words, well that's always a huge
pain
in
the butt. You're going to have to get rid of them
somehow.
Just change it for something else I guess.
.

.

.

.

.

.

.

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
So just to clarify the code below would indeed get the
values in the ComboBoxes and then get a percentage of them
values in the database by the total number of records?

If so good.

Many Thanks for you assistance on this matter I really do
appreciate it.

James
-----Original Message-----
The query to give the number of rows in tblMain that match
the 3 selections in the comboBoxes cboGender, cboQuestion
and cboAnswer on form frmMain and express that number as a
percentage of all the rows in tblMain will be

SELECT Count(1) AS ReturnCount, 100 * Count(1)/(Select
Count(*) FROM tblMain) AS ReturnPercentage
FROM tblMain
WHERE gender= Forms!frmMain!cboGender AND question =
Forms!frmMain!cboQuestion AND answer = Forms!frmMain! cboAnswer

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
Yes it was sorry for not mentioning it...

Well it has 4 tables...

tblGender

GenderID (AutoNumber)
Gender (Text)

tblQuestion

QuestionID (AutoNumber)
Question (Text) Just has question numbers in this from Q01
to Q25

tblAnswer

AnswerID (AutoNumber)
Answer (Text) Just True or False

tblMain

RespondantID (AutoNumber)
Gender (Text)
Question (Text)
Answer (Text)

I have some relationships which are the ID's from the
tblGender,tblAnswer and tblQuestion to the relevant fields
in the tblMain

Does this help?

Many Thanks

James
-----Original Message-----
I am getting the impression that the original SQL given to
you by your colleague were for example only and had nothing
to do with your database. That being the case, you should
remove the column Source from your table but post to this
thread all the column names that it does have.

Thanks
Gerald Stanley MCSD
-----Original Message-----
Ok I have added a Field called Source... Is there any
relevance to this field?? or could it be changed?

Also how do I get the Combo Boxes encorportated into this?

By the fact that what ever is in the combo boxes it comes
up and tells you the percentage of what is in the database
to the selections made... so for example...

There are 20% of everything in the database which are Male
and to Q2 they answered False!!

Many Thanks

James

-----Original Message-----

-----Original Message-----
What are they selecting - different columns from your
table
or different values from the same column?

Diffent Colums from diffent table(s)....


Maintable.Source was taken directly from you original
queries. Please remember that we cannot see your table
structures so we take it on faith that you have quoted
the
column names correctly

This I know...

What is the source though what sort of thing would I be
looking for? could you give me a brief example.. of a
made
up table's and there source??

Look at the table called Maintable in Design View and it
will show you the column names. One of these should be
called source.


That way I could get an Idea of whta I am looking for...

Many Thanks

James



-----Original Message-----

-----Original Message-----
Hi,

The code I have was given to me by someone else on
here..
Will it work?

As long as you enclose Number in [], it should be
fine.
It
is bad practice to use reserved words as column names
which
is why I names the Count column as SourceCount.


Also in your code below it mentiones Maintable.Source
ok
fine... but what would I be looking for in regards to
the
source???

Maintable.Source was taken directly from you original
queries. Please remember that we cannot see your table
structures so we take it on faith that you have quoted
the
column names correctly


And where you say SourceCount I take it this mean teh
Source mentioned above?

That is the name I gave to the count column. You can
name
it whatever you like but bear in mind the previous
comment
about using reserved words as column names.


So will I only need the one query shown below or all
three
which I have and add yours in?

My query should replace your 3 queries.



As for the combo box....

Well I just have three combo boxes where I need there
values in the query so it can do the percentage of the
the
total records by the selections made by the user in
the
three combo boxes...


What are they selecting - different columns from your
table
or different values from the same column?

Many Thanks

James
-----Original Message-----
I think that the following may give you what you are
after

SELECT Maintable.source, Count(1) AS SourceCount, 100
*
Count([1])/(Select Count(*) FROM Maintable) AS
SourcePercentage
FROM Maintable
GROUP BY Maintable.source;

Try to avoid using reserved words as column names.
If
you
must, then place them in []

You will have to repost to this thread to explain
further
about including combo boxes in this query.

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
I have the following code which I was given:

Well this SQL query:

--------------------------------
SELECT maintable.source, COUNT(maintable.source) AS
Number
FROM maintable
GROUP BY maintable.source;
--------------------------------

will give you the number of each source.

This query:

--------------------------------
SELECT COUNT(*) AS NumberOfRecords
FROM maintable;
--------------------------------

will give the total number of records.

Lets call them Query1 and Query2 respectively, then
I
think this query:

--------------------------------
SELECT Query1.source,
Query1.Number/Query2.NumberOfRecords*100 AS
Percentage
FROM Query1, Query2;
--------------------------------


Ok so whats my source in the table please could you
help
with this??

Also I have to get the combo boxes somehow into this
query
does anyone out there have any idea how to do this?

Also this reserved word thing... I have done the
following: "Number" and the error goes away would
the
code
still run or would it treat Number as something
diffrent
now?

Many Thanks

James
-----Original Message-----
Hmm.
The combo boxes must be bound to an object right.
So,
I
dunno what exactly you need to do.
As for reserved words, well that's always a huge
pain
in
the butt. You're going to have to get rid of them
somehow.
Just change it for something else I guess.
.

.

.

.

.

.

.

.

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