Sub Query?

[banshee343]

Hi im trying to have a query that reports down time for a date range

[tblMain]
ID
ShiftDate

[tblShift]
ID
MainID
Task

[tblDownTime]
starttime
stoptime
Fault
ShiftID

I'm trying to make a query to display the sum of tbldowntime for each date ie..

1/1/2010 444min
2/1/2010 323min

Where each date has multiple downtime entriesi can do this fine, but what would be alot better is if i can have it return 2 fields based on [tblDownTime].[Fault] i.e..

Date Fault1 Fault2
1/1/2010 200min 244min
2/1/2010 105min 218min

there is only ever 2 different faults types

Im really lost as how to achive this i can do it in 2 seperate querys but id really like to be able to do it in the 1 if i can, as i want to graph this data later and it'd be much eaiser if i could export 1 query to excel rather then multiple querys.

I assume i need 2 subquerys, one for each fault? ive had a look into them but really cant grasp it at the moment any guidance would be greatly appreciated

 

[FoFa]

If you can do it in two queries, and they have the same fields, just use UNION ALL to put the two queries together, would be the easiest.

SELECT Field1, Field2, Field3
FROM Table1
Where Something
UNION ALL
SELECT Field1, Field2, Field3
FROM Table1
Where SomethingElse

 

[banshee343]

Alright, ive got this set up using 3 querys 1 for each of the 2 "Fault"s and 1 to link them both together

Problem im now having thou is that if there is data in tbldowntime for both faults my main query works fine! however if there is no data for fault1 the query dosnt return any data so when i run my main query even if there is data for fault2 it just skips the date completly. is there a way i can just get it to return a 0 for fault1 so it dosnt skip the date

Pretty lost on this one