Getting rid of duplicate fields in a select query?

[sarajini]

Hi,
I have one table for info on clients (primary key=ClientID). It is connected to a query for clients’ cases, and a table for clients’ brief services. (Each includes a ClientID field.)
I wrote a query to display all the fields; I told the design grid to show ClientInfo.*, Cases.*, and BriefServices.*

The query looks good except for one problem.
The ClientID field is repeated three times, for ClientInfo, for Cases, and for Brief Services.

First of all, should this even be happening? Does this mean that my relationships are messed up? I made sure that “Relationships” has connected the ClientID in ClientInfo to the other two tables’ ClientID fields.

Second, if the field repetition does not signal a larger problem…what can I do to make sure that the ClientID field shows up only once on my query? Would this be a union query? How would I write that? Or can I just get rid of the repetition using the query grid?

Thanks!!!
sarajini

 

[PaulRAmes]

Try creating a query of ClientIDs only, set it to unique values only (right-click on top panel-Properties-UniqueValues/Records=Yes). Then use that query to create your one-to-many relationship.
I hope this helps,
Paul

 

[sarajini]

Hmmm…

I think the query is not coming out the way it should. I set the query to unique values only, but the query still displays the three fields for client id’s.
I’m sorry, I don’t understand…how should I use the query to create the one-to-many relationships? Should I go into relationships and connect the “clientID” in the main table and the two other tables to the “clientID” in the new clientid query?

Also, I have clientID’s 1-25 listed in ClientInfo (the main table), but I only have clientID’s 1-15 listed in the other tables. I want my big combine-all query to spit back clientID’s from 1-25.

I think I want my big query to display the clientID field for the clientinfo table, and hide the clientID fields for the case tables. I could just select each field (excepting clientID), but that seems sloppy and hard to do.


Thanks for helping out, Paul!

Ps. I’m not an MIS—I’m an admin.aide and this is my first time using access! : )

 

[lespaul]

In the SQL view try this:

SELECT * FROM CLIENTINFO
INNER JOIN CASES ON CLIENTINFO.CLIENTID = CASES.CLIENTID
INNER JOIN BRIEFSERVICE ON CLIENTINFO.CLIENTID = BRIEFSERVICE.CLIENTID
ORDER BY CLIENTID

change the table names to your actual table names and it should work for you.



Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[sarajini]

Hi Leslie!
Thanks for giving me that code.
Unfortunately, there seems to be a problem...
when I try clicking out of SQL mode, I get the following dialog box:

Syntax error (missing operator) in query expression
'CLIENTINFO.CLIENTID = CASES.CLIENTID
INNER JOIN BRIEFSERVICE ON CLIENTINFO.CLIENTID = BRIEFSERVICE.CLIENTID'

hmm...just some parentheses I need to add? or does this mean i need to revise something in the table?

thanks for your help!!!
sarajini

 

[swampBoogie]

SELECT * FROM (CLIENTINFO
INNER JOIN CASES ON CLIENTINFO.CLIENTID = CASES.CLIENTID)
INNER JOIN BRIEFSERVICE ON CLIENTINFO.CLIENTID = BRIEFSERVICE.CLIENTID
ORDER BY CLIENTID

using multiple joins in Access is a pain with the unwarranted need for parentheses.