Query for only first instance of a primary key

  • Thread starter Thread starter Nick Mirro
  • Start date Start date
N

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
 
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
________________________
 
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
 
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 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



Gary Miller said:
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 said:
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
Click to expand...
Click to expand...
 
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
________________________
Nick Mirro said:
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
________________________
Nick Mirro said:
Thanks Gary. I hate to plead ignorance but I'm not
Click to expand...
sure
how to apply Max()
function. Is this run in vb? Would you mind pasting
Click to expand...
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
Click to expand...
"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
Click to expand...
the
same
result?

Nick
Click to expand...
Click to expand...
Click to expand...
 
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

Gary Miller said:
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
________________________
Nick Mirro said:
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
Click to expand...
Click to expand...
Click to expand...
 
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
________________________
Nick Mirro said:
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
________________________
Nick Mirro said:
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
Click to expand...
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.
Click to expand...
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
Click to expand...
your
all of
the fields. Now we are going to make a 'contrived field'
that will be the maximum or last CallDate. In the
Click to expand...
next
blank
column put the following in the Field line...

LastCallDate: Max([CallDate])

This should now limit the records to the last
Click to expand...
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
Click to expand...
not
sure
how to apply Max()
function. Is this run in vb? Would you mind
Click to expand...
pasting
a
very small example?
I'm not much of a coder.

Nick


message
Nick,

Use the Max() function on the CallDate field in
Click to expand...
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
Click to expand...
get
the
same
result?

Nick
Click to expand...
Click to expand...
Click to expand...
 
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


Gary Miller said:
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
________________________
Nick Mirro said:
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
Click to expand...
Click to expand...
Click to expand...
 
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
________________________
Nick Mirro said:
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
________________________
Nick Mirro said:
Well I end up with the same number of records,
Click to expand...
including
duplicate values in
the VisitID column. I mistakenly assumed that I
Click to expand...
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
Click to expand...
creating
a
query to do this would have given you exactly that.
Click to expand...
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
Click to expand...
VisitID
and
Communication. Note that I did not include
Click to expand...
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
Click to expand...
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
Click to expand...
'contrived
field'
that will be the maximum or last CallDate. In
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
"ascending"
and
apply
TOP1 , I get the
correct number of key fields (280), but
Click to expand...
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
Click to expand...
to
get
the
same
result?

Nick
Click to expand...
Click to expand...
Click to expand...
 
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

Gary Miller said:
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
________________________
Nick Mirro said:
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
Click to expand...
Click to expand...
Click to expand...
 
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





John Spencer (MVP) said:
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

Gary Miller said:
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
Click to expand...
Click to expand...
Click to expand...
 
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.


Gary Miller said:
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
________________________
Nick Mirro said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top