Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
One form two tables to populate...... How do I????
- Thread starter robjam7
- Start date
Hi
More than 255 fields in one table - STOP - reconsider your table design, theer is almost certainly something very wrong
Regards
Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now - UK
More than 255 fields in one table - STOP - reconsider your table design, theer is almost certainly something very wrong
Regards
Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now - UK
- Thread starter
- #3
I understand what you are saying, and can reduce the number of fields. But is it possible to update two different tables with one form. If none of the tables require the same info.
ie 10 boxes to enter data into on the form, 5 of which are to populate table 1 and the other 5 to populate table 2???
ie 10 boxes to enter data into on the form, 5 of which are to populate table 1 and the other 5 to populate table 2???
Hi
Yes...
easiest way is if there is a relationship between the tables (as there would be in your 255 columns example), you could just make a query and use that as the recordsource of the form
If there is no relationship, you could update the table(s) via SQl commands, rather than via the bound form mechanism
Regards
Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now - UK
Yes...
easiest way is if there is a relationship between the tables (as there would be in your 255 columns example), you could just make a query and use that as the recordsource of the form
If there is no relationship, you could update the table(s) via SQl commands, rather than via the bound form mechanism
Regards
Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now - UK
- Thread starter
- #5
How does the SQL method work? And where do the SQL commands get attached??
The scenario being I have 56 Option boxes on the form, each with three options to select from.
The table that I want the information to go to is Called Safety Issues, and the option Boxes are labeled "Issue 1" thru to "Issues 56".?
The scenario being I have 56 Option boxes on the form, each with three options to select from.
The table that I want the information to go to is Called Safety Issues, and the option Boxes are labeled "Issue 1" thru to "Issues 56".?
Hi
I repeat my earlier advise, if you have more than 255 columns in a table re-examine you design.
I am loathe to spend time telling you to do things which will make things worse
Regards
Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now - UK
I repeat my earlier advise, if you have more than 255 columns in a table re-examine you design.
I am loathe to spend time telling you to do things which will make things worse
Regards
Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now - UK
Yeah, KenReay is 100 percent right. It is almost impossible to a) have a normalized db and b) exceed 255 fields. I realize that you may be thinking "these eggheads are just overcomplicating it" but in this case you need to listen to us.
If you can talk to us in general terms about the application, I will help you to develop a schema. I usually login here at least once a day, so we might finish the schema in less than ten days. I just got alot of help with a .bat file in another forum, so you might say I have a karmic debt to tek-tips
If you don't feel comfortable talking with us, you need to get a local dba involved in the project.
If you can talk to us in general terms about the application, I will help you to develop a schema. I usually login here at least once a day, so we might finish the schema in less than ten days. I just got alot of help with a .bat file in another forum, so you might say I have a karmic debt to tek-tips
If you don't feel comfortable talking with us, you need to get a local dba involved in the project.
- Thread starter
- #8
I understand what both of you are talking about. And would have created the whole database differently if id have had all the parameters and outputs required from day 1. The point is that this data base is for a pilot scheme, that if proved viable will be re-written in oracle. Unfortuantely, I do not have the time to rebuild the whole db, as this is not my core function. The main form is an inspection sheet with a large number of entries required. It is a legal requirement so I cannot slim them down. They now require an addtional set of options which by using a options box means only 1 extra field per 3 options. But even this means an addtional 56 fields, as each of these options has to be tied to each of these inspections.
If there is a way of pushin this data into a sub table beneath the original table that will shortcut the issue.
If there is a way of pushin this data into a sub table beneath the original table that will shortcut the issue.
Hi
If you put them in a 'sub table' as you term it, with a key field to link it to the main table, providing the is only only sub table row per main table row, you can simply make a query as I advised in earlier post, then use the query in the form, in this case there is no need to mess about with SQl updates, if you are pushed for time surely that approach is preferable?
Regards
Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now - UK
If you put them in a 'sub table' as you term it, with a key field to link it to the main table, providing the is only only sub table row per main table row, you can simply make a query as I advised in earlier post, then use the query in the form, in this case there is no need to mess about with SQl updates, if you are pushed for time surely that approach is preferable?
Regards
Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now - UK
Let's use an analogy to discuss this. We know that this project has something to do with safety issues. Here are some questions for you:
Does your external interface look something like this?
bolt strength {choose poor, average, or good}
widget soundness {choose poor, average, or good}
beam strength {choose poor, average, or good}
Does your table look something like this?
bolt strength poor
bolt strength average
bolt strength good
widget soundness poor
widget soundness average
widget soundness good
beam strength poor
beam strength average
beam strength good
You say that "they" want another set of options. To continue with my analogy, are they saying "Gee, we also want to have 'very poor' as an option for all of these."
In short, give us some information about the data that you are capturing.
Does your external interface look something like this?
bolt strength {choose poor, average, or good}
widget soundness {choose poor, average, or good}
beam strength {choose poor, average, or good}
Does your table look something like this?
bolt strength poor
bolt strength average
bolt strength good
widget soundness poor
widget soundness average
widget soundness good
beam strength poor
beam strength average
beam strength good
You say that "they" want another set of options. To continue with my analogy, are they saying "Gee, we also want to have 'very poor' as an option for all of these."
In short, give us some information about the data that you are capturing.
- Thread starter
- #11
The data thats being collected is around safety observations. There are 7 standard entries, ie site, location, date etc.
Then there are 58 observations that ae to be made. Each observation has to be identified as Applicable/ Non applicable, Compliant or non compliant. Each observation has memo field for comment if required.
The item that has to be added is an options box with three options for each observation.
In other words for each observation there are the following:-
Compliant/ non Compliant (tick box), Applicable/ Non Applicable (tick box), Comment field (text box).
The additional item for each observation is an option box with three options.
As I said before. If I had known about the additional requirements, I would have done it differently.
I hope that this explains the layout.
Then there are 58 observations that ae to be made. Each observation has to be identified as Applicable/ Non applicable, Compliant or non compliant. Each observation has memo field for comment if required.
The item that has to be added is an options box with three options for each observation.
In other words for each observation there are the following:-
Compliant/ non Compliant (tick box), Applicable/ Non Applicable (tick box), Comment field (text box).
The additional item for each observation is an option box with three options.
As I said before. If I had known about the additional requirements, I would have done it differently.
I hope that this explains the layout.
Maybe I'm missing something.
Your current structure is this
7 ID fields
58 compliant/noncompliant fields
58 applicable/ not applicable fields
58 comment fields
58 option box fields (the new requirement)
That is a very big record, but its only 239 fields, not 255.
In any case, I put two of those field groups together. I would have 58 fields with the following choices: compliant, noncompliant, or not applicable. It is obvious that the test was applicable, if they were judged compliant or noncompliant.
In addition, I would evaluate how many comments the reviewers typically make. If they make a comment on almost every test, they perhaps you really do need 58 comment fields. But if they only make one or two comments per record, you could put the comments into a child table. The table would have three fields: recordID, comment, test number.
Your current structure is this
7 ID fields
58 compliant/noncompliant fields
58 applicable/ not applicable fields
58 comment fields
58 option box fields (the new requirement)
That is a very big record, but its only 239 fields, not 255.
In any case, I put two of those field groups together. I would have 58 fields with the following choices: compliant, noncompliant, or not applicable. It is obvious that the test was applicable, if they were judged compliant or noncompliant.
In addition, I would evaluate how many comments the reviewers typically make. If they make a comment on almost every test, they perhaps you really do need 58 comment fields. But if they only make one or two comments per record, you could put the comments into a child table. The table would have three fields: recordID, comment, test number.
Hi
It could be that Access is saying it cannot create more fields, but you have not reached 255 fields
This is because if you create a fields, then delete it, that 'slot' is used up and counts towards the 255
To recover these 'lost' slots, compact the database
Regards
Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now - UK
It could be that Access is saying it cannot create more fields, but you have not reached 255 fields
This is because if you create a fields, then delete it, that 'slot' is used up and counts towards the 255
To recover these 'lost' slots, compact the database
Regards
Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now - UK
- Thread starter
- #14
Well, I think that KenReay has alot of technical knowledge, so he probably could give you a great answer. I can give you a good workaround solution. Remember that its really easy to create a new .mdb and import objects into it. If Access is saying "for some esoteric reason, this .mdb makes me barf" then create a new .mdb.
To import objects into the new .mdb, go to file/get external data/import. Browse until you locate the old .mdb. Then double-click the old .mdb. You will see an interface with tabs for each object type. Choose the appropriate tab. You can select just one object, or you can shift-click to choose several.
It may be that the compact action did not work, and if you import the table you will just import the problem. If you want to be extra safe, remake the table before you import it. Use a make-table query to create a new table with the appropriate fields. Then do what I explained previously.
To import objects into the new .mdb, go to file/get external data/import. Browse until you locate the old .mdb. Then double-click the old .mdb. You will see an interface with tabs for each object type. Choose the appropriate tab. You can select just one object, or you can shift-click to choose several.
It may be that the compact action did not work, and if you import the table you will just import the problem. If you want to be extra safe, remake the table before you import it. Use a make-table query to create a new table with the appropriate fields. Then do what I explained previously.
Hi
OhioSteve
Good advice, I would do likewise
Regards
Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now - UK
OhioSteve
Good advice, I would do likewise
Regards
Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now - UK
KenReay -
First, congratulations on being TipMaster of the week. A well-deserved honor.
Second, my issue. I have a form (bound to a query) that is for data entry. When completed, it needs to update data to two tables (both with less than 255 fields).
I can populate the first table because of my query (I assume), but cannot seem to update to the second table. I believe part of this issue is that I need to look ID values that are created in the first table.
Above, you mentioned "you could just make a query and use that as the recordsource of the form...". This seems logical, but I am a little lost on how to start. Can you give me a push?
First, congratulations on being TipMaster of the week. A well-deserved honor.
Second, my issue. I have a form (bound to a query) that is for data entry. When completed, it needs to update data to two tables (both with less than 255 fields).
I can populate the first table because of my query (I assume), but cannot seem to update to the second table. I believe part of this issue is that I need to look ID values that are created in the first table.
Above, you mentioned "you could just make a query and use that as the recordsource of the form...". This seems logical, but I am a little lost on how to start. Can you give me a push?
When you choose the data source for a form, that is called "binding" the data source. Most Access developers bind a data source to each form. You can only have one data source bound to a given form.
However, many database applications do not emphasize binding, and it is certainly NOT mandatory even in Access. So the short answer to your question is...don't use binding on a form that must update two tables.
If you don't use binding you will need alot of VB code to take the place of the binding. That's where gurus like Ken Reay come in...they can write such code. I could provide a solution, but it would not be an ideal solution.
However, many database applications do not emphasize binding, and it is certainly NOT mandatory even in Access. So the short answer to your question is...don't use binding on a form that must update two tables.
If you don't use binding you will need alot of VB code to take the place of the binding. That's where gurus like Ken Reay come in...they can write such code. I could provide a solution, but it would not be an ideal solution.
Hi
This is becomming imposible to keep track of, I think you need to use a sub form within your main form
Regards
Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now - UK
This is becomming imposible to keep track of, I think you need to use a sub form within your main form
Regards
Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now - UK
- Status
Similar threads
- Locked
- Question
