First and only occurrence

[Bill]

I have a query that will return a large quantity
of records based on a date. Associated with
each of these records is the name of an
account to which the date applies. I'd like
a version of that query that will only return
the first and only occurrence of any account
name found.

Example:

Existing query returns:
AccID Account Name
1010 Misc Income
1010 Misc Income
1010 Misc Income
1015 Rent Income
1015 Rent Income
3010 Taxable
3010 Taxable
3010 Taxable

What I'd like returned:
1010 Misc Income
1015 Rent Income
3010 Taxable

I can't find any SQL functions or operators
like NoDuplicate(AccID) that would limit
the query to only the first occurrence found.

Bill

 

[John W. Vinson]

I have a query that will return a large quantity
of records based on a date. Associated with
each of these records is the name of an
account to which the date applies. I'd like
a version of that query that will only return
the first and only occurrence of any account
name found.

Example:

Existing query returns:
AccID Account Name
1010 Misc Income
1010 Misc Income
1010 Misc Income
1015 Rent Income
1015 Rent Income
3010 Taxable
3010 Taxable
3010 Taxable

What I'd like returned:
1010 Misc Income
1015 Rent Income
3010 Taxable

I can't find any SQL functions or operators
like NoDuplicate(AccID) that would limit
the query to only the first occurrence found.

Bill

You can view the query's Properties and set the Unique Values property to Yes.

 

[Bill]

You can view the query's Properties and set the Unique Values property to
Yes.

John,
The query is an SQL Union Select each of which (3)
contain Inner Joins. I don't see where I can set any
properties you suggest. From DB view of Queries,
I right-click the query but don't see anything in the
properties dialog where any such option exists......
........am I missing something there?
Thanks,
Bill

 

[Bill]

Here's the existing query:
=============================================
SELECT ALL
[DonRegFam].[FundID],[Funds].[FundTitle],[Funds].[AccountNo],[Funds].[Source],[DOE],[Amount],[Type]
FROM [DonRegFam] INNER JOIN
[Funds] ON [DonRegFam].[FundID] = [Funds].[FundID]
Where DOE = DOEparms()

UNION ALL SELECT
[DonRegInd].[FundID],[Funds].[FundTitle],[Funds].[AccountNo],[Funds].[Source],[DOE],[Amount],[Type]
FROM [DonRegInd] INNER JOIN
[Funds] ON [DonRegInd].[FundID] = [Funds].[FundID]
Where DOE = DOEparms()

UNION ALL SELECT
[DonUnReg].[FundID],[Funds].[FundTitle],[Funds].[AccountNo],[Funds].[Source],[DOE],[Amount],[Type]
FROM [DonUnReg] INNER JOIN
[Funds] ON [DonUnReg].[FundID] = [Funds].[FundID]
Where DOE = DOEparms();

=============================================

 

[Piet Linden]

John,
The query is an SQL Union Select each of which (3)
contain Inner Joins. I don't see where I can set any
properties you suggest. From DB view of Queries,
I right-click the query but don't see anything in the
properties dialog where any such option exists......
.......am I missing something there?
Thanks,
Bill

So create a query based on the union query and make that return unique
results. What's so complicated about that?

 

[Bill]

message




John,
The query is an SQL Union Select each of which (3)
contain Inner Joins. I don't see where I can set any
properties you suggest. From DB view of Queries,
I right-click the query but don't see anything in the
properties dialog where any such option exists......
.......am I missing something there?
Thanks,
Bill

So create a query based on the union query and make that return unique
results. What's so complicated about that?

That's my original queston, sort of, what's the function or
keyword expression to include in the query to force a
return of records where the, say, the FundID is unique?
I just couldn't locate it in the SQL help file.
Bill

 

[John W. Vinson]

Here's the existing query:
=============================================
SELECT ALL
[DonRegFam].[FundID],[Funds].[FundTitle],[Funds].[AccountNo],[Funds].[Source],[DOE],[Amount],[Type]
FROM [DonRegFam] INNER JOIN
[Funds] ON [DonRegFam].[FundID] = [Funds].[FundID]
Where DOE = DOEparms()

UNION ALL SELECT
[DonRegInd].[FundID],[Funds].[FundTitle],[Funds].[AccountNo],[Funds].[Source],[DOE],[Amount],[Type]
FROM [DonRegInd] INNER JOIN
[Funds] ON [DonRegInd].[FundID] = [Funds].[FundID]
Where DOE = DOEparms()

UNION ALL SELECT
[DonUnReg].[FundID],[Funds].[FundTitle],[Funds].[AccountNo],[Funds].[Source],[DOE],[Amount],[Type]
FROM [DonUnReg] INNER JOIN
[Funds] ON [DonUnReg].[FundID] = [Funds].[FundID]
Where DOE = DOEparms();

=============================================

Just change the UNION ALL to UNION on both lines. UNION will de-duplicate the
results.

This query of course does not match your example - if FundID is the primary
key of DonRegxxx then all records will be different and there won't BE any
duplicates. Are there fields like this that have multiple values but you only
want to see one? If so make each SELECT clause in the UNION a Totals query,
using Group By on the fields that you want to group by, and FIRST on the
fields where you just want a representative arbitrary value. I can't see that
making sense with an Amount field though!

 

