Query Performance

[GBa]

I need help from whoever can please,
I have four tables


Hrs Delivered, sales, agent database, need the output as below
Date Name Hrs sales

When i run the query after linking the four tables it does not include the Days when the hours are 0 but there are sales (which is possible in the my case)

I have teh databse table as the ID of people is different in the Hrs table and Sales table, but i have all the names and corrosponding id's in the database table

Please help me

[June7]

Your 4 tables are?
Hrs
Delivered
Sales
Agent database

It is quite possible what you want can't be done in one query. Really need sample data and the SQL statement you attempted. You can attach project to a post to make available for analysis.

[GBa]

SELECT Date.F1, database.Name, hrs.Hrs
FROM ([Date] INNER JOIN ([database] INNER JOIN hrs ON database.[Hrs ID] = hrs.[Agent Name]) ON Date.F1 = hrs.Date) INNER JOIN Pmt ON (Date.F1 = Pmt.Date) AND (database.[$$ ID] = Pmt.[Agent Name]);

The data is like this
Date
12/01/2011
12/02/2011
12/03/2011
.
.
.
Hrs
Date Hrs id Hrs
12/01/2011 A 3.5
12/02/2011 A 4
12/01/2011 B 7
12/03/2011 B 6

Database
Name Hrs id Pymt ID
Ann A AA
Bann B BB

Pymt
Date pymt ID Pymt
12/01/2011 AA 20
12/01/2011 BB 25
12/02/2011 BB 30

As you see there are days where the hrs are missing and days where payments re missing
Output required
Date Name Pymt Hrs
12/01/2011 AA 20 3.5
12/01/2011 BB 25 7
12/02/2011 AA 0 4
12/02/2011 BB 30 0
12/03/2011 AA 0 0
12/03/2011 BB 0 6

Thank you for your help, hope the above illustartion would help

[June7]

Hard to relate the query to the sample data. You have a table of just dates? Table Pymt is referred to in the query as Pmt. Is [Agent Name] an input prompt? Why name - name is not in the Pymt table. There is no field named [$$ ID]. HrsID = Agent Name is not a proper link, nor is $$ID = Agent Name. Need to link on ID fields. F1 is name of the field in Date table?

The INNER join will exclude records where link data is not in both tables. Need LEFT or RIGHT join. I achieved your example output with 8 queries. Exception: the records for 4 and 6 hrs have no corresponding PymtID.

Date_Database
SELECT Date.F1, Database.Name FROM [Date], [Database];

Database_Hrs
SELECT Hrs.Date, Database.Name, Hrs.[Hrs ID], Hrs.Hrs, Database.[Pymt ID]
FROM [Database] RIGHT JOIN Hrs ON Database.[Hrs ID] = Hrs.[Hrs ID];

Database_Pymt
SELECT Pymt.Date, Database.Name, Database.[Hrs ID], Pymt.[Pymt ID], Pymt.Pymt
FROM [Database] RIGHT JOIN Pymt ON Database.[Pymt ID] = Pymt.[Pymt ID];

Hrs_Pymt
SELECT Database_Hrs.Date, Database_Hrs.[Hrs ID], Database_Hrs.Hrs, Database_Hrs.Name, Database_Pymt.Pymt, Database_Pymt.[Pymt ID]
FROM Database_Pymt RIGHT JOIN Database_Hrs ON (Database_Pymt.[Pymt ID] = Database_Hrs.[Pymt ID]) AND (Database_Pymt.[Hrs ID] = Database_Hrs.[Hrs ID]) AND (Database_Pymt.Date = Database_Hrs.Date);

Pymt_Hrs
SELECT Database_Pymt.Date, Database_Hrs.[Hrs ID], Database_Hrs.Hrs, Database_Pymt.Name, Database_Pymt.Pymt, Database_Pymt.[Pymt ID]
FROM Database_Pymt LEFT JOIN Database_Hrs ON (Database_Pymt.Date = Database_Hrs.Date) AND (Database_Pymt.[Hrs ID] = Database_Hrs.[Hrs ID]) AND (Database_Pymt.[Pymt ID] = Database_Hrs.[Pymt ID]);

DateDatabasePymt
SELECT Date_Database.F1, Date_Database.Name, Pymt_Hrs.[Hrs ID], Pymt_Hrs.Pymt, Pymt_Hrs.[Pymt ID]
FROM Pymt_Hrs RIGHT JOIN Date_Database ON (Pymt_Hrs.Date = Date_Database.F1) AND (Pymt_Hrs.Name = Date_Database.Name);

DateDatabaseHrs
SELECT Date_Database.F1, Date_Database.Name, Hrs_Pymt.[Hrs ID], Hrs_Pymt.Hrs, Hrs_Pymt.[Pymt ID]
FROM Hrs_Pymt RIGHT JOIN Date_Database ON (Hrs_Pymt.Date = Date_Database.F1) AND (Hrs_Pymt.Name = Date_Database.Name);

Final
SELECT DateDatabaseHrs.F1, DateDatabaseHrs.Name, DateDatabaseHrs.[Hrs ID], DateDatabaseHrs.Hrs, DateDatabasePymt.[Pymt ID], DateDatabasePymt.Pymt
FROM DateDatabasePymt INNER JOIN DateDatabaseHrs ON (DateDatabasePymt.Name = DateDatabaseHrs.Name) AND (DateDatabasePymt.F1 = DateDatabaseHrs.F1);

Date|HrsID|Hrs|Name|Pymt|PymtID
12/1/2011|A|3.5|Ann|20|AA
12/1/2011|B|7|Bann|25|BB
12/2/2011|||Bann|30|BB
12/2/2011|A|4|Ann
12/3/2011|||Ann
12/3/2011|B|6|Bann