Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 309
  • Last Modified:

Microsoft Access query help

I need this query to only list the softslip once.  See attachment for what it is currently displaying.  1st column is the softslip.


SELECT SoftSlips.SoftSlip, qryAnimalOwners.ContactID, qryAnimalOwners.OwnerID, qryAnimalOwners.DriversLicense, qryAnimalOwners.Firstname, qryAnimalOwners.LastName, [Address1] & (" "+[CONtblAddresses].[Direction]) & (" "+[CONtblAddresses].[Street]) & (" "+[CONtblAddresses].[Suffix]) & (" Unit "+[CONtblAddresses].[Unit]) AS Addr, [city] & ", " & [state] & " " & [zip] AS MailingCity, SoftSlips.Action, SoftSlips.ActionDate
FROM (CONtblPeople INNER JOIN (SoftSlips INNER JOIN qryAnimalOwners ON SoftSlips.SoftSlip = qryAnimalOwners.SoftSlip) ON CONtblPeople.ContactID = qryAnimalOwners.ContactID) INNER JOIN CONtblAddresses ON CONtblPeople.ContactID = CONtblAddresses.ContactID
WHERE (((SoftSlips.Action)="rto"));
dldisposition.jpg
0
J.R. Sitman
Asked:
J.R. Sitman
  • 6
  • 5
  • 2
  • +1
2 Solutions
 
Helen FeddemaCommented:
Can you post the database?  It is a lot easier to debug query problems in the query, as opposed to just looking at the SQL.
0
 
peter57rCommented:
So how do you decide whiich of the other fields to show, since they can be different.
0
 
gplanaCommented:
I think you want to use a group by query, but you should define what do you want to get and how are the involved tables defined.

Thanks.
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
J.R. SitmanIT DirectorAuthor Commented:
database is too large to post.  Attached are the fields I need.
dldisposition2.jpg
0
 
gplanaCommented:
But if you want to show distinct values for softslip you can't show the other fields unless you want just the min or max value of these fields.

Imagine you have this data:

  softslip | FirstName
1/1/2011   John
1/1/2011  Maria

In this case you want to show softslip 1/1/2011, but which name? You can do something like this:

SELECT softslip, MIN(FirstName)
FROM yourtable
GROUP BY softslip;

In this case just 1 value for softslip will be shown, and the minimum name for every softslip will be shown.

Hope it helps to understand the point. Regards.
0
 
J.R. SitmanIT DirectorAuthor Commented:
Maybe this will clarify what you need.  Each Softslip is only linked to 1 name,
0
 
peter57rCommented:
Your sample showed different names.  Was the sample not real data then?
0
 
J.R. SitmanIT DirectorAuthor Commented:
the first sample was real data and the highlighted portions show the same names for each softslip.  Not sure what you're referring to?
0
 
peter57rCommented:
In the version I see there two different names for 10-0325
0
 
J.R. SitmanIT DirectorAuthor Commented:
sorry missed that.  That is a user input error.  Never should be two names for 1 softslip.
0
 
peter57rCommented:
Then in your original query simply add the word Distinct after Select.

Select Distinct .........
0
 
J.R. SitmanIT DirectorAuthor Commented:
I'll still get multiple Softslip numbers.  Is the query below correct?

SELECT Distinct SoftSlips.SoftSlip AS Expr1, qryAnimalOwners.DriversLicense, [Address1] & (" "+[CONtblAddresses].[Direction]) & (" "+[CONtblAddresses].[Street]) & (" "+[CONtblAddresses].[Suffix]) & (" Unit "+[CONtblAddresses].[Unit]) AS Addr, [city] & ", " & [state] & " " & [zip] AS MailingCity, CONtblPhones.Phone, CONtblPhones.PhoneType, SoftSlips.Action
FROM (((CONtblPeople INNER JOIN (SoftSlips INNER JOIN qryAnimalOwners ON SoftSlips.SoftSlip = qryAnimalOwners.SoftSlip) ON CONtblPeople.ContactID = qryAnimalOwners.ContactID) INNER JOIN CONtblAddresses ON CONtblPeople.ContactID = CONtblAddresses.ContactID) INNER JOIN CONtblPhones ON CONtblPeople.ContactID = CONtblPhones.ContactID) INNER JOIN Claims ON SoftSlips.SoftSlip = Claims.SoftSlip
WHERE (((qryAnimalOwners.DriversLicense)=[Enter DL #]) AND ((SoftSlips.Action)="rto"));
0
 
peter57rCommented:
If you are getiing duplicates of the softslip value it means that there are other fields in your results that have different values for the same softslip value.  You will see all the different values.

You need to think through exactly what you want to see in ALL the fields and if you can say what that is then maybe we can help you get to that point.
0
 
J.R. SitmanIT DirectorAuthor Commented:
Thanks.  I now understand what you're saying.  I removed the fields with different values and it is working.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 6
  • 5
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now