Report Showing Multiple Entries per Dataset

[JewliaB]

I am working on a database which due to its complexity (and/or my lack of knowledge of access) has four queries based on two seperate tables. The first table is full of customer data and the second is transaction data linked to the customer data by the customer id no. The queries are as follows:

1. Query dealing with customer information, primarily concatenating the name fields.

2. Query finding most recent transaction date.

3. Query summing up data pertaining to our transactions with the customer.

4. Query linking queries 1, 2 & 3.

The report I have tried to create needs to contain the customer's name, id no., a few other pieces of customer data, the most recent transaction date, and the "sum data". It is working great, except that it is showing dulicate information for each transaction. The information is exactly the same and doesn't have any data regarding the transactions themselves. It really only has customer data and summary info. I would like to force the report to exclude duplicate data. I know how to do it for each individual field, but then the space is still there. Any idea would be helpful.

Thank you in advance,

Jewlia

 

[Vonnie]

The duplicate data is only showing up in the 4th final query? not any of the others ?

If so then you need to look at how that query is joining the other 3 queries to allow for duplicates. Maybe using the Distinct keyword if you aren't already.

You can also concatenate fields in the report itself if it would eliminate one of the queries. the fields just need to be part of the report recordsource. where the control source is like =[CL_FName1] & " " & [CL_LName1]

 

[JewliaB]

And thank for the tip for reducing the # of queries. I've done that before, but it just didn't occur to me this time. What is the distinct keyword?

 

[Vonnie]

Distinct is a predicate you can use in a SQL statement. You can also use it in your query, but you need to view your query in SQL view and the word must be directly after the Select statement and directly in front of the very first field which must be the field you are referencing.

for instance using the Northwind ACCDB
A query which in SQL view is:
SELECT Orders.[Order ID], [Order Details].[Status ID]
FROM Orders LEFT JOIN [Order Details] ON Orders.[Order ID] = [Order Details].[Order ID]
WHERE ((([Order Details].[Status ID])=2));
Will give you 50 records of every order that is invoiced with OrderID's listed multiple times.
A query which in SQL view is:
SELECT Distinct Orders.[Order ID], [Order Details].[Status ID]
FROM Orders LEFT JOIN [Order Details] ON Orders.[Order ID] = [Order Details].[Order ID]
WHERE ((([Order Details].[Status ID])=2));

This will give you 35 records with each OrderID listed only once.
Showing you all Order numbers that are invoiced.