Multiple Item query

I've read a few posts on this query and it looks like creating a table
where I input the item numbers in it and linking it to the query seems
to be a good way to get a lot of items in a query. Now what If I have
hundreds of numbers I want to look up? do I input all 100 numbers into
the table? Or what if it's like 10 numbers but I want create seperate
queries for different groups of numbers, should I have 1 table for one
group of numbers? What would be the easiest way for a novice to do
this?
0
ryan
2/29/2008 5:23:03 PM
access.queries 6343 articles. 1 followers. Follow

2 Replies
766 Views

Similar Articles

[PageSpeed] 38

It is easier to fill the table with just the values you need for the query 
you want to run, then, empty the table and refill it with new values for a 
new query.

You can, though, make a table with two columns, one column for the values, 
and the second column, a kind of QueryID:

TheValues    QueryID
102                1
105                1
16                   2
17                   2
19                   2



and you can then use:

    WHERE QueryID = 1

to get 102 and 105, for a given query
and


    WHERE QueryID = 2


to get 16, 17, and 19  for another query, without having to clear the table 
EACH time. Note that you may still have to clear all records where queryID=2 
before running the second query, with a new set of values, though. So, that 
way of doing things is more susceptible to problems than using a simpler 
approach. A better solution could be to use a different table for each 
query, and to call the table like:  Query1_parameters,  and 
Query2_parameters  and have Query1_parameters holding parameters JUST for 
Query1.



Hoping it may help,
Vanderghast, Access MVP


<ryan.fitzpatrick3@safeway.com> wrote in message 
news:9e8b8b85-ae6e-4ec0-81db-b008f81a3ec3@s13g2000prd.googlegroups.com...
> I've read a few posts on this query and it looks like creating a table
> where I input the item numbers in it and linking it to the query seems
> to be a good way to get a lot of items in a query. Now what If I have
> hundreds of numbers I want to look up? do I input all 100 numbers into
> the table? Or what if it's like 10 numbers but I want create seperate
> queries for different groups of numbers, should I have 1 table for one
> group of numbers? What would be the easiest way for a novice to do
> this? 


0
Michel
2/29/2008 6:56:00 PM
Thanks, this is what I did, I came to the fact that I have to enter in
the items and I created a class and subclass for easy searching. This
made my queries run a lot faster to.


On Feb 29, 10:56 am, "Michel Walsh"
<vanderghast@VirusAreFunnierThanSpam> wrote:
> It is easier to fill the table with just the values you need for the query
> you want to run, then, empty the table and refill it with new values for a
> new query.
>
> You can, though, make a table with two columns, one column for the values,
> and the second column, a kind of QueryID:
>
> TheValues    QueryID
> 102                1
> 105                1
> 16                   2
> 17                   2
> 19                   2
>
> and you can then use:
>
>     WHERE QueryID = 1
>
> to get 102 and 105, for a given query
> and
>
>     WHERE QueryID = 2
>
> to get 16, 17, and 19  for another query, without having to clear the table
> EACH time. Note that you may still have to clear all records where queryID=2
> before running the second query, with a new set of values, though. So, that
> way of doing things is more susceptible to problems than using a simpler
> approach. A better solution could be to use a different table for each
> query, and to call the table like:  Query1_parameters,  and
> Query2_parameters  and have Query1_parameters holding parameters JUST for
> Query1.
>
> Hoping it may help,
> Vanderghast, Access MVP
>
> <ryan.fitzpatri...@safeway.com> wrote in message
>
> news:9e8b8b85-ae6e-4ec0-81db-b008f81a3ec3@s13g2000prd.googlegroups.com...
>
> > I've read a few posts on this query and it looks like creating a table
> > where I input the item numbers in it and linking it to the query seems
> > to be a good way to get a lot of items in a query. Now what If I have
> > hundreds of numbers I want to look up? do I input all 100 numbers into
> > the table? Or what if it's like 10 numbers but I want create seperate
> > queries for different groups of numbers, should I have 1 table for one
> > group of numbers? What would be the easiest way for a novice to do
> > this?

0
ryan
2/29/2008 8:56:21 PM
Reply:

