Full Version: Forms using query of multiple tables or queries
UtterAccess Forums > Microsoft® Access > Access Forms
hu8mypho
Hello,

I'm trying to change the record source of my form from one query to another. The problem is, if I use a query that is querying multiple tables or multiple queries, the form will not allow me to update the fields. If I switch back to using a query of a single table then I am able to modify the fields.

Keep in mind that the multiple tables are just derived from the single large table. I just wanted to break the single table into multiple table where they are all linked by a pkey.

Ochecked the field properties, its not lock, from properties allows edits. Can anyone please help me with this problem, or at least let me know why its happening.

Thanks

Also, is it possible to have a form that links to multiple tables or queries?
Edited by: hu8mypho on Wed May 18 12:32:28 EDT 2005.
jmcwk
You need a Main/Subform Environment

However prior to building ANY forms you must ensure your structure is correct and you meet Normalization.

How are you tables linked ?
How is the structure set up?
Have you checked out the tutorials here in UA on Normalization etc.


By the Way! Welcome to UA!

Edited by: jmcwk on Wed May 18 12:32:57 EDT 2005.
hu8mypho
Thank you =).
created this table and form kind of on the fly.
Ocreated make-table queries to created the tables, and include the pkey from the original table in each small table. the tables are linked via this field.
I'm using the original form, the only field that i am able to edit is the unbound field that I use to search for individual records, the form returns the record that I search, but the fields will not allow me to edit even though the properties say I can. However, when I switch to the query of the single table, I can edit the fields.
Haven't seen tutorials yet, will look for them now.
ScottGem
John explained the issue to you. Try using main form/sub forms instead of a multi-table query.
hu8mypho
I've never used this type before, so basically I need to recreate multiple forms using the multiple tables and nest them on the main form.....?

My pkey is is not an autonumber, it is unique to each record, i'm kind of lost as how the pkey would be inserted into each table without apearing or needing to en re entered on each subform when a new record is created. And also, will this affect my search procedure as that is the field that I search on...

Thanks in advance.

Edit - I do have an autonumber field in my table though.
Edited by: hu8mypho on Wed May 18 13:47:26 EDT 2005.
fkegley
If your tables are properly linked, then the Form wizard will take care of setting the link field properties and moving the value to the subforms. No, you can still search on that field. I do it with a combo box set to Find a record in this form's recordset.
jmcwk
hu8mypho,
EFORE you go any further I strongly suggest you get your table structure properly Normalized if you continue going down the path you are describing you will have nothing but problems. NoahP has a great article on Normalization Here
ScottGem
As Frank mentioned, in a properly constructed database when you enter a record into a subform, it automatically picks up the linking field from the mainform as a foreign key.
You might also want to look at the tutorial on Natural vs Surrogate keys. its the overwhleming consensus here on UA that you should not use Natural keys.
hu8mypho
I've never built anything with more than 2 or 3 tables before. I currently have 12 tables, one linked to another via the pkey. It like a linear/circular link...will this work?? please advise.
hu8mypho
I will look at the articles and tutorials, thank you.
ScottGem
I suggest you post your database and let us take a look. Links should be from Primary key to foreign key.
hu8mypho
Post deleted by hu8mypho
ScottGem
Your structure needs a LOT of normalizing. First you don't link from PK to PK. You link from PK to FK. Second you have repeating groups in several tables. Anytim you have fields named Net_make1, nat_make2 etc. that is incorrect and your table should be more like:
et_Make_tbl
NetMakeID (PK Autonumber)
StoreNum (FK)
NetMakeType
NetMakeValue
Its not clear what this database is for so its hard to comment further. But there seems to be a lot of opportunity for normalization.
hu8mypho
Thank you everyone for your input, I will definitely take all thoughts into consideration. As for final resolutions, I guess I have no choice but to start from scratch. The only problem is that data already exist in this format, so I'm gonna have to spend plenty of time to do neccessary clean, and learn on the fly as well =). Anyone has more good links for instructions/tutorials I would greatly appreciate it. Thank you all.
egards,
fkegley
That is probably not right, though we will have to see it to be sure. Generally, a link goes from a Primary Key in one table to what is known as a Foreign Key in another table. Almost always, the tables are in a one-to-many relationship. One Customer can have many Orders, so the CustomerID would be the Primary Key in the Customers table but a Foreign Key in the Orders table. Access uses the fields in the relationships to intelligently match records in the "many" table to records in the "one" table or vice-versa.
If all your tables are linked via the Primary Key of each table, then you have built one-to-one relationships between the tables. While a one-to-one relationship is useful for some things, most often it is the one-to-many relationship that is used.
Every table does not necessarily need to linked to every other table. You must know how the data in a table relates to the data stored in the other tables.
The Northwind database is a good place to start.
hu8mypho
Scott,
The reason why I have repeating is because of the single table thing. Since each record would need to cover multiple items...
Any ideas as how I would be able to collect such repeating data to fit into proper structure? please advise...
ScottGem
I'd need to know more about what your database does and what type of data it collects to advise further. But generally a repeating group is broken out into a child table with at least 4 fieldsl; the Primary key, the Foreign key, a type field and a value field. For example: a contact might have several phone numbers (i.e. Home, office, fax, cell, etc.). Having a field for each in your table is a repeating group. Instead you have a child table like this:
blPhones
PhoneID (PK autonumber)
ContactID (FK)
PhoneType (Home, Office, Fax, Cell, etc.)
PhoneNumber
hu8mypho
Scott,
The database's primary usage is for storing survey information. Does that help at all?
hu8mypho
based on my tables, can anyone give me a basic diagram (verbal or drawing) of how my tables should be linked?
ScottGem
Not without understanding the data better. Mayber reposting with some sample data.
hu8mypho
will do, let me get some and I will repost.
hu8mypho
Thank you all for your assistance, I manage to recreate dB, using multiple tables and main/subforms as suggested. Only one last thing to take care of I have a table that has repeating fields, name1, name2, name3, etc..all the way up to 20. I have created a table that has just name, descriptions, etc, however, I need to find a way to query my data from the repeating format and input into new table. If anyone can suggest, it will be very helpful.
urrently: tblNameOld - used to display in form format
namesID(Pk),name1, name2,....name20, desc1, desc2,....desc20, etc, group(Fk).
new: tblNameNew - datasheet format
NameID(Pk), Name, Desc, Group(Fk)
Can anyone recommend a method to consolidate table from former table format to new table format.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.