Duplicate records suppressed

[Bill]

I have a query that returns a RecordSource
to a report based on a primary ID. The
table from which the records come has its
"Indexed" properties as "Yes (Duplicates OK)".
The content of the records populate textbox
controls in the detail section one for one.

What I seem to have encountered is an instance
of the report where duplicate records ARE NOT
being returned from the query, while in another
instance they ARE. The same query is used in
both instances where the report code is passed
the ID to be used in the filter expression.

I don't see anywhere in the query design sheet
a provision to explicitly allow duplicates.........
Am I missing something really basic here?

Thanks,
Bill

 

[Bill]

I attempted to add the ALL keyword in the
SQL view of the query and save it, but the
subsequent SQL view of the query DOES
NOT include the ALL predicate.

The query does have an INNER JOIN, but
I don't see how that would preclude the use
of ALL?

Bill

 

[John W. Vinson]

I have a query that returns a RecordSource
to a report based on a primary ID. The
table from which the records come has its
"Indexed" properties as "Yes (Duplicates OK)".
The content of the records populate textbox
controls in the detail section one for one.

What I seem to have encountered is an instance
of the report where duplicate records ARE NOT
being returned from the query, while in another
instance they ARE. The same query is used in
both instances where the report code is passed
the ID to be used in the filter expression.

I don't see anywhere in the query design sheet
a provision to explicitly allow duplicates.........
Am I missing something really basic here?

Thanks,
Bill

Are there any Nulls in the index? Nulls are wierd; two records that have
identical values will NOT be treated as duplicates if one of the fields being
compared is NULL in both records, because NULL is not equal to anything, not
even to another NULL.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Bill]

Hi John,
No, all fields have either date, text or numeric
contents, as verified by including the "offending"
ID in the criteria of the design view and running
the query.
Bill

 

[Bill]

John,

I've more-or-less verified that I'm up against
a duplicate record issue in that I changed one
character in ONE of the otherwise duplicate
records and ran the function where I could
observe the case where the duplicates were
being suppressed.

Still scratching my head.

Bill

 

[Bill]

RATS!!!! buried deep in the bowls of this
application is a subtle little switch I found
that triggers between two query names.

So, one of the queries returns duplicates
and the other does not. Still, I don't see
why.

Here's the two:

Returns duplicates------------------------
SELECT Families.FamilyID, [Salutations.Salutation] AS Salutation,
[Families.FamilyName] AS LastName, [Families.FamilyName] AS FirstName,
[Families.FamilyPOBox] AS POBox, [Families.FamilyAddress] AS Address,
[Families.FamilyCityState] AS CityState, [Families.FamilyZip] AS Zip,
Funds.FundTitle, DonRegFam.DOE, DonRegFam.Amount
FROM Funds INNER JOIN ((Families INNER JOIN Salutations ON
Families.FamilySalutation = Salutations.SalID) INNER JOIN DonRegFam ON
Families.FamilyID = DonRegFam.FamilyID) ON Funds.FundID = DonRegFam.FundID
ORDER BY [Families.FamilyName], DonRegFam.DOE;

DOES NOT return duplicates-------------
SELECT All [Families.FamilyID] AS ID, [Salutations.Salutation] AS
Salutation, [Families.FamilyName] AS LastName, [Families.FamilyName] AS
FirstName, [Families.FamilyPOBox] AS POBox, [Families.FamilyAddress] AS
Address, [Families.FamilyCityState] AS CityState, [Families.FamilyZip] AS
Zip, Funds.FundTitle, DonRegFam.DOE, DonRegFam.Amount
FROM Funds INNER JOIN ((Families INNER JOIN Salutations ON
Families.FamilySalutation = Salutations.SalID) INNER JOIN DonRegFam ON
Families.FamilyID = DonRegFam.FamilyID) ON Funds.FundID = DonRegFam.FundID
UNION SELECT [Registry.RegistryID] AS ID, [Salutations.Salutation] AS
Salutation, Registry.LastName, Registry.FirstName, Registry.POBox,
Registry.Address, Registry.CityState, Registry.Zip, Funds.FundTitle,
DonRegInd.DOE, DonRegInd.Amount
FROM (Registry INNER JOIN Salutations ON Registry.Salutation =
Salutations.SalID) INNER JOIN (Funds INNER JOIN DonRegInd ON Funds.FundID =
DonRegInd.FundID) ON Registry.RegistryID = DonRegInd.RegistryID
ORDER BY LastName, FirstName, DOE;

Obviously, the offending query is a UNION
Select where the other is not. The table that
contains the duplicate records is DonRegFam
while the table selected in the UNION is
DonRegInd and is of the same structure and
also provides allowance of duplicates in the
table def.

Bill

 

[John W. Vinson]

Obviously, the offending query is a UNION
Select where the other is not.

A UNION query hides duplicates. That's how it's designed to work.

Use the UNION ALL keyword (which has nothing to do with the SELECT ALL
operation) in place of UNION to see duplicates.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Bill]

BINGO!!!!!

As usual John, you're a great help.

Thanks,
Bill