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
|
|||
|
|||
Understanding what makes Access tick
I've been trying to create useable Access applications for about the last
four years and am getting really fed up always running into messy deadends about a day after trying to create a new .mdb file! All the text books follow the same, "If you do this, this, this and that, then .... ta da! Look what happens!", without any explanation of the fundamentals from whihc you can learn to create your own applications from scratch. Has anyone any tips of websites or books that for instance: Show the logic behind how Access processes Queries, and how they sometimes automatically 'pull-in' the related field on a table not included in the Query, but which is the subject of a table's lookup relationship. How Access uses Queries for instance to identify information that is in one table but not in another (a kind of negative-addition, verging on black magic) is a great mystery to me. Why it is so easy to make a query that filters / collates data from many tables, but going the other way, creating forms based on more than one table, that update all those necessary related tables behind the scenes, normally just results in the frustrating "Cannot update recordset" error message. Why do some but not all wizard-created table lookups not create a relationship in the Show All relationships window, and what else is Access hiding from us? Why does the Group By option in Queries so annoyingly not have a simple "Value" option in addition to Sum, Avg, Max and so on to make it useful?! For instance, I've a table that shows what you might call "Current Tasks" and a related table that shows the history of progress for those tasks. The history progress table has fields of HistoryID, TaskID, Current Status, DateRecordEntered. Common sense says that if I want a form or query that extracts the latest Status of multiple tasks I need a Select Query that Groups the TaskID, selects the maximum of the DateRecordEntered (so as to get the latest entry for that TaskID, and I want the VALUE of the CurrentStatus text field, but no! Access doesnt give that option!! Argh!!!! |
#2
|
|||
|
|||
On Mon, 25 Oct 2004 23:24:15 +0100, "ITMA"
wrote: Answers inline. I've been trying to create useable Access applications for about the last four years and am getting really fed up always running into messy deadends about a day after trying to create a new .mdb file! All the text books follow the same, "If you do this, this, this and that, then .... ta da! Look what happens!", without any explanation of the fundamentals from whihc you can learn to create your own applications from scratch. Has anyone any tips of websites or books that for instance: "Access 2003 Inside Out" by my friend and colleague John Viescas is an excellent reference of this type. Show the logic behind how Access processes Queries, and how they sometimes automatically 'pull-in' the related field on a table not included in the Query, but which is the subject of a table's lookup relationship. Lookup Fields are decried by most serious developers (see http://www.mvps.org/access/lookupfields.htm for a critique). They are both too intrusive and too ineffective; and they're not necessary. If you instead use the Relationships Window to define your joins, Access will create the needed foreign-key indexes; when you create a Query using the two tables, Access will automatically join them appropriately, and you can change the join fields or join type if need be. How Access uses Queries for instance to identify information that is in one table but not in another (a kind of negative-addition, verging on black magic) is a great mystery to me. This is called a "Frustrated Outer Join" query, and it's kind of neat. You don't need to use the wizard to create one; let's walk through an example. If you want to find all records in tblA which do NOT have any matching records in tblB you can take the following steps: - Create a Query joining tblA to tblB. This will use the default "Inner Join", showing only those records which DO have a match. - Select the Join line in query design and choose option 2 (or 3) - "Show all records in tblA and matching records in tblB". Those records which don't have a match will have NULL values in all tblB fields. - Select the joining field (the Foreign Key) from tblB, and put a criterion on it of IS NULL This will limit the retrieval to just those records which have a NULL value in the (required) joining field - that is, those records in tblA for which there is no corresponding record in tblB. Why it is so easy to make a query that filters / collates data from many tables, but going the other way, creating forms based on more than one table, that update all those necessary related tables behind the scenes, normally just results in the frustrating "Cannot update recordset" error message. Attempting to create a Grand Master Query that does everything is neither necessary nor a good way to go. Instead, use the power of Forms; if you have a one to many relationship, consider using a Form for the "one" side table, and a Subform for the "many". Access will use the joining fields defined in the relationships window as the Master/Child Link Fields and will keep the tables in synch, and you won't have updatability problems or repeating field values as you will in a Query. Why do some but not all wizard-created table lookups not create a relationship in the Show All relationships window, and what else is Access hiding from us? It's hiding the extra, redundant indexes which will slow update performance and contribute to database bloat; the relationships probably exist but just aren't shown, sometimes even if you select the Show All option in the relationship window. Why does the Group By option in Queries so annoyingly not have a simple "Value" option in addition to Sum, Avg, Max and so on to make it useful?! For instance, I've a table that shows what you might call "Current Tasks" and a related table that shows the history of progress for those tasks. The history progress table has fields of HistoryID, TaskID, Current Status, DateRecordEntered. Common sense says that if I want a form or query that extracts the latest Status of multiple tasks I need a Select Query that Groups the TaskID, selects the maximum of the DateRecordEntered (so as to get the latest entry for that TaskID, and I want the VALUE of the CurrentStatus text field, but no! Access doesnt give that option!! Argh!!!! Ok... hypothetical question. Suppose you add the DateRecordEntered field twice; on one of them you select Max to get the most recent date, and on the other you select Min to get the earliest date. What is the appropriate Value for CurrentStatus? How can Access tell which record you want? The correct way to do this is to use a "Subquery" - put a criterion =(SELECT Max([DateRecordEntered]) FROM [Current Tasks] AS X WHERE X.TaskID = [Current Tasks].TaskID) on the DateRecordEntered field. John W. Vinson[MVP] Join the online Access Chats Tuesday 11am EDT - Thursday 3:30pm EDT http://community.compuserve.com/msdevapps |
#3
|
|||
|
|||
I will say right away that your questions are actually quite good!
And, I will even more so say that there are some good answers. to each of these questions. John has given you some real good stuff.. Show the logic behind how Access processes Queries, and how they sometimes automatically 'pull-in' the related field on a table not included in the Query, but which is the subject of a table's lookup relationship. As mentioned, the built in table lookup feature is a source of much confusing. When you just start out using ms-access, that lookup feature at the table level seems quite cool. After a while..it just gets in your way. Hence, lets assume you need to grab data from a whole bunch of tables, then it is best to use the query builder. For example, you might have a form that has many combo boxes that display some "text" value, but in fact store the id of the other table....this approach in ms-access is a fine and common good practice (but, this is different then the confusing lookup feature at the table level...simply don't use them!!). Now, when it comes time to make a report, how do you grab, and get all those "text" values into a nice query for all those combo boxes that make the report easy to write? The solution is very simple. All you do is drop in the "main" table into the query builder. Now, if there is 4, or 5 lookup values, then just drop in those 4 or 5 other tables. You now have to draw a join line FROM the main table to the child tables. MOST IMPORTANTLY, you then must double click on the join line, and select a left join. If you don't do this..then you will only be allowed to drop in ONE table. By LEFT join we simply mean that we allow any "main" record..and "maybe" a child record. Once all the join lines a drawn, then you simply drop in any old field you need into the query grid. And, you also can now drop in those "text" fields you need from the other tables. And, if you wish, you can also include the "id" values that the combo boxes stored..but we don't care much at this point..do we? What is real nice is that now our report can display, and even group on these "text" values. This brings complete control back to YOU..and no surprises will occur when using grouping in a report. You group by on "text" values..and they also sort correctly here. How Access uses Queries for instance to identify information that is in one table but not in another (a kind of negative-addition, verging on black magic) is a great mystery to me. John gave you a nice answer... Why it is so easy to make a query that filters / collates data from many tables, but going the other way, creating forms based on more than one table, that update all those necessary related tables behind the scenes, normally just results in the frustrating "Cannot update recordset" error message. Actually, as mentioned, for "one" to many relationships, you use sub-forms anyway. However, you CAN edit relational data in a single form. You have to use LEFT joins (as mentioned above), and you usually have to expose the primary key in the main table, and also should include both the foreign key field (the field used to link to the main table), and also include the autonumber key from this child table also. If you do this..then the table in most cases will be updatable. Why do some but not all wizard-created table lookups not create a relationship in the Show All relationships window, and what else is Access hiding from us? Well, from day one, I avoided those lookup tables..as they are painful. Just use the query builder as I noted above..and life will be nice! extracts the latest Status of multiple tasks I need a Select Query that Groups the TaskID, selects the maximum of the DateRecordEntered (so as to get the latest entry for that TaskID, and I want the VALUE of the CurrentStatus text field, but no! Access doesnt give that option!! Actually, you can use sql to do this. I assume the first table is a list of tasknumbers...and then you have your above related table of tasks assigned.. So, I am assuming that you have a simple query that lists all task numbers. Perhaps this is a parent table. However, if it is in fact ONE table, then simply build a query that groups by taskNumber, and is distinct. What we want here is a simply list of tasknumbers. select TaskNumbers from OurListOfTaskNums Ok, so, we got a list of tasknubers here...(no duplicates..right...??). Now, lets assume you have your table of all that task information you just talked about. We go: select Tasknumbers, (select top 1 CurrentStatus from tblTasks where TaskNumber = OurListOfTaskNums.TaskNumber order by desc DateReocrdEntered) as lastStatus. From ourListOfTaskNumber .. You mentioned that it is easy to get the last date, but you actually what another column from that table based on the last date!..(right??). The above shows a nice way of doing this. Also, if there is the possibility of two dates on the same day, then simply add to the order by clause the ID of the child table, thus we get: select Tasknumbers, (select top 1 CurrentStatus from tblTasks where TaskNumber = OurListOfTaskNums.TaskNumber order by desc DateReocrdEntered,ID) as lastStatus. From ourListOfTaskNumber So, with the above, you can easily pluck out any column based on the last date..and that column does not have to be the lastdate column! This is not really a ms-access issue as it is learning sql. I hope the above helps you. I can only say that when you got a problem, ask here..as many will help you! I can really say that I always believe there is a reasonable solution to the common problems that people like you and I encounter on a regular basis. After all..you are not the only one encountering these types of obstacles! I can also say with ease that your questions were actually very good ones. -- Albert D. Kallal (Access MVP) Edmonton, Alberta Canada http://www.attcanada.net/~kallal.msn |
#4
|
|||
|
|||
On Mon, 25 Oct 2004 23:24:15 +0100, "ITMA"
wrote: I've been trying to create useable Access applications for about the last four years and am getting really fed up always running into messy deadends about a day after trying to create a new .mdb file! All the text books follow the same, "If you do this, this, this and that, then .... ta da! Look what happens!", without any explanation of the fundamentals from whihc you can learn to create your own applications from scratch. Has anyone any tips of websites or books that for instance: Many of the principles are suddenly learned when you develop a database that you know the *answers* for. something like MyCdCollection.mdb. It's personal, the outputs are known and you can mess with it and not break it. some of these links may help, but please come back and ask at each small step ... there's a lot of prinicples that shorten the learnign curve from that 4 yr frustration. Been there ... don't want anyone else to go there too. (these are the buzzwords that wrap around the Essential Knowledge) - Relational data design http://support.microsoft.com/default...;en-us;q175939 http://support.microsoft.com/default...;EN-US;q209534 http://support.microsoft.com/default...;en-us;Q234208 http://support.microsoft.com/default...EN-US;q288947& http://support.microsoft.com/default...b;en-us;288947 - Normalization http://databases.about.com/library/weekly/aa080501a.htm http://www.datamodel.org/NormalizationRules.html http://userpages.aug.com/frodo/filenorm.html http://support.microsoft.com/default...;EN-US;q209534 - Naming Conventions http://www.acc-technology.com/namconv.htm http://www.mvps.org/access/general/gen0012.htm http://msdn.microsoft.com/library/default.asp?url=/ARCHIVE/en-us/dnaraccgen/html/msdn_20naming.asp http://msdn.microsoft.com/archive/de...n_20naming.asp http://www.xoc.net/standards/rvbanc.asp - Queries (why and how they work) http://support.microsoft.com/default...b;en-us;304325 http://support.microsoft.com/default...b;en-us;304352 http://support.microsoft.com/default...b;en-us;182568 - Subforms (why and how they work) http://office.microsoft.com/en-au/as...883951033.aspx http://office.microsoft.com/en-au/as...878031033.aspx http://support.microsoft.com/default...b;en-us;209113 http://support.microsoft.com/default...b;en-us;209099 http://support.microsoft.com/default...b;en-us;113548 - Tab Controls http://support.microsoft.com/default...b;en-us;171222 http://support.microsoft.com/default...b;en-us;198502 http://support.microsoft.com/default...b;EN-US;208785 http://support.microsoft.com/default...b;en-us;208750 Good hunting ............. Brett Show the logic behind how Access processes Queries, and how they sometimes automatically 'pull-in' the related field on a table not included in the Query, but which is the subject of a table's lookup relationship. How Access uses Queries for instance to identify information that is in one table but not in another (a kind of negative-addition, verging on black magic) is a great mystery to me. Why it is so easy to make a query that filters / collates data from many tables, but going the other way, creating forms based on more than one table, that update all those necessary related tables behind the scenes, normally just results in the frustrating "Cannot update recordset" error message. Why do some but not all wizard-created table lookups not create a relationship in the Show All relationships window, and what else is Access hiding from us? Why does the Group By option in Queries so annoyingly not have a simple "Value" option in addition to Sum, Avg, Max and so on to make it useful?! For instance, I've a table that shows what you might call "Current Tasks" and a related table that shows the history of progress for those tasks. The history progress table has fields of HistoryID, TaskID, Current Status, DateRecordEntered. Common sense says that if I want a form or query that extracts the latest Status of multiple tasks I need a Select Query that Groups the TaskID, selects the maximum of the DateRecordEntered (so as to get the latest entry for that TaskID, and I want the VALUE of the CurrentStatus text field, but no! Access doesnt give that option!! Argh!!!! Cheers, Brett ---------------------------------------------- Be adventurous, be bold, be careful, be a star ---------------------------------------------- Brett Collings Business & Systems Analyst Management Information Systems Developer |
#5
|
|||
|
|||
Anyone know why when I read this in my Outlook Express I see only one reply
but in Google I can see three? |
#6
|
|||
|
|||
Anyone know why when I read this in my Outlook Express I see only one reply
but in Google I can see three? And why in Gravity newsreader I can only see one reply?! How can I see all postings as evidenced by whats on Google's Newsgroup section and have the facility to filter and reply to them? Clearly Gravity and Outlook Express are not fool-proof, and Gravity seems horrendously complicated in fact |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Images in a database | Franz | General Discussion | 10 | October 7th, 2004 09:35 AM |
mail merge tick box field from access | robert | Mailmerge | 3 | September 9th, 2004 03:45 PM |
access database wizard | Phil | General Discussion | 6 | August 24th, 2004 08:08 AM |
Useless Access 2003 | tired, angry, sucidial and bored | General Discussion | 10 | July 21st, 2004 11:52 PM |
Need help with Access decision | aualias | General Discussion | 23 | June 21st, 2004 02:04 AM |