Eliminating duplicate results in a query

[nushi]

Hi everyone,

I have created an MS Access (using 2010) database to manage clients and assignments. The current database setup has a junction table to keep a track of the clients to assignments relationship, and it is a many-to-many relationship, since one client can have several assignments, and an assignment could potentially have more than one client.

Now, I would like for the user to be able to edit the client-assignment relationship in case a mistake is made during the initial data entry, or a change is required. As such, in order to be able to edit said relationship, the user must be able to see a list of clients who can potentially be associated with a particular assignment in question.

I am thus trying to run a query which will display all clients NOT currently associated with the specific assignment OR who have been removed from the assignment (I have setup the junction table to incorporate a toggle allowing the user to turn off the relationship, thus making it seem the client is no longer associated with said assignment, and avoiding deleted records). My currently query setup is as follows:

the clients table (tblClients)
the junction table (tblJnxClientAs sign)

I have setup the relationship between the two tables to display all of tblClients and only those in Jnx table that are equal.

I am playing around with the criteria, and so far my results have always had individual clients come up multiple times, and already associated clients showing up due to them being assigned to other assignments. Any suggestions on a solution?


MS Access 2010
My SQL is none existent I am afraid, and little in the way of VBA programming, using Macros in this database so far.

[zmbd]

So just make sure I have this correct let me restate the problem:
Assignments 1 thru 10
Clients A() thru G() where A(X) would be assigned but not active.

So:
Assignment 1 could have A, B, C
Assignment 2 could have D, F
Assignment 3 could have E, F, G(x)
Assignment 4 could have A, B, G
etc...

So, if you query on Assignment 3 you would like to have Clients A,B,C,D,G show up in the list of available clients for (re-)association to Assignment 3.

Is this correct so far?

[nushi]

Hi zmbd,

What you described is exactly correct , and that is exactly the result I would want to get when querying on Assignment 3.

[zmbd]

I don't normally start out by providing code/sql as I feel that doing this firsthand is the best way to learn. So please stick with me and try to do this yourself... if you get stuck at any-point someone here (or myself) will walk you thru things.

My goal here is just to return the names of the clients. You know the Assignment and the user has selected that information.

So your join table Could have

[tbl_projects]
[PK][FK_Assignment][FK_Client][Active_Client]
[1 ][1            ][1        ][1] == Assignment 1 Client A
[2 ][1            ][2        ][1] == Assignment 1 Client B
[3 ][1            ][3        ][1] == Assignment 1 Client C
[...]
[6 ][3            ][5        ][1] == Assignment 3 Client E
[7 ][3            ][6        ][1] == Assignment 3 Client F
[8 ][3            ][7        ][0] == Assignment 3 Client G(X)

Notice a little change in [Active_Client]... I avoid the Boolean (y/n - t/f) datatype due to some issues I've ran across when migrating Access data to larger databases and because of this article [[ Allen Browne: Why I stopped using Yes/No fields ]] AB has most likely forgotten more than I've learned about databases; thus, I tend to follow his advise. :-)

Basic steps will be these - I'm doing this all by hand with a 2yr at my feet so there maybe a typo or two:

Q1) Query on tbl_projects to return all of your records with active clients
[FK_Assignment]=3; [Active_Client]=1
Return only the [FK_Client]

Q3) You can do this by hand; however, Access's wizard doesn't do too bad of a job and for the non-SQLer may be the way to go...
You need to show your unmatched from [Q1] vs [tbl_clients] create a new query and use the unmatched records wizard.

At this point we should have all of the information needed to start building your list

Q6) You can now take [Q3] against [tbl_client] to retrieve the friendly names by using a join between the [PK] and the [FK] fields and returning the friendly name on only matching records.

[nushi]

I was afraid this would need more than one query! lol

Ok, so I have gotten all the way to Q3, and it seems to be working perfectly already! :D The query returns the full list of non actively associated clients, and each client showing up only once. I am manipulating the dummy data to for testing, but seems to be working thus far. Now I can just add the friendly names (easy) and then work out the next steps of actually writing new selections (but thats a different issue all together and I think I can handle it).

[zmbd]

Friendly names... Q3 against tbl_clients:
Q6: +Join on [tbl_clients]![PK]>[Q3]![FK_clients]
+returning for example [tbl_clients]![Client_FirstNam e] (...) on matching records. [went back to my second post and labeled this as Q6 :) ]

If you want the assignment name next to each client name
Q7: add tbl_assignments , add Q6
no join
Return fields with the names
Set conditional on tbl_assignments [PK]= for the assignment number of interest

Yes, you most likely can combine all of these queries into one grand SQL statement and there are occasions where I do so; however, I tend to break my queries down into small bites when developing as it makes it easier to troubleshoot - then, often, I just don't go back and combine them into one I am a Chemist not DB-programmer so the priority just isn't there :D

-z

[nushi]

Hrmmm... I am still confused, sorry for being so slow!

As I see it, Q3 is all records in [tbl_Clients] that are NOT in Q1, with Q1 being all actively assigned clients to [FK_Assignment]=X. This by definition will already include any clients assigned to [FK_Assignment]=X that have been deemed inactive, which is the list that I want. Thus if tbl_Clients has 30 records, and 5 are assigned to FK_Assignment=3 . Of the 5, 2 are inactive, 3 are active. Q1 will return the 3 active. Q5 will then return everything from tbl_Client that is NOT in Q1, which will be 27 records (30 minus the 3 active clients).

Maybe what I did in Q3 was not exactly as per your instructions (which I may have misunderstood). Thus I am, accidentally, skipping Q2 and Q5 and still reaching the same result?

either way, I am getting the result I need, and it seems to be working fine! So thank you so much for your time and help! :D

[zmbd]

ahh... nushi... I think you are correct here.
:blush:

My only excuses are doing this in my head with a two year old at my feet (well, and the twins too - two high energy boys and my youngest daughter who isn't too far behind them on the energy chart housebound for winter and now spring rains - just a little bit of cabin-fever starting to set-in ! :D )

Yep, Q1 pulled against tbl_clients will pull all of the unassigned and the inactive clients. I'll edit my post so as not to confuse later readers. Usually I run a test database before posting answers!

Best of Luck with your project!
-z

[nushi]

Hahaha it is quite alright zmbd, I am just grateful for the help and the 2 query solution is working so far so good. Now on to my next step.

Thanks again for your time and effort, and good luck with the cabin-fever (I do not envy you! lol)!