Solved

Condense two queries to one

Posted on 2014-04-24
1
242 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

734 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