Only Return MAX AutoNum From relational Records

[Christopher Scott Rodgers]

HI,

I have the following problem (one of many).
***I am trying to return the latest marketing event from a table. ****

tblMarketing has fields like
autoNumMarketing,
MarketingComments,
DateOfContact and
AutoNumAgy which can be related many times to AutoNmAgy in tblAgency.
tblAgency also has other fields such as
intLevel,
State_county,
DateOfContact - to name a few.

I am using the following sql to gather records in a query

SELECT tblAgency.SalesType, tblAgency.IntLevel, tblAgency.State_County,
tblAgency.ContrType, tblAgency.ServiceOpps, tblMarketing.MarketingComments,
tblAgency.SalesLead, tblMarketing.DateOfContact, tblAgency.EstValue,
tblAgency.EstPrjStart, tblMarketing.AutoNumMarketing
FROM tblAgency INNER JOIN tblMarketing ON tblAgency.AutoNumAgy = tblMarketing.
AutoNumAgy
WHERE (((tblAgency.SalesType)="New-Open") AND ((tblAgency.IntLevel)
="Interested - Seeking Approval" Or (tblAgency.IntLevel)="Verbal Commitment"
Or (tblAgency.IntLevel)="Interested - Requested More Information"));

I have read through a thread from "Roger Tregelles" and found it very useful -
I was able to apply it and limit my returned records to one, however I can
not seem to limit the recordset to one record (the latest) from each
different agency. The inner join is giving me some trouble.

Any help would be greatly appreciated.

 

[Guest]

I did not fully alalyze your SQL but try a Left join and add to statement
like this --
SELECT TOP 1 tblAgency.SalesType, tblAgency.IntLevel,

 

[Guest]

Too fast fat fingers ---
Add like this ---
SELECT TOP 1 tblAgency.SalesType, tblAgency.IntLevel,
xxxx
Or (tblAgency.IntLevel)="Interested - Requested More Information"))
ORDER BY tblAgency.AutoNumAgy;
OR
xxxx
Or (tblAgency.IntLevel)="Interested - Requested More Information"))
ORDER BY tblMarketing.AutoNumAgy;

 

[Christopher Scott Rodgers]

Hi Karl,

Thanks for the reply,

Is this what you meant?

SELECT Top 1 tblAgency.SalesType, tblAgency.IntLevel, tblAgency.State_County,
tblAgency.ContrType, tblAgency.ServiceOpps, tblMarketing.MarketingComments,
tblAgency.SalesLead, tblMarketing.DateOfContact, tblAgency.EstValue,
tblAgency.EstPrjStart, tblMarketing.AutoNumMarketing

FROM tblAgency LEFT JOIN tblMarketing ON tblAgency.AutoNumAgy = tblMarketing.
AutoNumAgy

WHERE (((tblAgency.SalesType)="New-Open") AND ((tblAgency.IntLevel)
="Interested - Seeking Approval" Or (tblAgency.IntLevel)="Verbal Commitment"
Or (tblAgency.IntLevel)="Interested - Requested More Information"));

CSR

Too fast fat fingers ---
Add like this ---
SELECT TOP 1 tblAgency.SalesType, tblAgency.IntLevel,
xxxx
Or (tblAgency.IntLevel)="Interested - Requested More Information"))
ORDER BY tblAgency.AutoNumAgy;
OR
xxxx
Or (tblAgency.IntLevel)="Interested - Requested More Information"))
ORDER BY tblMarketing.AutoNumAgy;

HI,

[quoted text clipped - 29 lines]

Any help would be greatly appreciated.

 

[Guest]

If autoNumMarketing is an Autonumber datatype, you can't 100% rely on it
being sequential. In other words, the largest autoNumMarketing might not be
the latest.

 

[Christopher Scott Rodgers]

Thanks Jerry,

tblmarketing.autonumagy is really just a number and not an Autonumber
tblAgency.AutoNumAgy is an auto number. The real problem that I am having
however isnt with that. It is that I need to grab the most recent marketing
record for its associated agency. Karl indicated that I should use a left
join and TOP 1 . This will work if I can figure out how to do it when
tblMarket.AutoNumAgy = tblAgency.AutoNumAgy. The way I have it written now it
only collects one record from the whole table - not each most recent event
for each agency.

Thanks

CSR

If autoNumMarketing is an Autonumber datatype, you can't 100% rely on it
being sequential. In other words, the largest autoNumMarketing might not be
the latest.

HI,

[quoted text clipped - 29 lines]

Any help would be greatly appreciated.

 

[Christopher Scott Rodgers]

Hi All,

I'm going home now - will look at the posts later. Thanks.

CSR

Thanks Jerry,

tblmarketing.autonumagy is really just a number and not an Autonumber
tblAgency.AutoNumAgy is an auto number. The real problem that I am having
however isnt with that. It is that I need to grab the most recent marketing
record for its associated agency. Karl indicated that I should use a left
join and TOP 1 . This will work if I can figure out how to do it when
tblMarket.AutoNumAgy = tblAgency.AutoNumAgy. The way I have it written now it
only collects one record from the whole table - not each most recent event
for each agency.

Thanks

CSR

If autoNumMarketing is an Autonumber datatype, you can't 100% rely on it
being sequential. In other words, the largest autoNumMarketing might not be

[quoted text clipped - 4 lines]

 

[Christopher Scott Rodgers via AccessMonster.com]

Hi Jerry,

I was wandering if you had any more ideas about this one. I am able to get
the max record in this case but only for one record. Ideally I would like to
get the latest marketing event for all state_county's. Any Ideas?

Hi Karl,

Thanks for the reply,

Is this what you meant?

SELECT Top 1 tblAgency.SalesType, tblAgency.IntLevel, tblAgency.State_County,
tblAgency.ContrType, tblAgency.ServiceOpps, tblMarketing.MarketingComments,
tblAgency.SalesLead, tblMarketing.DateOfContact, tblAgency.EstValue,
tblAgency.EstPrjStart, tblMarketing.AutoNumMarketing

FROM tblAgency LEFT JOIN tblMarketing ON tblAgency.AutoNumAgy = tblMarketing.
AutoNumAgy

WHERE (((tblAgency.SalesType)="New-Open") AND ((tblAgency.IntLevel)
="Interested - Seeking Approval" Or (tblAgency.IntLevel)="Verbal Commitment"
Or (tblAgency.IntLevel)="Interested - Requested More Information"));

CSR

Too fast fat fingers ---
Add like this ---

[quoted text clipped - 12 lines]