Getting duplicate records, can't figure out why!

L

Leo

I'm creating a database to track inventory movements for reserve calculation
purposes. I start out with two tables, Inv Mov and Inv Bal.

From Inv Mov, I create a Query "Initial Movements" as a filter. When I run
this query, all looks good.

I create a new query, "Transaction Summary" that joins fields "P/N" from
table "Inv Bal" and Query "Initial Movements".

When I run this second query, every record is duplicated. I can't figure
out why, and I'm trying to eliminate this. Here's the SQL:

SELECT [Inv Bal Eco].[P/N], [Inv Bal Eco].[Total Qty], [Initial Movements
into Stock - Eco].[Tran Type], [Initial Movements into Stock - Eco].[Tran
Qty], [Initial Movements into Stock - Eco].[Tran Date], [Initial Movements
into Stock - Eco].[Fr Inv], [Initial Movements into Stock - Eco].[To Inv]
FROM [Initial Movements into Stock - Eco] INNER JOIN [Inv Bal Eco] ON
[Initial Movements into Stock - Eco].[P/N] = [Inv Bal Eco].[P/N]
WHERE ((([Inv Bal Eco].[Total Qty])>=0));

I have tried creating a new query from scratch just to try to eliminate
possible causes. Simply creating a select query from "Initial Movements" and
"Inv Bal" causes every record to be duplicate, regardless of what fields I
display or what criteria I enter.

Please Help!

Thanks in advance!!
 
C

Chris2

Leo said:
I'm creating a database to track inventory movements for reserve calculation
purposes. I start out with two tables, Inv Mov and Inv Bal.

From Inv Mov, I create a Query "Initial Movements" as a filter. When I run
this query, all looks good.

I create a new query, "Transaction Summary" that joins fields "P/N" from
table "Inv Bal" and Query "Initial Movements".

When I run this second query, every record is duplicated. I can't figure
out why, and I'm trying to eliminate this. Here's the SQL:

SELECT [Inv Bal Eco].[P/N], [Inv Bal Eco].[Total Qty], [Initial Movements
into Stock - Eco].[Tran Type], [Initial Movements into Stock - Eco].[Tran
Qty], [Initial Movements into Stock - Eco].[Tran Date], [Initial Movements
into Stock - Eco].[Fr Inv], [Initial Movements into Stock - Eco].[To Inv]
FROM [Initial Movements into Stock - Eco] INNER JOIN [Inv Bal Eco] ON
[Initial Movements into Stock - Eco].[P/N] = [Inv Bal Eco].[P/N]
WHERE ((([Inv Bal Eco].[Total Qty])>=0));

I have tried creating a new query from scratch just to try to eliminate
possible causes. Simply creating a select query from "Initial Movements" and
"Inv Bal" causes every record to be duplicate, regardless of what fields I
display or what criteria I enter.

Please Help!

Thanks in advance!!
Click to expand...

Leo,

Here's your query straightened up.

SELECT [Inv Bal Eco].[P/N]
,[Inv Bal Eco].[Total Qty]
,[Initial Movements into Stock - Eco].[Tran Type]
,[Initial Movements into Stock - Eco].[Tran Qty]
,[Initial Movements into Stock - Eco].[Tran Date]
,[Initial Movements into Stock - Eco].[Fr Inv]
,[Initial Movements into Stock - Eco].[To Inv]
FROM [Initial Movements into Stock - Eco]
INNER JOIN [Inv Bal Eco]
ON [Initial Movements into Stock - Eco].[P/N] = [Inv Bal
Eco].[P/N]
WHERE ((([Inv Bal Eco].[Total Qty])>=0));

There is nothing obvious to me in this query. INNER JOINs using "ON A
= B" don't usually generate duplicate records.

My basic guess would be that there are duplicate records in the source
tables, but without your table schemas and sample data to review, it's
hard to know.

What is the SQL for the [Initial Movements into Stock - Eco] query?


Sincerely,

Chris O.
 
L

Leo

Chris,

Sample Data looks like this for [Initial Movements into Stock - Eco]:

P/N Tran Type Tran Qty Tran Date Fr Inv To Inv
0001-011-0008 PORC 100 07-May-07 SP
0001-011-0008 PORC 100 22-May-07 SP
0001-022-0006 PORC 100 13-Mar-07 SP

Sample Data for [Inv Bal Eco]:

P/N Total Qty Value
0001-011-0008 180 11.538
0001-022-0006 94 2.8294

Sample Data from [Transaction Summary Eco]

P/N Total Qty Tran Type Tran Qty Tran Date Fr Inv ToInv
0001-011-0008 180 PORC 100 07-May-07 SP
0001-011-0008 180 PORC 100 07-May-07 SP
0001-011-0008 180 PORC 100 22-May-07 SP
0001-011-0008 180 PORC 100 22-May-07 SP
0001-022-0006 94 PORC 100 13-Mar-07 SP
0001-022-0006 94 PORC 100 13-Mar-07 SP

