• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 213
  • Last Modified:

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;
0
running32
Asked:
running32
  • 3
  • 2
1 Solution
 
peter57rCommented:
Remember that distinct looks at ALL the fields in the result to identify duplicates.
0
 
running32Author Commented:
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]);
0
 
sdstuberCommented:
does this work?

select * from
(
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])
) as q1
right join (
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]);
) as q2
on q1.hour = q2.hour


0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
running32Author Commented:
Nope this does not work, it states Syntax error near from.  Thanks
0
 
running32Author Commented:
thanks
0
 
sdstuberCommented:
you got it to work I assume?  
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now