multiple query criteria

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

0
Utf
5/1/2010 7:25:01 PM
access.queries 6343 articles. 1 followers. Follow

4 Replies
1051 Views

Similar Articles

[PageSpeed] 27

On Sat, 1 May 2010 12:25:01 -0700, olive <olive@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]
0
John
5/1/2010 9:54:16 PM
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

0
KenSheridan
5/2/2010 12:50:10 PM
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@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]
> .
> 
0
Utf
5/5/2010 5:05:02 PM
On Wed, 5 May 2010 10:05:02 -0700, olive <olive@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]
0
John
5/5/2010 8:30:48 PM
Reply:

Similar Artilces:

Creating a parameter in a Union Query
I have, with the help of this newsgroup, created a union query. However I now need to add a parameter to have it only show me the data for a specific period of time. I have pasted the union query sql below. SELECT "Type 1" AS [Catagory], COUNT(*) AS [Number of Patients] FROM [Diabetes] WHERE Diabetes_Type = "Type 1" GROUP BY "Type 1" UNION ALL SELECT "Type 2", COUNT(*) FROM [Diabetes] WHERE Diabetes_Type = "Type 2" GROUP BY "Type 2" UNION ALL SELECT "Gestational 1-18 years", COUNT(*) FROM [Diabetes] ...

Internet - Take Advantage of Multiple Windows When Surfing
-- Internet - Take Advantage of Multiple Windows When Surfing SUMMARY: Stop jumping back and forth between index pages by using several browser windows. Why use one web browser window when you can have multiple? If you have an adequate amount of memory, open up several web browsers to maximize your surfing experience. One great way to do this is via a start page. Select a page from where you would like to begin browsing, such as a table of contents or links page. Now, instead of left-clicking on links or entries to view other pages, right-click the links. From the pop-up menu that a...

Multiple search change calendar view
Hi Here's my problem : I go 2 calendar My view is day/week/month with preview When I do more than one search with the little search (not advanced) box, go to another folder then go back to calendar, view is changed to "active appointements". How can i prevent this ? Thanks Fred ...

Multiple aliases in Exchange 2003
I work for a company that provides both internet and television service. We run aan old version of qmail for our email server.(This is before I started working here) Recently the two areas split and formed two seperate companies. I am with the TV side and am in the process of moving us to Exchange 2k3. There are two employees of the internet company that helped found the overall company, that do not want to loose their email addresses and aliases. I have created domain accounts for them, but do not give them their passwords. I then created a contact to forward the incoming email to....

Excel VBA
Hi VBAers, I put some code in a workbook that I now want to re-use. My problem is I cannot remember which workbook it's in & thus, it could be in one of 30 workbooks. I was wondering if anyone can suggest a better way to find the code rather than searching for it 30 times please? Also, I think best practice is that I put my favourite code into my personal.xls file - is that correct? kazzy was thinking very hard : > Hi VBAers, > > I put some code in a workbook that I now want to re-use. My problem is > I cannot remember which workbook it's in & thus, it could be ...

Timesheet with multiple in/out daily
I have downloaded Pearsall's timesheet, which is wonderful. However, I need to have 3 sets of In/Out columns. I added the two additional columns, but now can't figure out how to correct the calculations. Also, is it possible to input the data without needing to use a colon? I don't care if I'd have to use military times. I have a ton of times to enter, and using only the numeric keypad would be most efficient. Thanks! Do you mean Chip Pearson's OT timesheet at http://www.cpearson.com/excel/overtime.htm ? If so you need to explain what you added You can us...

