Help re Join Conditions required please

[Guest]

*apologies if this is a duplicate, posted earlier but cannot find my post!*

I have 2 tables "Candidates" and "Roles" the join i have is related role 01,
related role 02 etc in "Candidates" joined to RM Number in "Roles". RM Number
is the primary key and an "Autonumber".

A role can have multiple candidates assigned to it but in a continuous
record subform I have setup only one candidate is shown against the selected
role.

Any help would be appreciated as i have beeing struggling with this for days.

Kind regards,

 

[Guest]

Your subform should be based on a selection query. Bring in both table and
ensure that your join properties are set to show all records from Roles and
any the Candidates that match. (TIP - There should be an arrow at the end of
the join line pointing to the Candidates Box if you have done it right)

In the query design page you can simply Click the join line to obtain the
drop down.

Hope this helps

Wayne

 

[Guest]

Many thanks Wayne,

Please excuse my ingorance, a bit of a newbie where Access is concerned -
have looked up Access help re the "Selection query" in your reply and am
unsure how I would go about creating a selection query that relates to the
subform?. The subform was created using the wizard and has no query attached
to it.

your patience is appreciated

thanks again in advance

 

[Guest]

Hi again
With no forms or table open you should see a box with a list down the left
hand side of the box. Tables, Queries, Forms, etc. Click Queries (Left -
once only) then click New (at the top of the box), select Design. You will
see a grid with a Blanc section above.
Once the new page is open right click on the top (Blanc section) and select
Show Table. Click each of the Candidates and Roles tables in turn (after
clicking each select Add). This will bring your tables into the query.
If there is a join (a line) between them select it (the line) then right
click Delete.
As your new form is going to be based on the Roles table select the ID of
the Role (Normally the topmost item) and left click drag it into the grip (or
simply double click and it will fall into the next available slot).
Do this for each field you wish to bring in to the new form (not create
yet). It's not a good idea to bring in more fields than you need as this
will slow things down.
Don't forget that you MUSTY bring in the joining field from BOTH tables
(this will prob be the Candidate’s ID in your case).
Now to join the 2 table in the query simply select the ID from the 1st table
and drag it into the 2nd table (it won't move but you will have created the
join / relationship)

If this won't work it is as you have not placed the Role ID in the
Candidates Table (if this is case save and close this screen and go to the
table and insert a field - make sure it is a numeric field so it will be able
to accept the data).

Assuming that you are able to create the join / relationship - right click
the line and select the type of join your want (there are 2 choices).

If you wish you may use the wizard to create your new sub form.

Save and close your new query then single left click then select the New
Object drop down (at the top of the page). Select form and choose which type
you want. In this case a continuous form.

Open your first form you created and delete the original sub form. Then
select toolbox - sub forms and click on the screen where you want it placed
(you can move it later). Select the join type you want Role ID from BOTH
choices then click next then finish.

Sit back and have a coffee.

Hope this helps.

Wayne

 

[Guest]

Thanks Wayne, just got back to the forum and noted your answer. Time to
tackle the database again. I will let you know how it goes.

Cheers,

 

[Guest]

Wayne - cracked it, the problem lay within the sql statement and it is now
forced to accept an "or" command in the joins.

Thanks again for all your advice, managed to get the selection query up and
running and am now going home - been in work 13 hours today and enoughs
enough.

Kind regards