"Recordset is not updateable" - form/query conflict

[justusj]

I have run into the issue of a "recordset" not being "updateable". I am running Access 2007.

My issue is that I have a query that is pulling data from two tables using a union statement. I have built a form that I would like to be able to use to update those tables.

I have heard that I may be able to somehow link a non-updateable query to an updateable table (or possibly another query), or eliminate the query and just use the tables themselves (which I have already done but would prefer the option to somehow update the query).

Is there a way to use Access/SQL to join or link the tables in the form (is this possible?) so that each table is updated as fields on the form are modified?

I am not sure the best method to use in my approach.

Any help is appreciated. Thank you.

 

[boblarson]

Need to ask a couple of questions.

1. Why do you need to bind to more than one table in the form?

2. What is the relationship (one-to-one, one-to-many, many-to-many) between the tables?

 

[justusj]

I would just prefer to be able to do this. I would like to know if I can even use a form to update more than one table --> i.e. properties/source data.

There is no relationship between the tables, but they are both indeterminately linked to another table.



Need to ask a couple of questions.

1. Why do you need to bind to more than one table in the form?

2. What is the relationship (one-to-one, one-to-many, many-to-many) between the tables?

 

[boblarson]

I would just prefer to be able to do this. I would like to know if I can even use a form to update more than one table --> i.e. properties/source data.

Not on one query.

There is no relationship between the tables, but they are both indeterminately linked to another table.

You would need to use an unlinked subform most likely. Indeterminately linked? I wonder if your design is of proper normalization design. It sounds rather "fishy" if I might say so. If you post either a copy of your database, or a screenshot of the relationships, that might also aid in getting to a good answer.

 

[justusj]

May I ask, what did you mean, "Not on one query".

Thank you.

Please see the attachment for the screenshot.

 

[Galaxiom]

CTVELISA and LEPROELISA tables should be merged and another field added to indicate whatever those names mean. They hold exactly the same kind of data.

CTVELISA and LEPROELISA should be records in a table against a key and that key used in the combined table to indicate which thing the record belongs to.

PTType should be stored in this table as the ID from the GRINwhatever table.
Are they quote makes in the table name?

Location table looks unlikely. I would expect this to have LocationID and LocationName. Another table would have records with NCGRNO and LocationID fields

 

[justusj]

Thank you Galaxiom,

This is my first venture into Access. I have C++ knowledge, but the SQL syntax is new to me. I have been given a db that is atrocious and I am working from the ground up.

Your ideas on the structure are logical improvements. Thank you!

I will be implementing some changes tomorrow and we'll see how that goes.

 

[justusj]

Galaxiom,

"CTVELISA and LEPROELISA tables should be merged and another field added to indicate whatever those names mean."

CTVELISA and LEPROELISA are descriptors used in biology and their meaning is understood by those who use the database

the location table is setup in the most efficient manner as far as i can tell. the location records are for plants that are not necessarily in the same location. in this table, NCGR (unique identifier) is used as the foreign key which refers to the NCGR table.

after looking at the CTVELISA and LEPROELISA relationships it would seem that they have the same type of data, but they in fact hold different types of information (text). these tables hold records regarding different biological tests that were completed on different dates, with different outcomes, and different PTTYPEs and PTCODEs. currently both of these tables have the NCGR number used as the foreign key and a automatically assigned Access number as the primary key.

the GRIN "PTTYPE" table (which has been changed to GRIN Database Standards) holds records of each of the different types of PTCODEs and PTTYPEs and related biological pathogens, and the automatically assigned Access number as the primary key.

"CTVELISA and LEPROELISA should be records in a table against a key and that key used in the combined table to indicate which thing the record belongs to."

"the thing that the record belongs to", is an individual plant. based on this information do you still think that the two tables can be merged?

thanks again for your help!

 

[justusj]

Have you had the opportunity to have a look at the screenshot and others posts I put here?

Thank you, any help is greatly appreciated.

 

[Galaxiom]

the location table is setup in the most efficient manner as far as i can tell.

the location records are for plants that are not necessarily in the same location. in this table, NCGR (unique identifier) is used as the foreign key which refers to the NCGR table.

