Query for only first instance of a primary key

[Nick Mirro]

I have a query with 2 tables sharing a one to many relationship. I want to
show the first instance of a datefield on the "many" side that matches the
primary key of the linked table, while all subsequent dates are not shown
for that key value. So while there are 380 records in tblCalls, around 100
share a VisitID and need to not be shown.

Problem is if I sort the datefield "ascending" and apply TOP1 , I get the
correct number of key fields (280), but all the desired fields from the many
side show blank???

If I sort the datefield "descending" and apply TOP1, I only get the newest
(1) record back.

Fields

tblVisits One VisitID
tblCalls Many CallDate
Communication

Why won't this work. Is there another way to get the same result?

Nick

 

[Gary Miller]

Nick,

Use the Max() function on the CallDate field in a Totals
query and group on the other info.

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________

 

[Nick Mirro]

Thanks Gary. I hate to plead ignorance but I'm not sure how to apply Max()
function. Is this run in vb? Would you mind pasting a very small example?
I'm not much of a coder.

Nick

 

[Gary Miller]

Glad to. Create a query, in your case maybe VisitID and
Communication. Note that I did not include CallDate. Now go
to View/Totals and this will convert it to a Totals query.
Now in the query grid you will a line for Total among
others. If you click in the Total line you will get a
dropdown box arrow. Click on it and you have a choice of
Accesses 'aggregate' functions. Choose Group for your all of
the fields. Now we are going to make a 'contrived field'
that will be the maximum or last CallDate. In the next blank
column put the following in the Field line...

LastCallDate: Max([CallDate])

This should now limit the records to the last CallDate. You
will need to modify your controls to refer to LastCallDate
instead of CallDate.

Regards,
--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________

 

[Nick Mirro]

Thanks again. I apologize if I may not have been very clear.

tblVisits.VisitID tblCallInfo.DateOfCall

1 06/02/03
2 01/04/03
3 01/06/03
3 04/11/03
3 01/09/03
4 01/02/03
5 08/08/03
5 01/22/03
6 01/23/03

needs to become...

1 06/02/03
2 01/04/03
3 01/06/03
4 01/02/03
5 01/22/03
6 01/23/03

where only the record with the earliest date for any VisitID is kept. All
records except the one with the newest DateOfCall should be removed "for
each VisitID"

Nick

Glad to. Create a query, in your case maybe VisitID and
Communication. Note that I did not include CallDate. Now go
to View/Totals and this will convert it to a Totals query.
Now in the query grid you will a line for Total among
others. If you click in the Total line you will get a
dropdown box arrow. Click on it and you have a choice of
Accesses 'aggregate' functions. Choose Group for your all of
the fields. Now we are going to make a 'contrived field'
that will be the maximum or last CallDate. In the next blank
column put the following in the Field line...

LastCallDate: Max([CallDate])

This should now limit the records to the last CallDate. You
will need to modify your controls to refer to LastCallDate
instead of CallDate.

Regards,
--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________

Thanks Gary. I hate to plead ignorance but I'm not sure how to apply Max()
function. Is this run in vb? Would you mind pasting a very small example?
I'm not much of a coder.

Nick

 

[Gary Miller]

Nick,

I would have thought that what I posted last on creating a
query to do this would have given you exactly that. What did
you find different when you set it up that way?

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________

Thanks again. I apologize if I may not have been very clear.

tblVisits.VisitID tblCallInfo.DateOfCall

1 06/02/03
2 01/04/03
3 01/06/03
3 04/11/03
3 01/09/03
4 01/02/03
5 08/08/03
5 01/22/03
6 01/23/03

needs to become...

1 06/02/03
2 01/04/03
3 01/06/03
4 01/02/03
5 01/22/03
6 01/23/03

where only the record with the earliest date for any VisitID is kept. All
records except the one with the newest DateOfCall should be removed "for
each VisitID"

Nick

Glad to. Create a query, in your case maybe VisitID and
Communication. Note that I did not include CallDate. Now go
to View/Totals and this will convert it to a Totals query.
Now in the query grid you will a line for Total among
others. If you click in the Total line you will get a
dropdown box arrow. Click on it and you have a choice of
Accesses 'aggregate' functions. Choose Group for your all of
the fields. Now we are going to make a 'contrived field'
that will be the maximum or last CallDate. In the next blank
column put the following in the Field line...