Similar Artilces:

Editing routing and BOM, All Items
When you try to edit a BOM and pick up one of the item number on manufacturing mudule, the list shows all items insted they that already have their BOM or Routing done. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Communi...

Copy & paste in multiple areas using VBA
I'm trying to select a series of ranges to 1. paste a named formula I've created and then 2. remove the formula & leave the value What I have so far is shown below, but I'm sure there must be a better way. The problem seems to be that I can't use the copy function in a multiple range. I guess I can't paste xlValues to multiple ranges either, so I'm being forced to handle each range, one at a time, which seems rather cumbersome. Sub Macro1() Range("G8:R45,G50:R59,G63:R110,G114:R121,G114:R122,G126:R134").Select Selection = "=ITNBu...

null values in querys?
I know its (Nz(field)) in VBA but what is the correct syntax for specifiying a null value in a query? For my example I need to specify IF the value is null to update it with info. Thanks! In the criteria row, enter: Is Null -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "GregB" <GregB@discussions.microsoft.com> wrote in message news:E4D29FE8-1D0E-478B-A6B4-46F400C698A0@microsoft.com... >I know its (Nz(field)) in VBA but what is the correct...

Adding multiple products to a contact: Challenge/HELP!!!
Here is a challenge: We sell products ( traing courses) where we have to be able to link a contact to a product. thanks to Andrew on the message "Mapping between standard and custom entity" we were able to do that using the ideas he posted. as well as his blog. (http://andrewn23.blogspot.com/) What we now run into is that we can have multiple courses on a single quote/order and want to associate the contact/stuent easily with the products. when one wants to for instance merge accounts there is the possibility for selecting multiples by usng the control key and then clicking ...

Deleted Items in Inbox
How can I get Outlook to stop showing deleted items in the Inbox? What version of Outlook are you doing? Are you using IMAP and if so have you purged your deleted items yet? -- -- -Ben- Ben M. Schorr, MVP Roland Schorr & Tower http://www.rolandschorr.com "Jeffery Rehm" <jr@jrehmconsulting.net> wrote in message news:#nW#$NAoIHA.3976@TK2MSFTNGP03.phx.gbl... > How can I get Outlook to stop showing deleted items in the Inbox? > IMAP? Use a view that hides deleted items and purge the folder occasionally -- Diane Poremsky [MVP - Outlook] Author, Teach Your...

Multiple ship-to same customer
We have multiple ship-to accounts with single vendors, i.e. one of our customers has three offices that we drop ship supplies directly from the vendor. The vendors have set up specific account numbers for each singular office/location, i.e. any location we ship to has a unique customer number. We suspect if these individual account numbers are not indicated on our PO to the vendor since we fax many of our orders to the vendor (in addition to the ship-to address) that we will end up with orders shipped to the wrong office(s). How do we set up one vendor (to link all the items from tha...

record and print amounts in multiple boxes on 1099 forms
We have had several clients that have been very disappointed to learn that they can only do 1 amount box per 1099. For instance, they might need an amount for rents and one for non-employee compensation. I think it would need to be settable at time of transaction entry. One of our clients said they could do that in Quickbooks and was very surprised to learn that GP won't do it. -- BrianB ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in th...

auto numbering in a query
Hi all, I need to build a query that have in a field auto numbering and in a certain formating for example: 0001 0002 ....... 0012 ....... is it posible? Is this to produce data that will be viewed in a report, by any chance? "thread" <yaniv.dg@gmail.com> wrote in message news:1175794888.467011.108490@w1g2000hsg.googlegroups.com... > Hi all, > I need to build a query that have in a field auto numbering and in a > certain formating > for example: > 0001 > 0002 > ...... > 0012 > ...... > is it posible? > On Apr 5, 2:42 pm, "BruceM&q...

What OL2003 does after you clear many messages from Deleted Items
I find this annoying and am wondering if anyone else has noticed it. I use OL2003 with a PST (no Exchange) on XPSP2. My PST is usually not much over 100MB. When going into Deleted Items and clearing out, I don't know, on the order of at least 500 or 1000 messages, I notice a sudden amount of disk activity. I've used Sysinternal's Filemon to find that it's Outlook, presumably doing some kind of subtle behind-the-scenes compaction. (Note that I have AutoArchive disabled, so it's not that.) Problem is that this stealth compaction goes on for 10, 15, 20 minutes or more,...

SMTP on multiple ports??
Hi, I have an exchange 2003 server with 15 users, 5 of them connect via pop. We are routing our mail through a 3rd party spam filter and need to set our firewall to only accept smtp traffic from that 3rd party server. This blocks out our pop users. Can I set up exchange to allow smtp traffic to come in over the standard port 25 and some secondary port as well? I would then change the pop clients to come in over the secondary port. Is this possible? Thanks for any input you can give me. Yes it is possible. Create a second SMTP virtual server with a different inbound port number. Nue ...

Multiple email accounts and inbox folders
I have 3 email accounts set up - one hotmail and two non-web-based accounts. The incoming hotmail messages automatically go into the hotmail inbox; however all email from the other email accounts winds up together in the "Outlook Today - [Personal Folders]" inbox. Is there a way that email received via these other ISP's can be directed to separate folders I specifically set up for them? Thanks in advance. Rick Johnston You have to use a rule to separate out POP accounts. Go with some like "when mail arrives through specified account, move to specified folder". You...

Multiple report filter using combo box on form
I need to be able to filter a report by multiple fields. First by the Cost Center number and then once that is done - then a further filter by Expense Account Number. I currently have a form that successfully filters the report by either Cost Center, Expense Acct Number or SubAccount using a Option Grp/Combo Box set up. The user selects one of the 3 fields mentioned which populates the combo box and then they hit a command button (Filter Report). This is the code I used. Dim iFilterBy As Integer Dim iReportType As Integer Dim iFilterValue As Integer If IsNull(cmbFilterBy) Then M...

Creating one trendline for multiple series
Hi, I have a document withfour different series and these are all plotted onto one curve. I want a trendline that fits all four series of data onto one trendline. I don't want to create a new series with all of the data and then fit a trendline to that. Is there any way I can just fit one to all four sets of data? Thanks > I don't want to create a new series with all of the data and then fit a > trendline to that. Is there any way I can just fit one to all four sets of > data? Unfortunately this is how it has to be done. - Jon ------- Jon Peltier, Microsoft Excel...

selecting a query from a combo box
HELP! Need to design a DB for my boss and I am lost! I have a database which lists students who have went on exchange over the last 17 years to over 20 countries and numerous institutions.. I have set up 3 queries/reports using parameters so the user can enter: 1) the year 2) the country or 3) the insitution. Now the problem is the insitution query as the name of the institution can get spelt various ways so I would prefer the user to select the institution from the drop down box which they use to enter the data into the table under the field "institution". My f...

