I've been meaning to get round to this for a while, are you thinking of sites, or records or both?

I have a simple view for site information (name, filecode, spatial ref etc)  but haven't yet tackled the taxon occurrences. I would have thought a decent SQL developer then it shouldn't be that hard for them to fathom it as the naming convention for the tables and fields is pretty straightforward.

I think the following FROM clause contains most of the major links necessary. They will need to select the preferred determination, and preferred locationname (where the occurrence is at a site - if not there will be no Location key in the sample table. Items like recorders and statuses are more complex and will branch off this structure. Ideally the view would also make use of the built in queries which convert dates into a more friendly format.

As I said, the following is just the start of the process, if you get a view that works as you wish, please share it, & save the rest of us the time.

FROM         dbo.TAXON_OCCURRENCE INNER JOIN
                      dbo.TAXON_DETERMINATION ON
                      dbo.TAXON_OCCURRENCE.TAXON_OCCURRENCE_KEY = dbo.TAXON_DETERMINATION.TAXON_OCCURRENCE_KEY INNER JOIN
                      dbo.INDEX_TAXON_NAME ON dbo.TAXON_DETERMINATION.TAXON_LIST_ITEM_KEY = dbo.INDEX_TAXON_NAME.TAXON_LIST_ITEM_KEY INNER JOIN
                      dbo.SAMPLE ON dbo.TAXON_OCCURRENCE.SAMPLE_KEY = dbo.SAMPLE.SAMPLE_KEY INNER JOIN
                      dbo.LOCATION ON dbo.SAMPLE.LOCATION_KEY = dbo.LOCATION.LOCATION_KEY INNER JOIN
                      dbo.LOCATION_NAME ON dbo.LOCATION.LOCATION_KEY = dbo.LOCATION_NAME.LOCATION_KEY

Worth mentioning again there are some useful SQL scalar-valued functions built into recorder which you will need for this process including

dbo.FormatEventRecorders
dbo.LCReturnVagueDateShort
dbo.LCFormatTaxonDesKind

I've almost done the job for you now.

I have tested the above view and it seems to work, but the fact that I had to leave the status field out in order to get the speed up is rather disappointing.

I was hoping to use a rather more complex version of this to produce a view of our records with statuses, in order to produce a GIS layer of notable records, but now that I have seen how slow the query is, I think I am going to stick with the report wizard version I have already used and which is much faster.

I realise I am therefore sacrificing the ability to have an up to date view, but if the view takes an hour or more to populate, that's hardly real-time.

Waiting for the wizard to do its stuff it sometimes feels like an eternity, but after today I am quite impressed with the Rec 6 wizard optimization