nested OR in queries

I have a table that has 20 date fields for each record. I need to select any
record that has a matching date in at least one of those fields so it becomes
a huge OR in a query and I have noticed that I am limited to 9 in a query. Is
there a way to do this efficiently or will I need multiple queries to test
all 20 date fields?

0
vgraybeal
3/27/2007 5:27:59 PM
access.queries 6343 articles. 1 followers. Follow

3 Replies
617 Views

Similar Articles

[PageSpeed] 41

If your table uses multiple date fields, the first thing you'll want to 
consider is coming up with a new table!

In a spreadsheet, adding 20 date fields may be the only way to handle a 
situation, but you won't get the best use of Access' features and functions 
if you don't use well-normalized tables.

You didn't explain much about those 20 fields, but I'll hazard a guess that 
they represent different steps or stages or types or categories of dates. 
In a relational database (e.g., Access), you'd handle this with something 
like:

    tblSomethingDate
        SomethingDateID
        SomethingID (to what does the following date apply?)
        YourDate (don't use "Date" as a field name-this is a reserved word 
in Access)
        YourDateTypeID (this is where you put the category you were using 20 
fields to show)

If I've mis-interpreted, please post back with a more detailed description.

Regards

Jeff Boyce
Microsoft Office/Access MVP


"vgraybeal" <u32853@uwe> wrote in message news:6fd0f78a391d8@uwe...
>I have a table that has 20 date fields for each record. I need to select 
>any
> record that has a matching date in at least one of those fields so it 
> becomes
> a huge OR in a query and I have noticed that I am limited to 9 in a query. 
> Is
> there a way to do this efficiently or will I need multiple queries to test
> all 20 date fields?
> 


0
Jeff
3/27/2007 5:51:31 PM
Sounds as if you are using QBE (Design view grid).

Click on any criteria row and then select INSERT Rows from the menu.

This will add an additional criteria row.

The problem here is that you have 20 date fields in one row of data.  It is 
probable that you should be storing these dates in another table that 
contains the date you are storing, the type of date (DOB, Communion Date, 
Date of Graduation, etc) and the primary key value from you present table. 
With that structure all you would need is to join the two tables and place 
criteria against one field.

-- 
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"vgraybeal" <u32853@uwe> wrote in message news:6fd0f78a391d8@uwe...
>I have a table that has 20 date fields for each record. I need to select 
>any
> record that has a matching date in at least one of those fields so it 
> becomes
> a huge OR in a query and I have noticed that I am limited to 9 in a query. 
> Is
> there a way to do this efficiently or will I need multiple queries to test
> all 20 date fields?
> 


0
John
3/27/2007 6:04:46 PM
On Tue, 27 Mar 2007 17:27:59 GMT, "vgraybeal" <u32853@uwe> wrote:

>I have a table that has 20 date fields for each record.

Then you have a spreadsheet, not a table. Your table design IS WRONG. If you
have a one to many relationship, you need to model it as a one to many
relationship between two tables! "Fields are expensive, records are cheap".

> I need to select any
>record that has a matching date in at least one of those fields so it becomes
>a huge OR in a query and I have noticed that I am limited to 9 in a query. Is
>there a way to do this efficiently or will I need multiple queries to test
>all 20 date fields?

You're not actually limited to 9 - if you insist on using this incorrect table
design, you *can* select one or more rows in the query grid and use the INSERT
ROWS menu option to add more rows. Or you can go into SQL view and edit the
SQL of the query to add multiple more OR's. At some point you'll get the
"Query Too Complex" error though - I'd really recommend that you correct your
table structure!

             John W. Vinson [MVP]
0
John
3/27/2007 6:41:15 PM
Reply:

Similar Artilces:

Calculating time in query
Hi there, I'm searching for some help with my query. I'm making access to be my administration program for my freelance job. I've made a table with a row 'starttime' and a row 'endtime'. Now in my query I want to calculate the difference between these two rows. And then I want to make another query to calculate the totals of these differences. Is this a logic way to do this? And I'm trying to make a expression to calculate the difference but with no luck. The result is something like this: 3,2515785. I've found a kb article but I still can't figu...

