Re: Duplicating lines

Thank you.... I appologize, but I am a junior but aspiring database
user/developer... I have also inherited this mess and am trying to add
functionality while cleaning up.
I have determined that this anamoly, the duplication only occurs with
records that have different part numbers but fall under the same NIIN. For
example 01-218-6522 can have parts with these part numbers: DL1028M58-1,
100000-12, 70250-13006, 70250-13006-102 or 70250-13006-103.

The SQL from my "new" query:
SELECT DISTINCTROW Stock.ID, [Part Information].NSN1, [Part
Information].NIIN, PartsFEDLOG.[AMDF NOMENCLATURE], Stock.Cage, Stock.[Part
No], [Part Information].[2410], Stock.[Ser Num], PartsFEDLOG.[AMDF UNIT
PRICE], PartsFEDLOG.[AMDF UI], Stock.[Contract No], Stock.[Date Insp],
Stock.[Pack Level], Stock.[Cond Code], Stock.Qty, Stock.Location
FROM (Stock INNER JOIN [Part Information] ON Stock.[Part No]=[Part
Information].[Part No]) INNER JOIN PartsFEDLOG ON [Part
Information].NIIN=PartsFEDLOG.NIIN
ORDER BY Stock.[Contract No] DESC;

A very small sample of the data*** view for the above query with the
duplicating records:

ID NSN1 NIIN AMDF NOMENCLATURE Cage Lookup to Part Information
21853 1680-01-218-6522 012186522 ACTUATOR,ELECTRO-ME 78286 100000-12,
1680012186522, 1680012186522
21853 1680-01-218-6522 012186522 ACTUATOR,ELECTRO-ME 78286 100000-12,
1680012186522, 1680012186522
21852 1680-01-218-6522 012186522 ACTUATOR,ELECTRO-ME 78286 100000-12,
1680012186522, 1680012186522
21852 1680-01-218-6522 012186522 ACTUATOR,ELECTRO-ME 78286 100000-12,
1680012186522, 1680012186522
21850 1680-01-218-6522 012186522 ACTUATOR,ELECTRO-ME 78286 DL1028M58-1,
1680012186522, 1680012186522
21850 1680-01-218-6522 012186522 ACTUATOR,ELECTRO-ME 78286 DL1028M58-1,
1680012186522, 1680012186522
21849 1680-01-218-6522 012186522 ACTUATOR,ELECTRO-ME 78286 DL1028M58-1,
1680012186522, 1680012186522
21849 1680-01-218-6522 012186522 ACTUATOR,ELECTRO-ME 78286 DL1028M58-1,
1680012186522, 1680012186522
21848 1680-01-218-6522 012186522 ACTUATOR,ELECTRO-ME 78286 DL1028M58-1,
1680012186522, 1680012186522
21848 1680-01-218-6522 012186522 ACTUATOR,ELECTRO-ME 78286 DL1028M58-1,
1680012186522, 1680012186522
21847 1680-01-218-6522 012186522 ACTUATOR,ELECTRO-ME 12511 DL1028M58-1,
1680012186522, 1680012186522
21847 1680-01-218-6522 012186522 ACTUATOR,ELECTRO-ME 12511 DL1028M58-1,
1680012186522, 1680012186522
21846 1680-01-218-6522 012186522 ACTUATOR,ELECTRO-ME 12511 DL1028M58-1,
1680012186522, 1680012186522
21846 1680-01-218-6522 012186522 ACTUATOR,ELECTRO-ME 12511 DL1028M58-1,
1680012186522, 1680012186522
21845 1680-01-218-6522 012186522 ACTUATOR,ELECTRO-ME 12511 DL1028M58-1,
1680012186522, 1680012186522
21845 1680-01-218-6522 012186522 ACTUATOR,ELECTRO-ME 12511 DL1028M58-1,
1680012186522, 1680012186522
21844 1680-01-218-6522 012186522 ACTUATOR,ELECTRO-ME 12511 DL1028M58-1,
1680012186522, 1680012186522
21844 1680-01-218-6522 012186522 ACTUATOR,ELECTRO-ME 12511 DL1028M58-1,
1680012186522, 1680012186522
21843 1680-01-218-6522 012186522 ACTUATOR,ELECTRO-ME 12511 DL1028M58-1,
1680012186522, 1680012186522
21843 1680-01-218-6522 012186522 ACTUATOR,ELECTRO-ME 12511 DL1028M58-1,
1680012186522, 1680012186522
21842 1680-01-218-6522 012186522 ACTUATOR,ELECTRO-ME 12511 DL1028M58-1,
1680012186522, 1680012186522
21842 1680-01-218-6522 012186522 ACTUATOR,ELECTRO-ME 12511 DL1028M58-1,
1680012186522, 1680012186522
21841 1680-01-218-6522 012186522 ACTUATOR,ELECTRO-ME 12511 DL1028M58-1,
1680012186522, 1680012186522
21841 1680-01-218-6522 012186522 ACTUATOR,ELECTRO-ME 12511 DL1028M58-1,
1680012186522, 1680012186522
21840 1440-00-478-0334 004780334 STRAP ASSEMBLY,MISS 18876 10679923,
1440004780334, 1440004780334
21839 5999-01-145-7729 011457729 CIRCUIT CARD ASSEMB 18876 13099729,
5999011457729, 5999011457729
21838 2840-00-860-9457 008609457 VALVE ASSEMBLY,OIL TA 99193 2-060-460-02,
2840008609457, 2840008609457
21837 2840-00-860-9457 008609457 VALVE ASSEMBLY,OIL TA 99193 2-060-460-02,
2840008609457, 2840008609457
21836 1440-00-462-2526 004622526 MOUNT,ALIGNMENT 10192040, 1440004622526,

Thanks for your time and assistance.

David

"John Nurick" wrote:

Without seeing the query there's not much point guessing. Please post
the SQL.

On Tue, 5 Sep 2006 23:00:01 -0700, DaMcH47D
<DaMcH47D@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

Hello again,

I have a linked text file that contains info from a federal database with
logistical information, price, nomenclature, unit of issue, shelf life codes
etc.. It has a common field NIIN with a table inside the database. This
table has information about the type of part: NIIN, NSN, part number, Supply
Class and system the part is used on. Finally, I have one more table which
lists each part stored in the warehouse by part number, location, qty,
contract number, condition, packaging.

I have a query to list all the stock. Before I created the linked table,
the query would combine the generic part info with the actual part info. I
wanted to add the price and shelf life, so I added in the current linked
table. I have tried to adjust the query, and even created a new query but
always with these same results. Without the linked table there are around
13, 480 items. After I add the linked table, the number increase to 13, 841.
If I look at a table, everything is good, no duplicates. But, If I look at
the query with the linked table, there are several duplicated lines. Remove
the link table from the query and the duplicates disappear. I have tried
breaking the join, started over, changed the join properties. I even tried
builiding a new query. But, if I add that linked table to a querry, the fun
starts. Some info is duplicated in the old tables, but I have not added
those duplicates to the query. Once I get the linked table to function
properly within the query, I will discard the excess info. I have inherited
the database and am trying to make it work without starting over. All other
queries seem to work without any visible errors. Any suggestions would be
greatly appreciated.

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

.