?
Solved

Grouping Query

Posted on 2013-12-04
18
Medium Priority
?
340 Views
Last Modified: 2013-12-07
A table in my database, just the columns that matter for this example.

Field Name              Field Type
PropertyID               Long
Owner                       Text
DefaultAddressYN    YN
ActiveYN                    YN
 
Some date might be:

PropertyID               DefaultAddr            Active        Owner        
1111111                        -1                           -1             John Smith
1111111                         0                           -1             Bob Smith
2222222                         0                           0               John Jones
2222222                         0                           -1             Larry Jones
2222222                         -1                           0              Joe Glary
2222222                         0                           0              Andy Tows

-1 = Y in a YN field, 0 = No in a YN field

The datebase rules are supposed to be 'one and only one address on a property is the default'.  Unfortunately I have no control over the integrity of the data I am working with and I've found the rule has not been enforced.

Every property does have at least one address, this has been enforced.

What I want to do:
Create a query returning one and only one address for a property.
If any address is a default I want that to be the one presented.
If none are selected as a default I want one of the 'Active' addresses selected.
If none are default or active it really doesn't matter which one is presented.

In the example above I would want my result query to contain:
1111111          John Smith               -1                           -1
2222222          Joe Glary                  -1                           0

I've tried several query variations to get hwta I want but none return the correct result.

I did a query grouping PropertyID  with Min on Default, Active and Name

I figured this would pull the any default to the top, if no default was found the active would be pulled to the top and the name would go along with it.

That's not how it worked out.  The Min function just selected the lowest value in each column for each property.  If there were any defaults for a property that column contained -1, if there were any actives that column contained a -1 and the name which sorted to the top alphabetically was always selected.  

In the above example I'd end up with
1111111                        -1                           -1             Bob Smith
2222222                         -1                          -1              Andy Tows

Now I see why that didn't work but I'm stumped as to how I can get it to work the way I want.

