ListBox query, 3 linked tables (1 Viewer)

[gunslingor]

Here are the three linked tables, the big one is the control source of the main form:

Here is the control I am trying to get to work on the main form, a 2 column listbox:

Here is the non-functional query:
SELECT CYBER_ASSET_TFEs.TFE_NUM, TFEs.TFE_Name FROM TFEs INNER JOIN CYBER_ASSET_TFEs ON TFEs.TFE_Num = CYBER_ASSET_TFEs.TFE_NUM;

I have always had issues in my DB development activities under the following condition: any time I have a big form who's control source is the main big table... then I try to add a control who's rowsource is a child table. In the above condition, even the child has a child!!! So I think it best to seek help.

what I am trying to do in psuedo code:
a)SELECT {all TFE numbers} FROM CYBER_ASSET_TFEs WHERE CYBER_ASSET_TFEs.key = me.key.value
b) set the first column in the list box equal to the above query
c) SELECT {all TFE names} that match any TFEs in column 1 and show these in column 2.

Seems pretty simple right? Yet I can't grasp the concept of what the SQL really needs to look like and why.

Thanks PEEPERS

 

[gunslingor]

I think I figured it out, kind of complicated and it slows the loading time down 4 fold... but it seems to be working. Here is the on load code:

Me.TFEs.RowSource = "SELECT TFEs.TFE_Num, TFEs.TFE_Name FROM TFEs INNER JOIN" & _
"(CYBER_ASSETS INNER JOIN CYBER_ASSET_TFEs ON CYBER_ASSETS.key = CYBER_ASSET_TFEs.key)" & _
" ON TFEs.TFE_Num = CYBER_ASSET_TFEs.TFE_NUM WHERE (((CYBER_ASSET_TFEs.key) =1))" & _
" ORDER BY TFEs.TFE_Num"

Here is the on change code (I'm using a list box to cycle records, so this is in the on click event of the listbox; if your using record navition buttons I'd probably use the forms on record change event instead):

Me.TFEs.RowSource = "SELECT TFEs.TFE_Num, TFEs.TFE_Name FROM TFEs INNER JOIN" & _
"(CYBER_ASSETS INNER JOIN CYBER_ASSET_TFEs ON CYBER_ASSETS.key = CYBER_ASSET_TFEs.key)" & _
" ON TFEs.TFE_Num = CYBER_ASSET_TFEs.TFE_NUM WHERE (((CYBER_ASSET_TFEs.key) = " & Me.key.Value & "))" & _
" ORDER BY TFEs.TFE_Num"

Let me know... whatever comments you have to offer, as I do not really understand what I did correctly.

 

[RainLover]

Capitalisation, or BLOCK letter for field names is a real pain. It is difficult to read.

Any Lookup, List Box or Combo Box should get its data from a separate stand alone Table.

You have two primary keys in the one table. Not the best approach. Try using AutoNumber as your Standard Primary key in all tables. A primary key should never be seen by the end user.

 

[gunslingor]

Capitalisation, or BLOCK letter for field names is a real pain. It is difficult to read.

Any Lookup, List Box or Combo Box should get its data from a separate stand alone Table.

You have two primary keys in the one table. Not the best approach. Try using AutoNumber as your Standard Primary key in all tables. A primary key should never be seen by the end user.

Regarding the capital letters, tell me about it:
http://www.access-programmers.co.uk/forums/showthread.php?p=1223693#post1223693

Regarding the stand alone table comment:
Does that mean your suggesting I use a subform for what I am trying to do? You understand why the data, as is, needs to be in 2 tables right? I can't image what other solution you could be proposing... I would think what I am trying to do is pretty standard.

Regarding the primary key comment:
One of the advantages of not using an autonumber, instead using a field that will always be unique, as the primary key is that you are guaranteed to maintain uniqueness of that field. Like if you use MAC address as the primary key, then people will absolutely under no circumstances be allowed to enter a MAC address twice. Anyway, I could have assigned a key to the TFE table and linked it to the CYBER_ASSETS_TFEs instead of using the TFE number, but I really don't think it would make much of a difference in this case.

