Duplicates in query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I run my query but instead of listing just the childs name for each social
worker, it lists all the childrens names under each social worker.
 
post the sql of your query. View: SQL from the menu and then copy and
paste.

Without seeing your query, I would guess that you have two tables
SocialWorkers
Clients (children)
and that you have put them both into a query, but have not set up a join
between the two tables - for instance from SocialWorker.ID to
Clients.ChildSocialWorkerID

If you don't set up a join on the tables (drag from one field to the
corresponding field in the other table) then you will get each social worker
associated with every child.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
How do I drag from one field to the other? Are you talking about with both
tables open? I am using access 2007.
 
In query design view, click on the relevant field in table one and while
holding down the mouse button drag to the relevant field in table two.

At least that is the way it works in versions prior to 2007.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
SELECT Client.[PFS SW], Client.[Client Last Name], Client.[Client First
Name], Client.Siblings, Client.[County SW], Client.[Client DOI],
Client.[Client DOB], Client.[Foster Home], [Foster Home].[Foster Mother],
[Foster Home].[Foster Father], [Foster Home].[Foster Home] & ", " & [Foster
Mother] & " & "+[Foster Father] & " " & [FH Address] & ", " &
[FH City] & ", " & [FH State] & ", " & [FH Zip] & " " & [FH Phone] AS
FosterHome, Client.Terminated
FROM ((Client INNER JOIN [PFS SW] ON Client.[PFS SW] = [PFS SW].[PFS SW])
INNER JOIN [Foster Home] ON Client.[Foster Home] = [Foster Home].[Foster
Home]) INNER JOIN CountySW ON Client.[County SW] = CountySW.[County SW]
WHERE (((Client.[PFS SW])="Edith McCormick") AND ((Client.Terminated)=No));
 
I don't understand your problem. This should be listing the SW and each
child (Client) assigned to the social worker. So you should be
getting all clients for Edith McCormick that have not been terminated.

One thing I do notice is that you probably don't need the CountySW table
in the query at all.
SELECT Client.[PFS SW]
, Client.[Client Last Name]
, Client.[Client First Name], Client.Siblings
, Client.[County SW]
, Client.[Client DOI]
, Client.[Client DOB]
, Client.[Foster Home]
, [Foster Home].[Foster Mother]
, [Foster Home].[Foster Father]
, [Foster Home].[Foster Home] & ", " & [Foster Mother] & " & " +
[Foster Father] & " " & [FH Address] & ", " &
[FH City] & ", " & [FH State] & ", " & [FH Zip] & " " & [FH Phone] AS
FosterHome
, Client.Terminated
FROM ((Client INNER JOIN [PFS SW]
ON Client.[PFS SW] = [PFS SW].[PFS SW])
INNER JOIN [Foster Home]
ON Client.[Foster Home] = [Foster Home].[Foster Home])
INNER JOIN CountySW
ON Client.[County SW] = CountySW.[County SW]
WHERE (((Client.[PFS SW])="Edith McCormick") AND ((Client.Terminated)=No));

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

SELECT Client.[PFS SW], Client.[Client Last Name], Client.[Client First
Name], Client.Siblings, Client.[County SW], Client.[Client DOI],
Client.[Client DOB], Client.[Foster Home], [Foster Home].[Foster Mother],
[Foster Home].[Foster Father], [Foster Home].[Foster Home] & ", " & [Foster
Mother] & " & "+[Foster Father] & " " & [FH Address] & ", " &
[FH City] & ", " & [FH State] & ", " & [FH Zip] & " " & [FH Phone] AS
FosterHome, Client.Terminated
FROM ((Client INNER JOIN [PFS SW] ON Client.[PFS SW] = [PFS SW].[PFS SW])
INNER JOIN [Foster Home] ON Client.[Foster Home] = [Foster Home].[Foster
Home]) INNER JOIN CountySW ON Client.[County SW] = CountySW.[County SW]
WHERE (((Client.[PFS SW])="Edith McCormick") AND ((Client.Terminated)=No));


John Spencer said:
post the sql of your query. View: SQL from the menu and then copy and
paste.

Without seeing your query, I would guess that you have two tables
SocialWorkers
Clients (children)
and that you have put them both into a query, but have not set up a join
between the two tables - for instance from SocialWorker.ID to
Clients.ChildSocialWorkerID

If you don't set up a join on the tables (drag from one field to the
corresponding field in the other table) then you will get each social worker
associated with every child.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
Click to expand...
Click to expand...
 
Back
Top