[Bill]

Here's the existing query:
=============================================
SELECT ALL
[DonRegFam].[FundID],[Funds].[FundTitle],[Funds].[AccountNo],[Funds].[Source],[DOE],[Amount],[Type]
FROM [DonRegFam] INNER JOIN
[Funds] ON [DonRegFam].[FundID] = [Funds].[FundID]
Where DOE = DOEparms()

UNION ALL SELECT
[DonRegInd].[FundID],[Funds].[FundTitle],[Funds].[AccountNo],[Funds].[Source],[DOE],[Amount],[Type]
FROM [DonRegInd] INNER JOIN
[Funds] ON [DonRegInd].[FundID] = [Funds].[FundID]
Where DOE = DOEparms()

UNION ALL SELECT
[DonUnReg].[FundID],[Funds].[FundTitle],[Funds].[AccountNo],[Funds].[Source],[DOE],[Amount],[Type]
FROM [DonUnReg] INNER JOIN
[Funds] ON [DonUnReg].[FundID] = [Funds].[FundID]
Where DOE = DOEparms();

=============================================

Just change the UNION ALL to UNION on both lines. UNION will de-duplicate
the
results.

This query of course does not match your example - if FundID is the
primary
key of DonRegxxx then all records will be different and there won't BE any
duplicates. Are there fields like this that have multiple values but you
only
want to see one? If so make each SELECT clause in the UNION a Totals
query,
using Group By on the fields that you want to group by, and FIRST on the
fields where you just want a representative arbitrary value. I can't see
that
making sense with an Amount field though!

John,

What I'm trying to do is create a list of the un-duplicated "FundID's" used
per
the DOE criteria provided. I've tried to use the DISTINCT and GROUP BY
expressions, but not being familiar with those particular SQL attributes I'm
not getting the query to run. Since the FundID's are unique to each
FundTitle,
I tried to GROUP BY [Funds].[FundTitle], but that too fails.

I have to pack it in for the night, but I'll be back at it again in the AM.

Thanks,
Bill

 

[Bill]

I made this much more difficult than needed.
Assuming use of an existing query, that is used
for a report's summary, was the best place to
start to create the data source for a form was
a prime piece of poor judgment. Anyway,
I finally recognized that and all is well with a
much simpler approach.

Thanks,
Bill

Here's the existing query:
=============================================
SELECT ALL
[DonRegFam].[FundID],[Funds].[FundTitle],[Funds].[AccountNo],[Funds].[Source],[DOE],[Amount],[Type]
FROM [DonRegFam] INNER JOIN
[Funds] ON [DonRegFam].[FundID] = [Funds].[FundID]
Where DOE = DOEparms()

UNION ALL SELECT
[DonRegInd].[FundID],[Funds].[FundTitle],[Funds].[AccountNo],[Funds].[Source],[DOE],[Amount],[Type]
FROM [DonRegInd] INNER JOIN
[Funds] ON [DonRegInd].[FundID] = [Funds].[FundID]
Where DOE = DOEparms()

UNION ALL SELECT
[DonUnReg].[FundID],[Funds].[FundTitle],[Funds].[AccountNo],[Funds].[Source],[DOE],[Amount],[Type]
FROM [DonUnReg] INNER JOIN
[Funds] ON [DonUnReg].[FundID] = [Funds].[FundID]
Where DOE = DOEparms();

=============================================

Just change the UNION ALL to UNION on both lines. UNION will de-duplicate
the
results.

This query of course does not match your example - if FundID is the
primary
key of DonRegxxx then all records will be different and there won't BE
any
duplicates. Are there fields like this that have multiple values but you
only
want to see one? If so make each SELECT clause in the UNION a Totals
query,
using Group By on the fields that you want to group by, and FIRST on the
fields where you just want a representative arbitrary value. I can't see
that
making sense with an Amount field though!

John,

What I'm trying to do is create a list of the un-duplicated "FundID's"
used per
the DOE criteria provided. I've tried to use the DISTINCT and GROUP BY
expressions, but not being familiar with those particular SQL attributes
I'm
not getting the query to run. Since the FundID's are unique to each
FundTitle,
I tried to GROUP BY [Funds].[FundTitle], but that too fails.

I have to pack it in for the night, but I'll be back at it again in the
AM.

Thanks,
Bill

 

[John W. Vinson]

Anyway,
I finally recognized that and all is well with a
much simpler approach.

Glad you got it working.

 

[Bill]

Glad you got it working.

Anyone having followed this thread would
certainly agree that:

SELECT DISTINCT FundID, DOE FROM
DonRegFam Where DOE = DOEparms()

UNION SELECT DISTINCT FundID, DOE
FROM DonRegInd Where DOE = DOEparms()

UNION SELECT DISTINCT FundID, DOE
FROM DonUnReg Where DOE = DOEparms();

is a bit simpler than originally posted.

The results (FundID's) are then used to build a
filter for a form display of the "Funds" referenced
on the date "DOE", (Date Of Entry).

Thanks again John,
Bill