Anyway, what I did above seems to have worked, the main form just loads slow now. I'll never understand why a front end with about 1000-2000 lines of code ends up being 30MB... seems rediculously large for such a tiny app and I think this is why it loads slow... it went up by 2MB when I added the aforementioned control, seems like poor compiling to me.

 

[RainLover]

Regarding the stand alone table comment:
Does that mean your suggesting I use a subform for what I am trying to do? You understand why the data, as is, needs to be in 2 tables right? I can't image what other solution you could be proposing... I would think what I am trying to do is pretty standard

As an example I will use Cities.

A typical place to use them is in Private and Business Addresses. I would have a Table that lists Cities so when I need a Value I get it from that Table. I would in Fact be storing the City Table's Primary key in the Address where it wpuld become a Foreign Key. I would even add to that City Table the State and Postal Code. So by using the Primary key I am actually storing three pieces of data.

Regarding the primary key comment:
One of the advantages of not using an autonumber, instead using a field that will always be unique, as the primary key is that you are guaranteed to maintain uniqueness of that field. Like if you use MAC address as the primary key, then people will absolutely under no circumstances be allowed to enter a MAC address twice. Anyway, I could have assigned a key to the TFE table and linked it to the CYBER_ASSETS_TFEs instead of using the TFE number, but I really don't think it would make much of a difference in this case.

I cannot say what you have done is wrong, however we all need to adopt Standards. I and most other experienced writers always use Autonumber. Time and Experience may teach you the advantages. Until then do what you feel is correct. Remember also One Number is faster than two Texts.

Anyway, what I did above seems to have worked, the main form just loads slow now. I'll never understand why a front end with about 1000-2000 lines of code ends up being 30MB... seems rediculously large for such a tiny app and I think this is why it loads slow... it went up by 2MB when I added the aforementioned control, seems like poor compiling to me.

You need to Compile your Code first. Then do a Compact and Repair. If this does not reduce the size then you should look at doing a Decompile. Always back up before doing thing like this.

Your Database should become smaller and a little faster.

I presume you have split the Database.

 

[gunslingor]

As an example I will use Cities.

A typical place to use them is in Private and Business Addresses. I would have a Table that lists Cities so when I need a Value I get it from that Table. I would in Fact be storing the City Table's Primary key in the Address where it wpuld become a Foreign Key. I would even add to that City Table the State and Postal Code. So by using the Primary key I am actually storing three pieces of data.

Yes, but as this example relates to my situation, you'd have to take it a step further... you'd have a table called "People" that relates to the table "Addresses" (1 person can have multiple addresses) then Addresses would relate to the cities_states table (except in my situation one address could have multiple cities).

Remember also One Number is faster than two Texts.

That is certainly good to know, i didn't really anticipate that but I suspect you are correct. The problem with using the autonumber as the P key in the TFEs table is that the ame TFE could be entered multiple times, I'd have to code search and compare for each entry... I'll double check, but I don't think access has a UNIQUE property for fields.

You need to Compile your Code first. Then do a Compact and Repair. If this does not reduce the size then you should look at doing a Decompile. Always back up before doing thing like this.

Your Database should become smaller and a little faster.

I presume you have split the Database.

Yes, the DB is split. Honestly, I didn't know how access actually compiles the code. I though maybe it compiled each time I saved or perhaps all the code is compiled at runtime... I didn't know you had a control to compile, and I've been coding in access for years.... I'll play with it.

Thanks!

 

[gunslingor]

I decompiled/recompiled my code using these instructions, second post:
http://stackoverflow.com/questions/3266542/ms-access-how-to-decompile-and-recompile

The original database size was 27.5 MB, now its 25.9 MB... helped a little I guess. And it did get faster, it used to take 2-4 seconds for the main form to load, now it seems to take 1-2 seconds.

Its not perfect, but thanks for the suggestion... it did certainly help!

 

[RainLover]

You can make any field unique. It does not have to be the Primary key to do that.

In the Table properties set the index to Yes (No Duplicates)

BTW you can also join two fields as an index and make that unique. So you could have John Smith and Mary Smith and John Brown and Mary Brown but only once. You cannot enter John Smith twice.