Query to select individual records

[Nirmalya Choudhury]

I have a table with 20 columns. They are Campaign, Company Name, Contact Name, Designation, Address 1, Address 2, Address 2, Address 3, City, Zip, Country, Phone, Email, Date, List Source, List Type.

I have multiple instances of interacting with a contact for each campaign.

e.g For Campaign 1 and Company A, Contact B I have 5 records of 5 differenttimes we contacted him logged by the date. I want to create a table that copies all the columns but selects only the row with the most recent interaction we had with the contact, the newest date.

I have concatenated the campaign, company and contact name to create a unique identifier. For each of those unique identifiers, I have multiple dates that they have been contacted. I want to only select the row with the most recent date's data.

It seems simple enough but when I try to use Group By for all columns and the Max date, I don't get the correct result.

Any help is appreciated.

Thanks,
Nirmalya

 

[John W. Vinson]

I have a table with 20 columns. They are Campaign, Company Name, Contact Name, Designation, Address 1, Address 2, Address 2, Address 3, City, Zip, Country, Phone, Email, Date, List Source, List Type.

I have multiple instances of interacting with a contact for each campaign.

e.g For Campaign 1 and Company A, Contact B I have 5 records of 5 different times we contacted him logged by the date. I want to create a table that copies all the columns but selects only the row with the most recent interaction we had with the contact, the newest date.

I have concatenated the campaign, company and contact name to create a unique identifier. For each of those unique identifiers, I have multiple dates that they have been contacted. I want to only select the row with the most recent date's data.

It seems simple enough but when I try to use Group By for all columns and the Max date, I don't get the correct result.

Any help is appreciated.

Thanks,
Nirmalya

You'll need a Subquery to do that. Put a criterion on the date field such by
typing

(SELECT Max(X.[Date]) FROM yourtable AS X WHERE X.Campagn = yourtable.Campaign
AND X.[Company Name] = yourtable.[Company Name] AND X.[Contact Name] =
yourtable.[Contact Name])

in the Criteria row of the query grid under the Date field.

A couple of suggestions: change the name of Date, it's a reserved word for the
builtin Date() function and Access can and will get confused; and avoid using
blanks in fieldnames. Use CamelCase instead. If you use blanks you must always
use [brackets] around the fieldnames.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Nirmalya Choudhury]

I have a table with 20 columns. They are Campaign, Company Name, ContactName, Designation, Address 1, Address 2, Address 2, Address 3, City, Zip, Country, Phone, Email, Date, List Source, List Type.

I have multiple instances of interacting with a contact for each campaign.

e.g For Campaign 1 and Company A, Contact B I have 5 records of 5 different times we contacted him logged by the date. I want to create a table that copies all the columns but selects only the row with the most recent interaction we had with the contact, the newest date.

I have concatenated the campaign, company and contact name to create a unique identifier. For each of those unique identifiers, I have multiple dates that they have been contacted. I want to only select the row with the most recent date's data.

It seems simple enough but when I try to use Group By for all columns and the Max date, I don't get the correct result.

Any help is appreciated.


Nirmalya

You'll need a Subquery to do that. Put a criterion on the date field suchby

typing



(SELECT Max(X.[Date]) FROM yourtable AS X WHERE X.Campagn = yourtable.Campaign

AND X.[Company Name] = yourtable.[Company Name] AND X.[Contact Name] =

yourtable.[Contact Name])



in the Criteria row of the query grid under the Date field.



A couple of suggestions: change the name of Date, it's a reserved word for the

builtin Date() function and Access can and will get confused; and avoid using

blanks in fieldnames. Use CamelCase instead. If you use blanks you must always

use [brackets] around the fieldnames.

--



John W. Vinson [MVP]

Microsoft's replacements for these newsgroups:

http://social.msdn.microsoft.com/Forums/en-US/accessdev/

http://social.answers.microsoft.com/Forums/en-US/addbuz/

and see also http://www.utteraccess.com

Thanks a lot, John! That worked for me and I made the changes you recommended. I even created a column called Concat that Concatenates the 3 fields toreduce the bulk of the subquery.

Now it is -

(SELECT Max(X.[DateContacted]) FROM yourtable AS X WHERE X.Concat = yourtable.Concat)


However, there are a few instances of records where there are no data available in the date column but I have to include the first instance of those campaign, company and contact combination (which I have created in my table with the concat field).

So, I sorted my initial table to ensure the first entry is always the most recent one and I modified my query to

(SELECT First(X.[Concat]) FROM yourtable AS X WHERE X.Concat = yourtable.Concat)

but it is copying all the records. Any ideas on how to fix this issue?

Thanks a ton!

Nirmalya.

 

[John W. Vinson]

