Duplicate and Null values

[Franklin]

Here's the breakdown:

I am working with a separate Internet database tool that allows me to export
information. I chose the categories I needed, exported the info, and imported
into Excel, to then import into Access (2007).

Some of the data contains multiple contacts per one record. For example,
record #123 could have 3 contacts. In anyone of those contacts another field
could, or could not have been chosen. A specific example:

Record ID: Date Created: Name: Ethnicity:
4340458 12/12/2006 Tom Smith
4340458 12/12/2006 Tom Smith
4340458 12/12/2006 Tom Smith Asian
4340458 12/14/2006 Tom Smith

You can see that the record has 4 contacts. In only one of the contacts was
the ethnicity recorded. Some will also have one or more contacts with nothing
recorded.

In the above example, I cannot sort it by Null values, because during one
certain contact, that information may have been collected.

I am trying to run a query/report where I can specify that every unique
record ID#, matched with another field (ethnicity in this case) will return
the correct number of cases AND also differentiate between those record ID#'s
where there was a Null value entered despite the number of contacts. For
example:

Total Records: 14,000

Asian Ethnicity: 1,000
Spanish: 10,000
White: 1,000
BLANK: 2,000

I have tried filtering out duplicate values (for the record ID#'s) in Excel
but it doesn't let me specify WHICH duplicates to filter. I have tried
running a Find Duplicates query in Access and did not get the results I
wanted.

Help!

--Franklin

 

[Jerry Whittle]

I'm a little confused on what you need returned. Using your example records,
or maybe expounding upon it, what do you wish returned from them in the query?

 

[Franklin]

I dont want the multiple contacts that have a blank/null value to be lumped
in with those record ID's that were genuinely left blank. I am basically
trying to weed out the actual number of records that chose a specific
ethnicity, and at the same have the number of records that were left blank.

Using this example again:

Record ID: Date Created: Name: Ethnicity:
4340458 12/12/2006 Tom Smith
4340458 12/12/2006 Tom Smith
4340458 12/12/2006 Tom Smith Asian
4340458 12/14/2006 Tom Smith

This is being read as having 3 blank fields and 1 Asian. HOWEVER, because it
is the same record ID#, it is really just 1 Asian. Another example:

Record ID: Date Created: Name: Ethnicity:
5328961 1/15/2007 Tom Smith
5328961 1/15/2007 Tom Smith

In this case, this should only count as 1 BLANK/Null record, because each
contact has a unique record ID.

Hope this clears things up!

--Franklin

 

[Jerry Whittle]

See if this helps. Create a query named qryEthnicity like so. You may need to
remove the colons if not actually part of the field name plus the correct
table name.

SELECT Ethnicity.[Record ID:],
Ethnicity.[Name:],
Nz([Ethnicity:],"None Listed") AS Ethnicities,
1 AS TheCount
FROM Ethnicity;

Next create a crosstab query based on the above query:

TRANSFORM Count(qryEthnicity.TheCount) AS CountOfTheCount
SELECT qryEthnicity.[Record ID:],
qryEthnicity.[Name:],
Count(qryEthnicity.TheCount) AS [Total Of TheCount]
FROM qryEthnicity
GROUP BY qryEthnicity.[Record ID:],
qryEthnicity.[Name:]
ORDER BY qryEthnicity.[Record ID:]
PIVOT qryEthnicity.Ethnicities;

 

[Franklin]

Thanks for that Jerry, but I keep getting a syntax error code when trying to
enter it - even with substituting with my info. I'll keep working on it...

See if this helps. Create a query named qryEthnicity like so. You may need to
remove the colons if not actually part of the field name plus the correct
table name.

SELECT Ethnicity.[Record ID:],
Ethnicity.[Name:],
Nz([Ethnicity:],"None Listed") AS Ethnicities,
1 AS TheCount
FROM Ethnicity;

Next create a crosstab query based on the above query:

TRANSFORM Count(qryEthnicity.TheCount) AS CountOfTheCount
SELECT qryEthnicity.[Record ID:],
qryEthnicity.[Name:],
Count(qryEthnicity.TheCount) AS [Total Of TheCount]
FROM qryEthnicity
GROUP BY qryEthnicity.[Record ID:],
qryEthnicity.[Name:]
ORDER BY qryEthnicity.[Record ID:]
PIVOT qryEthnicity.Ethnicities;
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I dont want the multiple contacts that have a blank/null value to be lumped
in with those record ID's that were genuinely left blank. I am basically
trying to weed out the actual number of records that chose a specific
ethnicity, and at the same have the number of records that were left blank.

Using this example again:

Record ID: Date Created: Name: Ethnicity:
4340458 12/12/2006 Tom Smith
4340458 12/12/2006 Tom Smith
4340458 12/12/2006 Tom Smith Asian
4340458 12/14/2006 Tom Smith

This is being read as having 3 blank fields and 1 Asian. HOWEVER, because it
is the same record ID#, it is really just 1 Asian. Another example:

Record ID: Date Created: Name: Ethnicity:
5328961 1/15/2007 Tom Smith
5328961 1/15/2007 Tom Smith

In this case, this should only count as 1 BLANK/Null record, because each
contact has a unique record ID.

Hope this clears things up!

--Franklin

 

[Gary Walter]

PMFBI

I wonder if you cannot just use a totals query
to get one record per ID

(the "ContactCnt" is not germaine to your
problem but just threw in "at no cost")

(replace "yurtable" with actual name of
your table)

qryEthnicity:

SELECT
[Record ID] As RecID,
[Name] As ContactName,
COUNT([Record ID]) As ContactCnt,
MAX(Nz([Ethnicity],"(Blank)") As Ethn
FROM
yurtable
GROUP BY
[Record ID],
[Name];

the "(Blank)" should be < any actual Ethnicity value
via string sorting thanks to "("

if it is possible that [Name] might vary
for same RecID, then use FIRST and
do not include in GROUP BY

SELECT
[Record ID] As RecID,
FIRST([Name]) As ContactName,
Count([Record ID]) As ContactCnt,
MAX(Nz([Ethnicity],"(Blank)") As Ethn
FROM
yurtable
GROUP BY
[Record ID];

your example data should then produce:

RecID ContactName ContactCnt Ethn
4340458 Tom Smith 4 Asian
5328961 Tom Smith 2 (Blank)

so another totals query should get your
report data

SELECT
Ethn As Ethnicity,
Count([Ethn]) As EthnCnt
FROM
qryEthnicity
GROUP BY
Ethn;

the final total count can easily be handled
in footer textbox

=Sum([EthnCnt])

Apologies again for butting in,

gary

"Franklin"wrote:

 

[Ken Sheridan]

Try this:

SELECT Ethnicity, COUNT(*) As Total
FROM YourTable
WHERE Ethicity IS NOT NULL
GROUP BY Ethnicity
UNION ALL
SELECT "Blank", COUNT(*)
FROM (SELECT DISTINCT [Record ID]
FROM YourTable As T1
WHERE NOT EXISTS
(SELECT *
FROM YourTable AS T2
WHERE T2.[Record ID] = T1.[Record ID]
AND T2.Ethnicity IS NOT NULL)) AS T3;

Ken Sheridan
Stafford, England