By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,731 Members | 1,129 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,731 IT Pros & Developers. It's quick & easy.

Need Query to return multiple rows per record in layman's terms

P: 2
i am working in Access 97, our database tables are linked from ODBCsqlsvr (not even sure what that means). i have a table where each record has a unique customer and their order information. there are 20 possible items to be ordered.

i need a query that will return a unique row for every item ordered. if the same customer (record) orders 3 items, i need the query to return 3 rows. Each row will have the same customer info (name, address, etc.) but the different items ordered. some item fields are yes/no, and other fields just record the quantity of that item and other items record the actual name of the item.

i have tried to make several queries, first i created 20 queries to return the records that ordered that item. then i added those 20 queries into one query but i didn't know how to link them or what the next step should be.

i also tried to use OR in criteria but i can only go 7 lines down, and i have 20 items.

so i tried to read through threads to find help but any threads i thought may come close to what i need confused me more. i think i found a similar problem in a thread called "How to extract multiple entries" from april 28th. although i have no idea what the language means.

if anyone can help explain to me what kind of query i need to create and maybe the basic criteria needed i would be forever thankful. please keep in mind, i don't understand the sql language so well, i don't even know what the purpose of a crosstab query or a form is. i am desperate to make this work. thanks.
May 5 '07 #1
Share this Question
Share on Google+
2 Replies


P: 2
are the ordered products in the same records

ie CustID , name , address , prod1 , prod2, prod7

or products in multiple rows like this

ie: CustID, Name, Address, prod1
CustID, Name, Address, prod2

If it is the later try putting this in the criteria

in ("prod1", "prod2", "prod3",...."Prod20")
May 5 '07 #2

P: 2
in the table the ordered products are all in the same record

ie Cust1 , name , address , prod1 , prod2, prod7
Cust2 , name , address , prod1 , prod2

but i want a query to give me multiple rows like this

ie: Cust1, Name, Address, prod1
Cust1, Name, Address, prod2
Cust1, Name, Address, prod7
Cust2, Name, Address, prod1
Cust2, Name, Address, prod2
May 5 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.