If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Query as data source for form
Before you proceed, consider reconsidering your data structure. It won't do
a lot of good to move on to forms (how you display data) if you don't already have the data organized in a way Access can (easily) work with it. Shut down your PC and grab paper/pencil. Sketch out the things about which you want to keep information (e.g., carriers, routes, carriers X routes, ???). Then draw lines between those and mark the relationships (one-to-many, ???). Those are your "entities" (things) and "relationships". Now figure what data elements are associated with each entitiy -- for example, I'll guess that carriers have first and last names. Now's a good time to turn the PC back on and use Access to build tables (approx. entities) that hold data elements. Post back with your table structure and relationships for more folks here to offer ideas. Good luck! Regards Jeff Boyce Microsoft Office/Access MVP "News Boy" wrote in message ... Hi, Jeff - I can't imagine the table you describe. But I have a bigger problem. I got a form to accept some data entry into like name and address fields, but it will not allow me to enter data into the fields with relationships. For example, the field Carriers.Account# should always be the same as the field Routes.Carrier. The form pointing to both fields will not allow me to change their data. The error message I get says to change the index to allow duplicates or get rid of the iindex, but then it refuses to save the table. Even when I delete all forms and queries, the table can't be closed with the design changes, and the error messages are very unhelpful. It looks like I'm going to have to start all over again from scratch. It really frustrates me; this in not a complicated data base. One table for carriers and one table for routes and one related field. Forget the subs; I can't even get this one simple task to perform. I really need to get this working and start using it. Hopefully your work is more successful. Kind regards, - Dixon "Jeff Boyce" wrote: Dixon The "sub1, sub 2, ... sub 'n'" is a dead giveaway! If you have (as many as) four (?3, 5, 9, ??) repeating columns in a table, you probably have a one-to-many relationship. To get the best use of Access' relationally-oriented features/functions, you need to look at creating a new "trelRouteSubs" that hold valid combinations of Routes and Subs. Regards Jeff Boyce Microsoft Office/Access MVP "News Boy" wrote in message ... Thank you, Jeff. Here are the tables: tblRoute AutoNum RouteNum Carrier (Points to Carrier.AccountNum) Sub1 (Points to Subs.AutoNum) : Sub4 (Points to Subs.AutoNum) other route and map information fields tblCarrier AutoNum AccountNum (Points to Routes.Carrier) other personal information fields tblSubs AutoNum (points to Routes.Sub1 .. Routes.Sub4 other personal information fields Then I should be able to pull up a report of which routes relate to which carriers, and which subs are available for which routes, and various other useful stuff. I ran into a problem using AutoNum to identify the subs in that it cannot be edited from a form, so I may have to add a tag field to the Subs table. I have not been able to get the query to act as data source, and was further confused when the wizard created a form that accessed all the tables simultaneously, but they were out of order. The help files say this is impossible. I will be heading into crisis mode here next week and probably only able to work on it a few minutes a day, but things should calm down in a week or two. Thanks again for your help. Sincerely, Dixon "Jeff Boyce" wrote: I'm still having a bit of difficulty "seeing" the tables/relationships... May I ask that you try posting a description something like: tblPerson PersonID FirstName LastName DOB tblClass ClassID ClassTitle ClassDescription trelEnrollment EnrollmentID PersonID ClassID EnrollmentDate (this is only an example) Regards Jeff Boyce Microsoft Office/Access MVP "News Boy" wrote in message news I'm sure it is I who do not understand relationships ... I have one table with fields RouteNumber, Carrier (which is the Carrier's Account Nmber off the Carriers table), Sub1,Sub2, etc (which are the AutoIncrement value in the records for the Subs table). I would think that the field Carriers.AccountNumber would have a one-to-one relation with the field Routes.Carrier. The Route can have only one Carrier, but the Carrier can have more than one Route. The Subs are identified by their Auto Increment field because some of them don't have an account number. The Routes table has 4 fields fo relate to Subs.ID: Routes.Sub1, Routes.Sub2, Routes.Sub3, and Routes.Sub4. A Route can only have one Sub1, etc., but Subs can sub for more than one Route. I would think that Route.Subx fields would relate one-to-one with Subs.ID, so it's probably one-to-many like you say. (There are other fields of information in the tables but they don't relate to one another.) "Jeff Boyce" wrote: Dixon I'm not sure I understand the relationships... for instance, how does "one-to-one" come into the picture? Regards Jeff Boyce Microsoft Office/Access MVP "News Boy" wrote in message ... Yes, thank you, Mr. Boyce. I stumbled upon that subform thing by constructing simple practice forms with one or two fields per table. I haven't discovered how to define one-to-one relationships yet; the wizard simply tells me "one-to-many" or "indeterminate" so far. I do have more than one route per carrier; the Routes table has one route per record with the carrier,s account number as one field. Therefore, the account number is unique on the Carriers table but duplicated on the Routes table. Subs are related to the Routes table via the fields Sub1, Sub2, etc which all point at the Auto ID field fo the Subs table. I am hoping to be able to enter and modify data like when a new carrier takes over a route, to just enter the new account number in the tag field of the Routes table, etc. and it looks like somehow the subform idea will work. I prefer elegance to brute force, and strive for a deeper understanding of how this Access software functions. Your help and feedback is very much appreciated. - Dixon Stuelke "Jeff Boyce" wrote: I'll take a guess that you have "one-to-many" relationships in there somewhere (e.g., one carrier, many routes). When you join all the tables together, Access has no way to know WHICH record to update unless you get very specific. Instead of trying to cram all the data from all the tables into a single form, consider using a main form (?carriers) with a sub-form (?routes), and that subform could have a subform of its own (?subs). There are a lot of questions marks in there because I don't know your data, so I don't know what the relationships are... Regards Jeff Boyce Microsoft Office/Access MVP "News Boy" News wrote in message news I have data in 3 tables called Carriers, Routes, and Subs. I can make a form display data from one table at a time. But, to access multiple tables, a query is necessary. I have created a query in Query Wizard which displays the relationships between the tables, etc. In Design View, with the Query as data source, the form displays all the field names of all the tables properly. But the form will not access any of the records in the tables. The help files suggest that the query may not be outputting any result, but does not say what to do to fix this. |
#12
|
|||
|
|||
Query as data source for form
My time in these 'groups, like most everyone else's, is voluntary.
You might want to consider continuing this thread, as you will get a lot more folks looking over your descriptions and offering ideas... Regards Jeff Boyce Microsoft Office/Access MVP "News Boy" wrote in message ... Things just go from bad to worse. Now I have created a table "carriers" with only 3 fields: ID (AutoNum key field), Last Name, and Account Number. My Routes table has about 20 records filled, with a relationship between carriers.AccountNumber and Routes.Carrier. The Carrier table has only 3 records filled. I made a Query in the Wizard which I intend to return all the data from both tables, but it only returns three records. Therefore, the form I made to enter data into these tables only accesses three records, and will not create a new record at all. Compounding the problem, I can't make head nor tail out of the help files and have no clue how to use Design View. How do I Insert an interface to a given field, when the only options the Insert button gives me are Picture, Object, Page Number, Date/Time, and ActiveX Control, which then lists a bunch of things I don't know about with no explanantion. Do you think you could find it in your heart to talk with me directly or would I need to pay extra for a phone call? - Dixon "Jeff Boyce" wrote: Dixon The "sub1, sub 2, ... sub 'n'" is a dead giveaway! If you have (as many as) four (?3, 5, 9, ??) repeating columns in a table, you probably have a one-to-many relationship. To get the best use of Access' relationally-oriented features/functions, you need to look at creating a new "trelRouteSubs" that hold valid combinations of Routes and Subs. Regards Jeff Boyce Microsoft Office/Access MVP "News Boy" wrote in message ... Thank you, Jeff. Here are the tables: tblRoute AutoNum RouteNum Carrier (Points to Carrier.AccountNum) Sub1 (Points to Subs.AutoNum) : Sub4 (Points to Subs.AutoNum) other route and map information fields tblCarrier AutoNum AccountNum (Points to Routes.Carrier) other personal information fields tblSubs AutoNum (points to Routes.Sub1 .. Routes.Sub4 other personal information fields Then I should be able to pull up a report of which routes relate to which carriers, and which subs are available for which routes, and various other useful stuff. I ran into a problem using AutoNum to identify the subs in that it cannot be edited from a form, so I may have to add a tag field to the Subs table. I have not been able to get the query to act as data source, and was further confused when the wizard created a form that accessed all the tables simultaneously, but they were out of order. The help files say this is impossible. I will be heading into crisis mode here next week and probably only able to work on it a few minutes a day, but things should calm down in a week or two. Thanks again for your help. Sincerely, Dixon "Jeff Boyce" wrote: I'm still having a bit of difficulty "seeing" the tables/relationships... May I ask that you try posting a description something like: tblPerson PersonID FirstName LastName DOB tblClass ClassID ClassTitle ClassDescription trelEnrollment EnrollmentID PersonID ClassID EnrollmentDate (this is only an example) Regards Jeff Boyce Microsoft Office/Access MVP "News Boy" wrote in message news I'm sure it is I who do not understand relationships ... I have one table with fields RouteNumber, Carrier (which is the Carrier's Account Nmber off the Carriers table), Sub1,Sub2, etc (which are the AutoIncrement value in the records for the Subs table). I would think that the field Carriers.AccountNumber would have a one-to-one relation with the field Routes.Carrier. The Route can have only one Carrier, but the Carrier can have more than one Route. The Subs are identified by their Auto Increment field because some of them don't have an account number. The Routes table has 4 fields fo relate to Subs.ID: Routes.Sub1, Routes.Sub2, Routes.Sub3, and Routes.Sub4. A Route can only have one Sub1, etc., but Subs can sub for more than one Route. I would think that Route.Subx fields would relate one-to-one with Subs.ID, so it's probably one-to-many like you say. (There are other fields of information in the tables but they don't relate to one another.) "Jeff Boyce" wrote: Dixon I'm not sure I understand the relationships... for instance, how does "one-to-one" come into the picture? Regards Jeff Boyce Microsoft Office/Access MVP "News Boy" wrote in message ... Yes, thank you, Mr. Boyce. I stumbled upon that subform thing by constructing simple practice forms with one or two fields per table. I haven't discovered how to define one-to-one relationships yet; the wizard simply tells me "one-to-many" or "indeterminate" so far. I do have more than one route per carrier; the Routes table has one route per record with the carrier,s account number as one field. Therefore, the account number is unique on the Carriers table but duplicated on the Routes table. Subs are related to the Routes table via the fields Sub1, Sub2, etc which all point at the Auto ID field fo the Subs table. I am hoping to be able to enter and modify data like when a new carrier takes over a route, to just enter the new account number in the tag field of the Routes table, etc. and it looks like somehow the subform idea will work. I prefer elegance to brute force, and strive for a deeper understanding of how this Access software functions. Your help and feedback is very much appreciated. - Dixon Stuelke "Jeff Boyce" wrote: I'll take a guess that you have "one-to-many" relationships in there somewhere (e.g., one carrier, many routes). When you join all the tables together, Access has no way to know WHICH record to update unless you get very specific. Instead of trying to cram all the data from all the tables into a single form, consider using a main form (?carriers) with a sub-form (?routes), and that subform could have a subform of its own (?subs). There are a lot of questions marks in there because I don't know your data, so I don't know what the relationships are... Regards Jeff Boyce Microsoft Office/Access MVP "News Boy" News wrote in message news I have data in 3 tables called Carriers, Routes, and Subs. I can make a form display data from one table at a time. But, to access multiple tables, a query is necessary. I have created a query in Query Wizard which displays the relationships between the tables, etc. In Design View, with the Query as data source, the form displays all the field names of all the tables properly. But the form will not access any of the records in the tables. The help files suggest that the query may not be outputting any result, but does not say what to do to fix this. |
|
Thread Tools | |
Display Modes | |
|
|