Display select query value in list control while maintaining list

[SemperSalvus]

This is probably both a form and query issue, but I decided to post it here.

I am running MS Access 2003.

Here is my bottom line, since the explanation below is long, but thorough, in case you need to understand my issue better.

BOTTOM LINE: I need to display table values (Yellow and Green) in a list box as choices, but have either Yellow or Green selected as the value for the list box's table recordsource, based on a select query (which has only one record that matches).

I have a large table with several hundred fields for conducting facility inspections. Each field is a category or issue that could be found good or bad at a given facility. The user has a choice of Green/Yellow/Red and in some cases just Green/Yellow, depending on the category and its urgency to be corrected if found in a bad state. I will use a simple example of Housekeeping here and use the Green/Yellow options.

The inspections are conducted annually and the form I display to the user shows the info from the last inspection, completely filling out all info in all 300+ fields on the form (which are broken down by major category into tab control pages). This way, they can see if the issue was corrected or not and make changes to the data accordingly, which is a new record in the table for the current inspection (i.e. the previous inspection record does not get overwritten of course).

Everything works perfectly, but it takes about 1.5 minutes for the form to load once the user chooses a building they are inspecting. This is too long and is a pain for the inspector. I discovered that the culprit is the tons and tons of DLookups I used in code to fill out the value for each field. Here is one of the evil DLookups:

If 
DLookup("HISTORY_LIB_BUILDING_INSPECTIONS_QUERY.HOUSEKEEPING_TRASH", 
"HISTORY_LIB_BUILDING_INSPECTIONS_QUERY") <> "" Then
            Me.ctlHOUSEKEEPING_TRASH.Value = 
DLookup("HISTORY_LIB_BUILDING_INSPECTIONS_QUERY.HOUSEKEEPING_TRASH", 
"HISTORY_LIB_BUILDING_INSPECTIONS_QUERY")
            End If

In an effort to get rid of DLookup, I figured that my answer is going to be the rowsource property for each list box. Currently the rowsource property is a select query done in query builder that displays the 2 options to the



user, Green and Yellow. The table for that is a simple 2 column table with the ID being G for Green and Y for Yellow, bound to the ID and displaying the words Green and Yellow in the list control. It is named YellowGreen (go figure). I also have RedYellowGreen, RedGreen and another which is a special case of choices, to simply pull just what is needed to display in each unique list control, depending on the category. The recordsource for the control is the table of inspection records and the list box values get stored in the table as simply G or Y, for Green and Yellow respectively.

I am using a named query in the query object view of the mdb to select the last inspection performed, if it in fact exists. Most exist, so the result of this query is a single row of the 300+ columns of catergories. The tons of DLookups are pulling their info from this query. The name of that query is HISTORY_LIB_BUILDING_INSPECTIONS_QUERY.

The field in this example is named HOUSEKEEPING_TRASH.

Every attempt to join the YellowGreen table to the Select Query fails.

Here is one that I tried:

SELECT YellowGreen.[YellowGreen ID], YellowGreen.Description
FROM YellowGreen
WHERE (((YellowGreen.[YellowGreen ID]) In (SELECT HOUSEKEEPING_TRASH FROM 
HISTORY_LIB_BUILDING_INSPECTIONS_QUERY WHERE YellowGreen.[YellowGreen ID]= 
HISTORY_LIB_BUILDING_INSPECTIONS_QUERY.HOUSEKEEPING_TRASH)));

I am not sure if a UNION will help me or what. I have googled this and looked at examples in my Access books, all to no avail.

Attached is a crude printscreen of one of the tab pages.

Thanks in advance for your help.

vr
Bob

 

[MajP]

completely filling out all info in all 300+ fields

And the alarm bells go off. If you have 300+ fields your data is not normalized. You have a mess on your hands.

It should look something like this:

tblFacility
facilityID_pk
other facility fields

tblInspectionCategory
inspCatID_pk
inspCatDescription
inspCatType (RG, RYG, Other)
other category fields

tblInspection
inspID_pk
inspDate
facilityID_fk
other fields unique not an inspection of a facility such as person inspecting

jncTblInspection_InspCategory
inspID_fk
inspCatID_fk
inspRating (R,G,Y,something else)

