MS Access Query with many items at once?

Question

Does anyone out there know how to pull an Access query for multiple (many) items. Expample: my database consists of about 1500 items with multiple (20 and up) entries for each item. I routinely need to query 100 items and doing this one item at a time is very time consuming. Each item has a unique number assigned (again each item has multiple entries and I'd like to query the 100 or so items at once and work with one query (versus 100 "1-item" queries).

Answer 1

From your request, it appears to me that you have one table. You have approximately 1500 unique items, that each can have multiple records in the table. You'd like to return the records for a list of select items. If this is the case, then you can use the keyword "IN" in your where clause.:

1.Create a query for one of your items

2.Set your criteria to match the one item

3.Change the text in criteria from:

="item1"

to

IN("item1","item2","item3")



You can also use each term in the OR statements below the first item.



You can also substitute an entire SELECT clause - that returns only one field, but multiple records - in place of the list of items. So if you can create one query to get your list of individual item identifiers, you can reference it within the IN clause:

In (SELECT * FROM Query2)



Where Query2 returns "testfield1" field with "Item1" and "Item3"



Yet another alternative is to create a join between queries. In this case, you'd select Query1 and Query2 then drag the fields you wanted to link the tables.



For more information to help get you further, check out "SQL Queries for Mere Mortals". The first couple chapters give great enlightenment on proper database design to make reporting the information a whole lot easier. The rest of the book details many of the common SQL Statements we can use and techniques to use in the SELECT statement. (in the icon on the upper left corner, click on SQL to edit/create the SQL syntax directly).



Another great resource is: http://www.w3schools.com/sql/default.asp

Answer 2

Is this query using just one table?



Are these Unique numbers you are querying determined programitcally? User inputed? Hard coded?



If they are known to you, you can alter your select statement to use the IN operator rather than the = operator in your WHERE clause.



For example



SELECT *

FROM MYTABLE

WHERE UniqueItemNum IN (1,2,3)



Where the numbers between the parentheses are your unique Item Numbers you want to query against.

Answer 3

select * from [table] order by [UniqueItemNumber]



This will give you every record that is in the table, with the items grouped with each other. Of course, substitute [table] and [uniqueitemnumber] with the actual names of the table and field you are using in your database.



If you are trying to pull data from more than one table, it would be helpful if you provided an example of how those tables are laid out. For example:



Select * from ItemMaster a inner join Transactions b on a.ItemID = b.ItemID order by a.ItemID



This illustrates a common relationship between an item's master table, and the table that shows the transactions for the item.

Answer 4

Easy but nasty anser: Make one UNION query with all 100 other queries within it.



Harder Answer: You may need to redesign you tables.



If you have one table with all 1500 items, then good.



Not enought information to answer question. Zip up you .mdb file and email it to me if you like and I'll have a look. email to: SashaFroyland (at) gmail.com

Answer 5

use

select distinct item from table where.....



using distinct will only fetch one item of the multiple entries.. hope it answers ur question

Answer 6

Look, if you have a lot of information in Access, it's gonna be hell slow. you should probably start thinking of migrating your Access system to Sql Server, it has a wizard for it. anyways I do hope you're doing an inner join, not a cycle of some sort...