Find first instance of a record

[KJ]

We use a third party software package for acctg that limits us on
field size - as a result, we at times have to enter an item into the
app multiple times in order to get all the information. I then
created an Access DB that is linked to the information and have
combined all data onto one record so that we can export to excel. The
next step for me is to eliminate all the other instances of this
particular item, leaving me with only the first instance of that item
no (so that I am left with just the combined data record).

The only thing that differentiates the items are their sequence number
- other than that, they have the same item no, description, etc.

If anyone has a method for extracting just the first instance of a
record that would be a great help.

Thanks!!

[Steve Jorgensen]

If the combined text is not in a Memo field, Just do a Group By query, group
by all the columns except the ID, and use the Min aggregate in the ID column.

On 3 Nov 2003 09:44:55 -0800, ka****@motioneng.com (KJ) wrote:

We use a third party software package for acctg that limits us on
field size - as a result, we at times have to enter an item into the
app multiple times in order to get all the information. I then
created an Access DB that is linked to the information and have
combined all data onto one record so that we can export to excel. The
next step for me is to eliminate all the other instances of this
particular item, leaving me with only the first instance of that item
no (so that I am left with just the combined data record).

The only thing that differentiates the items are their sequence number
- other than that, they have the same item no, description, etc.

If anyone has a method for extracting just the first instance of a
record that would be a great help.

Thanks!!

[Karyn Jervis]

Thanks for the suggestion - I have tried that method and not been
successful and have also tried choosing First. Is there some trick that
I am missing?
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[michael favor]

using "min" only identifies the first record, to erase all other records,
you need to create a second "delete query" that links the "minimum record
id" select query to the whole table (on all key fields), set the join
properties so that all records from the whole table are included, and set
the criteria so that only records with a null key field from the "minimum
record id" query are deleted. presto!

i hope this helps you. the previous poster is correct. this will work if
you do it right.

regards,
michael.

"Karyn Jervis" <ka****@motioneng.com> wrote in message
news:3f*********************@news.frii.net...

Thanks for the suggestion - I have tried that method and not been
successful and have also tried choosing First. Is there some trick that
I am missing?
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Connie]

Try using "First" under Totals in the query.

For Example:
SELECT First(Table1.a) AS FirstOfa, First(Table1.b) AS FirstOfb,
First(Table1.c) AS FirstOfc, First(Table1.d) AS FirstOfd
FROM Table1
HAVING (((First(Table1.a))="this") AND ((First(Table1.b))="is") AND
((First(Table1.c))="a") AND ((First(Table1.d))="test"));

It seemed to do what you were looking for.

ka****@motioneng.com (KJ) wrote in message news:<1a*************************@posting.google.c om>...

We use a third party software package for acctg that limits us on
field size - as a result, we at times have to enter an item into the
app multiple times in order to get all the information. I then
created an Access DB that is linked to the information and have
combined all data onto one record so that we can export to excel. The
next step for me is to eliminate all the other instances of this
particular item, leaving me with only the first instance of that item
no (so that I am left with just the combined data record).

The only thing that differentiates the items are their sequence number
- other than that, they have the same item no, description, etc.

If anyone has a method for extracting just the first instance of a
record that would be a great help.

Thanks!!