Duplicating lines

G

Guest

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

John Nurick

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

Guest

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

John Nurick

There's nothing unusual about your query, so the issue may be that you
don't fully understand your data. (Certainly *I* don't fully understand
your data.) For example, the existence of the [Ser No] field in the
Stock table suggests that there this table can have multiple records
with the same part number.

What are the primary keys of the three tables? Have you checked that
there are no duplicate rows in the textfile?

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 datasheet 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 said:
Without seeing the query there's not much point guessing. Please post
the SQL.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Access Dcount (multiple criteria) 3
Linking to Oracle 2
Combine Duplicates in Query 0
Update master and child table 1
link to a query in a second database 3
ODBC link to MySQL, can write, but not edit or delete 5
Appending Linked Data 3
Help with merging data and duplicate fields that need to not be re 1

Top