The beauty of this is you can make some much simpler and nicer interfaces and managing the controls and categories is so much simpler.

If it was me. I would add a new inspection for a given facility. It would then add the 300 records to the junction table. I would simply have a continous form with my categories listed in textboxes (but locked). One click it turns green, next yellow, next red, back to white... (or if the type is RG it would skip the yellow).

 

[MajP]

Here is a demo.

http://www.4shared.com/dir/6086223/c26decf2/PartCatClass.html

select the inspection.mdb database and on the next form at the very bottom, select "download" (it is not real obvious)


Click the text boxes and change the status

The partsCatClass.mdb is very similar with many more features.

 

[PHV]

Have a look here:

http://r937.com/relational.html

http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SemperSalvus]

MajP: Sorry, but I do not need that kind of help. Please get back to my question. I do not mean to sound conceited, but I have been designing db's for 15 years and I normalize with the best of them (I would hope so at least). One of my weak areas is SQL though and querying outside of Access' QBE, hence my original post and question.

Maybe that is one thing I did not explain very well, but here we go. The 300+ fields are all categories. Yes! All categories. There are just that many issues to look at during a facility inspection. There are only a few lookup fields that relate to the primary keys of related tables. I am not going to break up these categories into subcategories which would make it all much worse. Everything you talked about I have already:

The inspectors and all of their personal info are in their own table.

The facilities and all data related to them are in their own table.

SOP's and info on them are in their own table.

Issues that are generated from finding Yellows and Reds during an inspection are kept in their own table.

The Corrective actions for fixing the issues are all located in their own table.

As I stated, the lookups to fill in the lists of G/Y/R, G/Y, and G/R (and another special case that does not conform to that) are each in their own table as well.

Even the inspection of the doors at a facility are in a separate related table, since a door alone has nearly 20 inspection categories and each facillity can have lots of doors.

To explain a little further, before someone says I am doing something too massive for Access, the backend data is in a separate mdb. Inspections are performed remotely with a Panasonic Toughbook in an offline status. We work with explosives and other materials sensitive to electromagnetic radiation, so we cannot have wireless and bluetooth enabled on the Toughbooks. The hazard class rating of the Toughbooks is also necessary in many of our production areas. Once the inspector is back in the office and online, an mdb can be ran which syncs up the data captured in batch mode with the Oracle tables on the server. It all works very sweet actaully, but I am building numerous other apps we need in order to go paperless and track things better. Those are being built in Flex 3 with AIR and I will eventually convert this facility inspection app into an AIR component. The Oracle tables along with the middle tier CF cfc's will pretty much remain the same.

Now, can anyone answer my question?

I had asked if I need to use a UNION, but I am now thinking that an INSERT INTO will somehow do what I need. Once the facility is chosen and the history query selects the last inspection performed on it, maybe I can insert that data directly into the inspection table since the history table is a download of the full Oracle table and all fields marry up perfectly with the inspection table which will hold the new inspection records. I delete all rows from that table after a successful sync is done because it eliminated the need to use a "new record flag" which my code would then have to be written to figure out what to send up to Oracle. It avoids duplicates being sent up and the inspector views a report of the table (a summary) when they sync, to ensure what they did is correct, in case for some strange reason something went wrong. The report is then simply just a dump of the entire table into the report. After the sync is complete, the history table is where all the historical inspection records are stored, as I said, which is a mirror of the full Oracle inspection table.

Thanks again for the help.

 

[fneily]

From your first post, for your listbox's rowsource, why not just use a value list?

It seems you have one inspection that references many categories and one category that can be referenced by many inspections. A many-to-many relationship. So a junction table must be created, ala MajP.

You state:
"The 300+ fields are all categories." and "a door alone has nearly 20 inspection categories"
So you have just a single category table?

"single row of the 300+ columns of catergories" This violates the first normal form. Duplicate column headings - category, category, etc. And what do you get when all the columns aren't filled in OR you need to add more categories? Variable length records. TABOO in relational database design. Violates the first normal form.
"field is a category or issue". Violation of first normal form.

"designing db's for 15 years and I normalize with the best of them". Ok.

 

[MajP]

