Help with Distinct

M

Michael Horne

Need a little help here. I have a table that collects information on
Referrals made to each Contractor from each Worker. I need a query to return
results for us to contact the Worker and survey them on their Client
experience at the Contractor.

Referral table has 6936 records
The ID field is an autonumber field.

I have created qryReferral1 (6936 records returned) below:

SELECT tblReferrals.ID, tblReferrals.[Contract #], tblReferrals.[Contract
Name], tblReferrals.[Client Name], tblReferrals.Service, tblReferrals.[Visit
Date], tblReferrals.[Worker Name], CStr(tblReferrals.[Contract
#])+tblReferrals.[Worker Name] AS horne
FROM tblReferrals
ORDER BY tblReferrals.[Contract #], tblReferrals.[Worker Name];


I have created qryReferral2 (3149 records returned) below:

SELECT DISTINCT qryReferrals1.horne, qryReferrals1.[Contract #]
FROM qryReferrals1;

I must have the field ID in the results, but when I add, it returns every
row! (6936) How do I get it to only return the ID for the row I need?

Referral table contains this data below:
"ID" "Contractor Name" "Contract #" "Client Name" "Visit Date" "Service"
"Worker ID" Worker Name"
1 "Pizza Hut" "100" "Mouse, Micky" "01/01/2009" "Lunch" "1234" "Sikes, Sam"
<--- return this one
2 "Pizza Hut" "100" "Mouse, Minnie" "01/01/2009" "Lunch" "1234" "Sikes, Sam"
3 "Pizza Hut" "100" "Mouse, Joe" "01/01/2009" "Lunch" "1234" "Sikes, Sam"
4 "Pizza Hut" "100" "Mouse, Henry" "01/01/2009" "Lunch" "1234" "Sikes, Sam"
5 "Pizza Hut" "100" "Mouse, Micky" "05/05/2009" "Lunch" "1234" "Sikes, Sam"
6 "Pizza Hut" "100" "Mouse, Minnie" "06/01/2009" "Dinner" "1234" "Sikes, Sam"
7 "Cheese Cafe" "2309" "Mouse, Micky" "03/06/2009" "Lunch" "1234" "Sikes,
Sam" <--- return this one
8 "Cheese Cafe" "2309" "Mouse, Minnie" "04/07/2009" "Breakfast" "1234"
"Sikes, Sam"
9 "Tim's Tacos" "4110" "Jones, Roy" "02/14/2009" "Dinner" "1234" "Sikes,
Sam" <--- return this one
10 "Tim's Tacos" "4110" "Jones, Tamera" "02/14/2009" "Dinner" "1234" "Sikes,
Sam"
11 "Tim's Tacos" "4110" "Mouse, Henry" "02/01/2009" "Breakfast" "9111"
"Castro, Kim" <--- return this one
12 "Lisa's Tofu" "1999" "Lee, Wu" "02/01/2009" "Breakfast" "3007" "Clay,
Cassius" <--- return this one
13 "Pizza Hut" "100" "Mouse, Minnie" "06/01/2009" "Lunch" "3007" "Clay,
Cassius " <--- return this one
14 "Smoothie King" "25860" "Pitts, Omar" "03/15/2009" "Lunch" "7746"
"Dodger, Ron" <--- return this one
15 "Smoothie King" "25860" "Pitts, Emma" "03/15/2009" "Lunch" "7746"
"Dodger, Ron"
16 "Smoothie King" "25860" "Pitts, Salma" "03/15/2009" "Lunch" "7746"
"Dodger, Ron"

Thanks for any help!
 
M

Michael Horne

Karl Dewey,

Sir, my humblest apologies for not being clear. Please forgive me as I am a
new Access 2003 user. I sometimes have a difficult time expressing in
written form what my mind is thinking.

I have a table. I need to get a survey done for each unique Worker who does
a referral at a Contractor. (Worker+Contractor) A worker will get more than
one survey if they refer to more than one contractor. It does not matter how
many clients that worker sends to the same contractor. It does not matter
the dates. So, if a worker refers to a unique contractor 100 times, I would
just like to show a single row of values. The row can be the First, Last, or
whatever. I just need to be able to say: "On this date you sent client
"xxxxx" to contractor "xxxx" for service "xxxx". Did your client enjoy the
visit? Out of my example below I need it to return just 7 rows. If I get
the ID field in a list, then I am thinking I can create another query to
join, right?

By the way, when I run the query below:
SELECT tblReferrals.ID, tblReferrals.[Contract #], tblReferrals.[Contract
Name] AS Expr1, tblReferrals.[Client Name], tblReferrals.Service,
Max(tblReferrals.[Visit Date]) AS Last_Date, tblReferrals.[Worker Name],
CStr(tblReferrals.[Contract #])+tblReferrals.[Worker Name] AS horne
FROM tblReferrals
GROUP BY tblReferrals.ID, tblReferrals.[Contract #], tblReferrals.[Contract
Name], tblReferrals.[Client Name], tblReferrals.Service,
CStr(tblReferrals.[Contract #])+tblReferrals.[Worker Name],
tblReferrals.[Worker
Name]
ORDER BY tblReferrals.[Contract #], tblReferrals.[Worker Name];

I get this error message:
You tried to execute a query that does not include the specified expression
'Worker Name' as part of an aggregate function.

Thank you so much for sharing your knowledge with me,

Michael Horne


KARL DEWEY said:
There is no single row as you pull [Contract #]) and [Worker Name] when
there are multiple tblReferrals.ID for that combination.

You could pull the last tblReferrals.ID based on [Visit Date].
SELECT tblReferrals.ID, tblReferrals.[Contract #], tblReferrals.[Contract
Name], tblReferrals.[Client Name], tblReferrals.Service,
Max(tblReferrals.[Visit Date]) AS Last_Date, tblReferrals.[Worker Name],
CStr(tblReferrals.[Contract #])+tblReferrals.[Worker Name] AS horne
FROM tblReferrals
GROUP BY tblReferrals.ID, tblReferrals.[Contract #], tblReferrals.[Contract
Name], tblReferrals.[Client Name], tblReferrals.Service, tblReferrals.[Worker
Name], CStr(tblReferrals.[Contract #])+tblReferrals.[Worker Name]
ORDER BY tblReferrals.[Contract #], tblReferrals.[Worker Name];


Michael Horne said:
Need a little help here. I have a table that collects information on
Referrals made to each Contractor from each Worker. I need a query to return
results for us to contact the Worker and survey them on their Client
experience at the Contractor.

Referral table has 6936 records
The ID field is an autonumber field.

I have created qryReferral1 (6936 records returned) below:

SELECT tblReferrals.ID, tblReferrals.[Contract #], tblReferrals.[Contract
Name], tblReferrals.[Client Name], tblReferrals.Service, tblReferrals.[Visit
Date], tblReferrals.[Worker Name], CStr(tblReferrals.[Contract
#])+tblReferrals.[Worker Name] AS horne
FROM tblReferrals
ORDER BY tblReferrals.[Contract #], tblReferrals.[Worker Name];


I have created qryReferral2 (3149 records returned) below:

SELECT DISTINCT qryReferrals1.horne, qryReferrals1.[Contract #]
FROM qryReferrals1;

I must have the field ID in the results, but when I add, it returns every
row! (6936) How do I get it to only return the ID for the row I need?

Referral table contains this data below:
"ID" "Contractor Name" "Contract #" "Client Name" "Visit Date" "Service"
"Worker ID" Worker Name"
1 "Pizza Hut" "100" "Mouse, Micky" "01/01/2009" "Lunch" "1234" "Sikes, Sam"
<--- return this one
2 "Pizza Hut" "100" "Mouse, Minnie" "01/01/2009" "Lunch" "1234" "Sikes, Sam"
3 "Pizza Hut" "100" "Mouse, Joe" "01/01/2009" "Lunch" "1234" "Sikes, Sam"
4 "Pizza Hut" "100" "Mouse, Henry" "01/01/2009" "Lunch" "1234" "Sikes, Sam"
5 "Pizza Hut" "100" "Mouse, Micky" "05/05/2009" "Lunch" "1234" "Sikes, Sam"
6 "Pizza Hut" "100" "Mouse, Minnie" "06/01/2009" "Dinner" "1234" "Sikes, Sam"
7 "Cheese Cafe" "2309" "Mouse, Micky" "03/06/2009" "Lunch" "1234" "Sikes,
Sam" <--- return this one
8 "Cheese Cafe" "2309" "Mouse, Minnie" "04/07/2009" "Breakfast" "1234"
"Sikes, Sam"
9 "Tim's Tacos" "4110" "Jones, Roy" "02/14/2009" "Dinner" "1234" "Sikes,
Sam" <--- return this one
10 "Tim's Tacos" "4110" "Jones, Tamera" "02/14/2009" "Dinner" "1234" "Sikes,
Sam"
11 "Tim's Tacos" "4110" "Mouse, Henry" "02/01/2009" "Breakfast" "9111"
"Castro, Kim" <--- return this one
12 "Lisa's Tofu" "1999" "Lee, Wu" "02/01/2009" "Breakfast" "3007" "Clay,
Cassius" <--- return this one
13 "Pizza Hut" "100" "Mouse, Minnie" "06/01/2009" "Lunch" "3007" "Clay,
Cassius " <--- return this one
14 "Smoothie King" "25860" "Pitts, Omar" "03/15/2009" "Lunch" "7746"
"Dodger, Ron" <--- return this one
15 "Smoothie King" "25860" "Pitts, Emma" "03/15/2009" "Lunch" "7746"
"Dodger, Ron"
16 "Smoothie King" "25860" "Pitts, Salma" "03/15/2009" "Lunch" "7746"
"Dodger, Ron"

Thanks for any help!
Click to expand...
Click to expand...
 
K

KARL DEWEY

I get this error message: You tried to execute a query that does not
include the specified expression 'Worker Name' as part of an aggregate
function.
You did not remove the hard return inserted by the copying and pasting
actions --
.....
tblReferrals.[Worker ---- Hard return here
Name]
ORDER BY tblReferrals.[Contract #], ....

This will give you the last without tblReferrals.ID --
SELECT tblReferrals.[Contract #], tblReferrals.[Contract Name],
tblReferrals.[Client Name], tblReferrals.Service, Max(tblReferrals.[Visit
Date]) AS Last_Date, tblReferrals.[Worker Name], CStr(tblReferrals.[Contract
#])+tblReferrals.[Worker Name] AS horne
FROM tblReferrals
GROUP BY tblReferrals.[Contract #], tblReferrals.[Contract Name],
tblReferrals.[Client Name], tblReferrals.Service, tblReferrals.[Worker Name],
CStr(tblReferrals.[Contract #])+tblReferrals.[Worker Name]
ORDER BY tblReferrals.[Contract #], tblReferrals.[Worker Name];

You can then join this query with tblReferrals on tblReferrals.[Worker Name]
and tblReferrals.[Visit Date] to get the ID related to that combination.


Michael Horne said:
Karl Dewey,

Sir, my humblest apologies for not being clear. Please forgive me as I am a
new Access 2003 user. I sometimes have a difficult time expressing in
written form what my mind is thinking.

I have a table. I need to get a survey done for each unique Worker who does
a referral at a Contractor. (Worker+Contractor) A worker will get more than
one survey if they refer to more than one contractor. It does not matter how
many clients that worker sends to the same contractor. It does not matter
the dates. So, if a worker refers to a unique contractor 100 times, I would
just like to show a single row of values. The row can be the First, Last, or
whatever. I just need to be able to say: "On this date you sent client
"xxxxx" to contractor "xxxx" for service "xxxx". Did your client enjoy the
visit? Out of my example below I need it to return just 7 rows. If I get
the ID field in a list, then I am thinking I can create another query to
join, right?

By the way, when I run the query below:
SELECT tblReferrals.ID, tblReferrals.[Contract #], tblReferrals.[Contract
Name] AS Expr1, tblReferrals.[Client Name], tblReferrals.Service,
Max(tblReferrals.[Visit Date]) AS Last_Date, tblReferrals.[Worker Name],
CStr(tblReferrals.[Contract #])+tblReferrals.[Worker Name] AS horne
FROM tblReferrals
GROUP BY tblReferrals.ID, tblReferrals.[Contract #], tblReferrals.[Contract
Name], tblReferrals.[Client Name], tblReferrals.Service,
CStr(tblReferrals.[Contract #])+tblReferrals.[Worker Name],
tblReferrals.[Worker
Name]
ORDER BY tblReferrals.[Contract #], tblReferrals.[Worker Name];

I get this error message:
You tried to execute a query that does not include the specified expression
'Worker Name' as part of an aggregate function.

Thank you so much for sharing your knowledge with me,

Michael Horne


KARL DEWEY said:
How do I get it to only return the ID for the row I need?
Click to expand...
There is no single row as you pull [Contract #]) and [Worker Name] when
there are multiple tblReferrals.ID for that combination.

You could pull the last tblReferrals.ID based on [Visit Date].
SELECT tblReferrals.ID, tblReferrals.[Contract #], tblReferrals.[Contract
Name], tblReferrals.[Client Name], tblReferrals.Service,
Max(tblReferrals.[Visit Date]) AS Last_Date, tblReferrals.[Worker Name],
CStr(tblReferrals.[Contract #])+tblReferrals.[Worker Name] AS horne
FROM tblReferrals
GROUP BY tblReferrals.ID, tblReferrals.[Contract #], tblReferrals.[Contract
Name], tblReferrals.[Client Name], tblReferrals.Service, tblReferrals.[Worker
Name], CStr(tblReferrals.[Contract #])+tblReferrals.[Worker Name]
ORDER BY tblReferrals.[Contract #], tblReferrals.[Worker Name];


Michael Horne said:
Need a little help here. I have a table that collects information on
Referrals made to each Contractor from each Worker. I need a query to return
results for us to contact the Worker and survey them on their Client
experience at the Contractor.

Referral table has 6936 records
The ID field is an autonumber field.

I have created qryReferral1 (6936 records returned) below:

SELECT tblReferrals.ID, tblReferrals.[Contract #], tblReferrals.[Contract
Name], tblReferrals.[Client Name], tblReferrals.Service, tblReferrals.[Visit
Date], tblReferrals.[Worker Name], CStr(tblReferrals.[Contract
#])+tblReferrals.[Worker Name] AS horne
FROM tblReferrals
ORDER BY tblReferrals.[Contract #], tblReferrals.[Worker Name];


I have created qryReferral2 (3149 records returned) below:

SELECT DISTINCT qryReferrals1.horne, qryReferrals1.[Contract #]
FROM qryReferrals1;

I must have the field ID in the results, but when I add, it returns every
row! (6936) How do I get it to only return the ID for the row I need?

Referral table contains this data below:
"ID" "Contractor Name" "Contract #" "Client Name" "Visit Date" "Service"
"Worker ID" Worker Name"
1 "Pizza Hut" "100" "Mouse, Micky" "01/01/2009" "Lunch" "1234" "Sikes, Sam"
<--- return this one
2 "Pizza Hut" "100" "Mouse, Minnie" "01/01/2009" "Lunch" "1234" "Sikes, Sam"
3 "Pizza Hut" "100" "Mouse, Joe" "01/01/2009" "Lunch" "1234" "Sikes, Sam"
4 "Pizza Hut" "100" "Mouse, Henry" "01/01/2009" "Lunch" "1234" "Sikes, Sam"
5 "Pizza Hut" "100" "Mouse, Micky" "05/05/2009" "Lunch" "1234" "Sikes, Sam"
6 "Pizza Hut" "100" "Mouse, Minnie" "06/01/2009" "Dinner" "1234" "Sikes, Sam"
7 "Cheese Cafe" "2309" "Mouse, Micky" "03/06/2009" "Lunch" "1234" "Sikes,
Sam" <--- return this one
8 "Cheese Cafe" "2309" "Mouse, Minnie" "04/07/2009" "Breakfast" "1234"
"Sikes, Sam"
9 "Tim's Tacos" "4110" "Jones, Roy" "02/14/2009" "Dinner" "1234" "Sikes,
Sam" <--- return this one
10 "Tim's Tacos" "4110" "Jones, Tamera" "02/14/2009" "Dinner" "1234" "Sikes,
Sam"
11 "Tim's Tacos" "4110" "Mouse, Henry" "02/01/2009" "Breakfast" "9111"
"Castro, Kim" <--- return this one
12 "Lisa's Tofu" "1999" "Lee, Wu" "02/01/2009" "Breakfast" "3007" "Clay,
Cassius" <--- return this one
13 "Pizza Hut" "100" "Mouse, Minnie" "06/01/2009" "Lunch" "3007" "Clay,
Cassius " <--- return this one
14 "Smoothie King" "25860" "Pitts, Omar" "03/15/2009" "Lunch" "7746"
"Dodger, Ron" <--- return this one
15 "Smoothie King" "25860" "Pitts, Emma" "03/15/2009" "Lunch" "7746"
"Dodger, Ron"
16 "Smoothie King" "25860" "Pitts, Salma" "03/15/2009" "Lunch" "7746"
"Dodger, Ron"

Thanks for any help!
Click to expand...
Click to expand...
Click to expand...
 
M

Michael Horne

Ok, I removed each instance and made sure that the [Worker Name] did not
break at the line and start a new line. It did run and it returned 6936
results.

I have tried your 2nd suggestion below:
SELECT tblReferrals.[Contract #], tblReferrals.[Contract Name],
tblReferrals.[Client Name], tblReferrals.Service, Max(tblReferrals.[Visit
Date])
AS Last_Date, tblReferrals.[Worker Name], CStr(tblReferrals.[Contract
#])+tblReferrals.[Worker Name] AS horne
FROM tblReferrals
GROUP BY tblReferrals.[Contract #], tblReferrals.[Contract Name],
tblReferrals.[Client Name], tblReferrals.Service, tblReferrals.[Worker Name],
CStr(tblReferrals.[Contract #])+tblReferrals.[Worker Name]
ORDER BY tblReferrals.[Contract #], tblReferrals.[Worker Name];

and it did run and return 6748 results.
I have tried your last part of the 2ndsuggestion below:
SELECT tblReferrals.ID
FROM tblReferrals RIGHT JOIN x2 ON (tblReferrals.[Worker Name] = x2.[Worker
Name])
AND (tblReferrals.[Visit Date] = x2.Last_Date);

and it did run and return 11300 results. I know the final result has to
equal 3149 results, so I know I am still doing something wrong. Where did I
make a mistake?

--
Michael Horne


KARL DEWEY said:
include the specified expression 'Worker Name' as part of an aggregate
function.
You did not remove the hard return inserted by the copying and pasting
actions --
....
tblReferrals.[Worker ---- Hard return here
Name]
ORDER BY tblReferrals.[Contract #], ....

This will give you the last without tblReferrals.ID --
SELECT tblReferrals.[Contract #], tblReferrals.[Contract Name],
tblReferrals.[Client Name], tblReferrals.Service, Max(tblReferrals.[Visit
Date]) AS Last_Date, tblReferrals.[Worker Name], CStr(tblReferrals.[Contract
#])+tblReferrals.[Worker Name] AS horne
FROM tblReferrals
GROUP BY tblReferrals.[Contract #], tblReferrals.[Contract Name],
tblReferrals.[Client Name], tblReferrals.Service, tblReferrals.[Worker Name],
CStr(tblReferrals.[Contract #])+tblReferrals.[Worker Name]
ORDER BY tblReferrals.[Contract #], tblReferrals.[Worker Name];

You can then join this query with tblReferrals on tblReferrals.[Worker Name]
and tblReferrals.[Visit Date] to get the ID related to that combination.


Michael Horne said:
Karl Dewey,

Sir, my humblest apologies for not being clear. Please forgive me as I am a
new Access 2003 user. I sometimes have a difficult time expressing in
written form what my mind is thinking.

I have a table. I need to get a survey done for each unique Worker who does
a referral at a Contractor. (Worker+Contractor) A worker will get more than
one survey if they refer to more than one contractor. It does not matter how
many clients that worker sends to the same contractor. It does not matter
the dates. So, if a worker refers to a unique contractor 100 times, I would
just like to show a single row of values. The row can be the First, Last, or
whatever. I just need to be able to say: "On this date you sent client
"xxxxx" to contractor "xxxx" for service "xxxx". Did your client enjoy the
visit? Out of my example below I need it to return just 7 rows. If I get
the ID field in a list, then I am thinking I can create another query to
join, right?

By the way, when I run the query below:
SELECT tblReferrals.ID, tblReferrals.[Contract #], tblReferrals.[Contract
Name] AS Expr1, tblReferrals.[Client Name], tblReferrals.Service,
Max(tblReferrals.[Visit Date]) AS Last_Date, tblReferrals.[Worker Name],
CStr(tblReferrals.[Contract #])+tblReferrals.[Worker Name] AS horne
FROM tblReferrals
GROUP BY tblReferrals.ID, tblReferrals.[Contract #], tblReferrals.[Contract
Name], tblReferrals.[Client Name], tblReferrals.Service,
CStr(tblReferrals.[Contract #])+tblReferrals.[Worker Name],
tblReferrals.[Worker
Name]
ORDER BY tblReferrals.[Contract #], tblReferrals.[Worker Name];

I get this error message:
You tried to execute a query that does not include the specified expression
'Worker Name' as part of an aggregate function.

Thank you so much for sharing your knowledge with me,

Michael Horne


KARL DEWEY said:
How do I get it to only return the ID for the row I need?
There is no single row as you pull [Contract #]) and [Worker Name] when
there are multiple tblReferrals.ID for that combination.

You could pull the last tblReferrals.ID based on [Visit Date].
SELECT tblReferrals.ID, tblReferrals.[Contract #], tblReferrals.[Contract
Name], tblReferrals.[Client Name], tblReferrals.Service,
Max(tblReferrals.[Visit Date]) AS Last_Date, tblReferrals.[Worker Name],
CStr(tblReferrals.[Contract #])+tblReferrals.[Worker Name] AS horne
FROM tblReferrals
GROUP BY tblReferrals.ID, tblReferrals.[Contract #], tblReferrals.[Contract
Name], tblReferrals.[Client Name], tblReferrals.Service, tblReferrals.[Worker
Name], CStr(tblReferrals.[Contract #])+tblReferrals.[Worker Name]
ORDER BY tblReferrals.[Contract #], tblReferrals.[Worker Name];


:

Need a little help here. I have a table that collects information on
Referrals made to each Contractor from each Worker. I need a query to return
results for us to contact the Worker and survey them on their Client
experience at the Contractor.

Referral table has 6936 records
The ID field is an autonumber field.

I have created qryReferral1 (6936 records returned) below:

SELECT tblReferrals.ID, tblReferrals.[Contract #], tblReferrals.[Contract
Name], tblReferrals.[Client Name], tblReferrals.Service, tblReferrals.[Visit
Date], tblReferrals.[Worker Name], CStr(tblReferrals.[Contract
#])+tblReferrals.[Worker Name] AS horne
FROM tblReferrals
ORDER BY tblReferrals.[Contract #], tblReferrals.[Worker Name];


I have created qryReferral2 (3149 records returned) below:

SELECT DISTINCT qryReferrals1.horne, qryReferrals1.[Contract #]
FROM qryReferrals1;

I must have the field ID in the results, but when I add, it returns every
row! (6936) How do I get it to only return the ID for the row I need?

Referral table contains this data below:
"ID" "Contractor Name" "Contract #" "Client Name" "Visit Date" "Service"
"Worker ID" Worker Name"
1 "Pizza Hut" "100" "Mouse, Micky" "01/01/2009" "Lunch" "1234" "Sikes, Sam"
<--- return this one
2 "Pizza Hut" "100" "Mouse, Minnie" "01/01/2009" "Lunch" "1234" "Sikes, Sam"
3 "Pizza Hut" "100" "Mouse, Joe" "01/01/2009" "Lunch" "1234" "Sikes, Sam"
4 "Pizza Hut" "100" "Mouse, Henry" "01/01/2009" "Lunch" "1234" "Sikes, Sam"
5 "Pizza Hut" "100" "Mouse, Micky" "05/05/2009" "Lunch" "1234" "Sikes, Sam"
6 "Pizza Hut" "100" "Mouse, Minnie" "06/01/2009" "Dinner" "1234" "Sikes, Sam"
7 "Cheese Cafe" "2309" "Mouse, Micky" "03/06/2009" "Lunch" "1234" "Sikes,
Sam" <--- return this one
8 "Cheese Cafe" "2309" "Mouse, Minnie" "04/07/2009" "Breakfast" "1234"
"Sikes, Sam"
9 "Tim's Tacos" "4110" "Jones, Roy" "02/14/2009" "Dinner" "1234" "Sikes,
Sam" <--- return this one
10 "Tim's Tacos" "4110" "Jones, Tamera" "02/14/2009" "Dinner" "1234" "Sikes,
Sam"
11 "Tim's Tacos" "4110" "Mouse, Henry" "02/01/2009" "Breakfast" "9111"
"Castro, Kim" <--- return this one
12 "Lisa's Tofu" "1999" "Lee, Wu" "02/01/2009" "Breakfast" "3007" "Clay,
Cassius" <--- return this one
13 "Pizza Hut" "100" "Mouse, Minnie" "06/01/2009" "Lunch" "3007" "Clay,
Cassius " <--- return this one
14 "Smoothie King" "25860" "Pitts, Omar" "03/15/2009" "Lunch" "7746"
"Dodger, Ron" <--- return this one
15 "Smoothie King" "25860" "Pitts, Emma" "03/15/2009" "Lunch" "7746"
"Dodger, Ron"
16 "Smoothie King" "25860" "Pitts, Salma" "03/15/2009" "Lunch" "7746"
"Dodger, Ron"

Thanks for any help!
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

Maybe add some more joins.
[Contract #]
[Contract Name]
[Client Name]
Service

Just do one at a time.

Michael Horne said:
Ok, I removed each instance and made sure that the [Worker Name] did not
break at the line and start a new line. It did run and it returned 6936
results.

I have tried your 2nd suggestion below:
SELECT tblReferrals.[Contract #], tblReferrals.[Contract Name],
tblReferrals.[Client Name], tblReferrals.Service, Max(tblReferrals.[Visit
Date])
AS Last_Date, tblReferrals.[Worker Name], CStr(tblReferrals.[Contract
#])+tblReferrals.[Worker Name] AS horne
FROM tblReferrals
GROUP BY tblReferrals.[Contract #], tblReferrals.[Contract Name],
tblReferrals.[Client Name], tblReferrals.Service, tblReferrals.[Worker Name],
CStr(tblReferrals.[Contract #])+tblReferrals.[Worker Name]
ORDER BY tblReferrals.[Contract #], tblReferrals.[Worker Name];

and it did run and return 6748 results.
I have tried your last part of the 2ndsuggestion below:
SELECT tblReferrals.ID
FROM tblReferrals RIGHT JOIN x2 ON (tblReferrals.[Worker Name] = x2.[Worker
Name])
AND (tblReferrals.[Visit Date] = x2.Last_Date);

and it did run and return 11300 results. I know the final result has to
equal 3149 results, so I know I am still doing something wrong. Where did I
make a mistake?

--
Michael Horne


KARL DEWEY said:
I get this error message: You tried to execute a query that does not
Click to expand...
include the specified expression 'Worker Name' as part of an aggregate
function.
You did not remove the hard return inserted by the copying and pasting
actions --
....
tblReferrals.[Worker ---- Hard return here
Name]
ORDER BY tblReferrals.[Contract #], ....

This will give you the last without tblReferrals.ID --
SELECT tblReferrals.[Contract #], tblReferrals.[Contract Name],
tblReferrals.[Client Name], tblReferrals.Service, Max(tblReferrals.[Visit
Date]) AS Last_Date, tblReferrals.[Worker Name], CStr(tblReferrals.[Contract
#])+tblReferrals.[Worker Name] AS horne
FROM tblReferrals
GROUP BY tblReferrals.[Contract #], tblReferrals.[Contract Name],
tblReferrals.[Client Name], tblReferrals.Service, tblReferrals.[Worker Name],
CStr(tblReferrals.[Contract #])+tblReferrals.[Worker Name]
ORDER BY tblReferrals.[Contract #], tblReferrals.[Worker Name];

You can then join this query with tblReferrals on tblReferrals.[Worker Name]
and tblReferrals.[Visit Date] to get the ID related to that combination.


Michael Horne said:
Karl Dewey,

Sir, my humblest apologies for not being clear. Please forgive me as I am a
new Access 2003 user. I sometimes have a difficult time expressing in
written form what my mind is thinking.

I have a table. I need to get a survey done for each unique Worker who does
a referral at a Contractor. (Worker+Contractor) A worker will get more than
one survey if they refer to more than one contractor. It does not matter how
many clients that worker sends to the same contractor. It does not matter
the dates. So, if a worker refers to a unique contractor 100 times, I would
just like to show a single row of values. The row can be the First, Last, or
whatever. I just need to be able to say: "On this date you sent client
"xxxxx" to contractor "xxxx" for service "xxxx". Did your client enjoy the
visit? Out of my example below I need it to return just 7 rows. If I get
the ID field in a list, then I am thinking I can create another query to
join, right?

By the way, when I run the query below:
SELECT tblReferrals.ID, tblReferrals.[Contract #], tblReferrals.[Contract
Name] AS Expr1, tblReferrals.[Client Name], tblReferrals.Service,
Max(tblReferrals.[Visit Date]) AS Last_Date, tblReferrals.[Worker Name],
CStr(tblReferrals.[Contract #])+tblReferrals.[Worker Name] AS horne
FROM tblReferrals
GROUP BY tblReferrals.ID, tblReferrals.[Contract #], tblReferrals.[Contract
Name], tblReferrals.[Client Name], tblReferrals.Service,
CStr(tblReferrals.[Contract #])+tblReferrals.[Worker Name],
tblReferrals.[Worker
Name]
ORDER BY tblReferrals.[Contract #], tblReferrals.[Worker Name];

I get this error message:
You tried to execute a query that does not include the specified expression
'Worker Name' as part of an aggregate function.

Thank you so much for sharing your knowledge with me,

Michael Horne


:

How do I get it to only return the ID for the row I need?
There is no single row as you pull [Contract #]) and [Worker Name] when
there are multiple tblReferrals.ID for that combination.

You could pull the last tblReferrals.ID based on [Visit Date].
SELECT tblReferrals.ID, tblReferrals.[Contract #], tblReferrals.[Contract
Name], tblReferrals.[Client Name], tblReferrals.Service,
Max(tblReferrals.[Visit Date]) AS Last_Date, tblReferrals.[Worker Name],
CStr(tblReferrals.[Contract #])+tblReferrals.[Worker Name] AS horne
FROM tblReferrals
GROUP BY tblReferrals.ID, tblReferrals.[Contract #], tblReferrals.[Contract
Name], tblReferrals.[Client Name], tblReferrals.Service, tblReferrals.[Worker
Name], CStr(tblReferrals.[Contract #])+tblReferrals.[Worker Name]
ORDER BY tblReferrals.[Contract #], tblReferrals.[Worker Name];


:

Need a little help here. I have a table that collects information on
Referrals made to each Contractor from each Worker. I need a query to return
results for us to contact the Worker and survey them on their Client
experience at the Contractor.

Referral table has 6936 records
The ID field is an autonumber field.

I have created qryReferral1 (6936 records returned) below:

SELECT tblReferrals.ID, tblReferrals.[Contract #], tblReferrals.[Contract
Name], tblReferrals.[Client Name], tblReferrals.Service, tblReferrals.[Visit
Date], tblReferrals.[Worker Name], CStr(tblReferrals.[Contract
#])+tblReferrals.[Worker Name] AS horne
FROM tblReferrals
ORDER BY tblReferrals.[Contract #], tblReferrals.[Worker Name];


I have created qryReferral2 (3149 records returned) below:

SELECT DISTINCT qryReferrals1.horne, qryReferrals1.[Contract #]
FROM qryReferrals1;

I must have the field ID in the results, but when I add, it returns every
row! (6936) How do I get it to only return the ID for the row I need?

Referral table contains this data below:
"ID" "Contractor Name" "Contract #" "Client Name" "Visit Date" "Service"
"Worker ID" Worker Name"
1 "Pizza Hut" "100" "Mouse, Micky" "01/01/2009" "Lunch" "1234" "Sikes, Sam"
<--- return this one
2 "Pizza Hut" "100" "Mouse, Minnie" "01/01/2009" "Lunch" "1234" "Sikes, Sam"
3 "Pizza Hut" "100" "Mouse, Joe" "01/01/2009" "Lunch" "1234" "Sikes, Sam"
4 "Pizza Hut" "100" "Mouse, Henry" "01/01/2009" "Lunch" "1234" "Sikes, Sam"
5 "Pizza Hut" "100" "Mouse, Micky" "05/05/2009" "Lunch" "1234" "Sikes, Sam"
6 "Pizza Hut" "100" "Mouse, Minnie" "06/01/2009" "Dinner" "1234" "Sikes, Sam"
7 "Cheese Cafe" "2309" "Mouse, Micky" "03/06/2009" "Lunch" "1234" "Sikes,
Sam" <--- return this one
8 "Cheese Cafe" "2309" "Mouse, Minnie" "04/07/2009" "Breakfast" "1234"
"Sikes, Sam"
9 "Tim's Tacos" "4110" "Jones, Roy" "02/14/2009" "Dinner" "1234" "Sikes,
Sam" <--- return this one
10 "Tim's Tacos" "4110" "Jones, Tamera" "02/14/2009" "Dinner" "1234" "Sikes,
Sam"
11 "Tim's Tacos" "4110" "Mouse, Henry" "02/01/2009" "Breakfast" "9111"
"Castro, Kim" <--- return this one
12 "Lisa's Tofu" "1999" "Lee, Wu" "02/01/2009" "Breakfast" "3007" "Clay,
Cassius" <--- return this one
13 "Pizza Hut" "100" "Mouse, Minnie" "06/01/2009" "Lunch" "3007" "Clay,
Cassius " <--- return this one
14 "Smoothie King" "25860" "Pitts, Omar" "03/15/2009" "Lunch" "7746"
"Dodger, Ron" <--- return this one
15 "Smoothie King" "25860" "Pitts, Emma" "03/15/2009" "Lunch" "7746"
"Dodger, Ron"
16 "Smoothie King" "25860" "Pitts, Salma" "03/15/2009" "Lunch" "7746"
"Dodger, Ron"

Thanks for any help!
Click to expand...
Click to expand...
Click to expand...
 
Top