LastCallDate: Max([CallDate])

This should now limit the records to the last CallDate. You
will need to modify your controls to refer to LastCallDate
instead of CallDate.

Regards,
--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________

Thanks Gary. I hate to plead ignorance but I'm not

sure
how to apply Max()

function. Is this run in vb? Would you mind pasting

a
very small example?

I'm not much of a coder.

Nick


Nick,

Use the Max() function on the CallDate field in a Totals
query and group on the other info.

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
I have a query with 2 tables sharing a one to many
relationship. I want to
show the first instance of a datefield on the

"many"
side

that matches the
primary key of the linked table, while all subsequent
dates are not shown
for that key value. So while there are 380 records in
tblCalls, around 100
share a VisitID and need to not be shown.

Problem is if I sort the datefield "ascending" and apply
TOP1 , I get the
correct number of key fields (280), but all the desired
fields from the many
side show blank???

If I sort the datefield "descending" and apply TOP1, I
only get the newest
(1) record back.

Fields

tblVisits One VisitID
tblCalls Many CallDate
Communication

Why won't this work. Is there another way to get

the
same

result?

Nick

 

[Nick Mirro]

Well I end up with the same number of records, including duplicate values in
the VisitID column. I mistakenly assumed that I wasn't clear about the goal
for the query. I appreciate your taking time here. Should it work as is?

Nick

Nick,

I would have thought that what I posted last on creating a
query to do this would have given you exactly that. What did
you find different when you set it up that way?

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________

Thanks again. I apologize if I may not have been very clear.

tblVisits.VisitID tblCallInfo.DateOfCall

1 06/02/03
2 01/04/03
3 01/06/03
3 04/11/03
3 01/09/03
4 01/02/03
5 08/08/03
5 01/22/03
6 01/23/03

needs to become...

1 06/02/03
2 01/04/03
3 01/06/03
4 01/02/03
5 01/22/03
6 01/23/03

where only the record with the earliest date for any VisitID is kept. All
records except the one with the newest DateOfCall should be removed "for
each VisitID"

Nick

Glad to. Create a query, in your case maybe VisitID and
Communication. Note that I did not include CallDate. Now go
to View/Totals and this will convert it to a Totals query.
Now in the query grid you will a line for Total among
others. If you click in the Total line you will get a
dropdown box arrow. Click on it and you have a choice of
Accesses 'aggregate' functions. Choose Group for your all of
the fields. Now we are going to make a 'contrived field'
that will be the maximum or last CallDate. In the next blank
column put the following in the Field line...

LastCallDate: Max([CallDate])

This should now limit the records to the last CallDate. You
will need to modify your controls to refer to LastCallDate
instead of CallDate.

Regards,
--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
Thanks Gary. I hate to plead ignorance but I'm not sure
how to apply Max()
function. Is this run in vb? Would you mind pasting a
very small example?
I'm not much of a coder.

Nick


message
Nick,

Use the Max() function on the CallDate field in a Totals
query and group on the other info.

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
I have a query with 2 tables sharing a one to many
relationship. I want to
show the first instance of a datefield on the "many"
side
that matches the
primary key of the linked table, while all subsequent
dates are not shown
for that key value. So while there are 380 records in
tblCalls, around 100
share a VisitID and need to not be shown.

Problem is if I sort the datefield "ascending" and
apply
TOP1 , I get the
correct number of key fields (280), but all the
desired
fields from the many
side show blank???

If I sort the datefield "descending" and apply TOP1, I
only get the newest
(1) record back.

Fields

tblVisits One VisitID
tblCalls Many CallDate
Communication

Why won't this work. Is there another way to get the
same
result?

Nick

 

[Gary Miller]

Are you sure that you made it a totals query? Go to design
view, then to View/SQL and copy and paste what you have
there and let us take a look at it.
--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________

Well I end up with the same number of records, including duplicate values in
the VisitID column. I mistakenly assumed that I wasn't clear about the goal
for the query. I appreciate your taking time here. Should it work as is?

Nick

Nick,

I would have thought that what I posted last on creating a
query to do this would have given you exactly that. What did
you find different when you set it up that way?

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________

Thanks again. I apologize if I may not have been very clear.

tblVisits.VisitID tblCallInfo.DateOfCall

1 06/02/03
2 01/04/03
3 01/06/03
3 04/11/03
3 01/09/03
4 01/02/03
5 08/08/03
5 01/22/03
6 01/23/03

