Help for MS Access homework

[riccelalsim]

For our homework in our Computer science class, we were tasked to research by
asking expert advice on database forums. We were first asked to download
Census data to be analyzed using Microsoft Access. The data contained facts
about 1% all people living in large US Cities at the time on the last US
Census in the year 2000. The file gave the name of the city and the
racial/ethnic ancestry of the person concerned.

I was able to achieve the objectives of the first task by illustrating the ff
terms: fields, records, key fields, primary keys, foreign keys, relationship,
and SQL.

To finish the task, we need to create a sorted list showing the number of
Filipinos living in each city (with the cities with the largest number of
Filipinos at the top).

I am not sure how to do this exactly. I need your advice on how to adapt the
fragment of code below to show:

a) Two columns - the Filipino Population and the Total Population
b) The Filipino Population as a percentage of the total population

The code is:

SELECT DISTINCTROW POPDATA.CITYNUM
COUNT (*) FROM POPDATA
WHERE POPDATA.ANCNUMBER=720
GROUP BY POPDATA.CITYNUM
ORDER BY COUNT (*) DESC;

Thank you so much! I would really appreciate your advice on this.

[Bob Barrows]

riccelalsim wrote:
> For our homework in our Computer science class, we were tasked to
> research by asking expert advice on database forums. We were first
> asked to download Census data to be analyzed using Microsoft Access.

This is a SQL Server group so you are less likely to get relevant help
here than if you had posted your question to a relevant group like
microsoft.public.access.queries. So you only get partial credit so far
;-)

> The data contained facts about 1% all people living in large US
> Cities at the time on the last US Census in the year 2000. The file
> gave the name of the city and the racial/ethnic ancestry of the
> person concerned.
>
>

> To finish the task, we need to create a sorted list showing the
> number of Filipinos living in each city (with the cities with the
> largest number of Filipinos at the top).

Well, the code below, while not quite correct, does give the answer to
that question, i think.

>
> I am not sure how to do this exactly. I need your advice on how to
> adapt the fragment of code below to show:
>
> a) Two columns - the Filipino Population and the Total Population

of each city? or the country as a whole?

> b) The Filipino Population as a percentage of the total population

same question: of each city? or the country as a whole?
I will assum you need this broken out by city. And that you need these
displayed in a single query

>
> The code is:
>
> SELECT DISTINCTROW POPDATA.CITYNUM

You appear to have left out a comma here

> COUNT (*) FROM POPDATA
> WHERE POPDATA.ANCNUMBER=720
> GROUP BY POPDATA.CITYNUM
> ORDER BY COUNT (*) DESC;
>
> Thank you so much! I would really appreciate your advice on this.

For starters, the DISTINCTROW keyword is not relevant - you need to look
it up in Access online help to see what its real purpose is.

There are several ways to approach your problems: correlated subqueries,
etc. Here is a way to avoid the performance problems inherent with
correlated subqueries:

SELECT CITYNUM,
SUM(Iif(ANCNUMBER=720,1,0)) AS FilipinoPopulation,
COUNT(*) AS TotalPopulation,
SUM(Iif(ANCNUMBER=720,1,0))/COUNT(*) AS PercentFilipino
FROM POPDATA
GROUP BY CITYNUM
ORDER BY SUM(Iif(ANCNUMBER=720,1,0)) DESC

--
HTH,
Bob Barrows

[riccelalsim]

Thank you very much! :D I'll try to work on that. :)

[riccelalsim]

>This is a SQL Server group so you are less likely to get relevant help
>here than if you had posted your question to a relevant group like
>microsoft.public.access.queries. So you only get partial credit so far
>;-)

Haha. Okay noted. :D I posted my question here and in
http://forums.mysql.com/ :D Thank you very much for your advice here :D

>> a) Two columns - the Filipino Population and the Total Population
>
>of each city? or the country as a whole?
>
>> b) The Filipino Population as a percentage of the total population
>
>same question: of each city? or the country as a whole?
>I will assum you need this broken out by city. And that you need these
>displayed in a single query
>
>> The code is:
>>
>> SELECT DISTINCTROW POPDATA.CITYNUM

We have to get the population of the Filipinos and the percentage as a whole :
))

>For starters, the DISTINCTROW keyword is not relevant - you need to look
>it up in Access online help to see what its real purpose is.

Noted ;)

>There are several ways to approach your problems: correlated subqueries,
>etc. Here is a way to avoid the performance problems inherent with
>correlated subqueries:
>
>SELECT CITYNUM,
>SUM(Iif(ANCNUMBER=720,1,0)) AS FilipinoPopulation,
>COUNT(*) AS TotalPopulation,
>SUM(Iif(ANCNUMBER=720,1,0))/COUNT(*) AS PercentFilipino
>FROM POPDATA
>GROUP BY CITYNUM
>ORDER BY SUM(Iif(ANCNUMBER=720,1,0)) DESC

Wait, so if it's for the whole country, how am I gonna go about it? :o

Sorry, first time user. :)

[Bob Barrows]

riccelalsim wrote:
>
>> There are several ways to approach your problems: correlated
>> subqueries, etc. Here is a way to avoid the performance problems
>> inherent with correlated subqueries:
>>
>> SELECT CITYNUM,
>> SUM(Iif(ANCNUMBER=720,1,0)) AS FilipinoPopulation,
>> COUNT(*) AS TotalPopulation,
>> SUM(Iif(ANCNUMBER=720,1,0))/COUNT(*) AS PercentFilipino
>> FROM POPDATA
>> GROUP BY CITYNUM
>> ORDER BY SUM(Iif(ANCNUMBER=720,1,0)) DESC
>
> Wait, so if it's for the whole country, how am I gonna go about it? :o
>
> Sorry, first time user. :)

Remove CITYNUM from the query (along with the ORDER BY):

SELECT

SUM(Iif(ANCNUMBER=720,1,0)) AS FilipinoPopulation,
COUNT(*) AS TotalPopulation,
SUM(Iif(ANCNUMBER=720,1,0))/COUNT(*) AS PercentFilipino
FROM POPDATA

--
HTH,
Bob Barrows