Hi I have two tables (tMachine, tMachineTime) in a query, they are linked so there is a one-to-many link between the tables (tMachine as the one and tMachineTime as Many). In tMachine there is a list of all the machines in the plant; in tMachineTime there is a Date field, Machine field and a hours worked field.

ie.
tMachine
Machine1
Machine2
Machine3

tMachineTime
01/07/2008 Machine1 5
01/07/2008 Machine3 6
02/07/2008 Machine1 4
02/07/2008 Machine2 6

What I want to get in the query is:
Machine1 01/07/2008 5
Machine2 01/07/2008 0
Machine3 01/07/2008 6
Machine1 02/07/2008 4
Machine2 02/07/2008 6
Machine3 02/07/2008 0

So on every date I want to have list of all the machines and how long they have worked for that day, the query is also summing the information in the case that a machine was used twice in one day. I have no way of changing the information in tMachineTime as it is a linked table that is updated by another program.

I have been trying to get a query to give me this information but have been unsuccessful, if the machine is not in the tMachineTime on a specific date it will not show up in the query for that date (unless in a crosstab but i have more information then just hours worked that I need so it does not work). I have been racking my brain to find a way to do this, any and all help is greatly appreciated.

Thanks

OK
but i have more information then just hours worked that I need
this worries me because so often people come back and say that wont work because of ...info not originally provided.

I can only do this with 2 queries , whether it can be done better I leave to others, sub queries are not one of my strengths.
I called my tables tms and tmst and my field ms fdate and fours

quer1dates creates a list of all dates against all machines

SELECT tmst.fdate, tms.tm
FROM tmst, tms
GROUP BY tmst.fdate, tms.tm;

query1final then produces the result

SELECT Query1dates.fdate, Query1dates.tm, Sum(IIf(IsNull(tmst.fdate),0,[fhours])) AS Sumfhours
FROM Query1dates LEFT JOIN tmst ON (query1dates.fdate=tmst.fdate) AND (Query1dates.tm=tmst.tm)
GROUP BY Query1dates.fdate, Query1dates.tm;


Brian

Download the attached sample

Brian -

Thanks much for that! I had a similar situation and, while it took a while, was

able to convert your solution (eliminating the sum() process, which didn't

apply in my case). It works like a charm.

Thanks again.

Bob

Hi Bob
Thanks

I noticed that Khawar used Distinct in his first query rather than an Aggregate query is that better, I suspect it is. IT would convert my first query to

SELECT DISTINCT tmst.fdate, tms.tm
FROM tmst, tms;

which worked fine.

Brian

Thank you for all the help, I was able to use this to solved my problem.

Very much appreciated.

John