|  | Searching a recordset to find the first record with a NULL v | | |
| | | martinmike2 |  |
|  Posted: Thu Sep 25, 2008 1:24 pm Post subject: Searching a recordset to find the first record with a NULL v |  |
Hello,
I would like to be able to search through a recordset and find the
first record with NULL in the SSN Field programatically.
I have no idea what is the most efficient way to accomplish this. My
idea right now is to run a query to find all the records with this
null value, then MoveFirst. I would then be updating that record with
a SSN in the SSN field.
Is this the most efffiecient way? |
| |
| | | Rick | |
| | Posted: Thu Sep 25, 2008 1:24 pm Post subject: Re: Searching a recordset to find the first record with a NU | |
martinmike2 <martinmike2@gmail.com> wrote in news:22c24356-45a0-4646-84a3-
5f0d972fbf39@i76g2000hsf.googlegroups.com:
| Quote: | Hello,
I would like to be able to search through a recordset and find the
first record with NULL in the SSN Field programatically.
I have no idea what is the most efficient way to accomplish this. My
idea right now is to run a query to find all the records with this
null value, then MoveFirst. I would then be updating that record with
a SSN in the SSN field.
Is this the most efffiecient way?
|
Assuming an ID is how your table identifies what the first record is.
Assuming you want the whole record, I'd do:
select * from table as t1 inner join (select min(id) as minID from table
where SSN is null) as t2 on t1.id=t2.minid |
| |
| | | John Spencer | |
| | Posted: Thu Sep 25, 2008 1:24 pm Post subject: Re: Searching a recordset to find the first record with a NU | |
| |  | |
That may not be updatable.
If so try
SELECT *
FROM TABLE
WHERE Table.IDNum =
(SELECT Min(IDNum)
FROM Table
WHERE SSN is Null)
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Rick wrote:
| Quote: | martinmike2 <martinmike2@gmail.com> wrote in news:22c24356-45a0-4646-84a3-
5f0d972fbf39@i76g2000hsf.googlegroups.com:
Hello,
I would like to be able to search through a recordset and find the
first record with NULL in the SSN Field programatically.
I have no idea what is the most efficient way to accomplish this. My
idea right now is to run a query to find all the records with this
null value, then MoveFirst. I would then be updating that record with
a SSN in the SSN field.
Is this the most efffiecient way?
Assuming an ID is how your table identifies what the first record is.
Assuming you want the whole record, I'd do:
select * from table as t1 inner join (select min(id) as minID from table
where SSN is null) as t2 on t1.id=t2.minid |
|
| |
| | | Rick | |
| | Posted: Fri Sep 26, 2008 12:12 pm Post subject: Re: Searching a recordset to find the first record with a NU | |
| | | |
John Spencer <spencer@chpdm.edu> wrote in
news:#XU79HyHJHA.728@TK2MSFTNGP03.phx.gbl:
| Quote: | That may not be updatable.
If so try
SELECT *
FROM TABLE
WHERE Table.IDNum =
(SELECT Min(IDNum)
FROM Table
WHERE SSN is Null)
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Rick wrote:
martinmike2 <martinmike2@gmail.com> wrote in
news:22c24356-45a0-4646-84a3-
5f0d972fbf39@i76g2000hsf.googlegroups.com:
Hello,
I would like to be able to search through a recordset and find the
first record with NULL in the SSN Field programatically.
I have no idea what is the most efficient way to accomplish this.
My idea right now is to run a query to find all the records with
this null value, then MoveFirst. I would then be updating that
record with a SSN in the SSN field.
Is this the most efffiecient way?
Assuming an ID is how your table identifies what the first record is.
Assuming you want the whole record, I'd do:
select * from table as t1 inner join (select min(id) as minID from
table
where SSN is null) as t2 on t1.id=t2.minid
|
Likely not, but he didn't say it needed to be.
He said he was looking for efficiency, and it should be more efficient
than using an "in" but that would be updatable. |
| |
| | | martinmike2 | |
| | Posted: Fri Sep 26, 2008 3:16 pm Post subject: Re: Searching a recordset to find the first record with a NU | |
| | | |
On Sep 26, 10:12 am, Rick <f...@chance.com> wrote:
| Quote: | John Spencer <spen...@chpdm.edu> wrote innews:#XU79HyHJHA.728@TK2MSFTNGP03.phx.gbl:
That may not be updatable.
If so try
SELECT *
FROM TABLE
WHERE Table.IDNum > > (SELECT Min(IDNum)
FROM Table
WHERE SSN is Null)
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Rick wrote:
martinmike2 <martinmi...@gmail.com> wrote in
news:22c24356-45a0-4646-84a3-
5f0d972fb...@i76g2000hsf.googlegroups.com:
Hello,
I would like to be able to search through a recordset and find the
first record with NULL in the SSN Field programatically.
I have no idea what is the most efficient way to accomplish this.
My idea right now is to run a query to find all the records with
this null value, then MoveFirst. I would then be updating that
record with a SSN in the SSN field.
Is this the most efffiecient way?
|
Sorry Rick, but it needs to be updateable so I can place data in the
SSN Field of the returned record. John, ill give your suggestion a
go, ill let you know whta i uncover. may be a feew days though. |
| |
|
|