You are entirely missing the point. Placing the same kind of information in different fields is pointless. You haven't even tagged the main records with the location. The relationship itself is pointless. You might as well have included the location fields in tblNGCR. Separating tables like this is still a flat structure.

You can return no information other than that they were grown in four different places. You won't be able to report on a particular location. You won't be able to add new locations without modifying the tables. You will struggle to report on anything about location.

after looking at the CTVELISA and LEPROELISA relationships it would seem that they have the same type of data, but they in fact hold different types of information (text).

The tables have identical fields with identical types of information (text). The only thing different is the plant they apply to. Stop thinking of tables as spreadsheets.

If you don't want to apply the suggestions go ahead as you have it. However don't waste our time asking for advice and then arguing that your way is better.

 

[justusj]

Galaxiom,

I am not sure that my tone came accross as I had intended. I was not arguing that my way is better, simply clarifying the structure of the db that I have been given. I know "my" way is not better that is why I am here. I do appreciate the time and thought that you put into your replies.

"You are entirely missing the point. Placing the same kind of information in different fields is pointless. You haven't even tagged the main records with the location. The relationship itself is pointless. You might as well have included the location fields in tblNGCR. Separating tables like this is still a flat structure."

I assume when you say tagged the main records with the location, you mean that I haven't drawn the relationship between the two tables in the most efficient manner?


"You can return no information other than that they were grown in four different places. You won't be able to report on a particular location. You won't be able to add new locations without modifying the tables. You will struggle to report on anything about location."

The table with the locations was recently created from another table that had all of the location data for one plant in one column (Location). If this old method is best for reporting please let me know.


"The tables have identical fields with identical types of information (text). The only thing different is the plant they apply to. Stop thinking of tables as spreadsheets."

These are the tables that you suggested I merge. I will look into this more to get an understanding of how my organization should best approach this.

Thank you for your time. I hope to hear more of your ideas.

 

[Galaxiom]

By including the records for both plants in the same table you will be able to use the same form to enter and retrieve records of the plant type you are working with.

Add a field for the PlantID to the main records. Include this field in the record source query of the form and a criterion to compare it with a combo either on that form or a master form. Choose the plant you want and the form will show only that plant.

A Plant table will contain PlantID and PlantName and is used to translate the name to an ID. Other than in this table, throughout the database the plant will always be referred to by its ID.

I would include a LocationID field on the main records as well. Similarly the ID comes from the Location table. Unlike the one you curently have it will have LocationID and LocationName and will simply translate the location.

This arrangement will allow you to select all the records for a particular location. Perhaps there will be variation in the results from different locations due to environmental differences and you will be able to analyse based on location. You can easily recover records for any plants at a particular location or all locations with a particular plant. To report a list of plants at a location or locations with a particular plant, group by one of the fields.

All this information is coming form one table with very simple queries. Access will still return the results in moments even if there are a million records in that table. Separating the same kind of data on arbitrary field such as you have with plants is the most common misunderstanding made by new developers. It comes from thinking of tables as spreadsheets.

Using this structure you will also be able to add more locations and more plants without adding more fields or tables. Good database structure allows more variants to be added without adding new fields, just new records. This is one of the key tests when determining if your structure follows good database design.

The location table will have LocationID and a LocationName. Separating locations into their own fields (as you have done) isolates information and you don't want to do that.

So your main tables will have PlantID and LocationID foreign key fields along with the experiment type and result fields. Once again your experiment types will have a table with ExperimentID and ExperimentName fields. The main table will refer to the experiment by its ID. You can then easily retreive all the results of a particular experiment type from any plant or location by querying that ExperiementID.

So you want to know about a particular experiment on a particular plant at a particular location? Set criteria for each and you have it, all from the one table. The other tables only translate the IDs into meaningful names. The main records will look like a meaningless bunch of numbers but once again rememeber database tables are not spreadsheets. Presentation in a database is done by forms and reports rather than being combined with the data and processing as in a spreadsheet. The structure I have outlined is the most efficient way to store information. That is the difference from a spreadsheet.

Anything that uses a name, particular if they are long and easily misspelled, should be translated into an ID by a table and thereafter referred to in other tables only by the ID. This is far more efficient for the database to query. Comparing text strings is intensive work where comparing numbers is fast. It also allows avoid spelling mistakes and if one is made it need only be fixed in one place.

Hope this makes sense.