Solved

Grouping Query

Posted on 2013-12-04
18
295 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
  • 7
  • 6
  • 2
  • +2
18 Comments
 
LVL 13

Expert Comment

by:SagiEDoc
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 4

Expert Comment

by:ItWorked
Comment Utility
I thought you are SQL dev. But anyway please refer :
Access DB with dense_rank()
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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
Comment Utility
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 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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 31

Expert Comment

by:awking00
Comment Utility
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
Comment Utility
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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
Comment Utility
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 31

Expert Comment

by:awking00
Comment Utility
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
Comment Utility
awking00: Thank you but this is an MS Access query, the functionality that you are using is not available in that environment.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
Line 12 reads :

FROM tblProperty_Addressestbl

but should read:

FROM tblProperty_Addresses
0
 
LVL 1

Author Comment

by:mlcktmguy
Comment Utility
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 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
Comment Utility
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
Comment Utility
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 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
glad I could help and that you finally got it working the way you needed.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Access 2003 form, automatically input query parameter from list 5 30
Supress Detail 4 17
Dateadd 3 19
Resize text 4 14
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

762 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now