Query File As
My Table Fields First Middle Last Suffix Need the new field in the qurey Will be FileAs: What is the expression so that FileAs displays Last (space) Suffix (if not null) then a (,) and a (space) then Fist (space) Middle Can anyone give me the correct expression for this so I can copy and paste from your reply Thank You from Marsman Try this -- IIF([Last] Is Not Null, [Last] & IIF([Suffix] Is Not Null, " " & [Suffix], "") & ", " & [First], & " " & [Middle], "") -- Build a little, test a little...

Query based DL ?
Upgrading from E2K to E2K3. By default there is a All Groups Address List were all of the Dl are nested. When you create a new Query Based DL it does not show up in the All groups List but does under the GAL. The reason is the filter string for the All Groups DL does not contain the (objectCategory=msExchDynamicDistributionList) attribute. I wanted to add this additional filter to the default All groups Address List but it is greyed out. Sure if I create a new All goups I can edit to my hearts desire. Is there a way to edit the default All Groups Address List??? ...

Can't edit SQL question in MS Excel/Query
Hello, I'm running Win2k SP3, Excel 2000 SP-1 and have created SQL questions that fetch data from an Oracle 8i DB via ODBC. Sometimes (or actually quite often) I can't edit the SQL questions in MS Query. The MS Query icon just flashes in the taskbar and disappears. Any wiz got any idea what can be done about it? Regards Hans Hans I'm not sure about the MS Query icon you are talking about in the taskbar but the way I can get to MS Query using Excel 2000 and connectiong to Oracle database is choosing 'Data' followed by 'Get External Data', and then choosing '...

Writing an IF statement in Microsoft Query
I'm querying our Oracle data to Excel and can't seem to get a formula return as a new field. I need the proper way to write this sequence: IF(RM.RM_STD= "LOCKER",50,RM.RM_AREA*RMSTD.COST_OF_SPACE) Which would basically say that if the room standard is a locker, we charge $50 - otherwise, take the room area and multiply it by the cost per square foot. But it doesn't matter how I write it...it's not working! HELP??!!! hi MS Query uses SQL and in SQL, you have to use the SQL IIf instead of the vb IF. and the IIf statement goes in the MSQ SQL stateme...

Simple Access Query/Form Question
Hello all, I know this may be a stupid question but i'm a newbie to Access. Here the background on what I'm designing. I'm creating a database to track special orders for our store customers. Employees enter information about the order including customers name, contact information, item to be ordered and etc. The main form has multiple check box to note if the customer has paid, the order has been placed, arrived, when the customer was notified and when the customer picks up the item. I'm looking to create a query that checks the field to see if a specific check box is checke...

2nd Posting
Haven't had an answer to this one, so I'll try again, I really need some pointers: I've got a query that I'm not sure how to develop. My tables: Quotes - QuoteNo, RaisedBy, Customer QuoteItems - RecordID, QuoteNo, PartNo, Lifecycle, Value There's a one-to-many relationship between Quotes and QuoteItems, i.e. one quote can have many items. I need to run a query to show a list of quotes with totals from the QuoteItems table i.e. QuoteNo, RaisedBy, Customer, List of PartNos, List of Lifecycles, TotalValue I haven't got a clue how to start this, I know it needs to b...

Difference between sum and groupby in query time
Can you help with this one? SUM fails - Data type mismatch in criteria expretion SELECT [Time Keeping Base Report].[First Name], [Time Keeping Base Report]. [Last Name], Sum([Time Keeping Base Report].[Total Hours]) AS [SumOfTotal Hours] FROM [Time Keeping Base Report] GROUP BY [Time Keeping Base Report].[First Name], [Time Keeping Base Report]. [Last Name]; works ok SELECT [Time Keeping Base Report].[First Name], [Time Keeping Base Report]. [Last Name], [Time Keeping Base Report].[Total Hours] FROM [Time Keeping Base Report] GROUP BY [Time Keeping Base Report].[First Name], ...

MS QUERY COL HEADINGS
I have a query from Excel into a SQL2000 Database. I have some calculated fields like datepart and also some combined fields. The resultant query is retrieved without headings on calculated Columns. Also using a field with AS statement does not work and after saving the query and accessing again, the AS portion is gone. Example: I enter: ihead.invoice_no AS 'Invoice', save query, query returns column with heading of invoice_no. Look at SQL again and it shows ihead.invoice_no , Just putting a space where the AS 'Invoice' was. Probably a flag or setting, can't fin...

