How to populate a column in a row based on a value in another colu

[PhatAdo]

I have several tables and I’m having a problem with my last one. I have two
columns in this table, ‘License’, which are populated using combo boxes, and
these combo boxes are populated from using queries on other tables:

I have an ‘Entitlement’ table with has and Entitlement ID (different to it’s
primary Key ID). For each entitlement there can be one or more templates and
so I have a ‘Template’ table with a ‘Template ID’. As it happens a template
can also be in one or more entitlements so there is also an
‘Entitlement-Template’ table to manage the many to many link.

My license table has both the ‘Entitlement ID’ and the ‘Template ID’ and
what I want is that in any row, once you have selected an ‘entitlement ID’
that automatically the ‘Template ID’ column only populates with the Template
IDs that are associated with that particular Entitlement.

I have a query that returns the Template IDs that are associated with the
Entitlements, but that is a large list and not all of those Templates are
associated with each entitlements. At the moment you can select an
Entitlement ID, but when you go to chose a Template ID you can choose from
the whole list.

I know how to enter a criteria for a query of an exact value – but I don’t
know how to create a query on a table that is based on the current value of
another column in the same row in that table – or indeed if this is possible.

I do hope I’ve made this clear!!

Cheers,
A

 

[KARL DEWEY]

I do hope I’ve made this clear!!
No it is not. Below I tried to put your words to show table structure. Let
me know if I got it correct or fix any errors.

License –
EntitlementID –
TemplateID -

Entitlement –
-ID – primary key
EntitlementID –

Template –
TemplateID –

Entitlement-Template –
EntitlementID –
TemplateID –

License has the same fields as Entitlement-Template so why need second table?
How do you use a combo to populate a field in a table? Are you using a form
or are they look-up fields?

 

[PhatAdo]

Hi Karl,

Thanks for the reply.

The table structures are actually as follows:

NOTE: The Primary ID’s in tables are Auto numbers.

License:
-Primary ID
-Entitlement_Primary_ID (linked to Entitlement Table-> Primary_ID)
-Template ID
-Lock Code
-Code

Entitlement:
-Primary ID (linked to Entitlement-Template Table-> Entitlement_Primary_ID)
ALSO (linked to License Table-> Entitlement_Primary_ID)
-Entitlement ID {basically a unique textual representation of the Primary ID}
-Count
-Desc

Entitlement-Template:
-Primary ID
-Entitlement_Primary_ID (linked to Entitlement Table->Primary ID)
-Template_ID (linked to Template Table->Primary ID)

Template:
-Primary ID (linked to Entitlement-Template Table-> Template_ID )
-Desc
-Many More…

There is no direct link between ‘Template.Primary ID’ and
‘[License].Template ID’ – essentially the query I’m trying to write.

An Entitlement can contain one or more Templates, and a Template can exist
in one or more Entitlements. Every License issued must reference an
Entitlement ID, and a corresponding Template Id which has to be one of the
Templates that are associated with that entitlement.

In the License table I have populated the Entitlement_Primary_ID combo box
using a Lookup query that returns the Primary ID from the Entitlement table
(in fact it also returns the Textual entitlement ID and I display this using
the bound column and setting which columns to display so it’s more readable).

What I need is that once a user goes to fill in a row in the License table,
once they choose a particular Entitlement ID, the Template ID column in the
License table dynamically returns only the templates associated with the
entitlement ID that was entered for that specific row.

I have a query that returns the ‘Template ID’ and ‘Description’ from the
Template Table and ‘Entitlement ID’ and ‘Primary ID’ from the Entitlement
Table and this returns all the entitlements and all their corresponding
templates – but I don’t know how to make this specific to be dynamic.

I can put a condition of something like “Where [Entitlement].Entitlement
ID=’ExampleEntitlement123’â€, but this would mean the combo box for all rows
would only return the Templates for the entitlement ‘ExampleEntitlement123’.

I suppose I’m wondering is can I enter a critera in the Query Builder with
something like “[this].entitlement ID = entitlement.Primary ID†and it would
resolve it line by line.

Phew, I hope this is clearer!

Adrian

 

[KARL DEWEY]

You did not directly answer my question 'Are you using a form or are they
look-up fields?' but I glean that you have no forms at all and are working
directly in the tables.
That is a 'no-no' and further if you read some other threads you will see
that many in the know say not to use lookup fields in a table.
The prefered method of comunicating with tables is to use a query to feed a
form or report. You can use combos in the form.
Also it is best to set one-to-many relationships between the tables
selecting the Referential Integerity and Cascade Update options.
Once you have done that then search on 'Cascading Combos' for solution to
one combo setting criteria for the next combo.


--
Build a little, test a little.

Hi Karl,

Thanks for the reply.

The table structures are actually as follows:

NOTE: The Primary ID’s in tables are Auto numbers.

License:
-Primary ID
-Entitlement_Primary_ID (linked to Entitlement Table-> Primary_ID)
-Template ID
-Lock Code
-Code

Entitlement:
-Primary ID (linked to Entitlement-Template Table-> Entitlement_Primary_ID)
ALSO (linked to License Table-> Entitlement_Primary_ID)
-Entitlement ID {basically a unique textual representation of the Primary ID}
-Count
-Desc

Entitlement-Template:
-Primary ID
-Entitlement_Primary_ID (linked to Entitlement Table->Primary ID)
-Template_ID (linked to Template Table->Primary ID)

Template:
-Primary ID (linked to Entitlement-Template Table-> Template_ID )
-Desc
-Many More…

There is no direct link between ‘Template.Primary ID’ and
‘[License].Template ID’ – essentially the query I’m trying to write.

An Entitlement can contain one or more Templates, and a Template can exist
in one or more Entitlements. Every License issued must reference an
Entitlement ID, and a corresponding Template Id which has to be one of the
Templates that are associated with that entitlement.

In the License table I have populated the Entitlement_Primary_ID combo box
using a Lookup query that returns the Primary ID from the Entitlement table
(in fact it also returns the Textual entitlement ID and I display this using
the bound column and setting which columns to display so it’s more readable).

What I need is that once a user goes to fill in a row in the License table,
once they choose a particular Entitlement ID, the Template ID column in the
License table dynamically returns only the templates associated with the
entitlement ID that was entered for that specific row.

I have a query that returns the ‘Template ID’ and ‘Description’ from the
Template Table and ‘Entitlement ID’ and ‘Primary ID’ from the Entitlement
Table and this returns all the entitlements and all their corresponding
templates – but I don’t know how to make this specific to be dynamic.

I can put a condition of something like “Where [Entitlement].Entitlement
ID=’ExampleEntitlement123’â€, but this would mean the combo box for all rows
would only return the Templates for the entitlement ‘ExampleEntitlement123’.

I suppose I’m wondering is can I enter a critera in the Query Builder with
something like “[this].entitlement ID = entitlement.Primary ID†and it would
resolve it line by line.

Phew, I hope this is clearer!

Adrian

No it is not. Below I tried to put your words to show table structure. Let
me know if I got it correct or fix any errors.

License –
EntitlementID –
TemplateID -

Entitlement –
-ID – primary key
EntitlementID –

Template –
TemplateID –

Entitlement-Template –
EntitlementID –
TemplateID –

License has the same fields as Entitlement-Template so why need second table?

How do you use a combo to populate a field in a table? Are you using a form
or are they look-up fields?