Query to return 0 values too

[Romulus]

I have 3 tables
One has field about County: CountyCode, Name, SomeOtherInfo
The second has fields about cities: CountyCode, CityCode, CityName, Population, Elevation, Etc.
The third one has information about people within cities: CityCode, SocialSecurityNo, FirstName, SecondName, Profession, Validation, email, address, etc.


The validation field is a Yes/No Field (checkbox)

I need to set a query that returns a count of all valid person, on each city and each county.

Actually I did this one, but my trouble is that I need 0 values too to be displayed.

If a city has no valid people in it, 0 value should be displayed.

If I do a simple count of SocialSecurityNo, I am able to display the 0 values, but if I try to restrict the results only to show valid people, then only the cities that contains valid person will be displayed, the ones with 0 values being skipped.

Please advise....

[June7]

Need a dataset of all possible cities and people. This can be generated by a query that includes Cities and People tables without a JOIN clause which results in a Cartesian relationship - every record of each table will associate with every record of other table.

SELECT CityCode, SocialSecurityNo FROM Cities, People;

Then join (not an INNER JOIN) that query to third table on the two common fields - CityCode and SocialSecurityNo.

[Romulus]

the result of the first query is a table containing all SocialSecurityNo and their related CitiyCodes
The same result i get with a simple query based on these fields on the third table. More, the result will keep both valid and invalid records, since Validation field is not mentioned.
Please be more specific
If is possible, provide the syntax for the final query.
i am not looking for somebody to do my work (the tables are more complex then the example i provided), but i don't understand properly your answer.
Thank You

[June7]

Post your attempted queries for analysis.

[Romulus]

>>>>

The working query

SELECT Coduri_Judete.Judet, Coduri_Judete.Cod_Judet, Membri_Partid.Organizatia, Coduri_Localitati.Tip, Coduri_Judete.Circumscriptie_Judeteana, Membri_Partid.Cod_Org, Membri_Partid.inValidat, IIf(Count([CNP])=0,0,Sum([CNP_Valid]*(-1))) AS Valid, Count([CNP])-[Valid] AS inValid, Count(Membri_Partid.CNP) AS CountOfCNP
FROM (Coduri_Judete INNER JOIN Coduri_Localitati ON Coduri_Judete.Cod_Judet = Coduri_Localitati.Cod_Judet) LEFT JOIN Membri_Partid ON Coduri_Localitati.Cod_Localitate = Membri_Partid.Cod_Org
GROUP BY Coduri_Judete.Judet, Coduri_Judete.Cod_Judet, Membri_Partid.Organizatia, Coduri_Localitati.Tip, Coduri_Judete.Circumscriptie_Judeteana, Membri_Partid.Cod_Org, Membri_Partid.inValidat, Len([Cod_Org])=6
HAVING (((Coduri_Judete.Cod_Judet) Like IIf(IsNull([Forms]![Statistici_Frm]![Cod_Jud]),"*",[Forms]![Statistici_Frm]![Cod_Jud])) AND ((Membri_Partid.inValidat)=0) AND ((Len([Cod_Org])=6)=-1))
ORDER BY Coduri_Judete.Circumscriptie_Judeteana, Membri_Partid.Cod_Org;

This query will return a the number of total records that are not inValid, a calculated field of Valid CNP and a calculated field of invalid CNP as difference beween CNP and Valid

inValid Records are records for person that are no longer members of the organisation
CNP is a personal 13 digit ID code
ValidCNP is a CNP that passes the validation algorithm

LIMITATIONS

The query will display only the result for non-empty Cod_Org, other said, only the results form the cities where the organisation have members

WHAT IS NEEDED

To see the results for all Cod_Org, even those with 0 valid CNP and 0 not valid CNP, but the results should show only records that are not InValid

hope this wii help a bit

thank You

[June7]

Don't know the language so halfway guessing which fields have the unique identifiers for organization and city.

Cartesian query:

SELECT Cod_Org, Cod_Localitate FROM Membri_Partid, Coduri_Localitati;

This should generate a dataset of all possible combinations of organizations and cities. Whether or not you need this depends on how you want to group the records.

Try joining the query you posted with the Cartesian query and link on the two common fields.

Otherwise, just try joining the query you posted to the Cod_Org table (not INNER JOIN) and apply filter criteria on the appropriate field to remove the InValid records.

[Romulus]

The result of SELECT Cod_Org, Cod_Localitate FROM Membri_Partid, Coduri_Localitati; looks meaningless and returns a hudge set of data.
Based only on few hundred records, the query returns 23.000.000 lines
I don't even try to imagine what will happend when i wil run the query for 3.200 cities

The databes containt data of members of a nationwide political party

Table 1, about Couny: Coduri_Judete
Each county has it's own two letter unique ID, set as prymary key, called Cod_Judet
Also contains fields about county name, and a two digit numeric identifier called Circumscriptie_Judeteana