needs to become...

1 06/02/03
2 01/04/03
3 01/06/03
4 01/02/03
5 01/22/03
6 01/23/03

where only the record with the earliest date for any VisitID is kept. All
records except the one with the newest DateOfCall

should
be removed "for

each VisitID"

Nick



Glad to. Create a query, in your case maybe VisitID and
Communication. Note that I did not include CallDate.

Now
go

to View/Totals and this will convert it to a Totals query.
Now in the query grid you will a line for Total among
others. If you click in the Total line you will get a
dropdown box arrow. Click on it and you have a choice of
Accesses 'aggregate' functions. Choose Group for

your
all of

the fields. Now we are going to make a 'contrived field'
that will be the maximum or last CallDate. In the

next
blank

column put the following in the Field line...

LastCallDate: Max([CallDate])

This should now limit the records to the last

CallDate.
You

will need to modify your controls to refer to LastCallDate
instead of CallDate.

Regards,
--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
Thanks Gary. I hate to plead ignorance but I'm

not
sure

how to apply Max()
function. Is this run in vb? Would you mind

pasting
a

very small example?
I'm not much of a coder.

Nick


message
Nick,

Use the Max() function on the CallDate field in

a
Totals

query and group on the other info.

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
I have a query with 2 tables sharing a one to many
relationship. I want to
show the first instance of a datefield on the "many"
side
that matches the
primary key of the linked table, while all subsequent
dates are not shown
for that key value. So while there are 380 records in
tblCalls, around 100
share a VisitID and need to not be shown.

Problem is if I sort the datefield "ascending" and
apply
TOP1 , I get the
correct number of key fields (280), but all the
desired
fields from the many
side show blank???

If I sort the datefield "descending" and apply TOP1, I
only get the newest
(1) record back.

Fields

tblVisits One VisitID
tblCalls Many CallDate
Communication

Why won't this work. Is there another way to

get
the

same
result?

Nick

 

[Nick Mirro]

Gary,

Here's the sql.

SELECT DISTINCT tblVisits.VisitID, tblCallInfo.DateOfCall,
tblCallInfo.Communication, Max([DateOfCall]) AS LastCallDate
FROM tblVisits LEFT JOIN tblCallInfo ON tblVisits.VisitID =
tblCallInfo.VisitID
GROUP BY tblVisits.VisitID, tblCallInfo.DateOfCall,
tblCallInfo.Communication
HAVING (((tblCallInfo.DateOfCall) Is Not Null));

Nick

Are you sure that you made it a totals query? Go to design
view, then to View/SQL and copy and paste what you have
there and let us take a look at it.
--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________

Well I end up with the same number of records, including duplicate values in
the VisitID column. I mistakenly assumed that I wasn't clear about the goal
for the query. I appreciate your taking time here. Should it work as is?

Nick

Nick,

I would have thought that what I posted last on creating a
query to do this would have given you exactly that. What did
you find different when you set it up that way?

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
Thanks again. I apologize if I may not have been very
clear.

tblVisits.VisitID tblCallInfo.DateOfCall

1 06/02/03
2 01/04/03
3 01/06/03
3 04/11/03
3 01/09/03
4 01/02/03
5 08/08/03
5 01/22/03
6 01/23/03

needs to become...

1 06/02/03
2 01/04/03
3 01/06/03
4 01/02/03
5 01/22/03
6 01/23/03

where only the record with the earliest date for any
VisitID is kept. All
records except the one with the newest DateOfCall should
be removed "for
each VisitID"

Nick



message
Glad to. Create a query, in your case maybe VisitID and
Communication. Note that I did not include CallDate. Now
go
to View/Totals and this will convert it to a Totals
query.
Now in the query grid you will a line for Total among
others. If you click in the Total line you will get a
dropdown box arrow. Click on it and you have a choice of
Accesses 'aggregate' functions. Choose Group for your
all of
the fields. Now we are going to make a 'contrived field'
that will be the maximum or last CallDate. In the next
blank
column put the following in the Field line...

LastCallDate: Max([CallDate])

This should now limit the records to the last CallDate.
You
will need to modify your controls to refer to
LastCallDate
instead of CallDate.

Regards,
--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
Thanks Gary. I hate to plead ignorance but I'm not
sure
how to apply Max()
function. Is this run in vb? Would you mind pasting
a
very small example?
I'm not much of a coder.

