?
Solved

Where part of the query

Posted on 2014-01-04
4
Medium Priority
?
396 Views
Last Modified: 2014-01-04
I am trying to build a query and my "ON" part isn't working properly.

I only want this query for the values that have the identical FirstDay and DayRecorded. If those values are not the same, I don't want to include the data in the query. It keeps giving all the data even if the values are not the same. Thanks for any help you can provide!

SELECT qryBookingDayswithYear.CustomerID, qryBookingDayswithYear.FirstDay, qryBookingDayswithYearIII.DayRecorded, Sum(qryBookingDayswithYearIII.ExtendedPrice) AS SumOfExtendedPrice
FROM qryBookingDayswithYear INNER JOIN qryBookingDayswithYearIII ON qryBookingDayswithYear.CustomerID = qryBookingDayswithYearIII.CustomerID
GROUP BY qryBookingDayswithYear.CustomerID, qryBookingDayswithYear.FirstDay, qryBookingDayswithYearIII.DayRecorded;
0
Comment
Question by:cansevin
  • 2
4 Comments
 
LVL 40

Expert Comment

by:als315
ID: 39756432
I see no join on these dates in your query. Have you tried to join them?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39756441
you want to add the 2 fields in the join condition indeed:
SELECT qryBookingDayswithYear.CustomerID, qryBookingDayswithYear.FirstDay, qryBookingDayswithYearIII.DayRecorded, Sum(qryBookingDayswithYearIII.ExtendedPrice) AS SumOfExtendedPrice
FROM qryBookingDayswithYear INNER JOIN qryBookingDayswithYearIII ON ( qryBookingDayswithYear.CustomerID = qryBookingDayswithYearIII.CustomerID
AND qryBookingDayswithYear.FirstDay = qryBookingDayswithYearIII.DayRecorded ) GROUP BY qryBookingDayswithYear.CustomerID, qryBookingDayswithYear.FirstDay, qryBookingDayswithYearIII.DayRecorded; 

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39756442
0
 
LVL 52

Accepted Solution

by:
Gustav Brock earned 2000 total points
ID: 39756446
Try with:

SELECT qryBookingDayswithYear.CustomerID, qryBookingDayswithYear.FirstDay, qryBookingDayswithYearIII.DayRecorded, Sum(qryBookingDayswithYearIII.ExtendedPrice) AS SumOfExtendedPrice

FROM qryBookingDayswithYear INNER JOIN qryBookingDayswithYearIII ON qryBookingDayswithYear.CustomerID = qryBookingDayswithYearIII.CustomerID

HAVING qryBookingDayswithYear.FirstDay = qryBookingDayswithYearIII.DayRecorded

GROUP BY qryBookingDayswithYear.CustomerID, qryBookingDayswithYear.FirstDay, qryBookingDayswithYearIII.DayRecorded;

or replace HAVING  with WHERE.

/gustav
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

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.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

839 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