Solved: Query returning duplicate rows

[andysharps]

Hi,

I wonder if somebody can help me, I have the following query which returns the rows I expect but it returns duplicate rows, I've tried using Select Distinct Rows but that still returns duplicate rows.

The query is

SELECT FittingLists.*, CukOrders.OrderId, CukOrders.AmPm, CukOrders.JobNo, CukOrders.Postcode, [Forename] & " " & [Surname] AS Name, [House] & " " & [Address1] & " " & [Address2] & " " & [Address3] & " " & [Address4] AS Address, CukOrders.Total, FittingLists.ListDate, CukOrders.FittingDate, FittingLists.List, FittingLists.ListDate
FROM CukOrders RIGHT JOIN FittingLists ON CukOrders.FittingDate = FittingLists.ListDate
WHERE (((FittingLists.ListDate)=[Forms]![FittingLists]![ListDate]) AND ((FittingLists.List)=[Forms]![FittingLists]!
[list]))
ORDER BY CukOrders.AmPm, CukOrders.JobNo;

Any help or advice received is appreciated.

Thanks

[OBP]

Andy, I can't see why you are getting duplicates either.
Can you post a zipped copy of the database in Access 2000-2003 format with some dummy data in so that I can play around with the Query?

[andysharps]

Zipped up file attached.

the application is large so I have just built a small app with the relevant bits.

If you run FittingListNew form & enter the following parameters

List date = 24/12/09
List = 2

Hopefully you will see the same results as I do.

The query is on the form, so of course you can also go into design mode & run the query from there.

Thnaks Andy

[OBP]

Andy, you do not have any relationships set in this dummy database, what relationships are set for these 2 tables in the Real Database?
You are currently relating them in the query using the Date, whereas there should be something more concrete like the OrderID or the ListID, depending on which is the Dominant Table.

[andysharps]

Erm, I don't think there any, this is an app I have inherited (though I have been maintaining it for about 3 years now) I don't think there are any relationships set up.

I've looked in Tools - relationships & there is nothing there, as far as I knew the relationships are coded into the querys & the code if that makes sense.

Is that a big problem?

The customer is migrating to a new application in the next couple of months so I don't want to make big changes to the database structure, but they would still like this fixing before they migrate, do you think that's possible?

[OBP]

It is possible, but I need to know what comes first, the CUKOrders or the Fittinglists? Which is the primary table?
Also can a CUKOrder have more than one Fitting (One to Many)?
Or can On Fitting have more than one CUKOrder? (One to Many)
Those details decide how the Tables should be related, at the moment do you know what relates them?

[andysharps]

The CukOrders table comes first (an order is placed before a fitting date is assigned & then a fitting list is produced for that date & route).

It's a one to one relationship between CukOrders & FittingLists (each order can only be on 1 Fit list).

Previously they were related using Fittinglists.ListID - CukOrders.FittingListRef

I know what I am trying to achieve (what the customer wants) goes against how the app was initially designed, to try & give a bit of background..... previously the system automatically placed an order onto a fitting list & the users manually adjusted the jobs on the fitting lists.

Jobs were getting lost so now they don't want the system to do any automatic allocation of jobs onto any lists. So I have added the 'No' & 'List' fields to FittingLists, my thinking was to add all the jobs onto a master list for that day & set FittingLists.No = 1 (as a master list for that day) & then let the user allocate the job to a list by entering the list no to FittingLists.list.

So when they press enter on the ListDate field the next screen would only display the jobs for that date & list (hence the 24/12/09 & 2 parameter values), that seems to work apart from dispalaying duplicates.

Hope I haven't confused you , thanks for your help so far.

[andysharps]

Oh forgot, the original query before I started this work was as below.

SELECT FittingLists.*, CukOrders.OrderId, CukOrders.AmPm, CukOrders.JobNo, CukOrders.Postcode, [Forename] & " " & [Surname] AS Name, Trim([House] & " " & [Address1] & " " & [Address2] & " " & [Address3] & " " & [Address4]) AS Address, CukOrders.Total
FROM CukOrders RIGHT JOIN FittingLists ON CukOrders.FittingListRef = FittingLists.ListId
WHERE (((FittingLists.ListId)=[Forms]![FittingLists]![Listid]))
ORDER BY CukOrders.AmPm, CukOrders.JobNo;

[OBP]

Andy, the problem is the Join, if you use the original one it works, see the attached.

[andysharps]

So it does! I thought I needed to change the field the join used as due to the changes I was making that field wasn't used (within my logic!) I didn't realise it was vital to the join.

Many thanks for your help, I appreciate it.

Andy