Grouping and Renaming Fields in Crosstab Queries
Hello, I have built a crosstab query showing counts by crew numbers for a basis of a graph that will be shown by only 'Inside' and 'Outside'. I would like to group together in the crosstab all the 'outside' crews i.e. Crew 1,2,3 and show them all together in the crosstab as 'Outside'; conversely, I would like to group together 4,5,6 and show them all together as 'Inside'. Is anyone able to provide me with the expression to do this. Thank you It surely might help if you post your crosstab SQL. -- KARL DEWEY Build a little - Test a little &quo...

Countif Query
Hi All. I got a query today. I really hope you'll be able to help. I have a list of ages, and i want to count how many people are between the ages of 29 and 40. I though that the countif function would work but to my despare i couldn't get it to work. could someone please help me. It would be much appreciated. Thanks Dewald dewald Try this: =SUMPRODUCT((A2:A250>=29)*(A2:A250<=40)) Andy. "dewald" <loverboy_04@msn.com> wrote in message news:blea50$3ls$1@ctb-nnrp2.saix.net... > Hi All. > > I got a query today. I really hope you'll be able to help...

Number records in a query
Hi everyone - I am hoping someone can help me out with a problem that I am having. I have a table that contains customer account information, basically what securities they own. I am trying to create a query that will number (from 1 to n) the records belonging to each account number, basically giving me the following: (New Field) Account # Security Number 1 1 1 1 2 2 1 3 3 2 1 ...

Sum Query
My table name is trade - it has 5,000,000 records. I would like all fields in the table to be displayed in the qury result. For each Date and c=Customer, I need to sum Qty. I think this is easy but cannot get it to work. Thank you in advance. SELECT Trade.Customer, Trade.[Date], Sum(Trade.Qty) AS SumOfQty FROM Trade GROUP BY Trade.Customer, Trade.[Date], ORDER BY Trade.Customer, Trade.[Date]; You may need to plug in the correct table and field names. Also if the Date field include time, or it isn't an actual Date/Time datatype, you may get some strange results. With 5 milli...

Sumproduct query
I have named ranges as follows: Area refers to $C$11:$C$22 Option refers to $D$11:$D$22 Pessimistic refers to $E$11:$E$22 Formula =SUMPRODUCT(Pessimistic*(Area=$C4)*(Option=$D4)) Returns the correct result, being the same as =SUBTOTAL(9,Pessimistic) after autofiltering database $C$11:$E$22 on Area set to value in C4 and Option set to value in D4. However, formula =SUMPRODUCT(Pessimistic,Area=$C4,Option=$D4) Returns value zero (not desired). Could someone please tell me what is different about the logical process in evaluating SUMPRODUCT function in each of the above cases? I understand the...

Project Accounting related Query
Hi, We have situation, where the company is in the process of selling software to there clients. We need to book the product cost in Project accounting. With out using retention fees,project fees and other fees. can you help me out in booking the product cost in project accounting. with thanks ...

Write Name of Report Query in Report Footer
Is it possible to programmatically write the name of the query the report is based on in a text box in the report footer. "Steve Stad" <SteveStad@discussions.microsoft.com> wrote in message news:A1B0F793-C14A-4101-AB45-807BE72A4A11@microsoft.com... > Is it possible to programmatically write the name of the query the report > is based on in a text box in the report footer. Place a text box on your report, and set its Control Source property to: =[Report].[RecordSource] -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users...

