Solved

Condense two queries to one

Posted on 2014-04-24
1
225 Views
Last Modified: 2014-04-24
I have two query that I would like to condense to one. The second query only pulls value where the IsDate([DayRecorded])=True. Is there a way to write this "where" in the first query so there doesn't have to be two queries?

First query titled qryBookingDayswithYearIII

SELECT Bookings.CustomerID, Format([Bookings.DateRecorded],"mm-dd-yy") AS DayRecorded, [Booking Details Extended].BookNumber, Sum([Booking Details Extended].ExtendedPrice) AS SumOfExtendedPrice
FROM Bookings INNER JOIN [Booking Details Extended] ON Bookings.BookNumber = [Booking Details Extended].BookNumber
GROUP BY Bookings.CustomerID, Format([Bookings.DateRecorded],"mm-dd-yy"), [Booking Details Extended].BookNumber
HAVING (((Format([Bookings.DateRecorded],"mm-dd-yy"))=True));

Open in new window


2nd Query: qryBookingDayswithYearIIIB
SELECT *
FROM qryBookingDayswithYearIII
WHERE IsDate([DayRecorded])=True;

Open in new window

0
Comment
Question by:cansevin
1 Comment
 
LVL 6

Accepted Solution

by:
Dulton earned 500 total points
Comment Utility
Not sure what you're "Having" clause in query 1 is doing here. I'll eliminate it.

just move the isdate to the where and you should have it.

SELECT Bookings.CustomerID, Format([Bookings.DateRecorded],"mm-dd-yy") AS DayRecorded, [Booking Details Extended].BookNumber, Sum([Booking Details Extended].ExtendedPrice) AS SumOfExtendedPrice
FROM Bookings INNER JOIN [Booking Details Extended] ON Bookings.BookNumber = [Booking Details Extended].BookNumber
WHERE IsDate([Bookings].[DateRecorded])=True
GROUP BY Bookings.CustomerID, Format([Bookings.DateRecorded],"mm-dd-yy"), [Booking Details Extended].BookNumber; 

Open in new window

0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now