Update Inventory Items
Hi, Appreciate any help. I am looking at importing new values into the following fields below using IM - GP Adapter (Inventory Item) but not sure if my source fiile is actually correct. I'm unsure if these are all the fields I shud have. My source file has the following fields. I am looking to update exiting item records in GP and update the last 5 fields. Is this possible in IM? I am able to update this manually in GP. Item Number Item Description Current Cost - Item Master List Price - Item Master Standard cost - Item Master Primary Vendor - Item Qty Site window...

MS paramater Query on ODBC Table
This is a multi-part message in MIME format. ------=_NextPart_000_0008_01C38C4D.5F11AC30 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Is it possible to and if so how can I run a parameter driven MS query on = an ODBC database table? I want to be able to enter some info into a cell on a spreadsheet and = have a query run to pull back other info relating to my entry. --=20 Regards Dean=20 dkso@ntlworld.com=20 http://homepage.ntlworld.com/dkso ------=_NextPart_000_0008_01C38C4D.5F11AC30 Content-Type: text/html; charset="iso-885...

text conversion to number on select query
Hi, In a Select Query I'm joining 2 tables by Item ID (unique value, similar to Social Security Number) but 1 table created by IT has Item ID as a "number" value and the other table has it as a "text" value. How can I in a Select Query, create a formula that can either have the text as a number value and vice versa so i can link the 2 without getting "type mismatch in expression." I think I can use Cdbl Value or something like that in the formula but not sure. Thanks! "inspirz" wrote:subed going to jail > Hi, > ...