Sorry if I belittled you, I did not mean to do that. At the same note you need to be willing to take advice and learn from this site. I learn everyday that I am own it from the pros like PHV, Aceman, Remou and the other MVPs.
I hear you that a facility has 300+ unique criteria, but that sure does not mean each criteria needs its own field. I will bet that the majority of the Access power programmers will support my table design. With that said you are probably too far down the road to unscrew what you have. Even if my oracle back end did not match this design, I would design it this way and write the code to synch it back up.

However, I must be completely missing something, but you are not suggesting that you would write 300 seperate rowsource queries? If each listbox is bound to tableX, and you return a history query, why not write to the table the values from the query?

 

[SemperSalvus]

Why are you guys insisting on changing the db structure? I cannot do that and have no time for it. Besides that, it is correct and relational despite what you think.

fneily: There are no variable length records! If the inspector finds no problem with a particular category, he leaves it alone and does nothing. He glosses over it and moves on to the next page of categories. So, what happens is they default to GREEN. Now a report can show which ones were found to be GREEN if I desire. Is this a crime?

Whatever.

Why does a large table pose a problem? It is a massive amount of data right from the start! But, they are all CHAR 1 though! They are G or Y or R! With only a few lookup fields to related tables. Can we just get past that! Is it a problem that there are thousands of inspection records too? We have over 2,000 buildings and they each get inspected annually. Access can still bring it all up in a grid in about 2 seconds though.

This whole thing, like I said, will get converted to Flex/AIR. What I need help on is to tweak this lookup issue in order keep a system that is about to go away in 3-6 months working efficiently.

I should not have to explain these kinds of design decisions on a question like this. I thought I was providing way too much info to begin with anyway (like I am going overboard on now!). My very first post spelled out my issue well enough for someone to try to help me out without preaching relational db design 101 to me.

MajP: I love this site and it has helped me many times in the past? I am not turning away advice. I just want my question discussed and answered.

By the way, your junction table example is exactly what I already have from the history table query. I have the inspection info, the categories and the values all together already. I do not have a separate table for an inspection. I saw no need since I already have tons of things broken out into separate tables, more tables than I even listed in my previous post. All the inspection info is inspector (pulled from a smart card and related to my personnel table), a second inspector that is with them (related to the personel table), inspection date (set to Now()), and facility number (related to facility table). That is it. There was no reason to not have this where it is with the inspection "results", which are all the G's, Y's and R's for each category. This info and the results were all considered as one inspection record, one piece of info to contain within a table! One building inspected; one date of inspection; by one main inspector and a second one; with all the results in their category field columns. If that is not one record of a single inspection performed, then what is?

Ahhh!

Can someone new please chime in to answer my original question?

 

[MajP]

Again I still do not get it. It seems pretty basic. You can return the correct history which is a single record of 300 columns.

I am using a named query in the query object view of the mdb to select the last inspection performed, if it in fact exists. Most exist, so the result of this query is a single row of the 300+ columns of catergories

So why can you not simply insert this data into the table that is bound to your form with all the listboxes? Either build the insert statement dynamically (because there is no way you can build a 300 filed insert statement) or use a recordset where you simply read each field in this history query and write to the forms table.

 

[SemperSalvus]

Well, I am at a loss for how to do that insert. It is something I am not used to doing. What is confusing me is that all the list box rowsources taken up by the tie in to the YellowGreen table to create its list of choices.

But, the control source is tied into the table which will hold the new inspection records. Like you were saying, I started to believe I needed to insert that historical record into the new inspection records table. But how?

Should I set up an append query? Will Access let me append from a query like that into a table? I would assume so, but it is something I have never done. I guess I would have to run that query during the On Open event, before a record is prepared for display, right?

Once that record is there, I will need to point to that record when the form comes up and I think I can handle that in Access. I already have a public string holding the facility number after it is chosen by the inspector on a pop-up. That pop-up helps me "lock-in" the facility number so they cannot accidentally change it while in the form (something I have had happen before from inspectors).

You are probably right that it is basic, but it must be something I am not used to doing or never had a need to do until now.

Thanks.

 

[PHV]

Access can't deal with Table/Query having more than 255 fields, period.

 

[fneily]

For anyone's reference:
From the founder, creator, namer, lord of normalization, Ted Codd and his work "A Relational Model of Data for Large Shared Data Banks", the first normal form excludes relation-valued attributes (tables within tables).
Categories can be listed in a table so should not be field names. Repetitive column headings. I'm out.

 

