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

>>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?

>>‘License’, which are populated using combo boxes, 
How do you use a combo to populate a field in a table?  Are you using a form 
or are they look-up fields?

-- 
Build a little, test a little.


"PhatAdo" wrote:

> 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
> 

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" wrote:

> >>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?
> 
> >>‘License’, which are populated using combo boxes, 
> How do you use a combo to populate a field in a table?  Are you using a form 
> or are they look-up fields?
> 
> -- 
> Build a little, test a little.
> 
> 
> "PhatAdo" wrote:
> 
> > 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
> > 

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.


"PhatAdo" wrote:

> 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" wrote:
> 
> > >>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?
> > 
> > >>‘License’, which are populated using combo boxes, 
> > How do you use a combo to populate a field in a table?  Are you using a form 
> > or are they look-up fields?
> > 
> > -- 
> > Build a little, test a little.
> > 
> > 
> > "PhatAdo" wrote:
> > 
> > > 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
> > >