Hi guys

I've written the following SQL query but not sure if its as optimum as it could be. Its running in Access 2000. My company inspects mobile phones and I'm trying to get a list of all the duplicates from a particular job.

-------------------------------------------------------------------
SELECT Customer, Phone_Make, Imei, Date, Job_Ref, Pallet
FROM tbl_Imei AS i, tbl_Pallets AS j, tbl_Jobs AS k, tbl_Phones AS l
WHERE i.PalletID=j.PalletID
      AND j.JobID=k.JobID
      AND k.PhoneID=l.PhoneID
      AND Imei IN
                  (SELECT a.Imei FROM tbl_Imei AS a, tbl_Pallets AS b
                   WHERE a.Imei IN
                                   (SELECT Imei FROM tbl_Imei GROUP BY Imei HAVING COUNT(*) > 1)
                   AND b.JobID=[Forms]![frm_Jobs]![JobID]
                   AND a.PalletID=b.PalletID
                   GROUP BY a.Imei)
ORDER BY Imei, Date, Pallet;
-------------------------------------------------------------------
relationship diagram http://www.kamruz.pwp.blueyonder.co.uk/rels.gif

Its quite slow, but the numbers involved are quite big. Some jobs have 10 Pallets, each with 500 Units (=5000 per job) and we have had jobs before with 10,000 units so numbers will start to mount up soon. I've tried to restrict numbers by having a different DB for each type of phone.

I think the problem lies partly with this, the fact that I'm looking through the same table so many times and it is MS Access (but I may be wrong on all these points).

Any suggestions much appreciated.

Cheers
Kam

I'm not sure I understand what you are looking for. You say you are looking for 'duplicates', but duplicate what? That innermost SELECT statement finds duplicate values of 'Imei', but what does that have to do with a particular job?

Access is not the smartest DB in the world, and it may be that it is evaluating that query many many times.



Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Hi Jeff

Sorry, should have been clearer. I'm looking for duplicate Imei's (an Imei being a 15 digit unique serial number for each phone, stored as text in the DB). Reason I need to find these is to do with VAT reg's and previously traded stock. We often find our own stock itself back to us (no point in paying VAT on the same good's twice right?).

The Select statement I built followed this logic:
1. Get a list of all duplicates in table
2. Then check if any Imei's in job match those of duplicates
3. Then get all details of these Imei's in recordset

I understand Access is a bit rubbish (I'm an ASP web developer and not an applications developer so this is the best way I could think of doing it, generally I have no need for such large recordsets). The seperate queries themselves work fine, this whole thing also works fine AS LONG AS there is a low number of records.

I was hoping there was a smarter way of evaluting this.

If anything is still unclear I'll try and explain better (I can see it in my head but finding it difficult to explain it better... sorry)


Many thanks
Kam

I think you are close. I approach this sort of thing in steps. First, you are only interested in duplicate 'Imei' values. Your innermost query correctly finds the duplicate 'Imei' values:
Next, let's construct a query which obtains the desired information you wish to report from your tables, but without regard to duplicates or JobID selection:
(Forgive me from stripping off the 'tbl_' prefix from your table names - it's something that for some reason Access folks like to add to their table names and it drives me nuts :))
I prefer to use the 'infix' or 'new' style JOIN syntax.

Now, if you added a WHERE clause to the above query which selected based on JobID you'd get your desired data, though still for everything for that job and not just the duplicate 'Imei' values.

Which should give you a hint.

If a table existed which contained the results of the duplicates query, then you would simply JOIN that table in with the others on the above query and that would give you what you want. In SQL Server, you can used what's called a derived table, that is, I can directly include the duplicate-finding SELECT statement in the second query's FROM clause and treat it as though it were a table:
And this would give me the requested data, just for the duplicates. Add a WHERE clause selecting the desired JobID and then ORDER things appropriately, and you are done.

Unfortunately, I don't think you can use derived tables in Access. (Access is brain-dead). The above could also be done using a view instead of a derived table, and a view can be used in Access - that is, a stored query.

So, if you define another Access query which is the duplicate finding inner query, you can JOIN it as I have JOINed the derived table above.

Now, This query is certainly simpler than your original, but whether it is faster only trying it will tell...

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Hi Jeff

Thanks for the feedback. Never understood JOINs before (therefore always done it with WHERE's). I had to nest the Joins for Access to acdcept it, it was good practice and has helped me understand the the damn thing a little better. I don't think its had much impact on speed but least its a little easier to understand (and I've gained a bit of knowledge).

thanks again

Kam

----------------------------------------------------------------------
SELECT
    tbl_Jobs.Customer,
    tbl_Phones.Phone_Make,
    tbl_Imei.Imei,
    tbl_Jobs.Date,
    tbl_Jobs.Job_Ref,
    tbl_Pallets.Pallet

FROM tbl_Phones
INNER JOIN
    (tbl_Jobs INNER JOIN
        (tbl_Pallets INNER JOIN
            (tbl_Imei INNER JOIN
                (SELECT Imei FROM tbl_Imei GROUP BY Imei HAVING COUNT(*) > 1) AS Dups
            ON tbl_Imei.Imei = Dups.Imei)
        ON tbl_Imei.PalletID = tbl_Pallets.PalletID)
    ON tbl_Jobs.JobID = tbl_Pallets.PalletID)
ON tbl_Phones.PhoneID = tbl_Jobs.PhoneID