Adding a new record with a reference to another table

[TishyMouse]

Not sure if this is a 'Query' question or a database design question but
hopefully someone will be able to point me in the right direction.

I have a database with 3 tables, Roles, Targets and Actions, defined as
follows:

tblTargets
==========
TargetID (AutoNum, PK)
TargetText (Text)


tblRoles
========
RoleID (AutoNum, PK)
RoleText (Text, unique index)

tblActions
==========
ActionID (AutoNum, PK)
RoleID (Integer)
TargetID (Integer)
ActionText (Text)

TargetID+RoleID = Unique index

For each Target there may be several combinations of Roles/Actions.

I'd like to be able to create a query/form where I can enter a new
ActionText for a combination of RoleText and TargetText, i.e. entering the
text of the role rather than the ID (and also text of the Target rather than
the TargetID). At the moment I can't even get the join between tblRoles and
tblActions to work.

e.g. My query qryRolesActions looks like this:

SELECT tblActions.ActionID, tblActions.TargetID, tblActions.RoleID,
tblRoles.RoleText, tblActions.ActionText
FROM tblRoles LEFT JOIN tblActions ON tblRoles.RoleID = tblActions.RoleID
ORDER BY tblRoles.Role, tblActions.Actions;


This successfully returns the correct dataset for existing data, but when I
try to add a new record by entering the text corresponding to an existing
RoleText it tries immediately to create a new RoleID for this RoleText and
returns a duplicate error


Is there any way round this? Is my database design even correct?

TIA

TishyMouse

 

[Dale Fye]

Are the actions going to be a relatively static list, or will each ActionText
value be different? If different, then the structure you have looks like it
is probably good.

You might need to change the query to something like:

Select tblActions.ActionID, tblActions.TargetID, tblActions.RoleID,
tblRoles.RoleText, tblActions.ActionText
FROM tblActions INNER JOIN tblRoles
ON tblActions.RoleID = tblRoles.RoleID

You might also want to consider joining the Target table to this as well, so
you can see the TargetText.

This query should be updateable, and should add the record to tblActions
rather than tblRoles.

HTH
Dale

 

[TishyMouse]

Thanks very much for the response. Yes each ActionText valu will be different
- it is the roles that come from a limited list.

Unfortunately this doesn't work either. Just as before, the query is
updateable but as soon as I enter a value in the 'RoleText' column even if it
is a value that already exists in tblRoles it populates the 'RoleId' with a
new, unique number, leading to an error when saving the record because
RoleText is not unique in tblRole. If I do it the other way round and enter
the RoleID first it correctly populates RoleText and everything is fie but
unfortunately my users won't know what RoleID should be. Note that ultimately
I'll be using this in a cascading form but I figured I needed to get the
query working first...

 

[Dale Fye]

If you are going to put this in a form, leave the RoleText out of the query
altogether, and tblRoles too, for that matter.

On your form, have a combo box that uses tblRoles as its record source (bind
it to the RoleID field, but make that fields column width equal to zero).
Then, the users will be selecting the appropriate RoleText from the combo
box, but will actually be storing the RoleID value.

HTH
Dale

 

[TishyMouse]

Works a treat, thanks for your response.

If you are going to put this in a form, leave the RoleText out of the query
altogether, and tblRoles too, for that matter.

On your form, have a combo box that uses tblRoles as its record source (bind
it to the RoleID field, but make that fields column width equal to zero).
Then, the users will be selecting the appropriate RoleText from the combo
box, but will actually be storing the RoleID value.

HTH
Dale