By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,263 Members | 2,640 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,263 IT Pros & Developers. It's quick & easy.

Query picking unique field

P: n/a
Ok, I'm trying to write a query that is starting to wear me down.

What I'm trying to do is create a year end report that gets sent to all of
my customers who meet two criteria. One they are 'Residential' customer AND
they have had business with us during this past year.

My report is based on a query.

The query has two tables associated with it. One is tblClients which
provides client name, account type and the other relevant mailing info. The
second table, tblProjects, contains a date field, StartDate, which is used
to determine if the customer had any projects this year. The two tables are
joined by the ClientID field.

This part has been successful with the following sql statement...

SELECT DISTINCT tblClients.BillingName, tblClients.AccountType,
tblProjects.StartDate, tblClients.BillingAddress, tblClients.Contact,
tblClients.BillingCity, tblClients.BillingState, tblClients.BillingZip
FROM tblClients INNER JOIN tblProjects ON tblClients.ClientID =
tblProjects.ClientID
WHERE (((tblClients.AccountType)="Residential") AND
((Year([tblProjects].[StartDate]))=Year(Date())));

This query will return a list of all the residential clients who had a
project with a start date ending in 2006. The problem is, this will return
some clients several times if they had more than one project this year. I
cannot change the query properties for Unique Values or Unique Records to
yes because each of the records that the query returns does not fit that
criteria that would exclude it. A client may have had several projects
during the year, and each one would have a different start date.

After much research on this NG I found several attempts at a solution but
nothing that seemed very helpful. One suggestion was adding the Totals row
and using 'First' and 'Group By' to assist. I attempted the following...

SELECT DISTINCT First(tblClients.BillingName) AS FirstOfBillingName,
tblClients.AccountType, tblProjects.StartDate, tblClients.BillingAddress,
tblClients.Contact, tblClients.BillingCity, tblClients.BillingState,
tblClients.BillingZip
FROM tblClients INNER JOIN tblProjects ON tblClients.ClientID =
tblProjects.ClientID HAVING (((tblClients.AccountType)="Residential") AND
((Year([tblProjects].[StartDate]))=Year(Date())));

In Query Builder view this shows "First" in the totals column under
'BillingName' and "Expression" under each other column. When I run this
query I get an error... "You tried to execute a query that doesn't include
the specified expression 'AccountType' as part of an aggregate function."

Ok, try something different, keep "First" under 'BillingName' and change the
rest to "Group By" and run the query. Runs just like the first time when
there was no Totals row, i.e. correct results but lists many 'BillingName'
entries that are repeats.

Is there another option along these lines that I should be attempting?

Also read a few tidbits about using two queries to obtain the results I
want. Possibly running this query then running a second that only included
the BillingName field and using access Unique Records feature? But then
would I need a third to connect the BillingName back up with the mailing
info to get it on the report? How would I even go about running two queries
together and using one in the other?

Any help greatly appreciated (hopefully before I've pulled out too much more
hair).
Oct 3 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a

Jimmy Stewart wrote:
Ok, I'm trying to write a query that is starting to wear me down.

What I'm trying to do is create a year end report that gets sent to all of
my customers who meet two criteria. One they are 'Residential' customer AND
they have had business with us during this past year.

My report is based on a query.

The query has two tables associated with it. One is tblClients which
provides client name, account type and the other relevant mailing info. The
second table, tblProjects, contains a date field, StartDate, which is used
to determine if the customer had any projects this year. The two tables are
joined by the ClientID field.

This part has been successful with the following sql statement...

SELECT DISTINCT tblClients.BillingName, tblClients.AccountType,
tblProjects.StartDate, tblClients.BillingAddress, tblClients.Contact,
tblClients.BillingCity, tblClients.BillingState, tblClients.BillingZip
FROM tblClients INNER JOIN tblProjects ON tblClients.ClientID =
tblProjects.ClientID
WHERE (((tblClients.AccountType)="Residential") AND
((Year([tblProjects].[StartDate]))=Year(Date())));

This query will return a list of all the residential clients who had a
project with a start date ending in 2006. The problem is, this will return
some clients several times if they had more than one project this year. I
cannot change the query properties for Unique Values or Unique Records to
yes because each of the records that the query returns does not fit that
criteria that would exclude it. A client may have had several projects
during the year, and each one would have a different start date.

"Unique Values" is returning a row for each different [StartDate] in
the result set, so remove the [StartDate] column from the result set,
i.e.,
SELECT DISTINCT tblClients.BillingName, tblClients.AccountType,
tblClients.BillingAddress, tblClients.Contact,
tblClients.BillingCity, tblClients.BillingState, tblClients.BillingZip
FROM tblClients INNER JOIN tblProjects ON tblClients.ClientID =
tblProjects.ClientID
WHERE (((tblClients.AccountType)="Residential") AND
((Year([tblProjects].[StartDate]))=Year(Date())));

Oct 3 '06 #2

P: n/a
Sure, take the easy way! That was exactly what I was looking for.

"Gord" <gd*@kingston.netwrote in message
news:11*********************@i42g2000cwa.googlegro ups.com...
>
Jimmy Stewart wrote:
>Ok, I'm trying to write a query that is starting to wear me down.

What I'm trying to do is create a year end report that gets sent to all
of
my customers who meet two criteria. One they are 'Residential' customer
AND
they have had business with us during this past year.

My report is based on a query.

The query has two tables associated with it. One is tblClients which
provides client name, account type and the other relevant mailing info.
The
second table, tblProjects, contains a date field, StartDate, which is
used
to determine if the customer had any projects this year. The two tables
are
joined by the ClientID field.

This part has been successful with the following sql statement...

SELECT DISTINCT tblClients.BillingName, tblClients.AccountType,
tblProjects.StartDate, tblClients.BillingAddress, tblClients.Contact,
tblClients.BillingCity, tblClients.BillingState, tblClients.BillingZip
FROM tblClients INNER JOIN tblProjects ON tblClients.ClientID =
tblProjects.ClientID
WHERE (((tblClients.AccountType)="Residential") AND
((Year([tblProjects].[StartDate]))=Year(Date())));

This query will return a list of all the residential clients who had a
project with a start date ending in 2006. The problem is, this will
return
some clients several times if they had more than one project this year. I
cannot change the query properties for Unique Values or Unique Records to
yes because each of the records that the query returns does not fit that
criteria that would exclude it. A client may have had several projects
during the year, and each one would have a different start date.


"Unique Values" is returning a row for each different [StartDate] in
the result set, so remove the [StartDate] column from the result set,
i.e.,
SELECT DISTINCT tblClients.BillingName, tblClients.AccountType,
tblClients.BillingAddress, tblClients.Contact,
tblClients.BillingCity, tblClients.BillingState, tblClients.BillingZip
FROM tblClients INNER JOIN tblProjects ON tblClients.ClientID =
tblProjects.ClientID
WHERE (((tblClients.AccountType)="Residential") AND
((Year([tblProjects].[StartDate]))=Year(Date())));

Oct 3 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.