Making a Join with a strange table (2002 SP3?)

Topic

New Reply

I have a table of clients in a SQL Server database that I want an Access form to display

There are different kinds of status fields – for example, one called category
The Category field holds a value – for example ‘0009’ which represents a description – for example ‘Completed’

A separate table holds the values for all kinds of status fields – its fields are
Type;Unique_Key;Value

and, for the status field called ‘Category’, would have a bunch of records with Type = ‘Category’
Unique Key would then have the values, including one for ‘0009’, and a description ‘Completed’ under Value

That’s the structure I must work with (and no, I wouldn’t have designed it that way).

In Access – what do I have to do within the form so that it will display “Completed”, rather than ‘0009’ for the category status?
It goes without saying that I am still a learner with Access.

Reply | Quote

Replies

Just use a combobox on your form bound to the Category field. For the Rowsource of the combobox, create either a saved query ora SQL Select String like “SELECT Type, Unique_Key, Value FROM tblStatus WHERE Type = ‘Category'”. That will give you a list of the records for Category. You can hide the Unique_Key and Type columns by setting their widths to zero in the combobox properties. Bind the combobox to the second column (Unique_Key).

Reply | Quote

Thanks Charlotte – it is always so easy when explained by an expert

Reply | Quote