where to put the query ?

[Mclaren]

OK, so my DB is split. I noticed that i cannot link to queries in the back end. As a result, my front end has all my queries.



Originally my DB was designed as a non-split DB. some of my tables had lookup fields based on queries. Now that my DB is split, the tables in the back end do not show the correct data when opening the table in the back end.

If i open the table link in the front end, all is correct.

So now to my question, should i create the lookup based on "bult in" query, or should i leave it as is.

Also, should i base fields in forms on a query, or should i have the query embeded into the forms field ?

[SteveF]

Basically, yes, your understanding of a split application is correct: data tables only in the back-end, everything else (queries, forms, reports, code modules) in the front-end.

But, it looks like you've run up against at least one of the reasons many of us (myself included) stay far away from lookup fields in tables.

If you want the performance of a lookup field in a form without actually having one in the data, use a separate lookup TABLE, along with a combo box on the form that display the human-readable value on-screen and stores the actual related value in the table. Same thing for reports: build a query that links the tables & provides the data you need, use the query as the report's data source.

As far as the rest of the basic from design, you can go either way -- pull data directly from a table or put a query in between the table & the form. I prefer queries when possible; they give me a bit more control over the order in which records are displayed on the form. The query can be either a stand-alone in the query collection, or embedded into the data-source property of the form.

Steve

[Mclaren]

I am not really sure what you mean by this:

If you want the performance of a lookup field in a form without actually having one in the data, use a separate lookup TABLE, along with a combo box on the form that display the human-readable value on-screen and stores the actual related value in the table.

I have a feeling, that what you are telling me is: the lookup field should be changed to a number field and just store the ID of the item being looked up. I am not sure about how a second "lookup table" would look ?

Everything is wrking right now fine, but my main concern is that when i look into the back end tables, in the look up fields (becuase i based the lookup up fields on queries before the split) the back end tables on contain the ID of the "looked up" information, instead of the actual value i would like to see.

I currently am using queries for the most part as record sources for both forms and reports.

[Mclaren]

Do you have an example of a split db with no lookup fields but a lookup table, just so i can get my head around what you are saying ?

[SteveF]

I am not really sure what you mean by this:

I have a feeling, that what you are telling me is: the lookup field should be changed to a number field and just store the ID of the item being looked up. I am not sure about how a second "lookup table" would look ?

Everything is wrking right now fine, but my main concern is that when i look into the back end tables, in the look up fields (becuase i based the lookup up fields on queries before the split) the back end tables on contain the ID of the "looked up" information, instead of the actual value i would like to see.

I currently am using queries for the most part as record sources for both forms and reports.

Yes, the lookup field should be changed & just store the ID of the item. That's what it's actually doing behind the scenes anyway. The lookup table would have at least two columns: The ID of the item, the "actual value" you want to display in forms & reports, and possibly others, depending on how flexible you want to be.

Anytime you need to change the ID of the item, do so using a form with a combo box or list box. The RowSource for this control will read the lookup table and have two columns: the ID of the item (usually hidden) and the "actual value". The properties of the control are set to store the ID in the main-data table that corresponds to the "actual value" selected in the control.

If you need a read-only view of the actual data, use a query that joins the main-data table and the lookup table by the item ID, and display the "actual value" from the lookup table. Use the same method for reports.

I don't have a database handy right now that I'd feel comfortable sharing in its entirety; but I've attached a lookup table, along with a table that uses some of the lookup items, from one of my projects. This particular file is not split, but the main project uses a split schema. It includes a form that uses both tables.

[Mclaren]

Thanks. i will have a look at it this weekend.