|
|||||||||
|
|||||||||
|
|||||||||
 |
|||||||||
| |
||
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
|
|||||||||
| |
||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
ASP Free Forums Sponsor:
|
|
|
|
#1
May 21st, 2004, 11:30 AM
|
|||
|
|||
Need Help Merging 2 SQL queries
I have a situation where I need an SQL statement that is beyond my current skills.
I created a query in Access, then created a second query based upon the first one. I get good data out of Access, but I need to merge both statements into a single query that I can use in ASP page. Here is the code to both queries: Code:
QUERY 1 (kevin) SELECT tbl_Parts.int_Part_Report_ID FROM tbl_Assigned_Assemblies INNER JOIN tbl_Parts ON tbl_Assigned_Assemblies.int_Asm_ID=tbl_Parts.int_A sm_ID WHERE (((tbl_Assigned_Assemblies.txt_Vehicle)="2004 Malibu") AND ((tbl_Parts.dat_Part_Entry_Date) Between #5/1/2004# And #5/31/2004#)) QUERY 2 (based on kevin) SELECT tbl_Assigned_Assemblies.txt_Vehicle, tbl_Assigned_Assemblies.txt_Assembly_Type, tbl_Parts.txt_Defect, Count(tbl_Parts.int_Part_Report_ID) AS Total, Sum(IIf([txt_Part_Status]='Under Investigation',1,0)) AS [Open], Sum(IIf([txt_Part_Status]<>'Under Investigation',1,0)) AS Closed FROM tbl_Assigned_Assemblies INNER JOIN tbl_Parts ON tbl_Assigned_Assemblies.int_Asm_ID=tbl_Parts.int_A sm_ID WHERE (SELECT tbl_Parts.int_Part_Report_ID FROM tbl_Assigned_Assemblies INNER JOIN tbl_Parts ON tbl_Assigned_Assemblies.int_Asm_ID=tbl_Parts.int_A sm_ID WHERE (((tbl_Assigned_Assemblies.txt_Vehicle)="2004 Malibu") AND ((tbl_Parts.dat_Part_Entry_Date) Between #5/1/2004# And #5/31/2004#))) GROUP BY tbl_Assigned_Assemblies.txt_Vehicle, tbl_Assigned_Assemblies.txt_Assembly_Type, tbl_Parts.txt_Defect Some help would be GREATLY appreciated.
|
|
#3
May 21st, 2004, 11:40 AM
|
|||
|
|||
|
Quote:
The tbl_Parts.int_Part_Report_ID field is the unique recod identifier for the parts I will eventually be counting. The first query narrows the returned records to those with the correct vehicle and to those entered in the specified timeframe. The second query counts the remaining parts based on their part status, separating them into either OPEN (under investigation) or CLOSED (any other status). Does this help?
|
|
#4
May 21st, 2004, 04:41 PM
|
|||
|
|||
|
So the two queries query seperate information and you want brought back together. Correct??
If so look into using UNION in you query to bring the two queries together S-
|
|
#5
May 21st, 2004, 05:33 PM
|
|||
|
|||
Quote:
Actually, just the opposite. The first is the filter and the second is the counter. Several people at work have worked with me on this and the answer turned out to involve nesting the first query into the second query's WHERE statement. Thanks for the attention though. I was really stumped and grasping at straws.
|
|
| Viewing: ASP Free Forums > Database > SQL Development > Need Help Merging 2 SQL queries |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
|
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
