Two Table Query Problems

[robcarr]

Dear All,

I am new to working in access, I have 2 tables, 1 has call data and the other as Aux data,(tblsumDaily - call, tblauxDaily - aux data), I am trying to edit a query I have that will show me all calls from tblsumdaily , everytime I add tblsum daily to the query in design mode, it shows me all the calls the agent has taken for the dates selected, but for all the other data in the aux table, it just repeats the values, can someone help me with the uery code below to get it to show me all the data required for the dates selected, from both tables.

Query for 1 table worrks

SELECT DISTINCT [tblAuxDaily].[AgentName], [tblAuxDaily].[Dates], [tblAuxDaily].[StaffTime], [tblAuxDaily].[Break]
FROM tblAuxDaily LEFT JOIN tblSumDaily ON [tblAuxDaily].[AgentName]=[tblSumDaily].[AgentName]
GROUP BY [tblAuxDaily].[AgentName], [tblAuxDaily].[Dates], [tblAuxDaily].[StaffTime], [tblAuxDaily].[Break]
HAVING (((tblAuxDaily.AgentName)=[Forms]![frmMain]![cboAgentname]) AND ((tblAuxDaily.Dates) Between [Forms]![frmMain]![txtfromdate] And [Forms]![frmMain]![txttodate]));

query when I add tblsumDaily

SELECT DISTINCT tblAuxDaily.AgentName, tblAuxDaily.Dates, tblAuxDaily.StaffTime, tblAuxDaily.Break, tblSumDaily.Calls
FROM tblAuxDaily LEFT JOIN tblSumDaily ON tblAuxDaily.AgentName = tblSumDaily.AgentName
WHERE (((tblAuxDaily.AgentName)=[Forms]![frmMain]![cboAgentname]) AND ((tblAuxDaily.Dates) Between [Forms]![frmMain]![txtfromdate] And [Forms]![frmMain]![txttodate]));

could someone tell me where I am going wrong and what it should do.

Thanks

Thanks Rob.

 

[MakeItSo]

It might be caused by the join of your tables. If there is no corresponding record in the second table, you won't get any records as result.

MakeItSo

Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[robcarr]

the agent is in both tables, when I add the second part to the table it finds the agent but the data isnt displayed properly

Thanks Rob.