Here's the SQL for the [Initial Movements into Stock - Eco] Query:

SELECT [Inv Mov Eco].[P/N],
[Inv Mov Eco].[Tran Type],
[Inv Mov Eco].[Tran Qty],
[Inv Mov Eco].[Tran Date],
[Inv Mov Eco].[Fr Inv],
[Inv Mov Eco].[To Inv]
FROM [Inv Mov Eco]
WHERE ((([Inv Mov Eco].[Tran Type])="PORC"
Or ([Inv Mov Eco].[Tran Type])=" G-I"
Or ([Inv Mov Eco].[Tran Type])=" PIA"
Or ([Inv Mov Eco].[Tran Type])=" RMA"
Or ([Inv Mov Eco].[Tran Type])="WORC"
Or ([Inv Mov Eco].[Tran Type])="WORT")
AND (([Inv Mov Eco].[Fr Inv]) Is Null)
AND (([Inv Mov Eco].[To Inv])<>""
And ([Inv Mov Eco].[To Inv])<>"WP"
And ([Inv Mov Eco].[To Inv])<>"WS"
And ([Inv Mov Eco].[To Inv])<>"WX"
And ([Inv Mov Eco].[To Inv])<>"WD"));




Chris2 said:
Leo said:
I'm creating a database to track inventory movements for reserve calculation
purposes. I start out with two tables, Inv Mov and Inv Bal.

From Inv Mov, I create a Query "Initial Movements" as a filter. When I run
this query, all looks good.

I create a new query, "Transaction Summary" that joins fields "P/N" from
table "Inv Bal" and Query "Initial Movements".

When I run this second query, every record is duplicated. I can't figure
out why, and I'm trying to eliminate this. Here's the SQL:

SELECT [Inv Bal Eco].[P/N], [Inv Bal Eco].[Total Qty], [Initial Movements
into Stock - Eco].[Tran Type], [Initial Movements into Stock - Eco].[Tran
Qty], [Initial Movements into Stock - Eco].[Tran Date], [Initial Movements
into Stock - Eco].[Fr Inv], [Initial Movements into Stock - Eco].[To Inv]
FROM [Initial Movements into Stock - Eco] INNER JOIN [Inv Bal Eco] ON
[Initial Movements into Stock - Eco].[P/N] = [Inv Bal Eco].[P/N]
WHERE ((([Inv Bal Eco].[Total Qty])>=0));

I have tried creating a new query from scratch just to try to eliminate
possible causes. Simply creating a select query from "Initial Movements" and
"Inv Bal" causes every record to be duplicate, regardless of what fields I
display or what criteria I enter.

Please Help!

Thanks in advance!!
Click to expand...

Leo,

Here's your query straightened up.

SELECT [Inv Bal Eco].[P/N]
,[Inv Bal Eco].[Total Qty]
,[Initial Movements into Stock - Eco].[Tran Type]
,[Initial Movements into Stock - Eco].[Tran Qty]
,[Initial Movements into Stock - Eco].[Tran Date]
,[Initial Movements into Stock - Eco].[Fr Inv]
,[Initial Movements into Stock - Eco].[To Inv]
FROM [Initial Movements into Stock - Eco]
INNER JOIN [Inv Bal Eco]
ON [Initial Movements into Stock - Eco].[P/N] = [Inv Bal
Eco].[P/N]
WHERE ((([Inv Bal Eco].[Total Qty])>=0));

There is nothing obvious to me in this query. INNER JOINs using "ON A
= B" don't usually generate duplicate records.

My basic guess would be that there are duplicate records in the source
tables, but without your table schemas and sample data to review, it's
hard to know.

What is the SQL for the [Initial Movements into Stock - Eco] query?


Sincerely,

Chris O.
Click to expand...
 
C

Chris2

Leo said:
Chris2 said:
Leo said:
I'm creating a database to track inventory movements for reserve calculation
purposes. I start out with two tables, Inv Mov and Inv Bal.

From Inv Mov, I create a Query "Initial Movements" as a filter. When I run
this query, all looks good.

I create a new query, "Transaction Summary" that joins fields
Click to expand...
"P/N"
from
table "Inv Bal" and Query "Initial Movements".

When I run this second query, every record is duplicated. I
Click to expand...
can't
figure
out why, and I'm trying to eliminate this. Here's the SQL:

