Hi all!

I'm trying to play with views again and am stumbling at the moment.

I basically have 2 types of tables in my database, data tables and a code/display value table. I'm trying to create views to use as the source for some grids on my forms.

I have one "master" code/display value table whose structure is:
codetype (to diffentiate the groups of 'codes' for comboboxes)
codeno (unique 'meaningless' key)
displayvalue

It was recommended to me that I first create views for each type of code in my "master" code/display value table by putting the code/display value table in each view, then filtering by codetype (=apples, oranges, bananas, etc.) and ordering by display value so the comboboxes of display values are in alpha order for my users. The result are views such as ViewApples, ViewOranges, ViewBananas which just displays the codenos and display values for types of apples, oranges, bananas etc..

It was then suggested I index each of these code type views on the codeno(key field) so I can then relate these views to my data tables.

The final step is to create a view for a particular grid, eg. "Joe's Fruits" with a data table with a structure like:
* codeno_for_appletype
* number_of_apples_sold
* codeno_for_orangetype
* number_of_oranges_sold
* codeno_for_bananatype
* number_of_bananas_sold
and then add the appropriate codetype views for appletype, orangetype and bananatype to the final view and relate them to the data table.

I've been creating Left Joins from DataTable.codeno_for_appletype to ViewApple.codeno for each codeno in my DataTable. I chose Left Joins in case I have apple_codeno's that aren't in the code/display value table I still want those records to display. I've been doing this by dragging the DataTable field to the appropriate Viewcodetype field.

This appears to work for some of my resulting view columns, but I'm occasionally getting an error stating
"SQL: Column "codeno_for_orangetype" is not found"
even though the field is in the view's field list. I'm not sure what this error means and the help is help-less ;^). There are even times when I go back into the view and all my relations and fields have been removed from the various tab sections lists.

I'm also confused about how indexes on views work. For my codetype views, eg. ViewApples, I want the display Order to be on the displayvalue field, but I also need an index on the codeno to later relate to the Data Table. I've been doing:
index on codeno tag appletype
to create them.

But in my final grid, I'm not sure how to specify that I want the codeno index to be used to relate the DataTable and the codetype Views. Your thoughts?

Sorry for the novel... I just find I get better help when I explain the whole scenario :^)!

MTIA!
Mandy