Select all query fields for main form?

[tagteam]

I have a table with about 3800 records and about 150 fields. It also has 4 tables with related data that each have only one field. I am going to create a query to base my from from. Should I just take the star * and get all the fields for each table or should i manually specify them. There are only about 5 fields out of the 150 that I dont need and I need to see the rest.
Thanks

[June7]

Wildcard should work.

What is relationship of these tables? Why would related tables have only one field? Why would you include them in the query?

[tagteam]

Most of it is for data consistency. I have a list of railcars and they have components, like top unload valve, and the users will put the same thing twice with slightly different spelling. There are a lot of them though so I didnt want to use a value list. This is related to what we discussed in https://www.accessforums.net/access/...zed-37795.html

A couple of the tables do have more fields, like the customer table. What do you recommend as the best way to keep data consistent and allow the user a dropdown selection but also make it easy for them to add new items which does happen frequently (and there are sometimes over 150 items)

[June7]

Can use combobox with LimitToList set to yes then use NotInList event to allow new record creation in the source table. Review: http://www.blueclaw-db.com/access_no...ed_example.htm

[tagteam]

So are you saying that you can use a value list and some vba code to add new entries instead of creating a table to hold the values and then linking to an id field?

I am a bit confused because I thought that is what an access lookup field is and most people say to avoid lookup fields because you cant up size the database.

[June7]

I wasn't saying to use a value list. A lookup can definitely use a table as source for the list. The referenced link demos adding record to source table. However, code should be able to add to a value list, I've never done that.

What should be avoided is lookup using alias in table. Lookups themselves are just fine, with either ValueList or Table/query as RowSource, but build in form, not table.

[tagteam]

OK, I think it finally understand. The lookup field that people are saying to avoid is using the lookup wizard in a Table that will allow you to create a lookup that is kind of hidden in the table logic and is not tied to an external value list or table.

So if I were to use a value list for a text field for example and I want to limit the list I should not do that in the actual field set up in the table but create a form that has the text options in the form control?

If that is correct then the table itself would not actually limit the text that could go in the field but the form would.

[June7]

Not quite. What is an issue in table is a combobox set with a lookup that uses an alias. This can be done with a ValueList construct or by Table/Query query. The alias obscures the actual value in the field. The only reason for setting in table many of these control pertinent properties is so that when you create control on form by drag/drop from field list, the properties will be adopted by the control.

Because users should not work directly with tables and queries, only interact with forms and reports, table formatting should be irrelevant. As developer, when I view tables/queries I want to see the real data, not aliases or formatting.

[tagteam]

Ok, got it. Thanks for your help. I will use a query to sort my table and assign that as the source for my combobox and use the code you suggested above if the item is not in the list.
Thanks for all the help.

[tagteam]

Sorry, i am re opening this to ask a further question. I was playing with the combo box and I think i realized what you were talking about in post # 4. you dont actually have to take all the values from a field, make a table, then make a query and then reference that query in the combo box. You can just create the combo box, and it the combo box controls create a query that you then limit to list.

This would mean that I don't actually have to create a new table and query to accomplish my goals, just create a sql select statement in the combo box control row source.

My first method was:

1. query for all the unique values in the field
2. Create a table, tbl_lining, for those values
3. In the parent table change the filed from liningName to liningID
4. Link the parent and child on the liningID field
5. create a query. qry_liningTypes to get all values from tbl_lining
6. In the form, change the field control to combo box, set row source to qry_lininTypes

My second method is:

1. In the form, change the field control to combo box
2. set row source by using query builder
3. Set value of limit to list to Yes

Second method seems much easier but is it better in the long run or any problem if I try to upsize?

Thanks

[June7]

What table are you using as the source for the combobox list?

Your first approach is the more conventional. However, creating the source (lookup) table should be a one-time event. I do have situations where I use the destination data table as the source for combobox list but in that case I set LimitToList to No and users can enter whatever they want. Yes, we do get variations but the RowSource list does help minimize. I built a utility for 'cleaning up' variations when they are discovered.

Nitpicking here, but the destination data table and the lookup table are not a 'parent and child' relationship. A child table should not have records without a related parent record. This is managed by enforcing referential integrity. (An example would be Orders and OrderDetails tables.) On the other hand, a lookup table can have records that are never related to any record.

[tagteam]

Sorry, maybe my terminology is off. The source is is a query that queries a table to get the value names. I am not really sure what you mean source and destination table. I am used to saying parent and child because I have never used a table that does not have a related record in the parent table. For example

Table1: tbl_Railcars : Railcar table and it holds all the different information about a railcar ( 40 + fields)
Field : LiningTypeID: It holds a value (1 - 153)

Table2: tbl_Lining : lining table that has two fields - liningID, liningName
Field : liningID (autonumber): is used to link to tbl_Railcars.LiningTypeID

This results in the field value in tbl_Railcars (main table) being a number. then for the form i use a query and through the link have access to the name field.

Like this picture: http://www.tagteamcomputing.com/temp...lationship.png

This is what I have been calling a lookup table because the second table, tbl_Lining, only has an ID and Name field so it is really just used to link a name to the main table.

When you are talking about a look up table do you mean a table that has no autoid value that links back to another table. In essence it would literally just be a way to get a text value and insert it into the current field?

That would mean the value int tbl_Railcar would be the actual text value and not a number.

It would look like this picture: http://www.tagteamcomputing.com/temp...lationship.png

It seems that this second picture might be the better way because I can still use a table to limit the values for entry but I don't end up with a number in the field, which the user doesnt understand, and I dont have to create the linking relationship. This would make it more simple when creating reports, queries, and filters because the actual value is in the field. I should add that there are only about 5000 railcars so space is not too big an issue.

[tagteam]

I just realized something else. If i set up the linked tables and enforce referential integrity (Like I have been doing) it will force a value in each field. sometimes a car does not have a lining and I would need to leave it blank. So it would be better to create an unlinked table and use it as the row source.

[June7]

Source table is the table used to create the combobox RowSource. Destination table is table the value selected in combobox is saved to. Source table is normally a 'lookup' table. This is a table of established choices to be presented in combobox RowSource. Should be able to enter record in a lookup table even if the record never gets selected into the destination table, therefore it is not a 'child' table.

Are you using a 'lookup' table as source for the comobobox? Are you saving an ID (autonumer?) value or text descriptor in the destination table? Are there other fields in the source table that provide info about the item?

If you do set relationship then don't enforce referential integrity.