| Author |
 Topic  |
|
|
liamfitz
Starting Member
United Kingdom
10 Posts |
 Posted - 06/03/2012 : 05:53:41
|
I suspect the best way to get the Recordset I seek, is as follows. I have a Query ( qrySelectClient_IDByStaff ) which retrieves a single column of DISTINCT Client_ID Numbers i.e. No repetition of this ID Number, although there may be more than one instance of it in the target table, which I want to base another Query on. I need to select ALL Clients names whose Client_ID, is in the result of this query. e.g SELECT Forename, Surname From tblCLIENTS WHERE Client_ID = ( ALL the Client_IDs in my 'qrySelectClient_IDByStaff' recordset ) Any help much appreciated.
ljfitz |
|
|
jeffw8713
Aged Yak Warrior
USA
699 Posts |
Posted - 06/03/2012 : 09:40:25
|
quote:
Originally posted by liamfitz
I suspect the best way to get the Recordset I seek, is as follows. I have a Query ( qrySelectClient_IDByStaff ) which retrieves a single column of DISTINCT Client_ID Numbers i.e. No repetition of this ID Number, although there may be more than one instance of it in the target table, which I want to base another Query on. I need to select ALL Clients names whose Client_ID, is in the result of this query. e.g SELECT Forename, Surname From tblCLIENTS WHERE Client_ID = ( ALL the Client_IDs in my 'qrySelectClient_IDByStaff' recordset ) Any help much appreciated.
ljfitz
Here is one option:
SELECT c.Forename
, c.Surname
FROM tblCLIENTS c
WHERE EXISTS (SELECT c2.Client_ID
FROM qrySelectClient_IDByStaff c2
WHERE c2.Client_ID = c.Client_ID)
Another option - using IN would be:
SELECT c.Forename
, c.Surname
FROM tblCLIENTS c
WHERE c.Client_ID IN (SELECT c2.Client_ID
FROM qrySelectClient_IDByStaff c2)
Jeff |
 |
|
|
liamfitz
Starting Member
United Kingdom
10 Posts |
Posted - 06/03/2012 : 11:46:07
|
Thank you v.much. I'll try it now and let you know how it went.
ljfitz |
|
|
|
liamfitz
Starting Member
United Kingdom
10 Posts |
Posted - 06/03/2012 : 19:33:28
|
Both of these suggestions provide the recordset I require ( and allowing additions, most importantly ). So thank you. However, I'm also prompted for the fields Forename and Surname before loading the form correctly ( these prompts can be ignored 'cancelled', or type anything and the same result occurs ).
ljfitz |
|
|
|
liamfitz
Starting Member
United Kingdom
10 Posts |
Posted - 06/03/2012 : 19:39:52
|
Here's the actual SQl : SELECT c.Title, c.Forename, c.Surname
FROM tblClients AS c
WHERE c.Client_ID IN (SELECT c2.Client_ID
FROM qrySelectDistinctClient_IDByStaff c2)
ORDER BY c.Forename, c.Surname;
It would be perfect without the 'parameter' prompts !
ljfitz |
|
|
|
jeffw8713
Aged Yak Warrior
USA
699 Posts |
Posted - 06/04/2012 : 13:41:15
|
| It sounds like you are using Access and not SQL Server. If that is the case, I would need to see the qrySelectClient_IDByStaff to see how to embed that into the above queries. |
|
|
|
liamfitz
Starting Member
United Kingdom
10 Posts |
Posted - 06/05/2012 : 11:07:39
|
Thank you for your insight. I am as you say, designing for Access 2010 ( with a view to adapting the DB to a Client/Server environment ) The 'qrySelectClient_IDByStaff' in full is : SELECT DISTINCT tblReferrals.Client_ID
FROM tblReferrals;
DISTINCT seems to be the catalyst ( which I first used in the main query, rather than the above, which I use as a subquery ). This creates exactly the Recordset(s) I require, but also blocks additions to it ( i.e. tested by running the query stand-alone, and used programatically to populate a form in VBA. Any suggestions ?
ljfitz |
|
|
|
jeffw8713
Aged Yak Warrior
USA
699 Posts |
Posted - 06/05/2012 : 14:27:41
|
Okay - I am not sure what the issue you are having is. Instead of using the query as your subquery, I would recommend using this:
SELECT c.Title, c.Forename, c.Surname
FROM tblClients AS c
WHERE c.Client_ID IN (SELECT DISTINCT r.Client_ID FROM tblReferrals AS r)
Not sure what you mean when you says it blocks additions. Using a query with DISTINCT will not be updatable because you cannot determine which - of possible many - rows to be updated. |
|
|
|
liamfitz
Starting Member
United Kingdom
10 Posts |
Posted - 06/05/2012 : 14:35:39
|
I will of course try this. Thanks. I suspected that is why it is not allowing ADDITIONS ( as there may be other records not included in record set, so Access cannot ensure validation rules are met against them, when editing/adding to DB ) I'll need to look at this again, as I cannot see there is any other way of 'noramlising' the relationships/tables involved.
ljfitz |
|
|
|
liamfitz
Starting Member
United Kingdom
10 Posts |
Posted - 06/05/2012 : 15:11:47
|
I see what you're saying now I think !..... Without making any reference to 'tblReferrals', in my query, but just including it in the design view of the Query, it showed repetition of Client records i.e an instance of the client's details for EVERY referral they had. By taking it away, it returned a recordset of DISTINCT Clients, and accepted additions. Thanks for your questions/ideas.
ljfitz |
|
|
| |
Topic |
|
How Do I Create a Subquery ?