Table 2, about locations: Coduri_Localitati
Each county has from 50 to 150 places, with a unique ID called Cod_Localitate, obtained as a derivate of Cod_Judet and a 3 digit identifier (ex: AB-004)
Table 2 has _ fields
Cod_Judet as foriegn key of Table 1, in a one-to-many relation
Cod_Localitate is the unique primary key in table 2
Tip depicts what type of location is it: City, Town, Village
Localitate is the place name (Ex. New York, White Plains, Bronxville)

Table 3 contains data about person: Membri_Partid
Cod_Org is the Organization ID, same as Cod_Localitate, being the foreign key for table 3, as well in a one-to-many relation
Many people can be members of the same organization
Each person have it's own IDTag, an personal Numerical Code (CNP), FirstName, LastName, DOB, Address, phoneNo, e-mai, profession, Organizatia (the place name), etc.
The IDTag is the unique primary key of this table
CNP is a numeric field, with no duplicates allowed
Because on-field collected CNP may be sometimes faulty, a validation procedure is set on the form that adds the data.
If the CNP is Ok, the CNP_Valid is set to True, else it is set to False, using a checkbox
If a member wil leav the organization, a field called inVald will be set to true. The default value of inValid is False

The query shown above, will filter the not-inValid records and display a total count of filtered CNP as number of not-inValid records, and calculated fields of Valid and not Valid CNP for all that locations and counties that contains members (records)
If there is no member in a city, that city will be skipped from showing

This is waht I want to obtain: A query result that shos data for all cities, with 0 for those orgs where i have no members and with the Counts where I have.

Example

Cod_Judet	Judet	Circumscriptie_Judeteana	Organizatia	Tip	Cod_Org	Valid	inValid	CountOfCNP	inValidat
BV	BRASOV	08	BRASOV	MUNICIPIU (R)	BV-001	459	2	461	No
BV	BRASOV	08	CODLEA	MUNICIPIU	BV-002	0	0	0	No
BV	BRASOV	08	FAGARAS	MUNICIPIU	BV-003	22	2	24	No
BV	BRASOV	08	SACELE	MUNICIPIU	BV-004	54	0	54	No

[June7]

If you need the report to show a record for each city even if there is no CNP data, data must come from somewhere.

One option is to join your query to the table of cities, probably a RIGHT JOIN. This will list every city and those that don't have any organizations with CNP data will have Null in CNP.

Another option is a dummy CNP record with a 0 value for EVERY city.

If you need to show a 0 CNP for every organization in every city, that involves the Cartesian query.

[Romulus]

If I use this
SELECT DISTINCTROW Coduri_Judete.Cod_Judet, Coduri_Localitati.Cod_Localitate, Count(Membri_Partid.CNP) AS CountOfCNP
FROM (Coduri_Judete LEFT JOIN Coduri_Localitati ON Coduri_Judete.Cod_Judet = Coduri_Localitati.Cod_Judet) LEFT JOIN Membri_Partid ON Coduri_Localitati.Cod_Localitate = Membri_Partid.Cod_Org
GROUP BY Coduri_Judete.Cod_Judet, Coduri_Localitati.Cod_Localitate
ORDER BY Coduri_Judete.Cod_Judet;

I get this

Cod_Judet	Cod_Localitate	CountOfCNP
BV	BV-001	478
BV	BV-002	11
BV	BV-003	33
BV	BV-004	56
BV	BV-005	2
BV	BV-006	77
BV	BV-007	7
BV	BV-008	9
BV	BV-009	0
BV	BV-010	96
BV	BV-011	0
BV	BV-012	15

But if I try to add one more field like this

SELECT DISTINCTROW Coduri_Judete.Cod_Judet, Coduri_Localitati.Cod_Localitate, Count(Membri_Partid.CNP) AS CountOfCNP, Membri_Partid.inValidat
FROM (Coduri_Judete LEFT JOIN Coduri_Localitati ON Coduri_Judete.Cod_Judet = Coduri_Localitati.Cod_Judet) LEFT JOIN Membri_Partid ON Coduri_Localitati.Cod_Localitate = Membri_Partid.Cod_Org
GROUP BY Coduri_Judete.Cod_Judet, Coduri_Localitati.Cod_Localitate, Membri_Partid.inValidat
HAVING (((Membri_Partid.inValidat)=0))
ORDER BY Coduri_Judete.Cod_Judet;

I get this

Cod_Judet	Cod_Localitate	CountOfCNP	inValidat
BV	BV-001	461	No
BV	BV-002	11	No
BV	BV-003	24	No
BV	BV-004	54	No
BV	BV-005	2	No
BV	BV-006	72	No
BV	BV-007	7	No
BV	BV-008	9	No
BV	BV-010	96	No
BV	BV-012	15	No

How I get e result in the second format, but with BV-009 and BV-011 having 0 value

[June7]

I have already tried to explain what you need to. DISTINCTROW had nothing to do with suggestion and involved more than just adding another field to query.

What exactly do you not understand about the instructions already offered?

If you want to provide db for analysis, follow instructions at bottom of my post.