• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 219
  • 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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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