how to detect item selected from List Control?
Hi I'm trying to detect the index of the item selected from a list control when a user hits the up or down arrows from the keyboard. So I made a call to list.GetSelectionMark() in the List control's event LVN_KEYDOWN, however this always results in the call to GetSelectionMark returning the index of the item selected before the arrow key was hit any ideas of how to get the item selected after the arrow key has been hit, or a better event to detect a change in item selected? Thanks ahead of time. How about LVN_ITEMCHANGED notification message? Ali R. "Mark" <anonymou...

MS Query not installed for New Database Query
I have Excel 2000 SP3 & Windows XP Home When I go to Data | Get External Data | Run Saved Query I can imort data from an Access database into an Excel worksheet. Wheh I go to Data | Get External Data | New Database Query after a delay a message appears to tell me that MS Query is not installed. MSQRY32.EXE is in the Office folder & I have reinstalled it by renaming it to .old & running the Add/Remove facility in the Office installer to no avail. I have searched the MSKB & found an article at: http://support.microsoft.com/default.aspx?scid=kb;en-us;179686 that refers to...

CMap query
Hi, I have a text file and i read the complete file and build a FILELIST - Files & GROUPLIST - groups like odbc,,jet40, jet35 The Group list in the file consists of values like version | odbc | 430 version | Jet40 | 430 version | Jet35 | 430 version | oledb | 430 version | odbc | 440 version | Jet40 | 440 version | Jet35 | 440 version | oledb | 440 The File List consists of values like FILE | odbc | <COMPLETE path of odbc file like odbc32.dll> FILE | odbc | <COMPLETE path of odbc file like odbcji32.dll> FILE | oledb | <COMPLETE path of oledb dll1> FILE | ole...

Use query for Row Source?
I attempted to use a combo-box to look-up records on a form. The RowSource query that is generated by the wizard included 2 data fields. I wanted to show only a distinct list of the second, non-key data element (col1). When I changed the RowSource query to SELECT distinct Col1, the combo-box displays nothing. I then created a stored query with the SELECT distinct Col1. They query runs correctly, but when I put in in the RowSource, it displays nothing. Any clue what I could be doing wrong? On Fri, 26 Oct 2007 12:17:03 -0700, JHC wrote: > I attempted to use a combo-box to look-u...

adding supplier to items in PO
When you manually add items to a PO and then select the supplier in the header the items should be added to the items that the supplier carries if they aren't already. This would save loads of time adding suppliers to items. Craig ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the ...

Query is making a nuts
Hi Using Access 2007 I have a table and two of the fields (Status and OrigStatus) have, among other possible entries, the words "Member," "Customer" or "Request." I am trying to create a query that only displays records which do NOT have "Member," "Customer" or "Request" in either field. But it won't work! I have tried putting each word in its own Criteria column in each field using syntax such as: <>"Member" with no success. I have also tried <>"Member" OR "Customer" OR "...

Show / Hide items in Pivot Table as required
Hi I am using MS Excel 2003 and need a macro code for show only my selected items only in a Pivot Table. Query: One excel table empstatus.xls in which fields are Employee No , Department, Status, other fields. In status field items are Blank, Leave, Left, Resigned. I wanted to show only blank & leave based on when I’m selecting a particular month in my pivot table. So is there any code to hide all data items of status and only show blank & leave. Please suggest. -- ------------------------------ Thanks Nitesh ------------------------------ ...

_T macro across multiple lines!
Hi, We have the _T() macro which is supposed to be used for ansi/unicode compilations with string literals. But how do you use it with strings that span multiple lines? Ok: CString str = _T("a short string"); No ok: CString str = _T("A long string I might " "want to break up over a " "few lines"); Thanks "markww" <markww@gmail.com> wrote in message news:1155693172.252663.158120@m79g2000cwm.googlegroups.com... > Hi, > > We have the _T() macro which is sup...