loop query

[gillianleec]

I need to write a function (or nested query if that is easier) for a table with 39 fields: component where n=1 to 39. I want to compare each field against another database and include if criteria is met.


Where do I start??

Gillian

 

[Tranman]

Gillian,
Tell us a little more...are you trying to create a new table? Remove rows from your first table? Mark rows?

What sort of critera? Exact match? A formula? Some combination? Different criteria for different columns?

Do the tables share a common primary key (some way to identify corresponding rows)?

I just wrote a snippet for someone named Rosh that would probably do what you want with very few changes.

Tranman

 

[gillianleec]

Thanks in advance:
I am trying to create a query from which a report will be created. I need an exact match of test name (ex. ACBG). The table that I am comparing it to has one field that I should be able to find this test name in and another field in this table that tells me if this test is reportable. There is no common key between these tables.

Is this clearer?

Gillian

 

[Tranman]

Gillian,
Let's call the table that has the test name, TableA, and the other one, TableB.

You're saying that TableA has a column that is the test name, and TableB has a column that contains the test name plus some other info, and another column that tells you whether it's reportable or not. Is that correct? Or do you want to look for the test name in 39 different fields and report it if the name is in any of them?

I'm having a problem understanding the problem (and it isn't even Monday.

It would help if you could include some data from both tables along with a little explanation about the 39 fields.

Paul (Tranman)

 

[gillianleec]

I'm sorry this is confusing!! !!Management!!
TableA is a table with Group tests. These are hospital laboratory tests used for diagnosis, monitoring, etc... A group test can have any number of individual tests that help determine the final diagnosis. These individual tests are the ones in the 39 fields. Not all group tests have 39 individual tests.

TableB is where information on the individual tests is documented. Two of the fields that I am interested are the ID (name of test) and Reportable (yes/no flag to tell if there are results from this test).

I want to take a record from TableA:
Group Test=HEF. The individual tests in this record are Component_code0=ABCE, Component_code1=OOUI, etc. This are all fields in the record. I want to match each individual test to TableB.ID and check to see if TableB.Reportable=true.

If true, I want to return the value of TableA.Component_code0,... in a report.

so far, I have this.

Function test()
Dim intX As Integer
Dim mystring As String
Dim Mytext As String

For intX = 1 To 39
mystring = Me("component_code" & intX)
Mytext = Mytext + ";" & mystring
Next
End Function

The problem with this is it is not returning the field value. I feel like such a luddite!

Gillian

 

[MichaelRed]

Somewhat awkward to explain. A Union query for TableB (the 39 fields), including the "Reportable" field as the criteria (Where clause) in EACH subquery. Then a simple join of these results to TableA (GroupTests).

The Ubion Query would look (VERY BROADLY) like:

Select [Col1] as MySubTest from TableB Where [
Reportable] = True

Union All Select [Col2] as MySubTest from TableB Where [
Reportable] = True

[tab]-
[tab]-
[tab]-
;

This will (after a bit of gyration) produce a "single column" of all of the "Sub Tests" which are reportable. Join this to the "Main" test and you have the results.

I'm sure that there are other fields which must be of interest, and you will need to include them in one or the other of these.




Please use the ubiquitous {F1} to check the syntax for the Union query, I often get it a bit mixed up

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[gillianleec]

Thanks for this suggestion. I was not able to get it to work for me but I did find a solution if anyone else needs it.

In my query, I used an IIF statement and a nested subquery.


SELECT Group_Tests1.Group_test_id, IIf([component_code0] In (Select ID from [qselreportablestohis]),[component_code0]) AS code0, IIf([component_code1] In (Select ID from [qselreportablestohis]),[component_code1]) AS code1, IIf([component_code2] In (Select ID from [qselreportablestohis]),[component_code2]) AS code2, IIf([component_code3] In (Select ID from [qselreportablestohis]),[component_code3]) AS code3, IIf([component_code4] In (Select ID from [qselreportablestohis]),[component_code4]) AS code4

and so on for 0-39 fields.

Thanks for everyone's input.