Nick


message
Nick,

Use the Max() function on the CallDate field in a
Totals
query and group on the other info.

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
message
I have a query with 2 tables sharing a one to many
relationship. I want to
show the first instance of a datefield on the
"many"
side
that matches the
primary key of the linked table, while all
subsequent
dates are not shown
for that key value. So while there are 380
records in
tblCalls, around 100
share a VisitID and need to not be shown.

Problem is if I sort the datefield "ascending" and
apply
TOP1 , I get the
correct number of key fields (280), but all the
desired
fields from the many
side show blank???

If I sort the datefield "descending" and apply
TOP1, I
only get the newest
(1) record back.

Fields

tblVisits One VisitID
tblCalls Many CallDate
Communication

Why won't this work. Is there another way to get
the
same
result?

Nick

 

[Gary Miller]

Nick,

I think the problem is that the DateOfCall is in the
grouping line. Try this variation...

SELECT DISTINCT tblVisits.VisitID, tblCallInfo.DateOfCall,
tblCallInfo.Communication, Max([DateOfCall]) AS LastCallDate
FROM tblVisits LEFT JOIN tblCallInfo ON tblVisits.VisitID =
tblCallInfo.VisitID
GROUP BY tblVisits.VisitID, tblCallInfo.Communication
HAVING (((tblCallInfo.DateOfCall) Is Not Null));


--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________

Gary,

Here's the sql.

SELECT DISTINCT tblVisits.VisitID, tblCallInfo.DateOfCall,
tblCallInfo.Communication, Max([DateOfCall]) AS LastCallDate
FROM tblVisits LEFT JOIN tblCallInfo ON tblVisits.VisitID =
tblCallInfo.VisitID
GROUP BY tblVisits.VisitID, tblCallInfo.DateOfCall,
tblCallInfo.Communication
HAVING (((tblCallInfo.DateOfCall) Is Not Null));

Nick

Are you sure that you made it a totals query? Go to design
view, then to View/SQL and copy and paste what you have
there and let us take a look at it.
--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________

Well I end up with the same number of records,

including
duplicate values in

the VisitID column. I mistakenly assumed that I

wasn't
clear about the goal

for the query. I appreciate your taking time here. Should it work as is?

Nick

Nick,

I would have thought that what I posted last on

creating
a

query to do this would have given you exactly that.

What
did

you find different when you set it up that way?

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
Thanks again. I apologize if I may not have been very
clear.

tblVisits.VisitID tblCallInfo.DateOfCall

1 06/02/03
2 01/04/03
3 01/06/03
3 04/11/03
3 01/09/03
4 01/02/03
5 08/08/03
5 01/22/03
6 01/23/03

needs to become...

1 06/02/03
2 01/04/03
3 01/06/03
4 01/02/03
5 01/22/03
6 01/23/03

where only the record with the earliest date for any
VisitID is kept. All
records except the one with the newest DateOfCall should
be removed "for
each VisitID"

Nick



message
Glad to. Create a query, in your case maybe

VisitID
and

Communication. Note that I did not include

CallDate.
Now

go
to View/Totals and this will convert it to a Totals
query.
Now in the query grid you will a line for Total among
others. If you click in the Total line you will

get
a

dropdown box arrow. Click on it and you have a choice of
Accesses 'aggregate' functions. Choose Group for your
all of
the fields. Now we are going to make a

'contrived
field'

that will be the maximum or last CallDate. In

the
next

blank
column put the following in the Field line...

LastCallDate: Max([CallDate])

This should now limit the records to the last CallDate.
You
will need to modify your controls to refer to
LastCallDate
instead of CallDate.

Regards,
--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
Thanks Gary. I hate to plead ignorance but

I'm
not

sure
how to apply Max()
function. Is this run in vb? Would you mind pasting
a
very small example?
I'm not much of a coder.

Nick


message
Nick,

Use the Max() function on the CallDate field

in
a

Totals
query and group on the other info.

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
message
I have a query with 2 tables sharing a one

to
many

relationship. I want to
show the first instance of a datefield on the
"many"
side
that matches the
primary key of the linked table, while all
subsequent
dates are not shown
for that key value. So while there are 380
records in
tblCalls, around 100
share a VisitID and need to not be shown.

Problem is if I sort the datefield

"ascending"
and

apply
TOP1 , I get the
correct number of key fields (280), but