I really don't see a way to do it in a query.  Is it possible?
0
Comment
Question by:mlcktmguy
[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
  • 7
  • 6
  • 2
  • +2
18 Comments
 
LVL 13

Expert Comment

by:SagiEDoc
ID: 39697577
A little confusing. Do you want to update (set) the default for all records that have no default or are you just trying to see any address for records with no default when you query it?
0
 
LVL 4

Expert Comment

by:ItWorked
ID: 39697655
I thought you were looking forward for something like this.
in my case 1=-1

Create Table #Example
(
 PropertyID bigint
 ,Owner nvarchar(100)
 ,DefaultAddressYN bit
 ,ActiveYN  bit
)

Insert into #Example values(1111111,'John Smith',1,1)
Insert into #Example values(1111111,'Bob Smith',0,1)
Insert into #Example values(2222222,'John Jones',0,0)
Insert into #Example values(2222222,'Larry Jones',0,1)
Insert into #Example values(2222222,'Joe Glary',1,0)
Insert into #Example values(2222222,'Andy Tows',0,0)
Insert into #Example values(2222223,'Andy Tows',0,1)

Select *
from (
		Select *,dense_rank() over(PARTITION by PropertyID order by PropertyID,DefaultAddressYN desc,ActiveYN desc)	as t	
		from #Example		
		) as myData
		where myData.t=1	


drop table #Example

Open in new window

0
 
LVL 1

Author Comment

by:mlcktmguy
ID: 39697839
SagiEDoc: Thanks for your response: Not sure what is confusing but I'll try to explain it again.

This is purely a select query, nothing is being updated.

In this address file there is at least one entry for each property but many properties have multiple entries.

In the result set of the query I want to select only one address for a property but I want it to be the 'best' address.  If any of the address entries for a property are selected as the 'Default', that would be the 'best' address.  If multiple addresses are selected as 'default' any of those would be the 'best'.

If none of the addresses have the 'DefaultYN' field checked then any address that is 'Active' will do.

If none of the addresses are identified a 'Default' or 'Active' it really doesn't matter which address record is selected.

ItWorked: Thanks for your response also but I don't understand the select statement you have used.

Select *,dense_rank() over(PARTITION by PropertyID order by PropertyID,DefaultAddressYN desc,ActiveYN desc)      as t      

I am not familiar with the function 'dense_rank()' or the meaning of 'over (PARTITION'.
Could you please explain further.
This is being done in an MSAccess query.
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 4

Expert Comment

by:ItWorked
ID: 39697842
I thought you are SQL dev. But anyway please refer :
Access DB with dense_rank()
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 39697931
Is there a Unique ID (pk) associated with each record in this table?

If so, I would start with a query that identifies the "first" record for each PropertyID:
SELECT PropertyID, Min(ID) as MinID
FROM yourTable
WHERE DefaultAddr = -1 AND Active = -1

Open in new window

I would then use that query to identify an "Active" record from each of the other PropertyID fields, the ones which do not have a record with DefaultAddr = -1.  It would look something like:
SELECT PropertyID, Min(ID) as MinID
FROM yourTable
LEFT JOIN (
SELECT PropertyID, Min(ID) as MinID
FROM yourTable
WHERE DefaultAddr = -1 AND Active = -1
) as qryDefAdd
ON yourTable.PropertyID = qryDefAdd.PropertyID
where qyrDefAdd.MinID IS NULL
AND yourTable.Active = -1
GROUP BY PropertyID

Open in new window

If you union these together it will give you a set of PropertyID's and the MinID for each PropertyID where [Active] = -1.  You can then join that back to your table on MinID to get whatever fields you want from each of those records.  Something like:
Select yourTable.PropertyID, yourTable.DefaultAddr, yourTable.Active, yourTable.Owner
FROM (
SELECT PropertyID, Min(ID) as MinID
FROM yourTable
WHERE DefaultAddr = -1 AND Active = -1
UNION ALL
SELECT PropertyID, Min(ID) as MinID
FROM yourTable
LEFT JOIN (
SELECT PropertyID, Min(ID) as MinID
FROM yourTable
WHERE DefaultAddr = -1 AND Active = -1
) as qryDefAdd
ON yourTable.PropertyID = qryDefAdd.PropertyID
where qyrDefAdd.MinID IS NULL
AND yourTable.Active = -1
GROUP BY PropertyID
) as BestProperty
INNER JOIN yourTable
ON BestProperty.MinID = yourTable.MinID

Open in new window

You could do this using other fields (other than an ID) field, but you risk the chance of not having a unique value to use for each property.  For example, you might be able to use:
Select yourTable.PropertyID, yourTable.DefaultAddr, yourTable.Active, yourTable.Owner
FROM (
SELECT PropertyID, Min(Owner) as MinOwner
FROM yourTable
WHERE DefaultAddr = -1 AND Active = -1
UNION ALL
SELECT PropertyID, Min(Owner) as MinOwner
FROM yourTable
LEFT JOIN (
SELECT PropertyID, Min(Owner) as MinOwner
FROM yourTable
WHERE DefaultAddr = -1 AND Active = -1
) as qryDefAdd
ON yourTable.PropertyID = qryDefAdd.PropertyID
where qyrDefAdd.MinOwner IS NULL
AND yourTable.Active = -1
GROUP BY PropertyID
) as BestProperty
INNER JOIN yourTable
ON BestProperty.MinOwner = yourTable.MinOwner

Open in new window

0
 
LVL 1

Author Comment

by:mlcktmguy
ID: 39698495
fyed: I think I see where you're going but I get stopped right away when I try the first query because PropertyID is not part of an aggregate function.  This is what I have:

SELECT tblProperty_Addresses.PropertyID, Min(tblProperty_Addresses.ID) AS MinID
FROM tblProperty_Addresses
WHERE (((tblProperty_Addresses.DefaultAddress_YN)=-1) AND ((tblProperty_Addresses.Active_YN)=-1));

Open in new window


Also there is a possibility that a property has only addresses on which neither active or default is selected.
It is also possible for an property to have more than one address with default selected.
The only thing for certain is that there will always be at least one address for a property.
I have no control over the integrity of this data.
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 39698557
Sorry, forgot the Group By clause in the first query.  That should look like:
SELECT tblProperty_Addresses.PropertyID, Min(tblProperty_Addresses.ID) AS MinID
FROM tblProperty_Addresses
WHERE tblProperty_Addresses.DefaultAddress_YN=-1
AND tblProperty_Addresses.Active_YN=-1
GROUP BY  tblProperty_Addresses.PropertyID  '<---- Add this line

Open in new window

You would also need to add that line in several places in the larger query:
Select tblProperty_Addresses.PropertyID
, tblProperty_Addresses.DefaultAddr
, tblProperty_Addresses.Active
, tblProperty_Addresses.Owner
FROM (
SELECT PropertyID, Min(ID) as MinID
FROM tblProperty_Addresses
WHERE DefaultAddr = -1 AND Active = -1
GROUP BY PropertyID
UNION ALL
SELECT PropertyID, Min(ID) as MinID
FROM tblProperty_Addressestbl
LEFT JOIN (
SELECT PropertyID, Min(ID) as MinID
FROM tblProperty_Addresses
WHERE DefaultAddr = -1 AND Active = -1
GROUP BY PropertyID
) as qryDefAdd
ON tblProperty_Addresses.PropertyID = qryDefAdd.PropertyID
where qyrDefAdd.MinID IS NULL
AND tblProperty_Addresses.Active = -1
GROUP BY tblProperty_Addresses.PropertyID
) as BestProperty
INNER JOIN tblProperty_Addresses
ON BestProperty.MinID = tblProperty_Addresses.MinID

Open in new window

0
 
LVL 32

Expert Comment

by:awking00
ID: 39698591
If there were records such as -

33333333   0    -1    John Doe
33333333   0    -1    Jane Doe

Which record would you want and what's the criteria for that choice?
0
 
LVL 1

Author Comment

by:mlcktmguy
ID: 39698643
awking00: In that situation it doesn't matter which record is selected, if a criterion is necessary I could use 'DateAdded' which is also stamped on every record.  Meaning the assumption would be the record which has been added most recently  would be 'better'.
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 39698780
If more recent is better, then I would use MAX() instead of Min() and would use the DateAdded field instead of ID in my previous query.
0
 
LVL 1

Author Comment

by:mlcktmguy
ID: 39699045
Good suggestion fyed but I'm still having issues getting the original to work.  I'm getting a 'Syntax Error In Join Operation'.  I cut and pasted exactly what you posted into my query.  Here's what I have:
Select tblProperty_Addresses.PropertyID
, tblProperty_Addresses.DefaultAddr
, tblProperty_Addresses.Active
, tblProperty_Addresses.Owner
FROM (
SELECT PropertyID, Min(ID) as MinID
FROM tblProperty_Addresses
WHERE DefaultAddr = -1 AND Active = -1
GROUP BY PropertyID
UNION ALL
SELECT PropertyID, Min(ID) as MinID
FROM tblProperty_Addressestbl
LEFT JOIN (
SELECT PropertyID, Min(ID) as MinID
FROM tblProperty_Addresses
WHERE DefaultAddr = -1 AND Active = -1
GROUP BY PropertyID
) as qryDefAdd
ON tblProperty_Addresses.PropertyID = qryDefAdd.PropertyID
where qyrDefAdd.MinID IS NULL
AND tblProperty_Addresses.Active = -1
GROUP BY tblProperty_Addresses.PropertyID
) as BestProperty
INNER JOIN tblProperty_Addresses
ON BestProperty.MinID = tblProperty_Addresses.MinID

Open in new window

0
 
LVL 32

Expert Comment

by:awking00
ID: 39699172
select propertyid, owner, defaultaddr, active from
(select propertyid, defaultaddr, active, owner, dateadded,
 row_number() over (partition by propertyid order by defaultaddr, active, dateadded desc) rn
 from yourtable)
where rn = 1;
0
 
LVL 1

Author Comment

by:mlcktmguy
ID: 39699287
awking00: Thank you but this is an MS Access query, the functionality that you are using is not available in that environment.
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 39699337
Line 12 reads :

FROM tblProperty_Addressestbl

but should read:

FROM tblProperty_Addresses
0
 
LVL 1

Author Comment

by:mlcktmguy
ID: 39699750
Thanks, still getting errors.  I got 'PropertyID could refer to more than one table listed in the from clause of your sql statement

I added 'tblAddresses' qualifiers to all the references to PropertyID but then got the same error on 'tblProperty_Addesses.Active'.

I wish I could debug it myself, I know these are just syntax errors but this query is way above my SQL expertise.  I have never user Union ALL

Rather than combine things I usually do my queries in steps using the query designer.  In this case, based on your example, I create two  queries in MS Access:

'qryProperty_Addresses_Best_1_DefAndActive' with this SQL code:
SELECT tblProperty_Addresses.PropertyID, Min(tblProperty_Addresses.ID) AS MinAddrRecID
FROM tblProperty_Addresses
WHERE (((tblProperty_Addresses.DefaultAddress_YN)=True) AND ((tblProperty_Addresses.Active_YN)=True))
GROUP BY tblProperty_Addresses.PropertyID;

Open in new window



The result set of query 1 is a unique entry for all PropertyID's with Default and Active Selected.

and another query 'qryProperty_Addresses_Best_2_OnlyActive' with this SQL:
SELECT tblProperty_Addresses.[PropertyID], Min(tblProperty_Addresses.[ID]) AS MinAddrRecID
FROM tblProperty_Addresses LEFT JOIN qryProperty_Addresses_Best_1_DefAndActive ON tblProperty_Addresses.ID = qryProperty_Addresses_Best_1_DefAndActive.MinAddrRecID
WHERE (((tblProperty_Addresses.Active_YN)=True) And ((qryProperty_Addresses_Best_1_DefAndActive.MinAddrRecID) Is Null))
GROUP BY tblProperty_Addresses.[PropertyID];

Open in new window



The result set for query 2 is a a unique entry for all PropertyID's that weren't in query 1, with Active selected

My next query would have to 'Union ALL', combining the result sets from the first two queries.   I just don't know how to code a Union All query to do that.


Then I would need another query to contain a unique entry for all PropertID's *Not in query 1 or query 2) that have neither Active or Default selected.  I can code that one if I have the result set from my Union ALL of qry 1 and qry 2.

Then the final query would 'Union All' of the propertyID's is those three result sets to a single result set.

I could use the result set from the last query, joined with tblProperty_Addresses to pull the appropriate owner name.

Any help would be greatly appreciated.  I know my approach is remedial but I hate putting code into an application that I don't understand because it stops working I don't know how to correct it.
0
 
LVL 48

Accepted Solution

by:
Dale Fye earned 2000 total points
ID: 39699885
If query1 and query2 both return only the PropertyID and MinAddrRecID fields, then the Union All query would be:

SELECT query1.PropertyID, query1.MinAddrRecID FROM query1
UNION ALL
SELECT query1.PropertyID, query1.MinAddrRecID FROM query1

You would wrap that in a select statement to

SELECT myUnion.PropertyID, myUnion.MinAddrRecID
FROM (

SELECT query1.PropertyID, query1.MinAddrRecID FROM query1
UNION ALL
SELECT query1.PropertyID, query1.MinAddrRecID FROM query1
) as myUnion

once you have that, you can go back to the query design window and simply join that query to your table.
0
 
LVL 1

Author Closing Comment

by:mlcktmguy
ID: 39703098
fyed: Thanks for all of your help.  All of your answers helped get to the eventual solution.  I finally pulled all the bits together and got it to work and it's even done in a way that is understandable to my remedial SQL skill.
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 39703160
glad I could help and that you finally got it working the way you needed.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

801 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