hi,
One more thing may be the DISTINCTROW keyword. You may want to use DISTINCT
instead depending on your needs. Run several tests (3 should be enough)
using DISTINCT instead, adding a query condition (entering a warranty number
fwor which you know there has been some activity on), etc. to see if you get
a good count.
And once again, LEFT JOIN may be the better choice in query type.
DISTINCT:
Omits records that contain duplicate data in the selected fields. To be
included in the results of the query, the values for each field listed in
the SELECT statement must be unique. For example, several employees listed
in an Employees table may have the same last name. If two records contain
Smith in the LastName field, the following SQL statement returns only one
record that contains Smith:
SELECT DISTINCT
LastName
FROM Employees;
If you omit DISTINCT, this query returns both Smith records.
If the SELECT clause contains more than one field, the combination of values
from all fields must be unique for a given record to be included in the
results.
The output of a query that uses DISTINCT is not updatable and does not
reflect subsequent changes made by other users.
DISTINCTROW:
Omits data based on entire duplicate records, not just duplicate fields. For
example, you could create a query that joins the Customers and Orders tables
on the CustomerID field. The Customers table contains no duplicate
CustomerID fields, but the Orders table does because each customer can have
many orders. The following SQL statement shows how you can use DISTINCTROW
to produce a list of companies that have at least one order but without any
details about those orders:
SELECT DISTINCTROW CompanyName
FROM Customers INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
ORDER BY CompanyName;
If you omit DISTINCTROW, this query produces multiple rows for each company
that has more than one order.
Hope this helps,
geebee
Dustin Cook said:
Using a RIGHT JOIN brings me back to my original 11 results. I'm delivering
the product to the client right now, so I'll include this report in the next
update.
Thank you for your help, geebee. Do you have any other thoughts?
Does anyone else have an idea why this is happening? If you need more
information, please ask me.
geebee said:
hi,
I am not sure how your tables are designed, but you may want to try a RIGHT
JOIN. If you do this in design view, by right clicking on the line which
joins the 2 tables, you can see an explanation of the join type before you
run the query.
geebee
Dustin Cook said:
The query now displays 132 out of 665 records, and the sum does not work.
I am very confused, as I don't know of anything in my database that has 132
records. That seems like a strangely arbitrary number. Do you have any ideas?
:
Hi,
Change your query to a LEFT JOIN, which will list all warranty records and
any associated claims to those warranties.
geebee
:
I am attempting to produce two sub-totals. One is for the amount of money
paid in to buy extended warranties, and the other is the amount of money paid
out to cover warranty repairs. My SQL statement is below:
SELECT DISTINCTROW tblWarranties.WarrantyDateOfPurchase,
tblClaims.ClaimDateWorkPerformed, Sum(tblWarranties.WarrantyPrice) AS [Sum Of
WarrantyPrice], Sum(tblClaims.ClaimPrice) AS [Sum Of ClaimPrice]
FROM tblWarranties INNER JOIN tblClaims ON tblWarranties.WarrantyID =
tblClaims.WarrantyID
GROUP BY tblWarranties.WarrantyDateOfPurchase,
tblClaims.ClaimDateWorkPerformed;
Unfortunately for me, this returns a number of results that is limited by
the number of claims stored in tblClaims. There are 11 claims and 665
warranties. How do I make this query show all of both of them?