all
the

desired
fields from the many
side show blank???

If I sort the datefield "descending" and apply
TOP1, I
only get the newest
(1) record back.

Fields

tblVisits One VisitID
tblCalls Many CallDate
Communication

Why won't this work. Is there another way

to
get

the
same
result?

Nick

 

[John Spencer (MVP)]

You might try the following UNTESTED queries. It could be slow.

SELECT tblVisits.VisitID, tblCallInfo.DateOfCall,
tblCallInfo.Communication
FROM tblVisits
LEFT JOIN tblCallInfo
ON tblVisits.VisitID = tblCallInfo.VisitID
WHERE tblCallInfo.DateOfCall In
(SELECT Max(tmp.DateOfCall)
FROM tblCallInfo as tmp
WHERE tmp.VisitID = tblVisits.VisitID)

An alternative is to use stacked queries. This will not be updatable.

FirstQuery:
SELECT VisitID, Max(DateOfCall) as LastCallDate
FROM tblCallInfo

Save the above as QryOne and then use it in


SELECT DISTINCT tblVisits.VisitID,
tblCallInfo.DateOfCall,
tblCallInfo.Communication
FROM (tblVisits
INNER JOIN tblCallInfo ON
tblVisits.VisitID = tblCallInfo.VisitID)
INNER JOIN QryONE

Gary,

Here's the sql.

SELECT DISTINCT tblVisits.VisitID, tblCallInfo.DateOfCall,
tblCallInfo.Communication, Max([DateOfCall]) AS LastCallDate
FROM tblVisits LEFT JOIN tblCallInfo ON tblVisits.VisitID =
tblCallInfo.VisitID
GROUP BY tblVisits.VisitID, tblCallInfo.DateOfCall,
tblCallInfo.Communication
HAVING (((tblCallInfo.DateOfCall) Is Not Null));

Nick

Are you sure that you made it a totals query? Go to design
view, then to View/SQL and copy and paste what you have
there and let us take a look at it.
--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________

Well I end up with the same number of records, including duplicate values in
the VisitID column. I mistakenly assumed that I wasn't clear about the goal
for the query. I appreciate your taking time here. Should it work as is?

Nick

Nick,

I would have thought that what I posted last on creating a
query to do this would have given you exactly that. What did
you find different when you set it up that way?

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
Thanks again. I apologize if I may not have been very
clear.

tblVisits.VisitID tblCallInfo.DateOfCall

1 06/02/03
2 01/04/03
3 01/06/03
3 04/11/03
3 01/09/03
4 01/02/03
5 08/08/03
5 01/22/03
6 01/23/03

needs to become...

1 06/02/03
2 01/04/03
3 01/06/03
4 01/02/03
5 01/22/03
6 01/23/03

where only the record with the earliest date for any
VisitID is kept. All
records except the one with the newest DateOfCall should
be removed "for
each VisitID"

Nick



message
Glad to. Create a query, in your case maybe VisitID and
Communication. Note that I did not include CallDate. Now
go
to View/Totals and this will convert it to a Totals
query.
Now in the query grid you will a line for Total among
others. If you click in the Total line you will get a
dropdown box arrow. Click on it and you have a choice of
Accesses 'aggregate' functions. Choose Group for your
all of
the fields. Now we are going to make a 'contrived field'
that will be the maximum or last CallDate. In the next
blank
column put the following in the Field line...

LastCallDate: Max([CallDate])

This should now limit the records to the last CallDate.
You
will need to modify your controls to refer to
LastCallDate
instead of CallDate.

Regards,
--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
Thanks Gary. I hate to plead ignorance but I'm not
sure
how to apply Max()
function. Is this run in vb? Would you mind pasting
a
very small example?
I'm not much of a coder.

Nick


message
Nick,

Use the Max() function on the CallDate field in a
Totals
query and group on the other info.

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
message
I have a query with 2 tables sharing a one to many
relationship. I want to
show the first instance of a datefield on the
"many"
side
that matches the
primary key of the linked table, while all
subsequent
dates are not shown
for that key value. So while there are 380
records in
tblCalls, around 100
share a VisitID and need to not be shown.

Problem is if I sort the datefield "ascending" and
apply
TOP1 , I get the
correct number of key fields (280), but all the
desired
fields from the many
side show blank???

If I sort the datefield "descending" and apply
TOP1, I
only get the newest
(1) record back.

