using Count() (1 Viewer)

mcalex

Registered User.
Local time
Today, 22:28
Joined
Jun 18, 2009
Messages
141
I'm trying to do a tally of election types using count, but am getting the wrong answer.

In the database, LocalGovernments have Wards and Ward have Elections. An election can be one of two ElectionTypes ('In Person' or 'Postal'). Every Ward in a LocalGovernment has the same ElectionType.

I want a count of the number of LocalGovernments having each type of election.

I can do a query showing the ElectionType for each LocalGovernment, which correctly only outputs one row per LocalGovernment, but when I add totals to this query in the designer to get the count and then run it, it counts all the elections in all the _Wards_. Trying unique records | unique rows in the query properties doesn't change the result.

I can do a second query on the working query to get my answer, but I'm sure it can be done using only one query, and I'd prefer it that way.

Many thanks for any pointers.
mcalex
 

plog

Banishment Pending
Local time
Today, 09:28
Joined
May 11, 2011
Messages
11,611
I don't think it can be done in one query with your table structure. I believe you need an aggregate query based on an aggregate query and in that instance its going to take 2 queries.

However, this depends on your table structure which I think might be wrong. Where exactly is the election type field stored? Is it in Government table, Ward table or Election table? From your explanation it is an element of the Government and should be in that table, but in your query description it sounds like it is a field in the Election table.
 

jdraw

Super Moderator
Staff member
Local time
Today, 10:28
Joined
Jan 23, 2006
Messages
15,362
Why not show us your table layout and even some sample records?
 

mcalex

Registered User.
Local time
Today, 22:28
Joined
Jun 18, 2009
Messages
141
@jdraw. table is pretty much as described:
LocalGovernment
ID
Name
RegionID
LGType

Ward
ID
Name
LocalGovernmentID

Election
ID
WardID
ElectionDate
ElectionTypeID
TotalTurnout


LocalGovernment
1 Metropolis 1 Shire
2 Gotham City 2 City
3 Bizarria (the capital city of Bizarro world) 3 City
4 Smallville 1 Town

Ward
1 West 1
2 East 1
3 Central 1
4 Lower Gotham 2
5 Upper Gotham 2
6 City 2
7 Nodnol 3
8 Kroy Wen 3
etc

Election
1 1 10-Oct-2009 1 25633
2 2 10-Oct-2009 1 31393
3 3 10-Oct-2009 1 28473
4 4 15-Oct-2009 2 11586
5 5 15-Oct-2009 2 15833
6 6 15-Oct-2009 2 13185
7 7 12-Oct-2009 1 8922
8 8 12-oct-2009 1 9155
9 1 8-Oct-2011 2 25975
10 2 8-Oct-2011 2 30977
11 3 8-Oct-2011 2 29587


@plog. That all makes sense. ElectionType is a member of Election, as the Government can change election type for future elections - and all their elections will be of this type until a future change - though it would make things nice & easy if the Government table contained the field. I'm also up against the difficulty that I don't get the information about which type of election the government held until the election result form is submitted, and results are on a ward by ward basis.

Looks like I'm stuck with my query that queries a query, though someone suggested I should be able to rig up a subquery. meh. This is working and i'm on a deadline. Long term, I'll look into restructuring the table.

Many thanks
 

plog

Banishment Pending
Local time
Today, 09:28
Joined
May 11, 2011
Messages
11,611
I think your table structure is correct, your initial explanation was misleading.

I can do a query showing the ElectionType for each LocalGovernment, which correctly only outputs one row per LocalGovernment,

From your new explanation it sounds like election type belongs in the Election table because a LocalGovernment can have more than one type of election. I wouldn't change your structure.
 

Users who are viewing this thread

Top Bottom