Flag First Record

[Jennifer]

I have been asked to create a table of all of our providers with all of their
addresses, specialties, hospital affilliations etc.

I am also being asked to provide an indicator that shows if it is the first
record for the provider or a secondary record.

The final table is a 15 column table of unique records with no unique ID.

Is there an easy way to accomplish this task?

Jennifer

 

[Allen Browne]

You cannot do this if there is no way to determine which one is the 'first'
record.

In database theory, a table is just a bucket to hold records, i.e. there is
no physical ordering. You must therefore provide a field to indidate which
is the first (preferred?) record.

 

[Jennifer]

Where there is a will, there is alway a way.

1. Created a copy of the structure of the table. Added a AutoNumber ID
column and named it Provider Structure.

2 Changed the make table query to an append query. Made the Provider
Previously Listed column default to "Y"

3. Added the following steps to my macro:

Delete Provider
Copy Provider Structure to Provider

(The remaining macro steps append the data to my new Provider Table)

4 Created a make query to select the Min ID based on each providers name

5 Created an update query to update the Previously Listed field to "N"

6. Added 4 & 5 to my macro before the export command


Success!