Unique Value property

[Guest]

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.

 

[MGFoster]

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-----

 

[Guest]

thanks for the reply, I had just found the same answer myself! Though would a
UNION query with this SQL

SELECT OrderNum, OrderDate, ItemID, CustNum, Ordered, Shipped
FROM [Daily_allo-0225]
UNION SELECT OrderNum, OrderDate, ItemID, CustNum, Ordered, Shipped
FROM [Daily_allo-positive]
ORDER BY OrderNum;

produce the same results?

As to you suggestion about the PK, unfortunately the data tables given to me
*can* have duplicate records across all fields that actually refer to two
separate items ordered on the same OrderNum (don't ask me why, I haven't
figured that out yet myself!) All the tables that will need to be added for
the rest of the year will be given to me in simple comma delimited .txt files
which I import to manipulate. So my intention is to get these various tables
combined into one table up to the current date, then attempt restructure this
table into smaller tables with PK's using Table Analyzer. Or am I going about
this in a bassackwards fashion?

 

[MGFoster]

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

The UNION will just return all records from both tables - and remove any
duplicates. I guess you could do that, but if you used the UNION as the
source for the INSERT INTO statement you'd be trying to re-enter data
that is already in the table Daily_allo-positive (if those records
weren't eliminated by the UNION).

For data cleaning I'd suggest that anything that works is valid. There
are refinements that make it easier, but that is usually determined by
the state of the data received - the more screwed up it is the more
dancing & jumping thru hoops you have to do. ;-)
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQnK0coechKqOuFEgEQIJ0QCg2RxAH7Gy5c+Qt2G0UDceulTZoU4AoLr8
K/VvOSw5akTf7VJ9/gDURdj+
=k762
-----END PGP SIGNATURE-----