nesting with countif
Hi, I am having trouble with nesting the and command in a sheet I am attmepting to prepare a spreadsheet to keep track of the clients in the various programs at the not-for-profit blind services agency where I work. I pasted a small piece of the sheet below. I used the countif function in the formulas in column R below =COUNTIF($S$25:$S$31,"Alp II", =COUNTIF($S$25:$S$31,"Alp III", =COUNTIF($S$25:$S$31,"Alp II E", and =COUNTIF($S$25:$S$31,"Ancillary to add up the how many people were in each program, but I have not been able to figure out how to count for ...

Web Query Refersh Interval
Hi I am using Excel 2002 to get a web query, it work fine but the AUTOMATIC REFERESH Interval is min 1 minute. I want set the Interval to 20 seconds, can any one help me about how to? Using VBA ? can give some example code ? Thank you Kelvin Hi AFAIK this is the best you can get. If you need shorter intervals you have to use the OnTime method and program your data access yourself -- Regards Frank Kabel Frankfurt, Germany Kelvin wrote: > Hi > > I am using Excel 2002 to get a web query, it work fine but the > AUTOMATIC REFERESH Interval is min 1 minute. > > I want set...

Export a query to a new database
Access 2003 I have a large database with several tables. One of the tables has about 74 fields and I have created a quety using only 12 fields and criteria to filter only certain records. I need to send the results of the query (basically the records and fields) to an associate. How can I export the records and fields of that query to a new Access MDB file to send to them without sending my other data? Does it need to be in an .mdb file? You can export the results to a text file (using TransferText) or to an EXCEL file (using TransferSpreadsheet) -- these will be a bit easier to c...

Running MS Query on an Excel Spreadsheet
Is it possible to run 2007 excel MS Query on an Excel spreadsheet with the same results as if you'd run it on an ODBC table like SQL? I have an export from a data table into an Excel spreadsheet and searching keywords is pretty cumbersome so I was hoping that MS Query might yield a better result. TIA hi the guts of Microsoft Query (MSQ) is ODBC and the language behind it is SQL. it is just conveniently packaged as a MS Wizard ie point and click. can you say the words "recorded macro"? tell us more about this "searching keywords is pretty cumbersome" so that we mi...

Crosstab query question
I use a crosstab query in a database as a first step in recalling multiple monetary transactions made on the same day that, when added together, exceed $10,000.00 and then generate a report based on the results. The query currently returns multiple transactions made on the current day as per the design. The OrderDate field in the query's design view is as follows: Field: OrderDate Table: Transaction Total: Where Crosstab: Sort: Criteria: "Date( )" Or: I would like to be able to bring this data up from dates in the past a...

Translate if statement in query
I took both of these if statements from a query in an access database and I am trying to interpret them. New Request: IIf(IsNull([Demand]![Average requested]),0,[Demand]![Average requested]) Old Request: IIf(IsNull([Demand_1]![Average requested]),0,[Demand_1]![Average requested]) My interpretation is that each statement in the query is giving a field name to the field and then doing something or nothing depending on the data in TABLE: Demand / FIELD: Average requested, but I am not sure what. Are you help to help me translate these statements completely? Thank you f...

Recursive Query or Iteration
I am in the process of designing an Access application that helps predict the promotion prospects and likely date(s) of successful promotions of Army personnel. The application is supposed to carry out predictive processing by extracting people meeting promotion criteria from a table and iterate the process to predict subsequent promotions upto three ranks ahead. Each promotion is based on existence of a vacancy, and in turn creates a chain of sub-ordinate promotions. So I should be able to select an individual, Private X, and ask the application to predict when can he become a Staf...

Formula query
Hi I've devised a basic spreadsheet which calculates the cost to hire a unit. Col A = Item No Col B = Date from Col C = Date to Col D = No of days Col E = Cost per day Col F = Net cost Col G = VAT Col H = Gross This works fine, but what I want to include is the minimum rental period is three months. So If someone rents the item for less than three months, the figure in the net cost column would show the minimum cost TIA Steve Hi Steve if cell F2 formula would be =MAX(d2,90)*E2 Cheers JulieD "Steve" <swr1953(at)yahoo(dot)co(dot)uk> wrote in message news:419e234e...

Query Based DL
Hi, I have created a few query based DLs, should these appear in the default global address list? I created a new AL which i can use to show these, but the question above still remains. Matthew You need to add the following string to your GAL's ldap filter: (objectClass=msExchDynamicDistributionList) The following post shows you how to do this for the All Groups address list - you can similarly modify the purportedSearch attribute of your default GAL as well to make them show up: All Groups address list and Query-Based Distribution Groups http://exchangepedia.com/blog/2005/12/al...