Discussion Locked

how do u merge results of queries

0 helpful
5 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have a simple table with the product number and itz pertaining page number. SOme product numbers have multiple page numbers. I have a query that asks for a product number and returns itz page number(s). When i get a product with multiple page numbers they start to list them in spreadsheet forms. For example you have product 970005 on page 4 and on 5. The result looks like:
970005 4
970005 5
A 2*2 spreadsheet
I want the result to look like this:

970005 4 5
A 3*1 spreadsheet

How is one able to do this???
Please email me if you know...chotawala@aol.com

Thanks soo miuch

5 replies

by frescalus

That is more a more advanced Query that would take a lot of time to do. It *may* be possible to do the latter of the two formats, but, in most cases, very improbable. Because of the relationships between the fields you really can only have the first display because there is 1 product ID on many pages, hence: 1 to many relationship.

But then, I could be wrong so everyone feel free to correct me :)

Neil

by kees.valkenswaard

The question is not very clear, i.e. are there 1 or 2 page numbers, or an arbitrary amount? In the latter case do you need all or would the first and last (or lowest and highest) be sufficient?
Suppose this is the case and you use Access. Make a group-by query [use the totals-button] with first and last of page number. [and grouped on item number]
Use this query in another query with fields item number and firstofpage. Use an iif to test if firstofpage = lastoppage. If so a null value should be inserted, else give lastofpage.
e.g. Test: iif([firstofpage] = [lastofpage];null;[lastofpage])
NOTE: your list separator might be , and not ;
Example:
89789 11
89789 13
89789 19
90678 23
93456 25
93456 32
96786 56
would result in:
89789 11 19
90678 23
93456 25 32
96786 56

This discussion is closed to new replies.