Query going wrong

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am working on a customer inventory for services that we have preformed for
our customers over the year.

The problem I am having is that when I enter in the information for one

When I goto the Report Query Report it prints all the same information for
each person, it should only print the information for 1 client and that is
Andrew Jones, the rest should be blank and have no information.

Then when I am in a query where one customer has had the same service
preformed 4 different times throughout the year instead of listing it once,
it lists it 4 times and because we do services a few times it multiplies it
even more for each one, which I understand because each one is a different
type of service.

I guess I can understand why there are 36 results for 1 customer because of
all the information but what I don't understand is why it applies it to every
single customer when only one of them has the information.

The other problem that happens is if it doesn't apply it to all the
customers it gives it to one customer then prints the same report 36 times
for the 1 customer.

I have tried everything I can think of to try and fix this problem and I am
about at my wits end with it, so any help is appreciated.

Thanks
 
Well, you have told us your problem, but have not told us how your database
is designed, or what your query structure is. We can't tell you what you
are doing wrong if we don't know what you are doing to get to the wrong
results.
 
Wild guess! Do you have more than one table in the query? Have you joined the
tables together with a JOIN LINE?

You haven't given a lot of details about your query. Please post the SQL of
your query (Open in design view, change view to SQL, Copy the text and paste it
into the body of a message.
 
I do appologize for not being very detailed, I haven't used Access in a
while so I am still trying to remember what I am doing. Here is the SQL
statement from the Query.

SELECT Customers.CompanyName, [Mowing Query].MowingTimeTotal, [Fall Clean Up
Query].FallCleanUpDate, [Fall Clean Up Query].FallCleanUpTimeTotal, [Fall
Clean Up Query].FallCleanUpDebrisRemoval
FROM ((((((((((([Aeration Query] INNER JOIN Customers ON [Aeration
Query].CustomerID = Customers.CustomerID) INNER JOIN [Mowing Query] ON
Customers.CustomerID = [Mowing Query].CustomerID) INNER JOIN [Fall Clean Up
Query] ON Customers.CustomerID = [Fall Clean Up Query].CustomerID) INNER JOIN
[Fertilization Query] ON Customers.CustomerID = [Fertilization
Query].CustomerID) INNER JOIN [Pruning Query] ON Customers.CustomerID =
[Pruning Query].CustomerID) INNER JOIN [Bed Weeding Query] ON
Customers.CustomerID = [Bed Weeding Query].CustomerID) INNER JOIN [Coring
Query] ON Customers.CustomerID = [Coring Query].CustomerID) INNER JOIN
[Spring Clean Up Query] ON Customers.CustomerID = [Spring Clean Up
Query].CustomerID) INNER JOIN [Weed Control Query] ON Customers.CustomerID =
[Weed Control Query].CustomerID) INNER JOIN [Tree and Shrub Care Query] ON
Customers.CustomerID = [Tree and Shrub Care Query].CustomerID) INNER JOIN
[Overseeding Query] ON Customers.CustomerID = [Overseeding Query].CustomerID)
INNER JOIN [Edging Query] ON Customers.CustomerID = [Edging Query].CustomerID
ORDER BY Customers.CompanyName;

I am not a big programmer so I have no clue what it means when it's in SQL
format.

Thanks again for the help.

Andrew
 
Sorry for taking a while to get back to you.

In the SQL Text view
First: Add the word DISTINCT to the SELECT clause - this will screen out any
rows that have duplicate information.
Second: You need to add a WHERE clause to limit this query to returning data for
only the desired company. I arbitrarily added that (assumed numeric field for
customerId).

If you are using the query grid, then add Customers.CustomerID to the field list
and put 1 (or whatever the id is) in the criteria for that field. Right click in
the grey area at the top and select Properties. In the properties for the QUERY
(not a field), Set Unique Values to Yes.

I don't know why you have all those queries for each type of service. Normally,
I would expect the data to all be in one table of ServicesPerformed and that
table would have the data and type of service performed.

SELECT DISTINCT
Customers.CompanyName,
[Mowing Query].MowingTimeTotal,
[Fall Clean Up Query].FallCleanUpDate,
[Fall Clean Up Query].FallCleanUpTimeTotal,
[Fall Clean Up Query].FallCleanUpDebrisRemoval
FROM ((((((((((([Aeration Query] INNER JOIN Customers
ON [Aeration Query].CustomerID = Customers.CustomerID)
INNER JOIN [Mowing Query]
ON Customers.CustomerID = [Mowing Query].CustomerID)
INNER JOIN [Fall Clean Up Query]
ON Customers.CustomerID = [Fall Clean Up Query].CustomerID)
INNER JOIN [Fertilization Query]
ON Customers.CustomerID = [Fertilization Query].CustomerID)
INNER JOIN [Pruning Query]
ON Customers.CustomerID = [Pruning Query].CustomerID)
INNER JOIN [Bed Weeding Query]
ON Customers.CustomerID = [Bed Weeding Query].CustomerID)
INNER JOIN [Coring Query]
ON Customers.CustomerID = [Coring Query].CustomerID)
INNER JOIN [Spring Clean Up Query]
ON Customers.CustomerID = [Spring Clean Up Query].CustomerID)
INNER JOIN [Weed Control Query]
ON Customers.CustomerID = [Weed Control Query].CustomerID)
INNER JOIN [Tree and Shrub Care Query]
ON Customers.CustomerID = [Tree and Shrub Care Query].CustomerID)
INNER JOIN [Overseeding Query]
ON Customers.CustomerID = [Overseeding Query].CustomerID)
INNER JOIN [Edging Query]
ON Customers.CustomerID = [Edging Query].CustomerID
WHERE Customers.CustomerID = 1
ORDER BY Customers.CompanyName;
I do appologize for not being very detailed, I haven't used Access in a
while so I am still trying to remember what I am doing. Here is the SQL
statement from the Query.

SELECT Customers.CompanyName, [Mowing Query].MowingTimeTotal, [Fall Clean Up
Query].FallCleanUpDate, [Fall Clean Up Query].FallCleanUpTimeTotal, [Fall
Clean Up Query].FallCleanUpDebrisRemoval
FROM ((((((((((([Aeration Query] INNER JOIN Customers ON [Aeration
Query].CustomerID = Customers.CustomerID) INNER JOIN [Mowing Query] ON
Customers.CustomerID = [Mowing Query].CustomerID) INNER JOIN [Fall Clean Up
Query] ON Customers.CustomerID = [Fall Clean Up Query].CustomerID) INNER JOIN
[Fertilization Query] ON Customers.CustomerID = [Fertilization
Query].CustomerID) INNER JOIN [Pruning Query] ON Customers.CustomerID =
[Pruning Query].CustomerID) INNER JOIN [Bed Weeding Query] ON
Customers.CustomerID = [Bed Weeding Query].CustomerID) INNER JOIN [Coring
Query] ON Customers.CustomerID = [Coring Query].CustomerID) INNER JOIN
[Spring Clean Up Query] ON Customers.CustomerID = [Spring Clean Up
Query].CustomerID) INNER JOIN [Weed Control Query] ON Customers.CustomerID =
[Weed Control Query].CustomerID) INNER JOIN [Tree and Shrub Care Query] ON
Customers.CustomerID = [Tree and Shrub Care Query].CustomerID) INNER JOIN
[Overseeding Query] ON Customers.CustomerID = [Overseeding Query].CustomerID)
INNER JOIN [Edging Query] ON Customers.CustomerID = [Edging Query].CustomerID
ORDER BY Customers.CompanyName;

I am not a big programmer so I have no clue what it means when it's in SQL
format.

Thanks again for the help.

Andrew
John Spencer (MVP) said:
Wild guess! Do you have more than one table in the query? Have you joined the
tables together with a JOIN LINE?

You haven't given a lot of details about your query. Please post the SQL of
your query (Open in design view, change view to SQL, Copy the text and paste it
into the body of a message.
Click to expand...
Click to expand...
 
Back
Top