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 |
#1
|
|||
|
|||
Denormalizing for form only
Hi, all;
Many times I've answered questions like this in the queries and tabledesign forum: I have a table like this: MyThing ========== MyThingID MyThingDesc MyThingField1 MyThingField2 MyThingField3 Now, how do I query in such a way that Field1, Field2, Field3 are all treated in essentially the same way? And of course what I tell them is that they need a separate table, with MyThingField1, MyThingField2, and MyThingField3 as separate records. But there is a reason people keep asking questions like this, and it is that Access does a much better job of making it easy to make data entry forms with the structure above than with the "correct" structure. With the "correct" structure, you have to use a Left Join on some other table that will return you 3 records, and then you have to worry about a frustrated join. You also may have to add extra code to make sure that MyThingID gets into the MyNewThing table as a FK. And even then, your users are dealing with a vertical structure when the horizontal one might well be more user friendly. Or at least this is what I've always had to do. I'm wondering if there isn't something I've been missing, some wonderfully simple feature of Access, that makes using normalized data less painful from a form-building point of view. It's very frustrating telling people "you need to structure your data this way, but then once you do it you have to manhandle Access into allowing you to enter data." I'm thinking maybe it's something like PivotTable view, but so far I haven't seen any tutorials that show how to use it for this. TIA; Amy |
#2
|
|||
|
|||
Denormalizing for form only
Amy
Although the Excel-like Thing1, Thing2, Thing3 approach is familiar to Excel users, as you already know, it isn't necessary (or desirable) to use this in an Access database. What I've found quite useful for such one-to-many relationship is a main form/subform construction. Regards Jeff Boyce Microsoft Office/Access MVP "Amy Blankenship" wrote in message ... Hi, all; Many times I've answered questions like this in the queries and tabledesign forum: I have a table like this: MyThing ========== MyThingID MyThingDesc MyThingField1 MyThingField2 MyThingField3 Now, how do I query in such a way that Field1, Field2, Field3 are all treated in essentially the same way? And of course what I tell them is that they need a separate table, with MyThingField1, MyThingField2, and MyThingField3 as separate records. But there is a reason people keep asking questions like this, and it is that Access does a much better job of making it easy to make data entry forms with the structure above than with the "correct" structure. With the "correct" structure, you have to use a Left Join on some other table that will return you 3 records, and then you have to worry about a frustrated join. You also may have to add extra code to make sure that MyThingID gets into the MyNewThing table as a FK. And even then, your users are dealing with a vertical structure when the horizontal one might well be more user friendly. Or at least this is what I've always had to do. I'm wondering if there isn't something I've been missing, some wonderfully simple feature of Access, that makes using normalized data less painful from a form-building point of view. It's very frustrating telling people "you need to structure your data this way, but then once you do it you have to manhandle Access into allowing you to enter data." I'm thinking maybe it's something like PivotTable view, but so far I haven't seen any tutorials that show how to use it for this. TIA; Amy |
#3
|
|||
|
|||
Denormalizing for form only
"Jeff Boyce" wrote in message ... Amy Although the Excel-like Thing1, Thing2, Thing3 approach is familiar to Excel users, as you already know, it isn't necessary (or desirable) to use this in an Access database. What I've found quite useful for such one-to-many relationship is a main form/subform construction. So your position is to force the client to adapt to how Access does things, rather than finding a way to force Access to present information in the way your client finds easiest to work with. That's interesting, but it doesn't really answer my question. My preference is to try were possible to do things in a way that my client prefers. |
#4
|
|||
|
|||
Denormalizing for form only
"Amy Blankenship" wrote in
: "Jeff Boyce" wrote in message ... Amy Although the Excel-like Thing1, Thing2, Thing3 approach is familiar to Excel users, as you already know, it isn't necessary (or desirable) to use this in an Access database. What I've found quite useful for such one-to-many relationship is a main form/subform construction. So your position is to force the client to adapt to how Access does things, rather than finding a way to force Access to present information in the way your client finds easiest to work with. That's interesting, but it doesn't really answer my question. My preference is to try were possible to do things in a way that my client prefers. Amy, you seem a little frustrated, but you are in error when you say But there is a reason people keep asking questions like this, and it is that Access does a much better job of making it easy to make data entry forms with the structure above than with the "correct" structure. That's not the real reason, the truth is that until people are familiar with the relational model, they tend to think "spreadsheet on steroids", and build the database based on that presumption. I'm wondering if there isn't something I've been missing, some wonderfully simple feature of Access, that makes using normalized data less painful from a form-building point of view. Yes, what you've been missing is using a proper subform with properly defined relations between the tables and queries. Building proper data input and data editing form/subform sets becomes child's play once you learn how. The relational structure becomes transparent to the client. As to your preference in doing what your client prefers, I say it is just lack of confidence in asserting that he will prefer it once he's used to it. -- Bob Quintal PA is y I've altered my email address. ** Posted from http://www.teranews.com ** |
#5
|
|||
|
|||
Denormalizing for form only
"Bob Quintal" wrote in message ... "Amy Blankenship" wrote in : "Jeff Boyce" wrote in message ... Amy Although the Excel-like Thing1, Thing2, Thing3 approach is familiar to Excel users, as you already know, it isn't necessary (or desirable) to use this in an Access database. What I've found quite useful for such one-to-many relationship is a main form/subform construction. So your position is to force the client to adapt to how Access does things, rather than finding a way to force Access to present information in the way your client finds easiest to work with. That's interesting, but it doesn't really answer my question. My preference is to try were possible to do things in a way that my client prefers. Amy, you seem a little frustrated, but you are in error when you say But there is a reason people keep asking questions like this, and it is that Access does a much better job of making it easy to make data entry forms with the structure above than with the "correct" structure. That's not the real reason, the truth is that until people are familiar with the relational model, they tend to think "spreadsheet on steroids", and build the database based on that presumption. The truth is that spreadsheets are easier _for input_, and that is why new designers tend to try to use it. Proper table design makes it way easier to get data _out_ and is trivial to populate outside of Access, such as in a web form, but Access really fights you on it. I'm wondering if there isn't something I've been missing, some wonderfully simple feature of Access, that makes using normalized data less painful from a form-building point of view. Yes, what you've been missing is using a proper subform with properly defined relations between the tables and queries. Building proper data input and data editing form/subform sets becomes child's play once you learn how. The relational structure becomes transparent to the client. I love the way that you presume that I don't know what I'm doing, and that I'm asking from a point of complete ignorance. I am asking as someone who has been working in Access for many years, and I was wondering if maybe there wasn't some feature I'd missed that makes data entry into a normalized table structure easy, since clients often don't want to pay for the hoops you have to jump through to do it in Access--they simply don't realize how difficult it is. As to your preference in doing what your client prefers, I say it is just lack of confidence in asserting that he will prefer it once he's used to it. OK, so tell me exactly how you'd make this structure easy to do data entry on. This is a real world situation that I find myself up against, trying to solve it in a way that isn't going to use hundreds of dollars worth of my time. The situation is that of a SAT score conversion table. If the subject matter is "writing" all scores must be indexed against the essay score. If the subject matter is "reading" or "math", the score is not indexed. So for any one given score, there can either be one data point, or seven. It's much easier to keep mental track of the scores that index to a particular "raw" score if you can enter them all in a row, across. At a minimum, it means you don't have to enter the same raw score seven times. Each practice SAT exam can have its own score conversion table for math, reading, and writing. The table structure is this: ScoreSet =========== ScoreSetId-Autonumber, PK SubjectName-reading, writing, math ScoreSetDesc-will allow users to select this set later and associate it with an exam ScoreSetItems ============ ScoreID-Autonumber PK ScoreSetID-FK to scoreset WritingScore-score to index this on (will be 0 for subjects that don't apply, 0-6 for writing) RawScore-the actual score on the multiple choice questions SATScore-the scaled SAT score in the given subject Your task, should you choose to accept it, is to create a form structure that uses no code and no left joins and is not a royal pain in the butt to enter all 7 data points for scores from -12 to 49 (434 data points PER exam just for writing, plus the 65-90 data points for the ones that are not as complicated) and that will be easy to use and intuitive. Maybe that will explain to me what I've missed, and how this is so easy ;-). -Amy |
#6
|
|||
|
|||
Denormalizing for form only
Amy,
I certainly understood what you meant the first time. The two solutions you were offered were vertical entry-based, which is counter to what you stated that you wanted. I too have been frustrated at times by this limitation, and in at least one case, I've intentionally denormalized some to accomodate this limitation. In this case, the customer wanted a spreadsheet-like view of the data (simple enough with a crosstab result), however, the data also had to be editable. I can tell you that lots of people have requested this in the past to Microsoft. I just did a few weeks ago, and my contact at Microsoft replied that they have heard this request many times. I'm not positive, but I think one can accomodate this type of data entry with an ActiveX grid control, but that introduces issues related to using ActiveX controls (distribution, licensing, proper registration, etc.). Tom Wickerath Microsoft Access MVP http://www.accessmvp.com/TWickerath/ http://www.access.qbuilt.com/html/ex...tributors.html |
#7
|
|||
|
|||
Denormalizing for form only
"Amy Blankenship" wrote in message ... Hi, all; Many times I've answered questions like this in the queries and tabledesign forum: I have a table like this: MyThing ========== MyThingID MyThingDesc MyThingField1 MyThingField2 MyThingField3 Now, how do I query in such a way that Field1, Field2, Field3 are all treated in essentially the same way? And of course what I tell them is that they need a separate table, with MyThingField1, MyThingField2, and MyThingField3 as separate records. But there is a reason people keep asking questions like this, and it is that Access does a much better job of making it easy to make data entry forms with the structure above than with the "correct" structure. With the "correct" structure, you have to use a Left Join on some other table that will return you 3 records, and then you have to worry about a frustrated join. You also may have to add extra code to make sure that MyThingID gets into the MyNewThing table as a FK. And even then, your users are dealing with a vertical structure when the horizontal one might well be more user friendly. Why limit yourself to Access input only? If users are more comfortable with Excel-type input, let them use that. Then analyze the data in Access. The whole idea behind Microsoft Office is to be able to use a suite of programs that interoperate. You can have the front end be an Excel worksheet while the back end is Access. I've also seen Word used to create forms that created a data file for Excel or Access. Just as a professional carpenter has many tools and many types of the same tool to get a job done, so should we use all the tools we have! Tom Lake |
#8
|
|||
|
|||
Denormalizing for form only
"Amy Blankenship" wrote in
: "Bob Quintal" wrote in message ... "Amy Blankenship" wrote in : The situation is that of a SAT score conversion table. If the subject matter is "writing" all scores must be indexed against the essay score. If the subject matter is "reading" or "math", the score is not indexed. So for any one given score, there can either be one data point, or seven. It's much easier to keep mental track of the scores that index to a particular "raw" score if you can enter them all in a row, across. At a minimum, it means you don't have to enter the same raw score seven times. Each practice SAT exam can have its own score conversion table for math, reading, and writing. Before I can proceed with the task, please explain what you mean by indexed? do you mean scaled against the minimum and maximum? Please explain the process of creating the conversion table? (formulas, algotithm) The table structure is this: ScoreSet =========== ScoreSetId-Autonumber, PK SubjectName-reading, writing, math ScoreSetDesc-will allow users to select this set later and associate it with an exam ScoreSetItems ============ ScoreID-Autonumber PK ScoreSetID-FK to scoreset WritingScore-score to index this on (will be 0 for subjects that don't apply, 0-6 for writing) RawScore-the actual score on the multiple choice questions SATScore-the scaled SAT score in the given subject is RAWScore applicable to the writing test? or only WritingScore? How is SATScore determined? sonds like it should be a calculated field, or looked up from another table or query. Your task, should you choose to accept it, is to create a form structure that uses no code and no left joins and is not a royal pain in the butt to enter all 7 data points for scores from -12 to 49 (434 data points PER exam just for writing, plus the 65-90 data points for the ones that are not as complicated) and that will be easy to use and intuitive. Maybe that will explain to me what I've missed, and how this is so easy ;-). -Amy -- Bob Quintal PA is y I've altered my email address. ** Posted from http://www.teranews.com ** |
#9
|
|||
|
|||
Denormalizing for form only
"Bob Quintal" wrote in message ... "Amy Blankenship" wrote in : "Bob Quintal" wrote in message ... "Amy Blankenship" wrote in : The situation is that of a SAT score conversion table. If the subject matter is "writing" all scores must be indexed against the essay score. If the subject matter is "reading" or "math", the score is not indexed. So for any one given score, there can either be one data point, or seven. It's much easier to keep mental track of the scores that index to a particular "raw" score if you can enter them all in a row, across. At a minimum, it means you don't have to enter the same raw score seven times. Each practice SAT exam can have its own score conversion table for math, reading, and writing. Before I can proceed with the task, please explain what you mean by indexed? do you mean scaled against the minimum and maximum? I mean it is indexed. If the raw score is 1 and the writing score is 0, then the SAT score will be something like 220 (I don't have an exact table, this is just an estimate). If the raw score is 1 and the writing score is 1, then the SAT score will be more like 240. Please explain the process of creating the conversion table? The process is of tedious data entry. Look at the source graphic, enter the number where it goes. |
#10
|
|||
|
|||
Denormalizing for form only
"Thomas Lake" wrote in message ... "Amy Blankenship" wrote in message ... Hi, all; Many times I've answered questions like this in the queries and tabledesign forum: I have a table like this: MyThing ========== MyThingID MyThingDesc MyThingField1 MyThingField2 MyThingField3 Now, how do I query in such a way that Field1, Field2, Field3 are all treated in essentially the same way? And of course what I tell them is that they need a separate table, with MyThingField1, MyThingField2, and MyThingField3 as separate records. But there is a reason people keep asking questions like this, and it is that Access does a much better job of making it easy to make data entry forms with the structure above than with the "correct" structure. With the "correct" structure, you have to use a Left Join on some other table that will return you 3 records, and then you have to worry about a frustrated join. You also may have to add extra code to make sure that MyThingID gets into the MyNewThing table as a FK. And even then, your users are dealing with a vertical structure when the horizontal one might well be more user friendly. Why limit yourself to Access input only? If users are more comfortable with Excel-type input, let them use that. Then analyze the data in Access. The data needs to be in the database. Why is this concept so foreign? The whole idea behind Microsoft Office is to be able to use a suite of programs that interoperate. You can have the front end be an Excel worksheet while the back end is Access. I've also seen Word used to create forms that created a data file for Excel or Access. Just as a professional carpenter has many tools and many types of the same tool to get a job done, so should we use all the tools we have! Well, certainly I can write an import routine that can take a spreadsheet and split it out into the requisite records, but this thread is about trying to get Access to allow input of data in the format that you're actually supposed to use in Access. If you have to go outside Access to work with data in a format that makes sense to users, then write a routine to fix it, Access isn't really fit for the purpose it's advertised for. And that's more money my client has to spend without a real understanding of why, or I just have to eat. I'm really tired of solving this problem over and over! |
Thread Tools | |
Display Modes | |
|
|