Trying Again...Is this a DLookup case? (1 Viewer)

[Rich_Lovina]

Okay, on 14/9 I posted this problem and had a nil response...

Maybe I can rephrase what I'm trying to do.

In my MainTable I have a field where the text entered serves as the primary key in a related table (Non RI'd, because this fld may also be blank). My objective is to automate a series of queries which update a field depending upon the text data.

Here are 3 examples:
MainTable text:
e.g. 1 : Data Management Services
e.g. 2: Communications Control Centre
e.g. 3: Corporate Management

The related tables flds, where each of the above are unique (Primary Keyed) are:

1. Data Management Services. Fld 2 = DM
2. Communications Control Centre, Fld 2 = COM
3. Corporate Management, Fld 2 = CM


Corporate Data Communications

The function I require, preferably in a command button:

Whenever the word "Data" appears, update related fld to DM. Likewise for Corporate and Communications, etc.

This is because records are adding weekly by code, and then I wish to update the related codes, and analyse those results.

I hope this might be a bit clearer.
Thanks in advance....

 

[glynch]

I'm sorry but it is still not clear to me. The new data comes into which table? And needs to be updated in which table? I must be missing something because it seemed like you answered your own question.

Another question; are you updating values or appending records and if so where?

 

[Rich_Lovina]

Yes, you're right. I think that I've answered half my own question. But let me elaborate in case others have similar conceptual problems. The data comes into MainTable and appends c. 400 records weekly. Main has about 12 fields which have data appended, one of which is text called Sect1.

When that occurs I then want to automatically update the field related to Sect1 in its related table called Sect228.

I could do it at the end of the appending with a Command button running a query....a bit like a refresh, but am not sure of how to refresh or requery. I think the code is Docmd open query x.

Any help...

 

[Fornatian]

Rich, seems to me you need to

1. Set up a data table of words and associated codes
2. Run an update like query against each entry - possibly using dynamic queries to update the associated table automagically.

My own thoughts bring a contradiction to mind in that if a company was called Corporate Data Communications - which code would you adopt?

To avoid this you need to ensure the words which have a 'higher' priority are the adopted codes. Do this by running dynamic queries in reverse order of priority, that way the last queries and appends done will take the code of the highest priority word found. My method of doing this would be to create a form with a list box whose records I would loop to iterate the table - this way you can create an ordered list without delving into querydefs.

Not to complicate matters further, but you may need to create a 1 - M relationship because each company can possibly be involved in many sectors(?)

 

[Rich_Lovina]

Thanks Ian, you can see exactly what I want to do, and have conceptualised the problem precisely. They are not companies, but "Sub _Structures" but your example hits the point.

In the Corp Data Comms case, which may occur, I will rank subjectively by word frequency viz. I have fewest Comms tags, fewer Data tags and most Corp tags.

My code creation on the two other sub-structures uses the following:

Yr example would be CDC, but if already used, then CODC, if that's used CORDC.

Can you explain the dynamic query action first, a little?
Tks Rich

 

[Fornatian]

Right then, lets say your listbox is called lstWords and you is based on a table with the following fields...

1.Word
2.Association
3.Priority

What you need to do is something like this, this will not be entirely correct as I am doing it on the fly and am not that talented as to remember code straight off, (too much taxi licensing rotted my brain!)

'set a string var to hold the dynamic SQL
Dim strSQL as string
'set a counter
Dim i as integer
'loop the listbox
For i = 0 To Me.List0.ListCount - 1
'build the SQL dynamically from an example cut and pasted from the QBE grid
strSQL = "Your query " & Me.List0.ItemData(i) & "etc..."
'run the query
Docmd.RunSQL strSQL
Next i

If RunSQL is out of date please correct me someone.

Ensuring your listbox is correctly ordered then you should be able to run the prioritised query I mentioned in my previous post. unfortunately there is no such thing as wooly integrity so we can't do that!

 

[Rich_Lovina]

So lets come to terms with the defining the flds first, and will this be a separate table from Sect228?
viz. within Sect228, there are 4600 records but probably only 100 words to 'associate' and prioritise initially. The max no. of associations is 25.

Using my example, do I make a fld for the words to associate on?

e.g. in Table Sect228
Fld1 Data Management Services Fld2= Data Fld3=DM
e.g. 2: Fld1 Communications Control Centre Fld2= Communications Fld3=COM
e.g. 3: Fld1 Corporate Management Fld2 Corporate Fld3=CM

What will the priority fld contain? a number, such that in above Comms is a 1, data a 2 and corporate a 3?

Here's an actual example of the 1st 5 instances of "Data"

Sect1
A T O Datacorp
Administrative Data
Aeronautical Data Acquisition
Application Sppt & Data Mangmnt
Applications & Database Systems

Okay I then make a new form, and put all the fields in. I create a text box for Fld1, and then put the code in there, right?

 

[Fornatian]

Rich, please bear with me, will respond later today, must do some real work now!

 

[Rich_Lovina]

That's fine. I hv lots of other things happening with the data. Here's another aspect.

Code DM usually only is associated with two words that frequent; 'data' and 'records' or 'record'. Some of the other 25 codes may be tied to a wider no. of key words.

These associated words might be placed in another related table where DM becomes a primary key....I'm not sure.

 

[Fornatian]

Hey Rich, try this example, see if it fits your bill - hope so, it was a brain-ache

 

[Rich_Lovina]

Hey, Ian that's a little beauty! Just so the dummy doesn't mess it up, TbleCompanies will be my Sect228 right; and Tble Assns will be a new table. Then I go through ALL my tagged records and determine a strict priority order. Then when its all done throw your command button onto my conversion/update form.

Basically I think it fills the bill perfectly and does the dynamic query. Thanks greatly.

 

[Fornatian]

Rich, your assumptions are correct, although I did realise last night that there is no need to make a dynamic query because if you have an update query that looks at the listbox then by changing the row selected using:

Me.List0.Selected(0)=True

as per the example, then you can dynamically change the criteria and update value in a normal update query so instead of running the query in VBA you can run it with

Docmd.OpenQuery "UpdateQueryName"

This may add speed and efficiency to the running of the query because it will be pre-optimised.

Extra thought - you may also avoid overwriting the same record multiple times with different codes by adding an Is Null criteria to the update criteria.

Glad I could help, it's nice to know I can interpret needs(and of course to your explanatory skills!)

 

[Rich_Lovina]

Thanks, now I do the hard yards of prioritising the texts I'm looking at. Just one other;

If I have a group of words to prioritise will that work in this code?

e.g. If I decide that *Corporate Data* = Data = DM
and *Corporate Data Communications*=Communications =COM

Then I have another field in Sect228 which says if Fld2=<>" " update Fld_IT=yes, I think this can go in the same query as a second update, yes? (i.e. I've found before a QBE can update two flds at once, yes?

 

[Fornatian]

You'd have to try it. I think it will not work because the field won't have been recorded as updated yet therefore won't be able to recognise that it isn't empty.

It is possible to update two fields in synch but I don't think it is possible if the update value of one field is dependent on the update of the other having taken.

 

[Rich_Lovina]

Hi Ian, as I weed my way through 2,000 plus sets of texts, I find another criteria I need to put in place for some text.

e.g. The word 'business' may relate to a code BM (Business Manager) or BE (Business Executive), and I would like to differentiate usage according to the applicable salary field in another related table called POSCODE. Conceptually to me this sounds logically possible.

Perhaps I need an Iif expression somewhere in your testform, such that:

Iif text keyword is "Business", before coding it BM, run a test in table-field POSCODE!TO, and if To<$50,000 then code is BE, otherwise, BM. Can you see where and what code I would write to make this option happen?
Thanks in advance

 

[Fornatian]

This sounds like you could do it using a normal update query after the updates have been done, changing only the codes where the salary is >50000

 

[Rich_Lovina]

Thanks Ian. As I have c.25 queries running on code for the weekly import, I have a lot of pop-ups...."You are about to append, make table, delete, etc.

Did I read somehwere that these pop-ups can be turned off when running in code? What is the code for that?
Thanks, Rich.

 

[Fornatian]

Docmd.SetWarnings False - Turns warnings off

Important
Alway follow this with

Docmd.SetWarnings True

after the actions to ensure you are notified of any errors.

 

[Rich_Lovina]

This is possibly a wee problem....

This is probably fixable by test; excuse lazy English, but if a key word is "IT" as in many cases, I got to 2000 of 2600 text variations and found that I hadn't written "* IT *".

In the code you wrote, will I need to make allowances for these occurences?

basically, the created dictionary has thus far reduced the 2600 text variations to 360 unique words, so the slow. ploughing through is going to prove very useful.

Also, I found that where I absolutely must use more than one word for the associated code, your program is working perfectly.

Congrats again on a great bit of code....must have relevance to other Forum members.....

Better idiots leads to better systems......yippee!

 

[Fornatian]

Yes I would of thought you'd need to add a space after the "*" to exclude words wITh "IT" in them as per your example, unless you entered " IT " as the field value in the lookup table to take account of spaces.