Not sure where to begin on this one.....

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Table is Member and one of the fields is ZipCode. I need a query that lists
each zip code, the number of members in that zip code, and if possible, what
is the percent of all members living in that zip code.

I'm clueless where to even begin on this.

tia.
 
HB said:
Table is Member and one of the fields is ZipCode. I need a query that
lists
each zip code, the number of members in that zip code, and if possible,
what
is the percent of all members living in that zip code.

I'm clueless where to even begin on this.
Click to expand...

I used three queries:

Counts:
SELECT Members.[Zip Code], Count(Members.[Zip Code]) AS [CountOfZip Code]
FROM Members
GROUP BY Members.[Zip Code];

Total:
SELECT Sum(Counts.[CountOfZip Code]) AS [SumOfCountOfZip Code]
FROM Counts;

And finally, Percents:
SELECT Counts.[Zip Code], Counts.[CountOfZip Code], [CountOfZip
Code]/[SumOfCountOfZip Code]*100 AS [Percent]
FROM Counts, Total;

If you run the Percents query, the percents for each Zip Code will be given.
There's probably an easier way but this works.

Tom Lake
 
I was right. There IS a simpler way. I got it down to ONE query:

SELECT Members.[Zip Code], Count(Members.[Zip Code]) AS [CountOfZip Code],
DCount("[Zip Code]","Members","[Zip Code]='" & [Zip Code] &
"'")/DCount("[Zip Code]","Members")*100 AS Total
FROM Members
GROUP BY Members.[Zip Code], DCount("[Zip Code]","Members","[Zip Code]='" &
[Zip Code] & "'")/DCount("[Zip Code]","Members")*100;

Tom Lake
 
Thanks Tom, but I had an epiphany and did it this way. I think the same as
you (I do it in design view, copied this from SQL view)

SELECT MEMBER.ZipCode, Count(MEMBER.ZipCode) AS CountOfZipCode
FROM MEMBER
WHERE (((MEMBER.EquityStatus)="CURRENT"))
GROUP BY MEMBER.ZipCode;

And for percent, added calculated controls to the report and it worked!
Thanks for your help though.

Tom Lake said:
I was right. There IS a simpler way. I got it down to ONE query:

SELECT Members.[Zip Code], Count(Members.[Zip Code]) AS [CountOfZip Code],
DCount("[Zip Code]","Members","[Zip Code]='" & [Zip Code] &
"'")/DCount("[Zip Code]","Members")*100 AS Total
FROM Members
GROUP BY Members.[Zip Code], DCount("[Zip Code]","Members","[Zip Code]='" &
[Zip Code] & "'")/DCount("[Zip Code]","Members")*100;

Tom Lake
Click to expand...
 
Back
Top