How to add key field to result of query?

[rockman]

I have a query that returns a list of appointments. Some of the appointments are listed twice because of a one-to-many relationship in one of the associated fields.

If I try to loop through the query results to delete the duplicated appointments, I get an errror "Insufficient key column information for updating and refreshing". I suspect that this is because the results of the query don't have a unique field identifier.

How can I generate my query with a unique (autonumber-like) field?

Thanks,
Jeff

 

[dcx693]

Why not just run your query with the "DISTINCT" option?

 

[rockman]

I can't use the DISTINCT qualifier because the records returned are all DISTINCT.

Example query recordset:

[U]ApptID       PatientName       Insurance[/U]
1            Jones             BCBS
2            Smith             TriCare
2            Smith             Medicare
3            Peterson          Medicare

Note: Smith has two insurance plans.

What I'd like to get from my query is this:

[U]RecordNum    ApptID       PatientName       Insurance[/U]
1            1            Jones             BCBS
2            2            Smith             TriCare
3            2            Smith             Medicare
4            3            Peterson          Medicare

Thanks for any help,
Jeff

 

[rockman]

Thanks for your response Pat.

I'm creating my own recordset based on the querydef, as in:

Dim rs as ADODB.recordset
rs.Open "SELECT * from MyQuery", CurrentProject.Connection

I think I can safely delete records in rs without deleting data in the underlying data tables. Please let me know if this is incorrect.

I will try your First() function suggestion... thanks.

 

[dcx693]

I think I can safely delete records in rs without deleting data in the underlying data tables. Please let me know if this is incorrect.

This will delete the records from the underlying data. The recordset is the result of you running the query. It's just like when you run a query and you view the results on screen, except that you don't see them on screen. If you delete a record, it will be permanently deleted.

(You'll have some issues trying to delete records from your recordset anyway, since, based on your syntax, you are creating the default type of recordset in ADO: a read-only recordset.)

Follow Pat's suggestion.

 

[rockman]

Good Golly!

Thanks dcx for setting me straight on the deleting records issue!

I learn something new everyday! And luckily this time before I started indiscriminately deleting all my data! Geez!

I'm trying to implement the First() function but for some reason the response is exceedingly slow (minutes to execute). I'll keep working on it.

Thanks,
Jeff

 

[rockman]

Got it working quickly now.

Thanks for both your help, Pat and dcx!