Creating serial numbers for non-distinct records

[Remster]

Folks



I have a real puzzle to deal with. I need to select records from a 'view' that contains several memo fields but no unique key. Here's a simplified version of what I'm faced with:


View

Shape	Colour	Info
Round	Red	<256+ characters>
Round	Red	<256+ characters>
Square	Blue	<256+ characters>

Required output

Shape	Colour	Info
Round	Red	<256+ characters>
Square	Blue	<256+ characters>

The first thing to mention is that it doesn't matter which of the Round-Red records I select. But the problem is that I can't use FIRST, MAX, etc. without Access truncating the memo field for reasons of efficiency. What I could do with is a means of selecting a completely arbitrary Round-Red record so Access doesn't have to examine the contents of the memo field.

I've thought of starting by creating, through a query, a column of arbitrary serial numbers, but all methods of doing this seem to require a unique key, which I don't have.

Any ideas?

Remster

---

EDIT: Apologies for the misleading title of this thread, which made sense when I started writing the original post!

[orange]

Tell us more about your table structure and about the database generally.
You have no Primary Keys??

[Remster]

It's an external database that we have an ODBC connection to (if that's the right way to put it). Unfortunately, we can access only views and not the underlying tables – which presumaby do have primary keys.

[orange]

My first reaction was to find out if you can have Access with ODBC to tables without PK. I remember years ago that I could not link tables with Lotus Approach without a PK.
Anyway, you can, I found this link.

Is this required for this session only? And there is no requirement to match these "possibly existing pks"?

Create a local table with an autonumber id field, and the fields in the view. Read and append your records from the view into the local table. Now you can separate records based on the ID field. Use your local table for processing.

[Remster]

Coincidentally, the solution I've come up with is the one you propose:

1. Change the problematic select query to a make-table query for creating the table structure – I'll call this 'tblAllData'.
2. Run the make-table query.
3. Add an autonumber ID field to tblAllData and change the data type of the memo field to 'memo' (Access will have set it to 'text' and removed any characters after the 255th).
4. Change the make-table query to an append query – 'qryAppendNew'.
5. Create a delete query for deleting all records from tblAllData – 'qryDeleteOld'.
6. Create a select query based on tblAllData to select the first ID per non-unique reference number – 'qryUniqueReferences'.
7. Create a select query based on tblAllData and qryUniqueReferences to select all records from tblAllData where both the ID and the non-unique reference number match those in qryUniqueReferences – 'qryUniqueRecords'.
8. Run qryDeleteOld.
9. Run qryAppendNew.
10. Run qryUniqueRecords.

Bingo!