[SemperSalvus]

You are right. Sorry I exaggerated the amount, but I am actaully just shy of around 230 fields. I was just estimating to begin with. I knew it was a crapload of categories involved with the inspection.

All the more reason for me to convert to Flex/AIR within the next several months. This particular version will not grow any more, so I do not need to worry about the 255 limit.

Thanks.

 

[SemperSalvus]

fneily: As I said, I am not here to get an education on normalization. Who cares if I am in the first, second, or fifth normal form? With what I am doing for this app right now, I do not care.

But, I am shocked you did not refer to Dr. Codd by title and give him the respect he deserves. Shame on you. Were you close enough to him to be calling him Ted though? His real name was Edgar, but his nickname was Ted. I personally never met him, so I would still refer to him as Dr. Codd though.

Moot points all though, since I am not here to discuss his theory of normalization. I have a real world project to tweak and the app is what it is, up to this point.

By the way, I looked up everything about Dr. Codd online about 2 minutes ago. Does that make me an expert on him or normalization? No, and you making your statement does not make you one either, nor does it impress me, even if you learned it in a computer science class. Didn't what I state above sound kind of insane? Well it was, which is exactly my point about where you are trying to take this thread and my question.

Give me a break, because I am just a Safety Engineer with a background in a lot of programming (back then it was Basic, Fortran, Pascal, and C). I have done db design all my work time since safety has a lot to do with accident stats and those are obviously kept in databases.

I work with my IT folks and database manager to create the apps we need to conduct our day-to-day business. Our database manager has no problem with the way the table is designed, he knows of Dr. Codd, he knows all about the different normal forms, but he still understands what needs to be done, real world, to make something happen. I consult with him on all that I do regarding app design and creation. The mass of categories had to be in some table, so why not as part of the inspection itself? Under your design, I would still have a lookup to each category in the inspection table, because every category ends up either G, Y or R, so what do I gain? I would still have a ton of fields, though they would all be lookup fields. What is the point?

I am glad you are out of the thread, though thanks for sharing.

Is the thread war over now? I want to get back to what MajP was asking. MajP is trying to be constructive and help me get my head out of my ass on why I cannot get the list boxes to work the way I need them to.

By the way, in case someone wants to make a comment about it, my IT people here do not "do" Access. They work with Oracle and CF which is now expanding exponentially into Flex and AIR as front ends, where they used to use Powerbuilder and/or CF pages. So, no one here has been able to help me figure it out yet either.

 

[SemperSalvus]

Eureka! I just saw the problem with fneily. All your comments and slams now make perfect sense. Disclaimer: No offense to any teachers/professors out there. I had some great ones during undergrad and for my masters. They helped me out tremendously and I would not be where I am today without them.

fneily is listed as an instructor.

Please keep the theory in the classroom where it belongs. Like I said, I am doing something real world. We have time constraints and have to juggle quite a few things in order to get the job done. There is a big difference between theory and practice and most often the practical approach is the more prudent and efficient one.

There should be absolutely no more talk of normalization within this thread.

 

[MajP]

I do not want to argue with you cause you obviously are set in your ways, but I will say that fneily point is not merely academic. If this was designed as we have suggested this would be so much simpler in practice. The insert query to do what you are asking would take about 2 minutes to write plus you could harness the true power of Access the "bound continous form" and your form would only need one control instead of 230 controls and thus you would have been done a long time ago.

However, I understand you just want to get this done.

So if I understand correctly here are some possibilities.

My understanding:
1)You can return the history data in a query called:
HISTORY_LIB_BUILDING_INSPECTIONS_QUERY
2) This qry has 1 row and lots of fields
3) You want to prepopulate the new inspection with the data from the last inspection.
Is this goind into a temp table? Or which table gets prepopulated?
4) Your form is a bound form with a bunch of listboxes bound to the inspection categories (are they called SOPs)
4)Most of the data to populate the table comes from the history table, but some data comes from a variable or resides on a form.

The cleanest solution would simply build the insert query as a named query, but with so many fields that could get very tedious. Also that would require some detailed understanding of you tables. Since we are only talking one record I will use recordsets because I think this is generic.