Thanks a lot, John! That worked for me and I made the changes you recommended. I even created a column called Concat that Concatenates the 3 fields to reduce the bulk of the subquery.

Now it is -

(SELECT Max(X.[DateContacted]) FROM yourtable AS X WHERE X.Concat = yourtable.Concat)


However, there are a few instances of records where there are no data available in the date column but I have to include the first instance of those campaign, company and contact combination (which I have created in my table with the concat field).

So, I sorted my initial table to ensure the first entry is always the most recent one and I modified my query to

(SELECT First(X.[Concat]) FROM yourtable AS X WHERE X.Concat = yourtable.Concat)

but it is copying all the records. Any ideas on how to fix this issue?

Thanks a ton!

Nirmalya.

Sorry, but the Concat field - being derived data - should simply NOT EXIST in
your table at all! It's all too easy for it to be calculated and stored, and
then someone edits the company name, or Miss Jones gets married and becomes
Mrs. Roberts, or whatever. You really should consider normalizing your tables;
there should be a table of Campaigns, another table of Companies, a third
table of Contacts, all with autonumber numeric primary keys, and links (not
copied data) to these tables in your master table.

And the First() totals operator doesn't do what you might expect. It returns
the first record *IN DISK STORAGE ORDER* - an order over which you have no
control at all! It will sometimes return the record first entered into the
table, but it often won't; it's basically a way to get a record when you don't
care WHICH record it is other than that it matches the criteria.

If you have multiple records with no date, and without a sequential Autonumber
or some other way of determining, within the data, which is earliest - I fear
you're out of luck: there is no way, even in principle, to rank the records by
data entry order.

Please do note also that this forum has long since been abandoned by
Microsoft. Usenet Newsgroups like this are "the walking dead"; they cannot be
killed as long as anyone has a newsgroup server. But there are only a very few
of us traditionalists who still hang out here. You may want to look at my .sig
below for some more active alternatives.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://answers.microsoft.com/en-us/office/forum/access?tab=question&status=all
http://social.msdn.microsoft.com/Forums/office/en-US/home?forum=accessdev
and see also http://www.utteraccess.com

 

[Nirmalya Choudhury]

Thanks a lot, John! That worked for me and I made the changes you recommended. I even created a column called Concat that Concatenates the 3 fields to reduce the bulk of the subquery.
Now it is -
(SELECT Max(X.[DateContacted]) FROM yourtable AS X WHERE X.Concat = yourtable.Concat)
However, there are a few instances of records where there are no data available in the date column but I have to include the first instance of those campaign, company and contact combination (which I have created in my table with the concat field).
So, I sorted my initial table to ensure the first entry is always the most recent one and I modified my query to
(SELECT First(X.[Concat]) FROM yourtable AS X WHERE X.Concat = yourtable.Concat)
but it is copying all the records. Any ideas on how to fix this issue?
Thanks a ton!

Nirmalya.

Sorry, but the Concat field - being derived data - should simply NOT EXIST in

your table at all! It's all too easy for it to be calculated and stored, and

then someone edits the company name, or Miss Jones gets married and becomes

Mrs. Roberts, or whatever. You really should consider normalizing your tables;

there should be a table of Campaigns, another table of Companies, a third

table of Contacts, all with autonumber numeric primary keys, and links (not

copied data) to these tables in your master table.



And the First() totals operator doesn't do what you might expect. It returns

the first record *IN DISK STORAGE ORDER* - an order over which you have no

control at all! It will sometimes return the record first entered into the

table, but it often won't; it's basically a way to get a record when you don't

care WHICH record it is other than that it matches the criteria.



If you have multiple records with no date, and without a sequential Autonumber

or some other way of determining, within the data, which is earliest - I fear

you're out of luck: there is no way, even in principle, to rank the records by

data entry order.



Please do note also that this forum has long since been abandoned by

Microsoft. Usenet Newsgroups like this are "the walking dead"; they cannot be

killed as long as anyone has a newsgroup server. But there are only a very few

of us traditionalists who still hang out here. You may want to look at my .sig

below for some more active alternatives.

--



John W. Vinson [MVP]

Microsoft's replacements for these newsgroups:

http://answers.microsoft.com/en-us/office/forum/access?tab=question&status=all

http://social.msdn.microsoft.com/Forums/office/en-US/home?forum=accessdev

and see also http://www.utteraccess.com

John,

I figured out a workaround by creating an update query and filling the blank dates with the current day's date and then I pull out the max date. That logic suffices for my current need.

More importantly, thanks for pointing me to the right forums. I was an active user on the excel newsgroups during 2005 and 2006. I was pretty surprised with the lack of responses and wasn't aware of the new avenues to look for help.

Thanks for pointing me in the right direction.

Thanks,
Neel.

PS - I live in Atlanta so the "Walking Dead" reference was a little close to home.