DISTINCT QUERY

Meena

New member
Local time
Today, 02:34
Joined
Nov 6, 2001
Messages
7
I have 4 Tables:
(((* Means primary key)

1. User (*UserID, FirstName, LastName)
2. Docbase (*Docbase, *UserID)
3. Group (*Group, *UserID)
4. Proj (*Proj, UserID)


a user can belong to none or more then one group, docbase, and project.

I want to create a query that returns all the information about a user including all the docbases, groups and projects that he belongs to.

I have a query that returns the results such as:

UserID Docbase Group Proj
****** ******* ***** ****
User1 doc1 group1 proj1
User1 doc1 group2
User1 doc2 group1

I want a result that would be like:

UserID Docbase Group Proj
****** ******* ***** ****
User1 doc1 group1 proj1
User1 doc2 group2

***docbase, group and proj are not related to each other*******

Can someone please tell me how to write a query that will give me such results

Thank you for your response.
 
You cannot join unrelated tables in a single query and expect to make sense of the results. Look at your requested results sample and see if you can figure out how Jet should know based on the foreign keys you have defined that proj1 goes with doc1 and not doc2. The answer is, "there is no way", which is why you get the results you are getting. The type of results set you are seeing is called a cartesian product. A cartesian product takes every row from every table and combines it with every row of every other table. As you can see, it is normally a useless recordset.

Use a report based on the user table that includes subreports for each of the other sets of data. Make sure that the master/child links are properly set so that the subreports will be in sync with the main report.
 

Users who are viewing this thread

Back
Top Bottom