Mulit Function Query
in [General]
|
From: John W. Vinson on 6 Apr 2010 18:44 On Tue, 6 Apr 2010 15:23:01 -0700, LewisDUA <LewisDUA(a)discussions.microsoft.com> wrote: > >Personnel Table, i use a three fields: Name, UIC(Which is a number for the >unit), PARA(Which is their job slot) >Then in PMP Table i have DD 93 (Yes/No) SGLV(Yes/No) PQR(Yes/No) >DA2-1(Yes/No) 20 YR (Yes/No/N-A)....etc >My Personnel Tables key SoldierID and my PMP Table's ID is SoldierID. Their >is more tables, but i'm not using them in this query. >Does that help? Yes... unfortunately it does! Because your PMP table's structure *IS WRONG* and it will not meet your needs. Again... a yes/no field cannot be "missing". It cannot be Null. It can only be YES or NO. It will default to NO. If these fields are textboxes containing a text string "yes", "no", or "N/A" or the like you can escape this particular problem. It will be absolutely impossible to tell if a given soldier in fact has a NO value for their PQR, or if the PQR checkbox just never got checked. An additional problem that you can't escape is that if you ever need to add or change an column in the PMP table you'll need to change the structure of your table... redesign all the queries which include that table... rebuild all the forms and reports that use that table... OUCH!! It would seem that you have a Many to Many relationship from a given soldier to a whole bunch of "things to check" about that solder - her DD93, her SGLV, his PQR, etc. etc. The correct way to model this is with a many to many relationship: a table with one row per "thing to check", related one to many to a third table with fields for the soldier's unique ID, the ID of the "thing to check", and possibly other fields (if you need to know something other than the bare existance of a SGLV value). -- John W. Vinson [MVP]
From: LewisDUA on 6 Apr 2010 19:28 Ok, the tables are all setup correctly. The only thing i'm having problems with is ONE query. What i mean by missing is NO. I don't care about NULL or yes or N/A. I want to run a query that will show all of their NOs. Is their a line of code for that? Or should i just go higher then this? "John W. Vinson" wrote: > On Tue, 6 Apr 2010 15:23:01 -0700, LewisDUA > <LewisDUA(a)discussions.microsoft.com> wrote: > > > > >Personnel Table, i use a three fields: Name, UIC(Which is a number for the > >unit), PARA(Which is their job slot) > >Then in PMP Table i have DD 93 (Yes/No) SGLV(Yes/No) PQR(Yes/No) > >DA2-1(Yes/No) 20 YR (Yes/No/N-A)....etc > >My Personnel Tables key SoldierID and my PMP Table's ID is SoldierID. Their > >is more tables, but i'm not using them in this query. > >Does that help? > > Yes... unfortunately it does! > > Because your PMP table's structure *IS WRONG* and it will not meet your needs. > > Again... a yes/no field cannot be "missing". It cannot be Null. It can only be > YES or NO. It will default to NO. > > If these fields are textboxes containing a text string "yes", "no", or "N/A" > or the like you can escape this particular problem. > > It will be absolutely impossible to tell if a given soldier in fact has a NO > value for their PQR, or if the PQR checkbox just never got checked. > > An additional problem that you can't escape is that if you ever need to add or > change an column in the PMP table you'll need to change the structure of your > table... redesign all the queries which include that table... rebuild all the > forms and reports that use that table... OUCH!! > > It would seem that you have a Many to Many relationship from a given soldier > to a whole bunch of "things to check" about that solder - her DD93, her SGLV, > his PQR, etc. etc. The correct way to model this is with a many to many > relationship: a table with one row per "thing to check", related one to many > to a third table with fields for the soldier's unique ID, the ID of the "thing > to check", and possibly other fields (if you need to know something other than > the bare existance of a SGLV value). > > -- > > John W. Vinson [MVP] > . >
From: John W. Vinson on 6 Apr 2010 20:18 On Tue, 6 Apr 2010 16:28:01 -0700, LewisDUA <LewisDUA(a)discussions.microsoft.com> wrote: >Ok, the tables are all setup correctly. The only thing i'm having problems >with is ONE query. What i mean by missing is NO. I don't care about NULL or >yes or N/A. > >I want to run a query that will show all of their NOs. Is their a line of >code for that? Or should i just go higher then this? I don't understand your question. "Higher"? A query with a criterion such as WHERE [DD 93] = "No" OR SGLV="No" OR [DA2-1] = "No" OR [20 YR] = "No" OR ...etc will find records where any one (or more than one) of the fields contain the text string "No". Use = False instead of = "No" if the field is in fact a Yes/No field. -- John W. Vinson [MVP]
From: PieterLinden via AccessMonster.com on 8 Apr 2010 01:36 LewisDUA wrote: >Ok, the tables are all setup correctly. Really? How can you be sure? Can you post the structure? If you have a field that is named after a form, then you *definitely* need to rethink your design. If you don't believe me, try doing totals on a database like this. "Royal* PITA. (I know, I did it for six months.) Never mind incredibly slow. The usual way to determine missing items is to create a deliberate cartesian product. If you're dealing with People having submitted Forms, then you would have a table for People, a table for Forms and then you'd create the cartesian product... SELECT Person.PersonID, PaperForms.FormID FROM Person, PaperForms; Then you would create an query that would outer join the cartesian product to your "form submission" table (sPersonID, sFormID, SubmitDate, etc) and look for nulls. The nice thing about it is that you can add as many records to both tables as you want and you never have to rewrite any queries. -- Message posted via http://www.accessmonster.com
From: PieterLinden via AccessMonster.com on 8 Apr 2010 01:48
LewisDUA wrote: >Ok, the tables are all setup correctly. The only thing i'm having problems >with is ONE query. What i mean by missing is NO. I don't care about NULL or >yes or N/A. > Okay, post your new table structure. Should be only a few fields... Should be the join table between people and "Forms to fill out". The primary keys from the Personnel and RequiredForms tables, and maybe a "dateSubmitted" field. If you do it that way, you can query for anything you want. As a sort of side note - if your queries have to change as your data changes, or if you are continually adding columns to your tables, then your design is wrong. Your table structures should change very little if at all over time. Also, if you have to write insane queries to get a simple answer, then that should be a dead giveaway. If database design is something you've never done before, you might want to browse through Hernandez's Database Design for Mere Mortals. -- Message posted via http://www.accessmonster.com |