Solved

Microsoft Access query help

Posted on 2013-01-20
14
303 Views
Last Modified: 2013-01-21
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
Comment
Question by:J.R. Sitman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 2
  • +1
14 Comments
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 38798703
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
 
LVL 77

Expert Comment

by:peter57r
ID: 38798705
So how do you decide whiich of the other fields to show, since they can be different.
0
 
LVL 15

Expert Comment

by:gplana
ID: 38798712
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:J.R. Sitman
ID: 38798749
database is too large to post.  Attached are the fields I need.
dldisposition2.jpg
0
 
LVL 15

Expert Comment

by:gplana
ID: 38798777
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
 

Author Comment

by:J.R. Sitman
ID: 38798785
Maybe this will clarify what you need.  Each Softslip is only linked to 1 name,
0
 
LVL 77

Expert Comment

by:peter57r
ID: 38798789
Your sample showed different names.  Was the sample not real data then?
0
 

Author Comment

by:J.R. Sitman
ID: 38798848
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
 
LVL 77

Expert Comment

by:peter57r
ID: 38798856
In the version I see there two different names for 10-0325
0
 

Author Comment

by:J.R. Sitman
ID: 38798872
sorry missed that.  That is a user input error.  Never should be two names for 1 softslip.
0
 
LVL 77

Accepted Solution

by:
peter57r earned 400 total points
ID: 38800132
Then in your original query simply add the word Distinct after Select.

Select Distinct .........
0
 

Author Comment

by:J.R. Sitman
ID: 38801166
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
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 400 total points
ID: 38801240
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
 

Author Closing Comment

by:J.R. Sitman
ID: 38801615
Thanks.  I now understand what you're saying.  I removed the fields with different values and it is working.
0

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question