1)Do not set the recordsource of the form
2)On the forms on load event prepopulate the table

Public Sub qryToTable(strQryName, strTblName)
  Dim rs1 As DAO.Recordset
  Dim rs2 As DAO.Recordset
  Dim fld1 As DAO.Field
  Dim fld2 As DAO.Field
  Set rs1 = CurrentDb.OpenRecordset(strQryName)
  Set rs2 = CurrentDb.OpenRecordset(strTblName)
  rs2.AddNew
  'read each fld in the qry and add to the record in the table
  For Each fld1 In rs1.Fields
     rs2.Fields(fld1.Name) = fld1.Value
    Debug.Print fld1.Name & " " & fld1.Value
  Next fld1
  'add your other other fields
  rs2.Fields("inspectionID") = someVariable
  rs2.Fields("date") = someOtherVariable
  rs2.fields("someOther") = anotherVariable
  rs2.Update
End Sub

3) Now set the recordsource of the form. This should be a query that returns just the record you are working on.

But here is the question. If you prepopulate the table of inspections how do you determine which value is a current value and which is a prepopulated value from last inspection? So when the inspector is halfway done his inspection how does he know what is complete and what is a prepop value?

Now to beat the dead horse just one more time. Even if we do not agree on normalization, a structure like you propose is as you see very difficult to work with. Carrying over the previous values for visibility would be real simple to do with the structure I proposed. You simply prepopulate the old values into the new records and on the same form you can see last values and current values therefore showing what still needs to get filled-in.
jncTblInspection_InspCategory
inspID_fk
inspCatID_fk
inspRating (R,G,Y,something else)
previousInspRating

With your design you may need to set the some property of the control to show that the value has been updated. (ie. change the color of the control or change the caption)

 

[MajP]

Here is another thing to think about for speed. I assume that you really do not have a listbox for each field, but I would strongly recommend against that. If you have not designed it this way already then I suggest doing it as follows.

1)Create your tabs
2)Figure out the max number of controsl that a tab will have.
3)Create that many controls (lets say it is 20)
4)Drag these controls onto the tab so that they "float" over the tab. As you click the tabs these controls appear on every page
5)Now dynamically load the recordsource of the controls as you change each page. Show and hide the any extra controls.

It will appear as if you have hundreds of controls, but you only have 20 or so. This will make it much easier to design and much faster to load.

Now that you have a manageable number of controls you could pair each listbox with a textbox. As you set the recordsource of the listboxes you could also populate the textbox with the historical data. So now on one form you can see the historical value and the current value (if entered)

 

[SemperSalvus]

MajP: Sorry, but I have been indisposed with other issues at work.

Getting back on task with my question, I think the code you provided is pretty much what I needed to see. I have yet to try it, but I think that will get me to where I need to go. I think the code for looping through each field name is the key that I was missing.

I like your idea about dynamically handling the info in each tab. However, the current setup still works because if the inspector does not have to look at something at a building, he/she leaves it alone and those categories default to GREEN. With over 2,000 facilities to track info on, it is too hard to know the subtle differences of each one in order to dynamically present what is pertinent.

I will test this, maybe even today, then post how it goes.

Thanks.

 

[MajP]

With over 2,000 facilities to track info on, it is too hard to know the subtle differences of each one in order to dynamically present what is pertinent.

Not exactly what I was getting at. Assume for your inspections criteria (SOPs?) fall into four categories.

Electric (10 fields)
Plumbing (8 fields)
Security (12 Fields)
Fire (15 Fields)

I could then make 4 tabs and put fifteen listboxes floating over the tab control. As I click on each tab it appears as if the listboxes are on each tab. However, I then add code to show only the number of desired listboxes as I tab (10,8,12,15). Finally I set the recordsources dynamically as I tab. So when I click on "Plumbing" I only show 8 of 15 lisboxes and I set the recordsource of the 8 listboxes to the the plumbing fields. In this case with 15 listboxes it appears as if I have 45 listboxes, and works just like I had 45 listboxes.

Therefore when the form loads and defaults to tab "Electric" only 10 listboxes of the 15 get recordsources/rowsources. This will load far faster then the original 45 all with recordsources and rowsources. In your case of 200+ controls this will be substantial as well as far, far easier to manage.