Fields

tblVisits One VisitID
tblCalls Many CallDate
Communication

Why won't this work. Is there another way to get
the
same
result?

Nick

 

[Nick Mirro]

The first one you gave worked, finally! : ) I was a bit confused as to how
to apply it, but I think I sort of understand what's what. This produces an
enormously important bit of data for my business. I and the billing service
thank you.

Nick

You might try the following UNTESTED queries. It could be slow.

SELECT tblVisits.VisitID, tblCallInfo.DateOfCall,
tblCallInfo.Communication
FROM tblVisits
LEFT JOIN tblCallInfo
ON tblVisits.VisitID = tblCallInfo.VisitID
WHERE tblCallInfo.DateOfCall In
(SELECT Max(tmp.DateOfCall)
FROM tblCallInfo as tmp
WHERE tmp.VisitID = tblVisits.VisitID)

An alternative is to use stacked queries. This will not be updatable.

FirstQuery:
SELECT VisitID, Max(DateOfCall) as LastCallDate
FROM tblCallInfo

Save the above as QryOne and then use it in


SELECT DISTINCT tblVisits.VisitID,
tblCallInfo.DateOfCall,
tblCallInfo.Communication
FROM (tblVisits
INNER JOIN tblCallInfo ON
tblVisits.VisitID = tblCallInfo.VisitID)
INNER JOIN QryONE

Gary,

Here's the sql.

SELECT DISTINCT tblVisits.VisitID, tblCallInfo.DateOfCall,
tblCallInfo.Communication, Max([DateOfCall]) AS LastCallDate
FROM tblVisits LEFT JOIN tblCallInfo ON tblVisits.VisitID =
tblCallInfo.VisitID
GROUP BY tblVisits.VisitID, tblCallInfo.DateOfCall,
tblCallInfo.Communication
HAVING (((tblCallInfo.DateOfCall) Is Not Null));

Nick

Are you sure that you made it a totals query? Go to design
view, then to View/SQL and copy and paste what you have
there and let us take a look at it.
--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
Well I end up with the same number of records, including
duplicate values in
the VisitID column. I mistakenly assumed that I wasn't
clear about the goal
for the query. I appreciate your taking time here.
Should it work as is?

Nick

message
Nick,

I would have thought that what I posted last on creating
a
query to do this would have given you exactly that. What
did
you find different when you set it up that way?

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
Thanks again. I apologize if I may not have been very
clear.

tblVisits.VisitID tblCallInfo.DateOfCall

1 06/02/03
2 01/04/03
3 01/06/03
3 04/11/03
3 01/09/03
4 01/02/03
5 08/08/03
5 01/22/03
6 01/23/03

needs to become...

1 06/02/03
2 01/04/03
3 01/06/03
4 01/02/03
5 01/22/03
6 01/23/03

where only the record with the earliest date for any
VisitID is kept. All
records except the one with the newest DateOfCall
should
be removed "for
each VisitID"

Nick



message
Glad to. Create a query, in your case maybe VisitID
and
Communication. Note that I did not include CallDate.
Now
go
to View/Totals and this will convert it to a Totals
query.
Now in the query grid you will a line for Total
among
others. If you click in the Total line you will get
a
dropdown box arrow. Click on it and you have a
choice of
Accesses 'aggregate' functions. Choose Group for
your
all of
the fields. Now we are going to make a 'contrived
field'
that will be the maximum or last CallDate. In the
next
blank
column put the following in the Field line...

LastCallDate: Max([CallDate])

This should now limit the records to the last
CallDate.
You
will need to modify your controls to refer to
LastCallDate
instead of CallDate.

Regards,
--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
message
Thanks Gary. I hate to plead ignorance but I'm
not
sure
how to apply Max()
function. Is this run in vb? Would you mind
pasting
a
very small example?
I'm not much of a coder.

Nick


"Gary Miller" <[email protected]>
wrote in
message
Nick,

Use the Max() function on the CallDate field in
a
Totals
query and group on the other info.

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
message
I have a query with 2 tables sharing a one to
many
relationship. I want to
show the first instance of a datefield on the
"many"
side
that matches the
primary key of the linked table, while all
subsequent
dates are not shown
for that key value. So while there are 380
records in
tblCalls, around 100
share a VisitID and need to not be shown.

