help with cascading combos (1 Viewer)

[munkeyroot]

Hi everyone hope you can help

my heads hurtin :S

i am having issue with a cascading drop down issue, i have done some simple ones so not new to it. however this one is taxing me.

i have a third party db (TP_DB) which gives me the majority of the information i need, i cannot touch this db as it is live data.

so i have created a query for it to work from.

i also have a table with Stock category codes which are also stored in the TP_DB and the definition of those codes - the definition is the data which is not provided in the TP_DB.

this Stock Category table also provides me with a departmental break down for the the Stock items. Each stock item has the potential to be in multiple categories. so each department has its own heading and against each stock item depending on category has a True or False.

so i have created a query for the categories. now i have added the relationships for the TP_DB query with the categories. so now i can see TP_DB and the Categories together in sweet harmony

here's the tricky bit: i setup a simple form with three combo boxes i can assign drop down no issues. the issue is
how do i tell the department combo to pick up the departments from the Joint query?
how do i tell the category combo to display all the TRUE categories because of the department i have picked?

i hope some one could shed some light totally brain dead lol :banghead:

i will upload a db too

cheers munk

 

[pbaldy]

The design isn't normalized. I'd have a categories table, a departments table, and then a many-to-many junction table where each "true" that you have is a record. Like:

1010...CCTV
1020...CCTV
...
2100...Access
2100...Intruder

You query against that, filtering on department.

 

[munkeyroot]

ahh thank Paul i'll give it a shot and i'll let you know how i get on pal

thank you mate

 

[munkeyroot]

Hi Paul, OK I've done what you said, if i understood you correctly, I've put the db here.

Cheers Munk

 

[pbaldy]

That looks about right, though you haven't populated the ID fields that the relationships expect to see. I'd have the ID's or the descriptions, not both.

 

[munkeyroot]

Hey paul, sorry i have just replied ive been off sick, thank you for the message fella been having a look at my DB again sorry if i sound thick im not sure what you mean, could you elaborate further
Cheers munk

 

[pbaldy]

You have relationships defined between tbl_Categories and tbl_Dep_Cat on Cat_ID, but Cat_ID is not populated in tbl_Dep_Cat. Same with departments.