Selecting unique records from a dataset

[Antonio]

Hi there,


From the dataset attached, I need to select records from the Attribute table where the 'Constituent ID' field is unique (i.e. where there are no duplicate ID's) AND where the 'Constituent Specific Attributes Contact Preferences Date' field is empty.

It should return a dataset containing records such as:

Constituent ID	Constituent Specific Attributes Contact Preferences Import ID	Constituent Specific Attributes Contact Preferences Description	Constituent Specific Attributes Contact Preferences Date
944	00001-528-0000137528	Postal opt out
1865	00001-528-0000338527	Email opt out

etc.

I'm sure this is very easy, but my brain has turned off today.

I like to write queries in SQL code, so if anyone could create this query in SQL and type the statement under this thread it would be much appreciated!




Thanks in advance,

Antonio

[Minty]

I like to write queries in SQL code, so if anyone could create this query in SQL and type the statement under this thread it would be much appreciated!

If your SQL skill are a little new, by far the quickest way to learn is to build and experiment in the query designer. You can then view the SQL generated and if necessary then adapt that into a VBA code if required. I can write SQL queries of the cuff, but very rarely bother particularly if there are lots of fields and calculation required. I use the tools provided in Access (or SQL Server) .

[Antonio]

Yes I take your point Minty, and I do sometimes choose that option and see what the SQL code is afterwards, to learn.

However, for the purpose of doing what I am trying to do below, by using the query designer, how do I solve the problem?

[isladogs]

CReate an aggregate query on your Attributes table to include the two fields you named and any other fields you want.
On the totals row change ConsituentID to Count and in the criteria enter 1.
For the snappily named Date field, set the criteria to Null

[Antonio]

Ah, I see!

That is a very useful principal that I will use in future, and change it to whatever the need is.

In fact, when I have access to the dataset again later this evening, I might try and apply rhat to the other problem I posted earlier today (the one titled 'Selecting records with missing dates....').

Thanks very much Colin, your help has been enormously appreciated again.




Antonio

[isladogs]

No problem.
Just looked at the other thread and you should be able to adapt this solution for that one.
I'll leave you to it.

[Antonio]

I've just looked at this again and noticed that actually the query as you suggested is not doing what I need it to do.

It selects ALL ConstituentID's where the Date field is Null, i.e. even those constituentID's where they are NOT unique.

To give an example, it returns this:

CountOfConstituent ID	Constituent ID	Constituent Specific Attributes Contact Preferences Import ID	Constituent Specific Attributes Contact Preferences Description	Constituent Specific Attributes Contact Preferences Date	Constituent Specific Attributes Contact Preferences Comments	Old Attribute Code	AddedImportID
1	2637	00001-528-0000273247	Postal opt out
1	2637	00001-528-0000273248	Telephone opt out

There are actually 3 records of ConstituentID 2637 in the dataset, so under 'CountOfConstituentID' it should be showing as 3, not 1, and therefore not be selecting this!

Any ideas?

[Antonio]

Actually, please ignore my previous post.

I have very little experience with aggregate queries and was not careful enough with assigning the correct 'Total' for all the other fields I was including in my query. I was simply selecting 'Group By' for all the other fields when by selecting things like 'First' or 'Last' returns the correct data.

Antonio

[Antonio]

UPDATE:


Actually, I thought it was doing what I wanted it to do, but there are still problems with my solution.

For ease of explanation, I have attached the sample dataset, toegether with the query that doesnt fully work.

The query returns correct data such as COnstituentID 944 (because there is only one record with this ID, and it has no date), and 2637 (there are 3 records with this ID number, and even though 1 record is missing a date, the record is not being selected because the count of the ConstituentID = 3).

However, it is also returning incorrect data such as record with ConstituentID 2259, which should not be selected because as there are 2 records, the count of the ConstituentID SHOULD BE 2, therefore not selecting the record (even though one of the 2 ID's has a date missing).

The sample database contains the dataset I am querying from (called Attribute), and the query itself (qryUniqueConstituentsNoDate).

What am I doing wrong?? Any ideas massively appreciated.


Antonio

[isladogs]

Its been a few days and I'm not sure I remember exactly what you want from this query.

Try this - it gives 1577 records

SELECT Attribute.[Constituent ID], Attribute.[Constituent Specific Attributes Contact Preferences Import ID], Attribute.[Constituent Specific Attributes Contact Preferences Description], Attribute.[Constituent Specific Attributes Contact Preferences Comments]FROM Attribute
WHERE (((Attribute.[Constituent Specific Attributes Contact Preferences Date]) Is Null))
GROUP BY Attribute.[Constituent ID], Attribute.[Constituent Specific Attributes Contact Preferences Import ID], Attribute.[Constituent Specific Attributes Contact Preferences Description], Attribute.[Constituent Specific Attributes Contact Preferences Comments]
HAVING (((Count(Attribute.[Constituent ID]))=1));

[Antonio]

It still returns incorrect data - it is returning records like 2259 that appear twice in the dataset, and therefore should not be selected, but don't worry, I applied the solution given by Gicu (Vlad) under the 'Selecting records with missing dates where multiple records exist', which basically breaks down the query into 3 parts, and that works.

Thanks for your input and help again Colin - apologies for occasional late replies to your solutions because in order to beat the deadline and my clients expectations, I am sometimes asking questions very early on to make sure I am ready with the answer when I need it.


Antonio

[isladogs]

It still returns incorrect data - it is returning records like 2259 that appear twice in the dataset, and therefore should not be selected, but don't worry, I applied the solution given by Gicu (Vlad) under the 'Selecting records with missing dates where multiple records exist', which basically breaks down the query into 3 parts, and that works.

Thanks for your input and help again Colin - apologies for occasional late replies to your solutions because in order to beat the deadline and my clients expectations, I am sometimes asking questions very early on to make sure I am ready with the answer when I need it.

Antonio

I know you've marked this solved but I just rechecked the output as I thought you want all results in my previous answer
This gives 1329 results - one for each Constituent ID where duplicates exist

SELECT Attribute.[Constituent ID], First(Attribute.[Constituent Specific Attributes Contact Preferences Import ID]) AS [FirstOfConstituent Specific Attributes Contact Preferences Import ID], First(Attribute.[Constituent Specific Attributes Contact Preferences Description]) AS [FirstOfConstituent Specific Attributes Contact Preferences Description], First(Attribute.[Constituent Specific Attributes Contact Preferences Comments]) AS [FirstOfConstituent Specific Attributes Contact Preferences Comments]FROM Attribute
WHERE (((Attribute.[Constituent Specific Attributes Contact Preferences Date]) Is Null))
GROUP BY Attribute.[Constituent ID]
HAVING (((Count(Attribute.[Constituent ID]))=1));

The reason its different is last time I used Group By for each field (to get all possible results for those fields) & this time First (to get the first result obviously)
Out of interest does that give the same as the three step approach you used instead

[Antonio]

I actually was very curious to know if this could be done as a single, aggregate query, so was very eager to test your solution.

However having tested it, it still returns data that should not be there, such as record ID 2259, which appears twice in the dataset.

When I implement the 3 step solution, I get 1175 records, which is the correct answer.

Maybe its not possible as a single query, and only splitting into 3 works?


Antonio

[isladogs]

Well now I'm confused because I had checked and there are definitely no duplicates using my query from post 12 with the dataset from post 9 in this thread.
I specifically checked 2259 and there's no dupes for that or any other ConstituentID

BTW - I'm not sure which post contains the three step solution you mentioned so I can't compare

[Antonio]

I actually had to make slight amendments to your code because it wasn't liking your alias's for some reason - but all I did, to make the query work, is replace your 3 alias names with 'One', 'Two', 'Three' respectively.

It still returns record 2259, I think this will have to remain a mystery

I do understand your logic however, and in fact I had already made the corrections before you suggested picking up the First of the extra fields in my output, because I realised that being an aggregate query, all output fields can ONLY show 1 record per grouping, but again yes I was getting records such as 2259 being picked up.

If you are curious to run the 3 step approach, its in the post titled 'Selecting records with missing dates where multiple records exist', thread #2.


Antonio