Need Idiots guide to this please

  • Thread starter Thread starter Sal
  • Start date Start date
S

Sal

Good day

I have created just one table that holds all the
information that I need to work with.
I work for a firm of Surveyors and need to be able to
analysis imformation.
I have set up fields as follows:
Surveyor, Office, Source, Type etc.
We need to be able to see where our work is sourced and
be able to see in any one month what percentage of our
work comes from reommendations. This information is held
in our source field, which has up to 12 options (listed
as R,P,W etc)
If anyone can help me out here, please, please can it be
in real step-by-step format.
I am a very limited user.
Thanks in anticipation.
KR
S
 
Well this SQL query

-------------------------------
SELECT maintable.source, COUNT(maintable.source) AS Numbe
FROM maintabl
GROUP BY maintable.source
-------------------------------

will give you the number of each source

This query

-------------------------------
SELECT COUNT(*) AS NumberOfRecord
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 Percentag
FROM Query1, Query2
-------------------------------

Will give the percentage of each

So just create those three queries, go into SQL view, and paste that SQL, then the third query should do the job

Of course that query can then be bound to a form - you know how to do that, right; if not, it should be documented in access itself
 
Hi
Just got in. Thank you for your help, I will try it out.
Kindest regards.
S
-----Original Message-----
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:
Click to expand...
Query1.Number/Query2.NumberOfRecords*100 AS Percentage
FROM Query1, Query2;
--------------------------------

Will give the percentage of each.

So just create those three queries, go into SQL view,
Click to expand...
and paste that SQL, then the third query should do the
job.
Of course that query can then be bound to a form - you
Click to expand...
know how to do that, right; if not, it should be
documented in access itself.
 
Back
Top