468,736 Members | 2,167 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,736 developers. It's quick & easy.

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!!
Nov 12 '05 #1
4 12551
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!!


Nov 12 '05 #2
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!
Nov 12 '05 #3
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!

Nov 12 '05 #4
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!!

Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Professor Frink | last post: by
7 posts views Thread by Not Me | last post: by
3 posts views Thread by Sarah Smith via AccessMonster.com | last post: by
3 posts views Thread by oscar | last post: by
3 posts views Thread by POL8985 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.