Re: Unique Value property

malick wrote:
Hi,

I have a series of Orders tables (each dated a Friday for 8 weeks) from Accounting that I need to combine, with the following FIELDS (OrderNum, OrderDate, ItemID, ItemDesc, CustNum, CustName, Ordered, Shipped). They each have approx 6 weeks of data, but we need to have the whole year 2005 in one table. Each table has ~50,000 records, and hundreds are duplicates, so I'd like to APPEND one to previous, and if these fields are equal (OrderNum, OrderDate, ItemID, CustNum, Ordered, Shipped) for a record, I'd like to skip it. I thought I would just have to set up the Append query with the Unique Value property to YES, and the query would do what I want. But it appends every single record from the later table, including all the duplicated records! What am I not understanding? Here's the SQL:

INSERT INTO [Daily_allo-positive] ( OrderNum, OrderDate, ItemID, CustNum, Ordered, Shipped )
SELECT DISTINCT [Daily_allo-0225].OrderNum, [Daily_allo-0225].OrderDate, [Daily_allo-0225].ItemID, [Daily_allo-0225].CustNum, [Daily_allo-0225].Ordered, [Daily_allo-0225].Shipped
FROM [Daily_allo-0225];


Thanks much, this seems like such an easy fix, but I'm killing myself looking for a solution. And I can't just delete or ignore all records before a previous date, because occassionally an Ordered or Shipped value will change as to be expected.


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Unique (DISTINCT) just means the records *selected* will be distinct it
doesn't mean that the query will check if the records are already in the
table you are inserting them into.  You need to do something like this:

INSERT INTO [Daily_allo-positive] ( OrderNum, OrderDate, ItemID,
CustNum, Ordered, Shipped )
SELECT DISTINCT OrderNum, OrderDate, ItemID, CustNum, Ordered, Shipped
FROM [Daily_allo-0225] As A
WHERE NOT EXISTS (SELECT * FROM [Daily_allo-positive]
                  WHERE OrderNum = A.OrderNum
                    AND OrderDate = A.OrderDate
                    AND ItemID = A.ItemID
                    AND CustNum = A.CustNum
                    AND Ordered = A.Ordered
                    AND Shipped = A.Shipped)

BTW, the best way to avoid duplicates is to have a significant Primary
Key on the table.  In your case you have indicated that the PK would
consist of (OrderNum, OrderDate, ItemID, CustNum, Ordered, Shipped).
Read a good book on DB design or data modeling to find out more about
Primary Keys.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQnKsaIechKqOuFEgEQJz9QCgo3Qz03N/vbIpelsfktm4jS8j0h8AoP+1
4t/pTX1chefKydYfl3xxVMrm
=bjeJ
-----END PGP SIGNATURE-----
.

Relevant Pages

  • Re: Unique Value property
    ... UNION SELECT OrderNum, OrderDate, ItemID, CustNum, Ordered, Shipped ...
    (microsoft.public.access.queries)
  • Re: Return just one record for each value pair?
    ... >I have a simple Orders table that contains, among other things, customer ... >CustNum, OrderNum, ItemID ... SELECT CustNum, OrderNum, Min ...
    (microsoft.public.access.queries)