multiple query criteria
in [Queries]
|
From: olive on 1 May 2010 15:25 Let's start with the fact that I am an Access novice, so if this looks like I am way off base, and cannot get any guidance here, just let me know. What I have is a table listing vehicle inspection standards. A vehicle tag number, and then 50 fields such as, wipers inop, headlight inop, etc. My fields are numeric, because there is a point system used to determine a pass/fail. I input the tag number, and if the specific area is deficient, I enter either a 10 or a 25 for point values. What I end up with, is a table with tag numbers and then fields showing points deducted from the overall 100%. What I want to do is run a report that lists the vehicle tag number, and only the fields identified with a numeric penalty (10 or 25). There will never be all 50 fields on the report, at most, five or six of the areas will be marked as having a deficiency. If I put "IS NOT NULL" in the design grid for each of the fields, my report comes back empty. Is there a way to make this work? Any help would be much appreciated. Thank You
From: John W. Vinson on 1 May 2010 17:54 On Sat, 1 May 2010 12:25:01 -0700, olive <olive(a)discussions.microsoft.com> wrote: >Let's start with the fact that I am an Access novice, so if this looks like I >am way off base, and cannot get any guidance here, just let me know. Well, if you're way off base, you may get more guidance not less <g>... take it for what it's worth. > What I have is a table listing vehicle inspection standards. A vehicle >tag number, and then 50 fields such as, wipers inop, headlight inop, etc. >My fields are numeric, because there is a point system used to determine a >pass/fail. That is in fact a less than ideal table design. Each vehicle has many (50) InspectionPoints; each InspectionPoint applies to many vehicles. This kind of many to many relationship is better handled with three tables: Vehicles (probably using the tag number or VIN as the primary key); a fifty row (today, you might have more points next year if the legislature or agency adds some) table of Points; and a Rating table with fields for the TagNumber, PointID, and Points. > I input the tag number, and if the specific area is deficient, I enter >either a 10 or a 25 for point values. With the normalized design a subform with a combo box selecting the InspectionPoint and storing the points for that issue would be ideal. > What I end up with, is a table with tag numbers and then fields showing >points deducted from the overall 100%. > >What I want to do is run a report that lists the vehicle tag number, and >only the fields identified with a numeric penalty (10 or 25). There will >never be all 50 fields on the report, at most, five or six of the areas will >be marked as having a deficiency. If I put "IS NOT NULL" in the design grid >for each of the fields, my report comes back empty. Is there a way to make >this work? Not easily with your wide flat design. Trivially easy with the normalized table. Any chance you could correct the table design? If not post back, it's doable with a UNION query; just a lot more work. >Any help would be much appreciated. >Thank You -- John W. Vinson [MVP]
From: KenSheridan via AccessMonster.com on 2 May 2010 08:50 It is just about possible with your existing table. One way is a UNION query as John mentioned, but with 50 separate columns this would be cumbersome. There is another way, which is to manipulate the report's layout in code in its module at runtime so that Null columns are hidden and those with data are repositioned on a vehicle by vehicle basis. This is not trivial, however, and once again the total number of columns involved would make the code extensive. I would not recommend either approach. The only sensible solution is to normalize the database design by 'decomposing' your table into a set of related tables along the lines John has described. If each vehicle can have more than one inspection, however, e. g. annually, then you'd need to go introduce one more table, so what you'd have would be the following tables: Vehicles ….TagNumber (primary key) ….<other columns representing vehicle attributes> InspectionCategories ….CategoryID (autonumber primary key) ….Category This table would have one row per category, e.g. 'wipers inop' etc. Inspections ….InspectionID (autonumber primary key) ….InspectionDate ….TagNumber The TagNumber in this is table a foreign key referencing the primary key of Vehicles InspectionDetails ….InspectionID ….CategoryID ….Points The primary key of this table is a composite one made up of InspectionID and CategoryID. Individually each of these columns is a foreign key referencing the keys of Inspections and Categories respectively. Each of the above tables represents an 'entity type' with the columns in each table representing attributes which are specific to that entity type. This is how a relational database works. The InspectionDetails table represents a special kind of entity type because it also models a many-to-many- relationship between Inspections and Categories. By having separate columns for each inspection category in your table you are doing what is known as 'encoding data as column headings'. This breaks one of the fundamental rules of the relational model, the 'information rule' which requires all data to be stored as values at column positions in rows in tables and in no other way. As you've found out this leads to practical difficulties in using the database. With a correctly normalized design as above creating a report becomes very simple. All that's necessary is to join the tables in a query like so: Vehicles---<Inspections---<InspectionDetails>---Categories Include the columns from each table which you need in your report and base the report on the query. Group the report first by Vehicle, then by Inspection, with a group header for each and include the Category and Points in the detail section. For each vehicle inspection the report will only include those categories for which there are data. If you wish you can sum the points in a text box in a group footer for the Inspection group to give the total points per vehicle inspection. If you have a manageable amount of data at present you might be able to renter the data manually into the new model, being sure to fill the Vehicles and Categories tables first, followed by the Inspections table, and finally the InspectionDetails table. With a large amount of existing data you can largely automate the process by using append and update queries, but with your 50 separate columns, while not a difficult task, this would nevertheless be a tedious one. Which ever is the case I would recommend that you bite the bullet, though, as to persist with your current flawed design is only going to give rise to more problems in the future. We shall of course be happy to guide you through the process. Ken Sheridan Stafford, England olive wrote: >Let's start with the fact that I am an Access novice, so if this looks like I >am way off base, and cannot get any guidance here, just let me know. > > What I have is a table listing vehicle inspection standards. A vehicle >tag number, and then 50 fields such as, wipers inop, headlight inop, etc. >My fields are numeric, because there is a point system used to determine a >pass/fail. > I input the tag number, and if the specific area is deficient, I enter >either a 10 or a 25 for point values. > What I end up with, is a table with tag numbers and then fields showing >points deducted from the overall 100%. > >What I want to do is run a report that lists the vehicle tag number, and >only the fields identified with a numeric penalty (10 or 25). There will >never be all 50 fields on the report, at most, five or six of the areas will >be marked as having a deficiency. If I put "IS NOT NULL" in the design grid >for each of the fields, my report comes back empty. Is there a way to make >this work? >Any help would be much appreciated. >Thank You -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201005/1
From: olive on 5 May 2010 13:05 For some reason, my last two replies did not post. I hope they all don't show up at once, if so, please forgive the repetitiveness (is that a word?) Anyway, I took your advice and corrected my table structure. I now have three tables. Table 1, Vehicles - has vehicle tag number, vin, and specific user Table 2, PointID - has PointId (with my 50 inspection items), and the value of each item 10 or 25points. Table 3, Ratings - has three fields, TagNumber, PointID, and Points. Now, I have table 2 and 3 related by pointId, and Table 1 and 3 related by Tag Number. Just to establish my next step. I should create a form to input the tag#, inspection item, and points into my ratings table. From this table I should be generating my queries and reports? If this sounds good, let me know and I will press on with making my form and queries. Thanks so much for the help Olive "John W. Vinson" wrote: > On Sat, 1 May 2010 12:25:01 -0700, olive <olive(a)discussions.microsoft.com> > wrote: > > >Let's start with the fact that I am an Access novice, so if this looks like I > >am way off base, and cannot get any guidance here, just let me know. > > Well, if you're way off base, you may get more guidance not less <g>... take > it for what it's worth. > > > What I have is a table listing vehicle inspection standards. A vehicle > >tag number, and then 50 fields such as, wipers inop, headlight inop, etc. > >My fields are numeric, because there is a point system used to determine a > >pass/fail. > > That is in fact a less than ideal table design. Each vehicle has many (50) > InspectionPoints; each InspectionPoint applies to many vehicles. This kind of > many to many relationship is better handled with three tables: Vehicles > (probably using the tag number or VIN as the primary key); a fifty row (today, > you might have more points next year if the legislature or agency adds some) > table of Points; and a Rating table with fields for the TagNumber, PointID, > and Points. > > > I input the tag number, and if the specific area is deficient, I enter > >either a 10 or a 25 for point values. > > With the normalized design a subform with a combo box selecting the > InspectionPoint and storing the points for that issue would be ideal. > > > What I end up with, is a table with tag numbers and then fields showing > >points deducted from the overall 100%. > > > >What I want to do is run a report that lists the vehicle tag number, and > >only the fields identified with a numeric penalty (10 or 25). There will > >never be all 50 fields on the report, at most, five or six of the areas will > >be marked as having a deficiency. If I put "IS NOT NULL" in the design grid > >for each of the fields, my report comes back empty. Is there a way to make > >this work? > > Not easily with your wide flat design. Trivially easy with the normalized > table. > > Any chance you could correct the table design? If not post back, it's doable > with a UNION query; just a lot more work. > > > >Any help would be much appreciated. > >Thank You > -- > > John W. Vinson [MVP] > . >
From: John W. Vinson on 5 May 2010 16:30 On Wed, 5 May 2010 10:05:02 -0700, olive <olive(a)discussions.microsoft.com> wrote: >For some reason, my last two replies did not post. I hope they all don't >show up at once, if so, please forgive the repetitiveness (is that a word?) >Anyway, >I took your advice and corrected my table structure. > >I now have three tables. > >Table 1, Vehicles - has vehicle tag number, vin, and specific user > >Table 2, PointID - has PointId (with my 50 inspection items), and the value >of each item 10 or 25points. 50 rows, not 50 fields... right?? >Table 3, Ratings - has three fields, TagNumber, PointID, and Points. Yep! >Now, I have table 2 and 3 related by pointId, and Table 1 and 3 related by >Tag Number. > >Just to establish my next step. >I should create a form to input the tag#, inspection item, and points into >my ratings table. From this table I should be generating my queries and >reports? Yes. It's a *bit* redundant to have both the PointID and the Points in the Ratings table, since in principle you could just store the PointID and use a query to look up the points; but I'm guessing that the number of points might change over time, or even be a variable (a trivial, minor or serious violation of an inspection point might score 5, 10 and 25 points), if not now then in the future. If so you'll need a smidgen of VBA code or a one-line macro to record the points. I'd see a Form based on your Vehicles table, with a Subform based on Ratings. On the Subform you would have a Combo Box or Listbox based on the PointID table, with the PointID, a text description of the point (unless the person using the database has all 50 ID's memorized), and the Points value. The combo's Control Source would be the PointID, and you could put a macro or VBA code in the combo's AfterUpdate event to push the combo's Points value into the Points field on the subform: Private Sub cboPoints_AfterUpdate() If Not IsNull(Me!cboPoints) Then ' did the user select a row? Me!Points = Me!cboPoints.Column(2) ' the third field, it's zero based End If End Sub -- John W. Vinson [MVP]
|
Pages: 1 Prev: Access Query - returns 5 and 7 day information Next: Access query duplicates the table records |