What a mess ???? 1

[Animaniac]

Hi there,
Could anyone out there help me with a problem that I've just inherited . At present we have a humungous white elephant of a database that only contains one HUGE.. Table,
which my boss wants referenced via query , sql or code .
The way i can currently access the records he wants is via SQL as detailed below .
SELECT FAR2.ID, FAR2.[Opened Date], FAR2.[Unit Under Test], FAR2.[Drawing No], FAR2.[Unit Serial No], FAR2.QTY, FAR2.REFERENCE, FAR2.[FAILED PART ASSY], FAR2.[FAILED ASSY DRG], FAR2.[FAILED ASSY SERIAL NO], FAR2.[SUB ASSEMBLY], FAR2.[SUB ASSY DRG NO], FAR2.[SUB SERIAL NO], FAR2.[FAIL STAGE], FAR2.ENVIRONMENT, FAR2.[RUNNING HOURS], FAR2.[PROJECT/PRODUCT], FAR2.[BATCH NO], FAR2.[SUB-ASSY TEST], FAR2.[UNIT TEST], FAR2.RSP, FAR2.[POST RSP], FAR2.[SYSTEM TEST], FAR2.[DETAILS OF FAILURE], FAR2.[CAUSE OF FAILURE], FAR2.[ACTION PROPOSED], FAR2.[REPLACEMENT PART], FAR2.[REPLACEMENT DRAWING], FAR2.[REPLACEMENT SERIAL NO], FAR2.ID1, FAR2.FAULT, FAR2.[DQN/ECR REQUIRED], FAR2.[DCR/ECR REQUIRED], FAR2.[CONCESSION REQUIRED], FAR2.[RAISED BY], FAR2.[RAISED DATE], FAR2.[REWORKED BY], FAR2.[REWORKED DATE], FAR2.[INSPECTED BY], FAR2.[INSPECTED DATE], FAR2.[RETESTED BY], FAR2.[RETEST DATE], FAR2.[ANALYSIS/FURTHER ACTION], FAR2.[SOLDERING FAULT], FAR2.[WIRING FAULT], FAR2.DOCUMENTATION, FAR2.DAMAGE, FAR2.CLEANLINESS, FAR2.IDENTIFICATION, FAR2.[MISSING ITEMS], FAR2.[SHORTAGE FAULTS], FAR2.[INCORRECTLY ASSEMBLED], FAR2.[DESIGN FAULT], FAR2.[INCORRECT ITEM], FAR2.[FINISHING FAULT], FAR2.[OPERATIONS MISSED], FAR2.[COMPONENT FAILURE], FAR2.[BONDING OR WELDING FAULTS], FAR2.[PROCESS FAILURE], FAR2.[SEALING:LEAK/PRESSURE], FAR2.[NO FAULT FOUND], FAR2.[UNDER INVESTIGATION], FAR2.DQN, FAR2.DCR, FAR2.CONCESSION, FAR2.[FAULT CLEARED], FAR2.[Prod/Eng], FAR2.[Incident Code], FAR2.[Failure Code], FAR2.[Defect Class Code], FAR2.Issue, FAR2.[Works Order No], FAR2.[Sample Size], FAR2.[Number of Incidents], FAR2.[Component Ident], FAR2.[Sub Issue], FAR2.[Circuit Reference], FAR2.[Ecp/dci number], FAR2.[R & M Log No], FAR2.[Log Date], FAR2.Env2, FAR2.[Parent FAR], FAR2.[Child FAR], FAR2.[Corrective Date], FAR2.Status1, FAR2.[Signed Off], FAR2.[Signed off date]
FROM FAR2
WHERE (((FAR2.[FAILED ASSY DRG])=[Please type in failed Assy No]) AND ((FAR2.[FAILED ASSY SERIAL NO])=[Please type in Unit Serial No])) OR (((FAR2.[Drawing No])=[Please type in UUT No]) AND ((FAR2.[Unit Serial No])=[Please type in UUT SER No])) OR (((FAR2.[SUB ASSY DRG NO])=[Please type in Sub Assy No]) AND ((FAR2.[SUB SERIAL NO])=[Please type in Sub Assy Serial No]));

The problem is i can do this the SQL way and he has to type the part No and the serial No 3 times ( and it works fine), and he just wants to type in the PART No Once and the Serial No Once ( as in the query he wants to run the other two instances are duplicates of the first two entries.) , is there any way that i could do this in code , as i have been trying but i seem to go round in circles .

Thanx for any comments or suggestions. >:O>

 

[pcdaveh]

Do you still have a problem with this? If so post another message here and I'll check back with you.

 

[BoxHead]

Animaniac,

Your query calls for three pair of criteria. Is this a special query that your boss runs where the criteria data is repeated? Or, do the values always repeat?

Does [FAILED ASSY DRG] always equal [Drawing No] always equal [SUB ASSY DRG NO]? If so, I don't understand why you would need to specify more than one as criteria.

If not (and this is a special query your boss runs with repeating criteria), you could use a pop-up form asking for the six criteria with a Command Button to run the query:

Text1 = [FAILED ASSY DRG]
Text2 = [Drawing No]
Text3 = [SUB ASSY DRG NO]
Text4 = [FAILED ASSY SERIAL NO]
Text5 = [Unit Serial No]
Text6 = [SUB SERIAL NO]

Choose two textboxes to be required for your boss; say, Text1 & Text4.

On your button's Click event run

   If IsNull(Text2) Then
        Text2() = Text1()
   End If

   If IsNull(Text3) Then
        Text3() = Text1()
   End If

   If IsNull(Text5) Then
        Text5() = Text4()
   End If

   If Isnull(Text6) Then
        Text6() = Text4()
   End If

Substitute the current prompts with

[Forms]![MyPopUpForm]![Text1]
[Forms]![MyPopUpForm]![Text2]

etc...

This will allow others who want/need to enter more criteria to do so.

HTH

John

 

[Animaniac]

Thanx John ,
I think this will be very helpful ,
The part numbers and serial numbers do not always match , the query that the boss wants written is a special one off , where he wants to find out if the failed part was a complete assembly or a sub assembly , and how many times the one part type and serial no failed. i'll try it tomorrow when i'm in work.
And Let you know how i got on.
Thanx for your help ,
I appreciate it. B-)

 

[Animaniac]

Thanx John , This Worked A treat.
Shug
>:O>