SELECT [Inv Bal Eco].[P/N], [Inv Bal Eco].[Total Qty], [Initial Movements
into Stock - Eco].[Tran Type], [Initial Movements into Stock - Eco].[Tran
Qty], [Initial Movements into Stock - Eco].[Tran Date], [Initial Movements
into Stock - Eco].[Fr Inv], [Initial Movements into Stock -
Click to expand...
Eco].[To
Inv]
FROM [Initial Movements into Stock - Eco] INNER JOIN [Inv Bal
Click to expand...
Eco]
ON
[Initial Movements into Stock - Eco].[P/N] = [Inv Bal Eco].[P/N]
WHERE ((([Inv Bal Eco].[Total Qty])>=0));

I have tried creating a new query from scratch just to try to eliminate
possible causes. Simply creating a select query from "Initial Movements" and
"Inv Bal" causes every record to be duplicate, regardless of
Click to expand...
what
fields I
display or what criteria I enter.

Please Help!

Thanks in advance!!
Click to expand...

Leo,

Here's your query straightened up.

SELECT [Inv Bal Eco].[P/N]
,[Inv Bal Eco].[Total Qty]
,[Initial Movements into Stock - Eco].[Tran Type]
,[Initial Movements into Stock - Eco].[Tran Qty]
,[Initial Movements into Stock - Eco].[Tran Date]
,[Initial Movements into Stock - Eco].[Fr Inv]
,[Initial Movements into Stock - Eco].[To Inv]
FROM [Initial Movements into Stock - Eco]
INNER JOIN [Inv Bal Eco]
ON [Initial Movements into Stock - Eco].[P/N] = [Inv Bal
Eco].[P/N]
WHERE ((([Inv Bal Eco].[Total Qty])>=0));

There is nothing obvious to me in this query. INNER JOINs using "ON A
= B" don't usually generate duplicate records.

My basic guess would be that there are duplicate records in the source
tables, but without your table schemas and sample data to review, it's
hard to know.

What is the SQL for the [Initial Movements into Stock - Eco] query?


Sincerely,

Chris O.
Click to expand...

Chris,

Sample Data looks like this for [Initial Movements into Stock - Eco]:

P/N Tran Type Tran Qty Tran Date Fr Inv To Inv
0001-011-0008 PORC 100 07-May-07 SP
0001-011-0008 PORC 100 22-May-07 SP
0001-022-0006 PORC 100 13-Mar-07 SP

Sample Data for [Inv Bal Eco]:

P/N Total Qty Value
0001-011-0008 180 11.538
0001-022-0006 94 2.8294

Sample Data from [Transaction Summary Eco]

P/N Total Qty Tran Type Tran Qty Tran Date Fr Inv ToInv
0001-011-0008 180 PORC 100 07-May-07 SP
0001-011-0008 180 PORC 100 07-May-07 SP
0001-011-0008 180 PORC 100 22-May-07 SP
0001-011-0008 180 PORC 100 22-May-07 SP
0001-022-0006 94 PORC 100 13-Mar-07 SP
0001-022-0006 94 PORC 100 13-Mar-07 SP

Here's the SQL for the [Initial Movements into Stock - Eco] Query:

SELECT [Inv Mov Eco].[P/N],
[Inv Mov Eco].[Tran Type],
[Inv Mov Eco].[Tran Qty],
[Inv Mov Eco].[Tran Date],
[Inv Mov Eco].[Fr Inv],
[Inv Mov Eco].[To Inv]
FROM [Inv Mov Eco]
WHERE ((([Inv Mov Eco].[Tran Type])="PORC"
Or ([Inv Mov Eco].[Tran Type])=" G-I"
Or ([Inv Mov Eco].[Tran Type])=" PIA"
Or ([Inv Mov Eco].[Tran Type])=" RMA"
Or ([Inv Mov Eco].[Tran Type])="WORC"
Or ([Inv Mov Eco].[Tran Type])="WORT")
AND (([Inv Mov Eco].[Fr Inv]) Is Null)
AND (([Inv Mov Eco].[To Inv])<>""
And ([Inv Mov Eco].[To Inv])<>"WP"
And ([Inv Mov Eco].[To Inv])<>"WS"
And ([Inv Mov Eco].[To Inv])<>"WX"
And ([Inv Mov Eco].[To Inv])<>"WD"));
Click to expand...

Leo,

We're getting closer.

You've shown me the output of the query, [Initial Movements into
Stock - Eco], and its SQL. This latest query also doesn't appear to
show any indication that it would cause duplication.

I also have output and SQL for the query [Transaction Summary Eco].

Now I just need to know the sample data from the tables, so I can
recreate them, and I'll be able to try and duplicate your situation by
running the queries myself.

Since I haven't seen the data from the tables, I'm still assuming that
there are duplicate rows in them.


Sincerely,

Chris O.
 

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

VBA works in orignal but fails in copy of database 1
creating a query from two tables 2
Combining multiple records per inventory number 1
Query returning duplicate/unexpected results 3
Automating Links 1
Printing out duplicate records automatically 3
Query fails to eliminate duplicate information 4
Calculate Percent Return for Stocks 5

Top