Problem is if I sort the datefield "ascending"
and
apply
TOP1 , I get the
correct number of key fields (280), but all
the
desired
fields from the many
side show blank???

If I sort the datefield "descending" and apply
TOP1, I
only get the newest
(1) record back.

Fields

tblVisits One VisitID
tblCalls Many CallDate
Communication

Why won't this work. Is there another way to
get
the
same
result?

Nick

 

[Nick Mirro]

Thanks for the help Gary. All is working now. I greatly appreciate your
time and would happily return the favor if I knew a durn thing about sql
programming.

Nick,

I think the problem is that the DateOfCall is in the
grouping line. Try this variation...

SELECT DISTINCT tblVisits.VisitID, tblCallInfo.DateOfCall,
tblCallInfo.Communication, Max([DateOfCall]) AS LastCallDate
FROM tblVisits LEFT JOIN tblCallInfo ON tblVisits.VisitID =
tblCallInfo.VisitID
GROUP BY tblVisits.VisitID, tblCallInfo.Communication
HAVING (((tblCallInfo.DateOfCall) Is Not Null));


--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________

Gary,

Here's the sql.

SELECT DISTINCT tblVisits.VisitID, tblCallInfo.DateOfCall,
tblCallInfo.Communication, Max([DateOfCall]) AS LastCallDate
FROM tblVisits LEFT JOIN tblCallInfo ON tblVisits.VisitID =
tblCallInfo.VisitID
GROUP BY tblVisits.VisitID, tblCallInfo.DateOfCall,
tblCallInfo.Communication
HAVING (((tblCallInfo.DateOfCall) Is Not Null));

Nick

Are you sure that you made it a totals query? Go to design
view, then to View/SQL and copy and paste what you have
there and let us take a look at it.
--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
Well I end up with the same number of records, including
duplicate values in
the VisitID column. I mistakenly assumed that I wasn't
clear about the goal
for the query. I appreciate your taking time here.
Should it work as is?

Nick

message
Nick,

I would have thought that what I posted last on creating
a
query to do this would have given you exactly that. What
did
you find different when you set it up that way?

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
Thanks again. I apologize if I may not have been very
clear.

tblVisits.VisitID tblCallInfo.DateOfCall

1 06/02/03
2 01/04/03
3 01/06/03
3 04/11/03
3 01/09/03
4 01/02/03
5 08/08/03
5 01/22/03
6 01/23/03

needs to become...

1 06/02/03
2 01/04/03
3 01/06/03
4 01/02/03
5 01/22/03
6 01/23/03

where only the record with the earliest date for any
VisitID is kept. All
records except the one with the newest DateOfCall
should
be removed "for
each VisitID"

Nick



message
Glad to. Create a query, in your case maybe VisitID
and
Communication. Note that I did not include CallDate.
Now
go
to View/Totals and this will convert it to a Totals
query.
Now in the query grid you will a line for Total
among
others. If you click in the Total line you will get
a
dropdown box arrow. Click on it and you have a
choice of
Accesses 'aggregate' functions. Choose Group for
your
all of
the fields. Now we are going to make a 'contrived
field'
that will be the maximum or last CallDate. In the
next
blank
column put the following in the Field line...

LastCallDate: Max([CallDate])

This should now limit the records to the last
CallDate.
You
will need to modify your controls to refer to
LastCallDate
instead of CallDate.

Regards,
--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
message
Thanks Gary. I hate to plead ignorance but I'm
not
sure
how to apply Max()
function. Is this run in vb? Would you mind
pasting
a
very small example?
I'm not much of a coder.

Nick


"Gary Miller" <[email protected]>
wrote in
message
Nick,

Use the Max() function on the CallDate field in
a
Totals
query and group on the other info.

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
message
I have a query with 2 tables sharing a one to
many
relationship. I want to
show the first instance of a datefield on the
"many"
side
that matches the
primary key of the linked table, while all
subsequent
dates are not shown
for that key value. So while there are 380
records in
tblCalls, around 100
share a VisitID and need to not be shown.

Problem is if I sort the datefield "ascending"
and
apply
TOP1 , I get the
correct number of key fields (280), but all
the
desired
fields from the many
side show blank???

If I sort the datefield "descending" and apply
TOP1, I
only get the newest
(1) record back.

Fields

tblVisits One VisitID
tblCalls Many CallDate
Communication

Why won't this work. Is there another way to
get
the
same
result?

Nick