create a select query in visual basic

[Guest]

I have a table that in Field 1 has A, B, and C data. There are numerous
records (rows) with either A, B, or C in this field. For each occurrence of
A, B, or C there is Field 2 that has the value C, D, E, or F. I want a
query that can look at each occurrence of data A, and if the value is only C,
return one occurrence of data A. I want the query to be able to go on to data
B and C and test for the condition. I see no way to do this without writing
vb programming which I do not know how to do. Thanks for any help that can
be provided.

 

[Guest]

I don't quite understand the rules. Can you be a little more specific and
perhaps give an example of the expected results.

 

[Guest]

There are two tables involved. Table 1 has a column/field that is a document
number which is a primary key. This table consists of an effective date and
description. I call it my description table. There is only one occurrence of
each record in this table. Table 1 is linked to, what I call, an input
table. Call it Table 2. There is a relationship between these two tables,
and they are connected/joined by the document number. Table 2, for example,
has a column/field for aircraft number 1 thru 15. Each aircraft, 1 thru 15,
has a field with the same document number as mentioned above. Each aircraft
1 thru 15 has a record for each document number in Table 1.

Table 2 also has a status field that shows the status of the document
number. The statuses are "T"erminated, "O"pen, N/A, and "R"epetitive. I
want to be able to do a query of each document number and be able to
displaythe document number when aircraft 1 thru 15 are all in the status T.
I don't want to display the document number when aircraft 1 thru 15 are all
N/A (Not applicable). For each aircraft 1 thru 15 the status could be a
combination of O, T, R, and N/A. I need to some how read all the similar
document numbers for aircraft 1 thru 15, one at a time. (I have roughly 700
document numbers and each document number gets assigned to aircraft 1 thru
15). If the status is either T or N/A, but not just N/A, then I want the
document number to display. This way I know all of my 15 aircraft have
complied with that particular document. Hope this clears it up. Thanks

Airplane Fixer

 

[Guest]

Sorry to be so long getting back. Yesterday was a fire drill. One of the
managers suddenly realized (like I haven't been saying so for 6 months) that
his labor report was wrong.

Anyway, As I understand it, each document pretains to all 15 aircraft and if
the status of that document in all 15 aircraft records are N/A, you do not
want them included; however, if any one of them is T, you want only those
with T included, or is it you want all 15 for that document?

This is an interesting, but not simple, problem.

 

[Guest]

If the status is N/A (not applicable) for all aircraft I do not want to see
the document number. If the status is N/A and/or T, I want the document
number displayed. If there is 1 aircraft that is still Open and the rest are
either T or N/A,I do not want to see the document number. Only when the
entire 15 aircraft have had the document incorporated do I want to see the
document number. Thanks

Airplane Fixer

 

[Guest]

I think this will take a VBA function to get what you want. Because it will
have to use some DCounts in the function, it will make the query run slowly,
but I can't think of another way to do it.

Build your query based on your documents table. Use a Calculated field to
call the function that will check the status fields of the planes table and
return True if the document should be included and false if it is not. Then
filter the field on True.

The function will not care whether there are 1 or 15 or 35 planes.

Function CheckPlaneStatus(strDocNo As String) As Boolean
Dim lngPlaneCount

CheckPlaneStatus = True

'count the planes
lgnPlanceCount = DCount("*","PlaneTableName")

'Are All N/A
If DCount("*","PlaneTableName","[Status] = 'N/A'") = lngPlaneCount Then
CheckPlaneStatus = False
End If

'Are any open
If DCount("*","PlaneTableName","[Status] = 'O'") <> 0 Then
CheckPlaneStatus = False
End If

 

[Guest]

Thanks, I'm sure there are some other things I need to do to get this to work
but I think that takes care of the basic programming.

I think this will take a VBA function to get what you want. Because it will
have to use some DCounts in the function, it will make the query run slowly,
but I can't think of another way to do it.

Build your query based on your documents table. Use a Calculated field to
call the function that will check the status fields of the planes table and
return True if the document should be included and false if it is not. Then
filter the field on True.

The function will not care whether there are 1 or 15 or 35 planes.

Function CheckPlaneStatus(strDocNo As String) As Boolean
Dim lngPlaneCount

CheckPlaneStatus = True

'count the planes
lgnPlanceCount = DCount("*","PlaneTableName")

'Are All N/A
If DCount("*","PlaneTableName","[Status] = 'N/A'") = lngPlaneCount Then
CheckPlaneStatus = False
End If

'Are any open
If DCount("*","PlaneTableName","[Status] = 'O'") <> 0 Then
CheckPlaneStatus = False
End If

If the status is N/A (not applicable) for all aircraft I do not want to see
the document number. If the status is N/A and/or T, I want the document
number displayed. If there is 1 aircraft that is still Open and the rest are
either T or N/A,I do not want to see the document number. Only when the
entire 15 aircraft have had the document incorporated do I want to see the
document number. Thanks

Airplane Fixer