I am working on creating a report for some tables linked to Oracle. One of 

the reports is for an Email turnaround time where I get the Creation Date 

of a record and then the Date associated with an OutBound Email sent code 

in a Calls table related to each record. I can do this but the problem is 

that some records have more than one OutBound Email and I want only the 

first one to show up in the record. How would I go about getting only the 

first one to show up an dstill be able to get that Date associated with 

the OutBound Email (since the Date will make it DISTINCT). There is a 

CALL_ID field that is unique to each record in the Calls subtable but 

trying to do a DISTINCT subquery will not allow me to get this since it 

would make each record DISTINCT also. Does anyone have a solution? Please 

feel free to contact me if you need more info on it. I just think that if 

I put the SQL Statement down associated with the problem it will not help 

because all the tables and fields will nto be defined for you.

This is typically a two-step (that is, two-query) procedure.  The first

query does a GROUP BY on the fields that should be common to all records

associated with a given call, and takes the Min() of your outbound e-mail

date, so you get your key fields along with the date of the earliest e-mail.

The second query joins the first back to the base table, pulling out

whatever other info you need from the record (e.g., who generated the

outbound e-mail, it's text--whatever).



Note that since you're using ORACLE for a back-end, it'd be most efficient

to use an ORACLE view or stored procedure for this--if that's an option.



Hope that helps.



Cheers,



-Roy



-----Original Message-----

From: brose@u... [mailto:brose@u...]

Sent: Thursday, June 28, 2001 9:20 AM

To: Access

Subject: [access] Limiting to First Record Given





I am working on creating a report for some tables linked to Oracle. One of 

the reports is for an Email turnaround time where I get the Creation Date 

of a record and then the Date associated with an OutBound Email sent code 

in a Calls table related to each record. I can do this but the problem is 

that some records have more than one OutBound Email and I want only the 

first one to show up in the record. How would I go about getting only the 

first one to show up an dstill be able to get that Date associated with 

the OutBound Email (since the Date will make it DISTINCT). There is a 

CALL_ID field that is unique to each record in the Calls subtable but 

trying to do a DISTINCT subquery will not allow me to get this since it 

would make each record DISTINCT also. Does anyone have a solution? Please 

feel free to contact me if you need more info on it. I just think that if 

I put the SQL Statement down associated with the problem it will not help 

because all the tables and fields will nto be defined for you.