Consider this scenario.
There are two Forms, Form A and Form B.
In form A, there are ID and cluster name.
In form B, there are ID, cluster name and Common.
The problem is for the Common field in form B, i want it display those IDs that have same cluster name as form A. The cluster name in form B is autoupdated by a subform. I try to use SQL statement to do the display but it does not display the outcome that I want.
The SQL statement that I use is "SELECT DISTINCT ID FROM Table A WHERE (table b.cluster name = table a.cluster name)". However the result that I get is it display all the IDs that have cluster names. Can someone help me??
which tables go to which forms?
Form A = Records from Table A ?
Form B = Records from Table B ?
Subform = just container?
Are you comparing Records in Table A to Table B ? Or someone has just entered the Cluster Name in the sub form....?
How many matches are you expecting to return, each time?
Can you post a little more info or a screen shot?
Are you using any VBA?
Form A = Table A (fields are IDs, cluster name, etc)
Form B = Table B (fields are IDs, cluster name, Common,etc)
Subform is just a container of information IDs and cluster name obtained from table A.
Therefore the cluster name in table B is autoupdated when the user input the ID using the subform.
In the field "Common" of form B, it suppose to shows IDs that have the same cluster name. Meaning there are several IDs which have the same cluster name in table A. Thus in form B, by entering one ID, the form will autoupdate the cluster name and by clicking to "Common", it will show the IDs that have the same cluster name. For the autoupdating, I am using VBA. The rest I never use. Could you help me?
If you send it to me... I could look at it tomorrow night...I am all booked up today and tomorrow during the day. I think I understand what you are saying...and it sounds like it should be able to be done rather quickly.
SELECT [Cluster Level Table].[Master Site ID]
FROM [Cluster Level Table]
WHERE ([Cluster Level Table].[Cluster Name]) In (SELECT [All Table].[Cluster Name]
FROM [All Table] GROUP BY [All Table].[Cluster Name])
I may have got the tables the wrong way round for your requirement but you can probably figure that out.