Check Boxes Queries

[MKH2001]

I have a form that the user is required to check for each identified defect they identify.
If the box is checked = there is a defect
If not no defect found.

What I want is to filter out all identified defects from the table (there are 23 possible checkboxes to tick and multiple defects can be found) and identify them in a query so that I can produce a report of all the faults.

I also need a method to identify that each defect has been repaired and therefore doesnt get pulled out in the report again.

 

[MKH2001]

Have tried to create each query as seperate an run as all from a control button (sorry realize I am not too good at explaining these things =/)

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click
Dim DB As Database
Dim rs As Recordset
Dim RecordCount As Integer

Set DB = CurrentDb

Dim stDocName As String

stDocName = "qry_SLVFCM"
DoCmd.OpenQuery stDocName, acNormal, acEdit
MsgBox ("Query SLVFCM done")
stDocName = "qry_SLVBFC"
DoCmd.OpenQuery stDocName, acNormal, acEdit
MsgBox ("Query SLVBFC done")
stDocName = "qry_SLVBCO"
DoCmd.OpenQuery stDocName, acNormal, acEdit
MsgBox ("Query SLVBCO done")
stDocName = "qry_SLVSCB"
DoCmd.OpenQuery stDocName, acNormal, acEdit
MsgBox ("Query SLVSCB done")
stDocName = "qry_SLVCCC"
DoCmd.OpenQuery stDocName, acNormal, acEdit
MsgBox ("Query SLVCCC done")

Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Sub

Example of individual query

INSERT INTO tbl_Alldefects ( AnnualServInspNo, UnitID, SLVBDL, SLVBDL_Repaireddate )
SELECT tbl_AnnualServInsp.AnnualServInspNo, tbl_AnnualServInsp.UnitID, tbl_AnnualServInsp.SLVBDL, tbl_AnnualServInsp.SLVBDL_Repaireddate
FROM tbl_AnnualServInsp
WHERE (((tbl_AnnualServInsp.SLVBDL)=-1) AND ((tbl_AnnualServInsp.SLVBDL_Repaireddate) Is Null));

As soon as the query hits another line where the value of the checkbox is ticked, it alters the value to not checked.

The idea is to return only those fields where the defect is checked.

I am not sure the easiest method of doing this....
I think I am probably barking up the wrong tree, but I am an access newbie.

Any ideas?

 

[MKH2001]

Have tried to create each query as seperate an run as all from a control button (sorry realize I am not too good at explaining these things =/)

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click

Dim stDocName As String

stDocName = "qry_SLVFCM"
DoCmd.OpenQuery stDocName, acNormal, acEdit
MsgBox ("Query SLVFCM done")
stDocName = "qry_SLVBFC"
DoCmd.OpenQuery stDocName, acNormal, acEdit
MsgBox ("Query SLVBFC done")
stDocName = "qry_SLVBCO"
DoCmd.OpenQuery stDocName, acNormal, acEdit
MsgBox ("Query SLVBCO done")
stDocName = "qry_SLVSCB"
DoCmd.OpenQuery stDocName, acNormal, acEdit
MsgBox ("Query SLVSCB done")
stDocName = "qry_SLVCCC"
DoCmd.OpenQuery stDocName, acNormal, acEdit
MsgBox ("Query SLVCCC done")

Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Sub

Example of individual query

INSERT INTO tbl_Alldefects ( AnnualServInspNo, UnitID, SLVBDL, SLVBDL_Repaireddate )
SELECT tbl_AnnualServInsp.AnnualServInspNo, tbl_AnnualServInsp.UnitID, tbl_AnnualServInsp.SLVBDL, tbl_AnnualServInsp.SLVBDL_Repaireddate
FROM tbl_AnnualServInsp
WHERE (((tbl_AnnualServInsp.SLVBDL)=-1) AND ((tbl_AnnualServInsp.SLVBDL_Repaireddate) Is Null));