Query Related to Dialog Box having CListCtrl on it.
Hello All, I have a dialog based application having a CListCtrl on it. I have also implemented OnCustonDraw to color few items in that list control. I also have a button on the dialog box which when clicked opens a new dialog (for adding data to CListCtrl on main dialog box). When I move this 2nd dialog box the (the newly opened dialog for adding new item to a list) the background (or parent dialog to be precise) dialog gets messed up....! :( i mean it doesn't repaints the invalidated region.... :(( What am I suppose to do ???? please reply asap as its very urgent.....! Thanks in adva...

Count with difficult/multiple citeria
Hi, I have a complicated question for help. I hope that I make it sound ok. First thing to remember is that this sheet is linked to another, which is in-turn link to another...ive been filtering data! so what i have is: >From B3:B200 a list of brands >From column F2 to to Z2 I have a list of shop names and then their respective sales in F3:Z200 What I want is for it to idenitfy where there are only sales in one store (so where there are it will say one)...that is easy I can do that! but the next bit is where I am stuck. The brands can be the same, so there could be more tha...

Generate multiple ACH Files (NACHA files) based on routing numbers
We are using GP 9.0 with Direct Deposit Module. We would like to find if there a way in GP to Generate multiple ACH Files (NACHA files) based on routing numbers setup for employee's in Great Plains. We are currently setup to generate 1 ACH file per company. Customer support response was that there is no solution in any version of GP. If someone can of any assistance with this request, it would be great appreciated. Thanks. Azfar Azfar, You could write a VBScript or VB program that will take the output file and split it in multiple files based on routing number. This should be...

Item being recognized in multiple accounts
hello, I was wondering if someone can point me in the right direction with this requirement. We are currently looking to implement a new Chart of Accounts in GP and one of the requirements is that an item can be recognized against multiple accounts depending on some parameters (departments, function) of how it's sold. In our current configuration all of our items just hit one account. To support this I see two approaches ... either we dynamically construct the account the item has to hit on input and override the accounts in the transaction or we have multiple versions of the sam...

Charting Multiple Series Data with Dates?
Hello I am plotting 5 data series on the same line chart. Dates are plotted on the x axis and concentrations of chloride on the y axis. The dates are formatted like "5/12/2003" as "general" data in the "alignment" menu of "format". The trick is, each data set does not have the exact same date range. For example, one sampling site may have 10 data points that range from 4/2/1991 to 5/12/2003 where as another sampling site may have 12 data points ranging from 4/30/1991 to 5/1/2003. That is to say that the sampling dates for each sampling site are not ...

How to Pass Filter from Query to Report?
Hello, I would appreciate any help with this problem that's been driving me nuts. I have a query of inventory items with only a subset of fields from the complete inventory datasheet. I have designed a report that will take the items from the query and print tags. Sometimes, I would like to print only a few tags. I apply a filter to one of the columns in the query datasheet, but this filter has no effect on the report. What can I do so that when I apply a filter to the query, it will also be reflected in the report? I was thinking of putting a formula of some kind in the filter pro...

Query for Monthly Report
I'm using SSRS to write reports, and am very new to SQL queries. I have a report that shows totals for each day in the month to-date, for two data elements. It needs to run daily via an automatic snapshot, and be distributed via e-mail subscription. As such, the time frame cannot be a user input item. The data is always a day behind, so on the first day of the month, it needs to report the previous month, not the current month. The field I need to key on is called DateTime. My current query for the month of April 2010 is: SELECT DateTime, Sum(PatientDays) AS '...

Pivot table from Multiple Consolidation Ranges
Dear All Excel Experts, Creating a Pivot Table from 1 data table in 1 worksheet gives me the Pivot Table the way I want it, meaning as many Page Fields as I defined them at the top left of the Pivot Table. These Page Fields represent the column lables and in the drop-down I can choose the data under that lable of the respective column. Now I wish to have precise the same Pivot Table, but this time from data tables spread over several worksheets, whereby the format of these data tables are exactly the same. But now the drop-down of the Page Fields at the top left of the Pivot Table g...

Multiple calendars, how to choose which one meetings go into
Hi I have 3 calendars setup in outlook XP , when someone sends me a meeting request how can i change which calendar the meeting goes into, at the moment they all go into what i guess is the default calendar which i want to change to a different one any ideas? thanks Chris You really can't change it. Outlook treats certain folders special. "Chris S" <fred@fred.com> wrote in message news:43cf7167$0$23294$db0fefd9@news.zen.co.uk... > Hi > > I have 3 calendars setup in outlook XP , when someone sends me a meeting > request how can i change which calend...

Receiving Multiple Copies Of Certain Emails
I just switched from Pocomail to Outlook 2007, and find that certain emails (and only certain emails) are downloaded more than once. Specifically, this relates to auto-generated messages from my two blog accounts. I have a Blogger account and a LiveJournal account, both set up to notify me when comments are posted. I'm find I'm receiving four copies of the Blogger notification, and two copies of the LJ notification. This does not happen with emails from any other sender. Any ideas what's going on? Thanks. Hi Merk, have a look on this site : "Why does my Outlook keep r...

Generating Multiple MS Reports
I have two tables in MS Access and would like to generate a master report separated on individual pages for eahc user. Table 1: PeopleID Fields: NameID, Last Name, First Name Table 2: Contributions Fields: NameID, Date, Amount I would like to be able to generate a report that would print a page for each NameID so they can use for tax purposes that shows their contributions to a fund. Any help on writing this query and report would be helpful. Thanks, michael_quackenbush@yahoo.com wrote: >I have two tables in MS Access and would like to generate a master >report separated ...

Update query question 04-26-10
Hello, I would like to update a record by looking at the value from a form combo box column(1). I have tried Forms![Lab Works Manager]![cmbTo].column(1) but it does not work. The bond column(0) has the ID, but in this case I need to use the actual value from column(1) and not column(0). Any idea how? Thank you, Silvio Add a hidden control to your form and set its value to =Forms![Lab Works Manager]![cmbTo].column(1) Then you can reference that hidden control in the query. Your other option is to write a VBA function and pass in the name of the form, the name of the co...

Select Statement in query to append to a table
Hello, I have a table with the following Master Stock Code Header ID and I want to append this information to a table, kind of ike a cross tab query but I want the value of the header ID to be in the column not as a column name. I have a max of 12 header id's, so I have 12 column names in the table i want to append to (ie. Hdr1, Hdr2 etc) There could be more than one Header ID per stock code. WHat is the proper syntax for the select statement in my query so I don't get duplicate rows per stock code. ...

Linked Table
Hi, I have a Access 2003 mdb that I have used for years that has worked OK. Recently one of my queries malfunctioned to an external Progress db's linked table. Specifically, the query's table list is missing many of the fields in the table though when I inspect the linked table in the Tables Objects Section I see all the fields. How can I fix my query? TIA, Dan Why post the same question in different newsgroups? (already answered in the other newsgroups you posted in). If your situation is one of the (few) unusual circumstances in which it is necessary/appropriat...

Avoiding duplicate data based on criteria for another field in a q
I look after the admin for our fishing club. I've created a database (access 2003) with around 1200 records. we have members and non members who fish with seperate tables for each. Ive been working on a method to show the takings from between two dates for the non members. I've achieved it with one date (eg todays, or yesterdays) but if I put dates in covering several days or weeks It serves up junk! The problem is to create a query which will show each date on which someone fished (between the criteria dates entered using a form into the "date of fishing" field in the...

merging multiple documents without losing formatting
This is my first post, so thanks in advance for your help... Firstly, I'm using word 2007. I have in excess of 40 documents to merge into one, and create a table of contents. each of the original documents has different formatting, orientations and margins. to make things more difficult some use different font styles. the numbering on the font styles has been removed on the original files but when I "insert" "text from file" I lose the original formatting and the numbering on the font styles re-appears on the new document. the inserted text (and ta...

LDAP Query needed
I need an ldap query to find all users with an exchange mailbox where the users alias does not equal the nickname of the smtp email address(s). Thanks! "MrRAlan" <MrRAlan@discussions.microsoft.com> wrote: >I need an ldap query to find all users with an exchange mailbox where the >users alias does not equal the nickname of the smtp email address(s). Thanks! I don't believe LDAP supports what you want to do. You'll have to write a small program (vbs or perl) that creates a mail address and then queries the AD for a match. -- Rich Matheisen MCSE+I, Exchange ...

SUM and Frequency for Multiple SHeets
Hi....in sheet 1, I have a column that has unique numbers and using the following formula to lookup the field in sheet 46500! Column H and then count the unique frequencies in sheet 46500! column A. =SUM(1*(FREQUENCY(IF(('46500'!$H$2:$H$43207<>"")*('46500'!$H$2:$H$43207=$A4),'46500'!$A$2:$A$43207),'46500'!$A$2:$A$43207)>0)) How do I change that formula so that it also reads additional sheets (46501, 47400, 43100, 43103, 47300) and only counts the unique combination like the formula above does for one sheet? Please Help Yo...

Toggling between multiple notebook windows
I have been a user of QuattroPro for ten years, and I am just now trying to convert everything over to Excel. In QPW, when I have multiple notebooks (files) open at the same time, each notebook's name is shown as a button at the bottom of the screen in the status bar. (The files are also listed under the Window drop down menu at the top.) This makes it really easy to toggle from one file to the next by clicking on the button with the mouse. Can this be done in Excel also?? The only thing I have found so far is the Window drop down menu, and the file names in the taskbar, but bot...