asked on

getting multiple results in query

I created a select query in access. The problem is that if I query on two dates its returns 2 records for each hour when I should only return one. I'm guess I have an incorrect joint but I'm just not sure where? Thank you

SELECT DISTINCT qelMemberperhourbydaterange.hour, qelMemberperhourbydaterange.Expr1, qselGuestCountbyHourbyRange.CountOflngpassesused, qelMemberperhourbydaterange.CountOflngmemberid, Nz([countoflngPassesused],0)+Nz([CountOflngmemberid],0) AS Expr2
FROM qelMemberperhourbydaterange right JOIN qselGuestCountbyHourbyRange ON qelMemberperhourbydaterange.hour=qselGuestCountbyHourbyRange.hour
ORDER BY qelMemberperhourbydaterange.hour, qelMemberperhourbydaterange.Expr1;

peter57r

Remember that distinct looks at ALL the fields in the result to identify duplicates.

running32

ASKER

I guess I need to join two existing queries together on Date and Time. How can I join the two queries below and not get cross joins? Thanks

Query 1
SELECT Format([dtmdate],"mm/dd/yyyy") AS Expr1, Count(tblGuestPass.lngpassesused) AS CountOflngpassesused, DatePart("h",[dtmdate]) AS [hour]
FROM tblGuestPass
GROUP BY Format([dtmdate],"mm/dd/yyyy"), DatePart("h",[dtmdate]), Format([dtmdate],"mm/dd/yyyy")
HAVING (((Format([dtmdate],"mm/dd/yyyy")) Between [Start Date] And [End Date]))
ORDER BY Format([dtmdate],"mm/dd/yyyy"), DatePart("h",[dtmdate]);

Query 2
SELECT Format([dtmcheckin],"mm/dd/yyyy") AS Expr1, Count(tblcheckin.lngmemberid) AS CountOflngmemberid, DatePart("h",[dtmcheckin]) AS [hour], tblcheckin.lngtype
FROM tblcheckin
GROUP BY Format([dtmcheckin],"mm/dd/yyyy"), DatePart("h",[dtmcheckin]), Format([dtmcheckin],"mm/dd/yyyy"), tblcheckin.lngtype
HAVING (((Format([dtmcheckin],"mm/dd/yyyy")) Between [Start Date] And [End Date]) AND ((tblcheckin.lngtype)=1))
ORDER BY Format([dtmcheckin],"mm/dd/yyyy"), DatePart("h",[dtmcheckin]);

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

running32

ASKER

Nope this does not work, it states Syntax error near from. Thanks

running32

ASKER

thanks

Sean Stuber

you got it to work I assume?