As soon as the query hits another line where the value of the checkbox is ticked, it alters the value to not checked.

The idea is to return only those fields where the defect is checked.

I am not sure the easiest method of doing this....
I think I am probably barking up the wrong tree, but I am an access newbie.

Any ideas?

 

[lespaul]

could you tell us about your table instead of your query? What is the structure? What are you identifing defects of? Can you provide some sample data and what results you expect from that data?


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[MKH2001]

Tried to link pic of the table in the original post

http://www.angelfire.com/extreme/Sorcery/images/DefectTable.jpg

Should show the fields.

What I have is multiple types of defects identified on an individual service inspection.

What I want to do is report only those where the defect field is checked.
Ideally I would be able to pull each defect out so that they become row in their own table with a unique number so that I can keep tabs on it and put in a resolution etc.

Any assistance with this would be greatly appreciated.

 

[lespaul]

don't see anything on that page but the Angel Fire logo (is that Angel Fire, NM?)

So is your table that tracks the defects set up like:

InspectionID  Defect1   Defect2   Defect3  Defect4  ad nauseum
1               F          F          T       F
2               T          F          F       T

and you want to transform it to:

DefectID   InspectionID  DefectType  
1              1               Defect3
2              2               Defect1
3              2               Defect4

SELECT InspectionID, 'Defect1' As DefectType FROM TableName WHERE Defect1
UNION
SELECT InspectionID, 'Defect2' FROM TableName WHERE Defect2
UNION
SELECT InspectionID, 'Defect3' FROM TableName WHERE Defect3
etc..

Now change that to a Make Table Query like:

INSERT INTO NewTable (SELECT A.InspectionID, A.DefectType FROM
(SELECT InspectionID, 'Defect1' As DefectType FROM TableName WHERE Defect1
UNION
SELECT InspectionID, 'Defect2' FROM TableName WHERE Defect2
UNION
SELECT InspectionID, 'Defect3' FROM TableName WHERE Defect3
etc..
) As A

leslie

 

[MKH2001]

Aye,

Thats the idea of what I want it to do....
However, the above code doesn't seem to identify that I only want to select the defect when the condition is true (ie checked=-1)

It will be a continous process that the existing table will be used for the import of data and i need to pull just the defects that are checked into their own row in a new table.

SELECT InspectionID, 'SLVFCM' As DefectType FROM Frm_AVServInspect WHERE SLVFCM =-1
UNION
SELECT InspectionID, 'SLVBFC' FROM Frm_AVServInspect WHERE SLVBFC =-1
UNION
SELECT InspectionID, 'SLVBCO' FROM Frm_AVServInspect WHERE SLVBCO =-1
etc..

The other alternative is to make some sort of even for when the checkbox is clicked?

 

[lespaul]

I'm confused, what does a form have to do with any of it? You have information that comes from some other source, you want to take that information and normalize it. Write a query that uses the SOURCE of the information in the FROM clause. You will get a result set that you can then use to create a make table query (or append query if you are going to continually run this process).

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[MKH2001]

Got the 1st part of the code working.

SELECT AnnualServInspNo, 'SLVFCM' As DefectType FROM tbl_AnnualServInsp WHERE SLVFCM =-1
UNION SELECT AnnualServInspNo, 'SLVBFC' FROM tbl_AnnualServInsp WHERE SLVBFC =-1
UNION SELECT AnnualServInspNo, 'SLVBCO' FROM tbl_AnnualServInsp WHERE SLVBCO =-1
UNION SELECT AnnualServInspNo, 'SLVSCB' FROM tbl_AnnualServInsp WHERE SLVSCB =-1
UNION SELECT AnnualServInspNo, 'SLVCCC' FROM tbl_AnnualServInsp WHERE SLVCCC =-1
UNION SELECT AnnualServInspNo, 'SLVMDD' FROM tbl_AnnualServInsp WHERE SLVMDD =-1
UNION SELECT AnnualServInspNo, 'SLVBDL' FROM tbl_AnnualServInsp WHERE SLVBDL =-1
UNION SELECT AnnualServInspNo, 'SLVCDR' FROM tbl_AnnualServInsp WHERE SLVCDR =-1
UNION SELECT AnnualServInspNo, 'SLVODR' FROM tbl_AnnualServInsp WHERE SLVODR =-1
UNION SELECT AnnualServInspNo, 'SLVBDC' FROM tbl_AnnualServInsp WHERE SLVBDC =-1
UNION SELECT AnnualServInspNo, 'SLVDAC' FROM tbl_AnnualServInsp WHERE SLVDAC =-1
UNION SELECT AnnualServInspNo, 'SLVCSG' FROM tbl_AnnualServInsp WHERE SLVCSG =-1
UNION SELECT AnnualServInspNo, 'SLVCSS' FROM tbl_AnnualServInsp WHERE SLVCSS =-1
UNION SELECT AnnualServInspNo, 'SLVCCB' FROM tbl_AnnualServInsp WHERE SLVCCB =-1
UNION SELECT AnnualServInspNo, 'SLVCBP' FROM tbl_AnnualServInsp WHERE SLVCBP =-1
UNION SELECT AnnualServInspNo, 'SLVHPR' FROM tbl_AnnualServInsp WHERE SLVHPR =-1
UNION SELECT AnnualServInspNo, 'SLVMPP' FROM tbl_AnnualServInsp WHERE SLVMPP =-1
UNION SELECT AnnualServInspNo, 'SLVBLY' FROM tbl_AnnualServInsp WHERE SLVBLY =-1
UNION SELECT AnnualServInspNo, 'SLVMLB' FROM tbl_AnnualServInsp WHERE SLVMLB =-1
UNION SELECT AnnualServInspNo, 'SLVBLB' FROM tbl_AnnualServInsp WHERE SLVBLB =-1
UNION SELECT AnnualServInspNo, 'SLVFFP' FROM tbl_AnnualServInsp WHERE SLVFFP =-1
UNION SELECT AnnualServInspNo, 'SLVRRD' FROM tbl_AnnualServInsp WHERE SLVRRD =-1
UNION SELECT AnnualServInspNo, 'SLVNCR' FROM tbl_AnnualServInsp WHERE SLVNCR =-1;

But havent been able to get it to create a table with its own key yet.
When try the code above

INSERT INTO NewTable (SELECT A.InspectionID, A.DefectType FROM
(SELECT AnnualServInspNo, 'SLVFCM' As DefectType FROM tbl_AnnualServInsp WHERE SLVFCM =-1
UNION SELECT AnnualServInspNo, 'SLVBFC' FROM tbl_AnnualServInsp WHERE SLVBFC =-1
UNION SELECT AnnualServInspNo, 'SLVBCO' FROM tbl_AnnualServInsp WHERE SLVBCO =-1
UNION SELECT AnnualServInspNo, 'SLVSCB' FROM tbl_AnnualServInsp WHERE SLVSCB =-1
UNION SELECT AnnualServInspNo, 'SLVCCC' FROM tbl_AnnualServInsp WHERE SLVCCC =-1
UNION SELECT AnnualServInspNo, 'SLVMDD' FROM tbl_AnnualServInsp WHERE SLVMDD =-1
UNION SELECT AnnualServInspNo, 'SLVBDL' FROM tbl_AnnualServInsp WHERE SLVBDL =-1
UNION SELECT AnnualServInspNo, 'SLVCDR' FROM tbl_AnnualServInsp WHERE SLVCDR =-1
UNION SELECT AnnualServInspNo, 'SLVODR' FROM tbl_AnnualServInsp WHERE SLVODR =-1
UNION SELECT AnnualServInspNo, 'SLVBDC' FROM tbl_AnnualServInsp WHERE SLVBDC =-1
UNION SELECT AnnualServInspNo, 'SLVDAC' FROM tbl_AnnualServInsp WHERE SLVDAC =-1
UNION SELECT AnnualServInspNo, 'SLVCSG' FROM tbl_AnnualServInsp WHERE SLVCSG =-1
UNION SELECT AnnualServInspNo, 'SLVCSS' FROM tbl_AnnualServInsp WHERE SLVCSS =-1
UNION SELECT AnnualServInspNo, 'SLVCCB' FROM tbl_AnnualServInsp WHERE SLVCCB =-1
UNION SELECT AnnualServInspNo, 'SLVCBP' FROM tbl_AnnualServInsp WHERE SLVCBP =-1
UNION SELECT AnnualServInspNo, 'SLVHPR' FROM tbl_AnnualServInsp WHERE SLVHPR =-1
UNION SELECT AnnualServInspNo, 'SLVMPP' FROM tbl_AnnualServInsp WHERE SLVMPP =-1
UNION SELECT AnnualServInspNo, 'SLVBLY' FROM tbl_AnnualServInsp WHERE SLVBLY =-1
UNION SELECT AnnualServInspNo, 'SLVMLB' FROM tbl_AnnualServInsp WHERE SLVMLB =-1
UNION SELECT AnnualServInspNo, 'SLVBLB' FROM tbl_AnnualServInsp WHERE SLVBLB =-1
UNION SELECT AnnualServInspNo, 'SLVFFP' FROM tbl_AnnualServInsp WHERE SLVFFP =-1
UNION SELECT AnnualServInspNo, 'SLVRRD' FROM tbl_AnnualServInsp WHERE SLVRRD =-1
UNION SELECT AnnualServInspNo, 'SLVNCR' FROM tbl_AnnualServInsp WHERE SLVNCR =-1;
) As A

I get a syntax error in Input to statement.

Ideally yes after I have created the original table, it will need to be an append query.
I will also need some sort of additional field in the original table that will update to say that that record has been exported.
Otherwise each time the query is run it will continue to export those records that have already been tranfered.

The above is one possibility.

Another thing I was thinking of is:
My problem is that there are 138000 odd assets each being inspected once a year, with the possibility of 23 individual defects on each.

As such the query itself is going to be quite a burden.

I was thinking of doing some sort of on_click event on the input form so that each when it is clicked to save the data the event runs the append query straight away... just for the details on the input form.

Sound plausible?

 

[PHV]

I get a syntax error
Get rid of the first (

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MKH2001]

Now get syntax error in from clause =(

 

[PHV]

Get rid of the ; in the last SELECT instruction.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MKH2001]

Nope that didn't work =(

INSERT INTO NewTable SELECT A.InspectionID, A.UnitID, A.DefectType FROM 
(SELECT AnnualServInspNo, UnitID, 'SLVFCM' As DefectType FROM tbl_AnnualServInsp WHERE SLVFCM =-1 and Exported = 0
UNION SELECT AnnualServInspNo, UnitID, 'SLVBFC' FROM tbl_AnnualServInsp WHERE SLVBFC =-1and Exported = 0
UNION SELECT AnnualServInspNo,  UnitID, 'SLVBCO' FROM tbl_AnnualServInsp WHERE SLVBCO =-1and Exported = 0
UNION SELECT AnnualServInspNo, UnitID, 'SLVSCB' FROM tbl_AnnualServInsp WHERE SLVSCB =-1and Exported = 0
UNION SELECT AnnualServInspNo,  UnitID,'SLVCCC' FROM tbl_AnnualServInsp WHERE SLVCCC =-1and Exported = 0
UNION SELECT AnnualServInspNo,  UnitID,'SLVMDD' FROM tbl_AnnualServInsp WHERE SLVMDD =-1and Exported = 0
UNION SELECT AnnualServInspNo,  UnitID,'SLVBDL' FROM tbl_AnnualServInsp WHERE SLVBDL =-1and Exported = 0
UNION SELECT AnnualServInspNo,  UnitID,'SLVCDR' FROM tbl_AnnualServInsp WHERE SLVCDR =-1and Exported = 0
UNION SELECT AnnualServInspNo,  UnitID,'SLVODR' FROM tbl_AnnualServInsp WHERE SLVODR =-1and Exported = 0
UNION SELECT AnnualServInspNo,  UnitID,'SLVBDC' FROM tbl_AnnualServInsp WHERE SLVBDC =-1and Exported = 0
UNION SELECT AnnualServInspNo,  UnitID,'SLVDAC' FROM tbl_AnnualServInsp WHERE SLVDAC =-1and Exported = 0
UNION SELECT AnnualServInspNo,  UnitID,'SLVCSG' FROM tbl_AnnualServInsp WHERE SLVCSG =-1and Exported = 0
UNION SELECT AnnualServInspNo,  UnitID,'SLVCSS' FROM tbl_AnnualServInsp WHERE SLVCSS =-1and Exported = 0
UNION SELECT AnnualServInspNo,  UnitID,'SLVCCB' FROM tbl_AnnualServInsp WHERE SLVCCB =-1and Exported = 0
UNION SELECT AnnualServInspNo,  UnitID,'SLVCBP' FROM tbl_AnnualServInsp WHERE SLVCBP =-1and Exported = 0
UNION SELECT AnnualServInspNo,  UnitID,'SLVHPR' FROM tbl_AnnualServInsp WHERE SLVHPR =-1and Exported = 0
UNION SELECT AnnualServInspNo,  UnitID,'SLVMPP' FROM tbl_AnnualServInsp WHERE SLVMPP =-1and Exported = 0
UNION SELECT AnnualServInspNo,  UnitID,'SLVBLY' FROM tbl_AnnualServInsp WHERE SLVBLY =-1and Exported = 0
UNION SELECT AnnualServInspNo,  UnitID,'SLVMLB' FROM tbl_AnnualServInsp WHERE SLVMLB =-1and Exported = 0
UNION SELECT AnnualServInspNo, UnitID,'SLVBLB' FROM tbl_AnnualServInsp WHERE SLVBLB =-1 and Exported = 0
UNION SELECT AnnualServInspNo,  UnitID,'SLVFFP' FROM tbl_AnnualServInsp WHERE SLVFFP =-1 and Exported = 0
UNION SELECT AnnualServInspNo,  UnitID,'SLVRRD' FROM tbl_AnnualServInsp WHERE SLVRRD =-1 and Exported = 0
UNION SELECT AnnualServInspNo,  UnitID,'SLVNCR' FROM tbl_AnnualServInsp WHERE SLVNCR =-1 and Exported = 0)
As A

Any ideas? It stops running at the SELECT directly after the FROM.
Could it be that still using Access '97?

 

[PHV]

Replace this:
SELECT A.InspectionID,
By this:
SELECT A.AnnualServInspNo,

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MKH2001]

Nope =(

INSERT INTO NewTable SELECT A.AnnualServInspNo, A.UnitID, A.DefectType FROM 
(SELECT AnnualServInspNo, UnitID, 'SLVFCM' As DefectType FROM tbl_AnnualServInsp WHERE SLVFCM =-1 and Exported = 0
UNION SELECT AnnualServInspNo, UnitID, 'SLVBFC' FROM tbl_AnnualServInsp WHERE SLVBFC =-1and Exported = 0
UNION SELECT AnnualServInspNo,  UnitID, 'SLVBCO' FROM tbl_AnnualServInsp WHERE SLVBCO =-1and Exported = 0
UNION SELECT AnnualServInspNo, UnitID, 'SLVSCB' FROM tbl_AnnualServInsp WHERE SLVSCB =-1and Exported = 0
UNION SELECT AnnualServInspNo,  UnitID,'SLVCCC' FROM tbl_AnnualServInsp WHERE SLVCCC =-1and Exported = 0
UNION SELECT AnnualServInspNo,  UnitID,'SLVMDD' FROM tbl_AnnualServInsp WHERE SLVMDD =-1and Exported = 0
UNION SELECT AnnualServInspNo,  UnitID,'SLVBDL' FROM tbl_AnnualServInsp WHERE SLVBDL =-1and Exported = 0
UNION SELECT AnnualServInspNo,  UnitID,'SLVCDR' FROM tbl_AnnualServInsp WHERE SLVCDR =-1and Exported = 0
UNION SELECT AnnualServInspNo,  UnitID,'SLVODR' FROM tbl_AnnualServInsp WHERE SLVODR =-1and Exported = 0
UNION SELECT AnnualServInspNo,  UnitID,'SLVBDC' FROM tbl_AnnualServInsp WHERE SLVBDC =-1and Exported = 0
UNION SELECT AnnualServInspNo,  UnitID,'SLVDAC' FROM tbl_AnnualServInsp WHERE SLVDAC =-1and Exported = 0
UNION SELECT AnnualServInspNo,  UnitID,'SLVCSG' FROM tbl_AnnualServInsp WHERE SLVCSG =-1and Exported = 0
UNION SELECT AnnualServInspNo,  UnitID,'SLVCSS' FROM tbl_AnnualServInsp WHERE SLVCSS =-1and Exported = 0
UNION SELECT AnnualServInspNo,  UnitID,'SLVCCB' FROM tbl_AnnualServInsp WHERE SLVCCB =-1and Exported = 0
UNION SELECT AnnualServInspNo,  UnitID,'SLVCBP' FROM tbl_AnnualServInsp WHERE SLVCBP =-1and Exported = 0
UNION SELECT AnnualServInspNo,  UnitID,'SLVHPR' FROM tbl_AnnualServInsp WHERE SLVHPR =-1and Exported = 0
UNION SELECT AnnualServInspNo,  UnitID,'SLVMPP' FROM tbl_AnnualServInsp WHERE SLVMPP =-1and Exported = 0
UNION SELECT AnnualServInspNo,  UnitID,'SLVBLY' FROM tbl_AnnualServInsp WHERE SLVBLY =-1and Exported = 0
UNION SELECT AnnualServInspNo,  UnitID,'SLVMLB' FROM tbl_AnnualServInsp WHERE SLVMLB =-1and Exported = 0
UNION SELECT AnnualServInspNo, UnitID,'SLVBLB' FROM tbl_AnnualServInsp WHERE SLVBLB =-1 and Exported = 0
UNION SELECT AnnualServInspNo,  UnitID,'SLVFFP' FROM tbl_AnnualServInsp WHERE SLVFFP =-1 and Exported = 0
UNION SELECT AnnualServInspNo,  UnitID,'SLVRRD' FROM tbl_AnnualServInsp WHERE SLVRRD =-1 and Exported = 0
UNION SELECT AnnualServInspNo,  UnitID,'SLVNCR' FROM tbl_AnnualServInsp WHERE SLVNCR =-1 and Exported = 0)
As A

Think the wrong field name was just an slight slip with copy and paste =( /sigh can't even get that right it seems.

Thanks for all these assists and prompt replies.

 

[PHV]

Why not simply create a saved query doing the normalization (ie the UNION query) named, say, qryNormalizeInsp, and then, provided that NewTable is correctly defined:
INSERT INTO NewTable SELECT * FROM qryNormalizeInsp;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MKH2001]

Aye,

This worked =P

Shame couldn't get it to work the other way.... is it because using '97?

Anyway thanks PHV and LESPAUL.
Stars to each of you, LESPAUL for the original part that worked which is going to be handy for the rest of the development of the DB. And PHV for bringing it to a conclusion so effectively =)

Thanks guys.

 

[PHV]

is it because using '97?
Yes, ac97 doesn't like embedded view ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886