Solved

Condense two queries to one

Posted on 2014-04-24
1
232 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
ID: 40020963
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

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…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

821 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