Loss of Data when joiing tables

[Guest]

Morning,
I have created a make tabke query to join 2 table (say table 1 and table
2) to make a third (table 3). I have selected option 2 in the join
properties ("include ALL records from table one and only those records from
table 2 where the joined fields are equal.")

When I run the query in end up ~5000 rows short on my final table (origional
table (1) had 273737 new table (3) has 268787).
Why is this happening if all the rows from table 1 are supposed to be
included?
If this is normal is there any way around this?

Notes: I am using Access2003 and am still new to Access in general.

I can supply more detaisl if needed. thanks for your help

 

[Guest]

Hi.

You may try, Tool»Options»Find/Edit tab and change the value. I think, that
there's something looks like in regedit.

Marco

 

[Guest]

Show us the SQL. Open the query in design view. Next go to View, SQL View
and copy and past it here. Information on primary keys and relationships
would be a nice touch too.

 

[Guest]

Thanks,
First I fixed the origional problem of not enough rows (the query was
grouping where it shouldn't be), but now I have a new issue the table is
about 2000 rows too large.

Let me give you some background before I post the SQL (it s quite long). I
have a table that I import containing a bunch of servie/repair info. This
table list parts by number, my boss want to see parts by name and number.
The table is over 250,000 rows large so manual data entry isn't really
efficient.

So basically I want to add a part name column to the original table that
contains the correct part name for each corresponding part number.
So first I created a new table with 2 rows (a part number row and a part
name row). Then i designed a simple make table query joining the 2 tables
by part number and outputing a new table showingall the original columns plus
the part name column.
This is where the problem occrs. The new table is about 2000 rows larger
then the origional table. Why would this be happening? I have no primary
key and the row are not indexed (I inherited this table and due to doubles it
won't let me assign a primary key)


Here is the SQL:
SELECT *****(these are all the columns i need displayed)****
[all so raw Before Part Numbers].SYSORD,
[all so raw Before Part Numbers].REPORD,
[all so raw Before Part Numbers].CHGCDE,
[all so raw Before Part Numbers].PARTNO,
[Part Number List].PartName, ***(this is the new column )****
[all so raw Before Part Numbers].SERIAL,
[all so raw Before Part Numbers].FAILCD,
[all so raw Before Part Numbers].REPCDE,
[all so raw Before Part Numbers].MONTH,
[all so raw Before Part Numbers].YEAR,
[all so raw Before Part Numbers].MODEL,
[all so raw Before Part Numbers].CO,
[all so raw Before Part Numbers].BILLTO,
[all so raw Before Part Numbers].CUSNAM,
[all so raw Before Part Numbers].CUSCTY, [all so raw Before Part
Numbers].CUSST,
[all so raw Before Part Numbers].SDATE,
[all so raw Before Part Numbers].EDATE,
[all so raw Before Part Numbers].LBRHRS
INTO [all so raw]
***here is the join statement******
FROM [all so raw Before Part Numbers] LEFT JOIN [Part Number List] ON [all
so raw Before Part Numbers].PARTNO = [Part Number List].PARTNO;

 

[Guest]

I bet that it's the lack of a primary key. The [all so raw Before Part
Numbers].PARTNO field needs to be the PK or you could return dupes. Run the
following to tell if you have any duplicate PARTNO. If so you'd need to fix
it before making that field the PK:

SELECT PARTNO, Count(PARTNO)
FROM [all so raw Before Part Numbers]
WHERE Count(PARTNO) > 1 ;

You'd also need to check for nulls.

SELECT PARTNO
FROM [all so raw Before Part Numbers]
WHERE PARTNO is Null;

After that I'd suspect that you have more than one matching record for each
[all so raw Before Part Numbers].PARTNO in the [Part Number List] table. The
SQL below should show you which PARTNO is listed more than once in [Part
Number List]. Usually it's perfectly fine to have more than one matching
record in the child table unless you have a 1-1 relationship between them.

SELECT R.PARTNO, Count(P.PARTNO)
FROM [all so raw Before Part Numbers] as R,
[Part Number List] as P
WHERE R.PARTNO = P.PARTNO
AND Count(P.PARTNO) > 1 ;
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Thanks,
First I fixed the origional problem of not enough rows (the query was
grouping where it shouldn't be), but now I have a new issue the table is
about 2000 rows too large.

Let me give you some background before I post the SQL (it s quite long). I
have a table that I import containing a bunch of servie/repair info. This
table list parts by number, my boss want to see parts by name and number.
The table is over 250,000 rows large so manual data entry isn't really
efficient.

So basically I want to add a part name column to the original table that
contains the correct part name for each corresponding part number.
So first I created a new table with 2 rows (a part number row and a part
name row). Then i designed a simple make table query joining the 2 tables
by part number and outputing a new table showingall the original columns plus
the part name column.
This is where the problem occrs. The new table is about 2000 rows larger
then the origional table. Why would this be happening? I have no primary
key and the row are not indexed (I inherited this table and due to doubles it
won't let me assign a primary key)


Here is the SQL:
SELECT *****(these are all the columns i need displayed)****
[all so raw Before Part Numbers].SYSORD,
[all so raw Before Part Numbers].REPORD,
[all so raw Before Part Numbers].CHGCDE,
[all so raw Before Part Numbers].PARTNO,
[Part Number List].PartName, ***(this is the new column )****
[all so raw Before Part Numbers].SERIAL,
[all so raw Before Part Numbers].FAILCD,
[all so raw Before Part Numbers].REPCDE,
[all so raw Before Part Numbers].MONTH,
[all so raw Before Part Numbers].YEAR,
[all so raw Before Part Numbers].MODEL,
[all so raw Before Part Numbers].CO,
[all so raw Before Part Numbers].BILLTO,
[all so raw Before Part Numbers].CUSNAM,
[all so raw Before Part Numbers].CUSCTY, [all so raw Before Part
Numbers].CUSST,
[all so raw Before Part Numbers].SDATE,
[all so raw Before Part Numbers].EDATE,
[all so raw Before Part Numbers].LBRHRS
INTO [all so raw]
***here is the join statement******
FROM [all so raw Before Part Numbers] LEFT JOIN [Part Number List] ON [all
so raw Before Part Numbers].PARTNO = [Part Number List].PARTNO;

Show us the SQL. Open the query in design view. Next go to View, SQL View
and copy and past it here. Information on primary keys and relationships
would be a nice touch too.

 

[Guest]

I bet that it's the lack of a primary key. The [all so raw Before Part
Numbers].PARTNO field needs to be the PK or you could return dupes. Run the
following to tell if you have any duplicate PARTNO. If so you'd need to fix
it before making that field the PK:

SELECT PARTNO, Count(PARTNO)
FROM [all so raw Before Part Numbers]
WHERE Count(PARTNO) > 1 ;

You'd also need to check for nulls.

SELECT PARTNO
FROM [all so raw Before Part Numbers]
WHERE PARTNO is Null;

After that I'd suspect that you have more than one matching record for each
[all so raw Before Part Numbers].PARTNO in the [Part Number List] table. The
SQL below should show you which PARTNO is listed more than once in [Part
Number List]. Usually it's perfectly fine to have more than one matching
record in the child table unless you have a 1-1 relationship between them.

SELECT R.PARTNO, Count(P.PARTNO)
FROM [all so raw Before Part Numbers] as R,
[Part Number List] as P
WHERE R.PARTNO = P.PARTNO
AND Count(P.PARTNO) > 1 ;
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Thanks,
First I fixed the origional problem of not enough rows (the query was
grouping where it shouldn't be), but now I have a new issue the table is
about 2000 rows too large.

Let me give you some background before I post the SQL (it s quite long). I
have a table that I import containing a bunch of servie/repair info. This
table list parts by number, my boss want to see parts by name and number.
The table is over 250,000 rows large so manual data entry isn't really
efficient.

So basically I want to add a part name column to the original table that
contains the correct part name for each corresponding part number.
So first I created a new table with 2 rows (a part number row and a part
name row). Then i designed a simple make table query joining the 2 tables
by part number and outputing a new table showingall the original columns plus
the part name column.
This is where the problem occrs. The new table is about 2000 rows larger
then the origional table. Why would this be happening? I have no primary
key and the row are not indexed (I inherited this table and due to doubles it
won't let me assign a primary key)


Here is the SQL:
SELECT *****(these are all the columns i need displayed)****
[all so raw Before Part Numbers].SYSORD,
[all so raw Before Part Numbers].REPORD,
[all so raw Before Part Numbers].CHGCDE,
[all so raw Before Part Numbers].PARTNO,
[Part Number List].PartName, ***(this is the new column )****
[all so raw Before Part Numbers].SERIAL,
[all so raw Before Part Numbers].FAILCD,
[all so raw Before Part Numbers].REPCDE,
[all so raw Before Part Numbers].MONTH,
[all so raw Before Part Numbers].YEAR,
[all so raw Before Part Numbers].MODEL,
[all so raw Before Part Numbers].CO,
[all so raw Before Part Numbers].BILLTO,
[all so raw Before Part Numbers].CUSNAM,
[all so raw Before Part Numbers].CUSCTY, [all so raw Before Part
Numbers].CUSST,
[all so raw Before Part Numbers].SDATE,
[all so raw Before Part Numbers].EDATE,
[all so raw Before Part Numbers].LBRHRS
INTO [all so raw]
***here is the join statement******
FROM [all so raw Before Part Numbers] LEFT JOIN [Part Number List] ON [all
so raw Before Part Numbers].PARTNO = [Part Number List].PARTNO;

Show us the SQL. Open the query in design view. Next go to View, SQL View
and copy and past it here. Information on primary keys and relationships
would be a nice touch too.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

:

Morning,
I have created a make tabke query to join 2 table (say table 1 and table
2) to make a third (table 3). I have selected option 2 in the join
properties ("include ALL records from table one and only those records from
table 2 where the joined fields are equal.")

When I run the query in end up ~5000 rows short on my final table (origional
table (1) had 273737 new table (3) has 268787).
Why is this happening if all the rows from table 1 are supposed to be
included?
If this is normal is there any way around this?

Notes: I am using Access2003 and am still new to Access in general.

I can supply more detaisl if needed. thanks for your help

 

[Guest]

Oops took me so long to write it that it actually logged me out, didn't
realize when it went back to the post screen that it deleted what I wrote.
here i go again.

First Thanks for all the help so far

I guess I haven't done a good job explaining the database's function.

First to answer your questions then I will explain some more if you want to
keep reading

First There are no nulls in the part number field

Second I had already verified there were no duplicates in the [part number
list] table by doing a make table query grouping by part number and creating
a new table without doubles. I deleted the old one and renamed the new one
[part number list]

Third, and this is where I did a poor job explaining
It is acceptable to have duplicat part numbers in the table, let me explain
The data base is to record details of servie repairs. Each repair and a
repair order number which is associated with the parts replaced on the
repair. Sop you have a row for each incident of a repair order and part let
me show you

Repair order # part #
1 1
1 2
2 3
3 2
3 3

As you can see there are duplicates in both the part number and repair order
columns so I can't assign a primary key (at least I dont knwo how)

I just don't understand why this make table query is adding rows. The join
rules simply state to use all the rows from Table one and when the
requirements are met to add info from table 2 into column [all so
raw].PartName. To me this would mean there would be no additional row added.

Thank you again for reading through this and for your time. I will continue
to trouble shoot and might just have to give up on adding that part name
column.

just a note: I tried to setup the queries you posted but I get an error
stating that i can't have an aggregate funtion in the where clause.

I bet that it's the lack of a primary key. The [all so raw Before Part
Numbers].PARTNO field needs to be the PK or you could return dupes. Run the
following to tell if you have any duplicate PARTNO. If so you'd need to fix
it before making that field the PK:

SELECT PARTNO, Count(PARTNO)
FROM [all so raw Before Part Numbers]
WHERE Count(PARTNO) > 1 ;

You'd also need to check for nulls.

SELECT PARTNO
FROM [all so raw Before Part Numbers]
WHERE PARTNO is Null;

After that I'd suspect that you have more than one matching record for each
[all so raw Before Part Numbers].PARTNO in the [Part Number List] table. The
SQL below should show you which PARTNO is listed more than once in [Part
Number List]. Usually it's perfectly fine to have more than one matching
record in the child table unless you have a 1-1 relationship between them.

SELECT R.PARTNO, Count(P.PARTNO)
FROM [all so raw Before Part Numbers] as R,
[Part Number List] as P
WHERE R.PARTNO = P.PARTNO
AND Count(P.PARTNO) > 1 ;
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Thanks,
First I fixed the origional problem of not enough rows (the query was
grouping where it shouldn't be), but now I have a new issue the table is
about 2000 rows too large.

Let me give you some background before I post the SQL (it s quite long). I
have a table that I import containing a bunch of servie/repair info. This
table list parts by number, my boss want to see parts by name and number.
The table is over 250,000 rows large so manual data entry isn't really
efficient.

So basically I want to add a part name column to the original table that
contains the correct part name for each corresponding part number.
So first I created a new table with 2 rows (a part number row and a part
name row). Then i designed a simple make table query joining the 2 tables
by part number and outputing a new table showingall the original columns plus
the part name column.
This is where the problem occrs. The new table is about 2000 rows larger
then the origional table. Why would this be happening? I have no primary
key and the row are not indexed (I inherited this table and due to doubles it
won't let me assign a primary key)


Here is the SQL:
SELECT *****(these are all the columns i need displayed)****
[all so raw Before Part Numbers].SYSORD,
[all so raw Before Part Numbers].REPORD,
[all so raw Before Part Numbers].CHGCDE,
[all so raw Before Part Numbers].PARTNO,
[Part Number List].PartName, ***(this is the new column )****
[all so raw Before Part Numbers].SERIAL,
[all so raw Before Part Numbers].FAILCD,
[all so raw Before Part Numbers].REPCDE,
[all so raw Before Part Numbers].MONTH,
[all so raw Before Part Numbers].YEAR,
[all so raw Before Part Numbers].MODEL,
[all so raw Before Part Numbers].CO,
[all so raw Before Part Numbers].BILLTO,
[all so raw Before Part Numbers].CUSNAM,
[all so raw Before Part Numbers].CUSCTY, [all so raw Before Part
Numbers].CUSST,
[all so raw Before Part Numbers].SDATE,
[all so raw Before Part Numbers].EDATE,
[all so raw Before Part Numbers].LBRHRS
INTO [all so raw]
***here is the join statement******
FROM [all so raw Before Part Numbers] LEFT JOIN [Part Number List] ON [all
so raw Before Part Numbers].PARTNO = [Part Number List].PARTNO;

Show us the SQL. Open the query in design view. Next go to View, SQL View
and copy and past it here. Information on primary keys and relationships
would be a nice touch too.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

:

Morning,
I have created a make tabke query to join 2 table (say table 1 and table
2) to make a third (table 3). I have selected option 2 in the join
properties ("include ALL records from table one and only those records from
table 2 where the joined fields are equal.")

When I run the query in end up ~5000 rows short on my final table (origional
table (1) had 273737 new table (3) has 268787).
Why is this happening if all the rows from table 1 are supposed to be
included?
If this is normal is there any way around this?

Notes: I am using Access2003 and am still new to Access in general.

I can supply more detaisl if needed. thanks for your help

 

[Guest]

Let's say that I have two tables with PartNo fields.

tblPartNo1.PartNo1 tblPartNo2.PartNo2
1 1
2 2
3 2
4 3

How many records with the following SQL, which is similar to yours, return?
SELECT tblPartNo1.PartNo1,
tblPartNo2.PartNo2
FROM tblPartNo1 LEFT JOIN tblPartNo2
ON tblPartNo1.PartNo1 = tblPartNo2.PartNo2;

The answer is 5 whic is one more than either table has. There are duplicates
on one side and that produces the following results. Even though PartNo1 has
only one "2", it will match up twice with the 2s in PartNo2.

PartNo1 PartNo2
1 1
2 2
2 2
3 3
4

Whoops! I goofed up on the SQL.

SELECT PARTNO, Count(PARTNO)
FROM [all so raw Before Part Numbers]
WHERE Count(PARTNO) > 1
GROUP BY PARTNO
;

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Oops took me so long to write it that it actually logged me out, didn't
realize when it went back to the post screen that it deleted what I wrote.
here i go again.

First Thanks for all the help so far

I guess I haven't done a good job explaining the database's function.

First to answer your questions then I will explain some more if you want to
keep reading

First There are no nulls in the part number field

Second I had already verified there were no duplicates in the [part number
list] table by doing a make table query grouping by part number and creating
a new table without doubles. I deleted the old one and renamed the new one
[part number list]

Third, and this is where I did a poor job explaining
It is acceptable to have duplicat part numbers in the table, let me explain
The data base is to record details of servie repairs. Each repair and a
repair order number which is associated with the parts replaced on the
repair. Sop you have a row for each incident of a repair order and part let
me show you

Repair order # part #
1 1
1 2
2 3
3 2
3 3

As you can see there are duplicates in both the part number and repair order
columns so I can't assign a primary key (at least I dont knwo how)

I just don't understand why this make table query is adding rows. The join
rules simply state to use all the rows from Table one and when the
requirements are met to add info from table 2 into column [all so
raw].PartName. To me this would mean there would be no additional row added.

Thank you again for reading through this and for your time. I will continue
to trouble shoot and might just have to give up on adding that part name
column.

just a note: I tried to setup the queries you posted but I get an error
stating that i can't have an aggregate funtion in the where clause.

I bet that it's the lack of a primary key. The [all so raw Before Part
Numbers].PARTNO field needs to be the PK or you could return dupes. Run the
following to tell if you have any duplicate PARTNO. If so you'd need to fix
it before making that field the PK:

SELECT PARTNO, Count(PARTNO)
FROM [all so raw Before Part Numbers]
WHERE Count(PARTNO) > 1 ;

You'd also need to check for nulls.

SELECT PARTNO
FROM [all so raw Before Part Numbers]
WHERE PARTNO is Null;

After that I'd suspect that you have more than one matching record for each
[all so raw Before Part Numbers].PARTNO in the [Part Number List] table. The
SQL below should show you which PARTNO is listed more than once in [Part
Number List]. Usually it's perfectly fine to have more than one matching
record in the child table unless you have a 1-1 relationship between them.

SELECT R.PARTNO, Count(P.PARTNO)
FROM [all so raw Before Part Numbers] as R,
[Part Number List] as P
WHERE R.PARTNO = P.PARTNO
AND Count(P.PARTNO) > 1 ;
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Thanks,
First I fixed the origional problem of not enough rows (the query was
grouping where it shouldn't be), but now I have a new issue the table is
about 2000 rows too large.

Let me give you some background before I post the SQL (it s quite long). I
have a table that I import containing a bunch of servie/repair info. This
table list parts by number, my boss want to see parts by name and number.
The table is over 250,000 rows large so manual data entry isn't really
efficient.

So basically I want to add a part name column to the original table that
contains the correct part name for each corresponding part number.
So first I created a new table with 2 rows (a part number row and a part
name row). Then i designed a simple make table query joining the 2 tables
by part number and outputing a new table showingall the original columns plus
the part name column.
This is where the problem occrs. The new table is about 2000 rows larger
then the origional table. Why would this be happening? I have no primary
key and the row are not indexed (I inherited this table and due to doubles it
won't let me assign a primary key)


Here is the SQL:
SELECT *****(these are all the columns i need displayed)****
[all so raw Before Part Numbers].SYSORD,
[all so raw Before Part Numbers].REPORD,
[all so raw Before Part Numbers].CHGCDE,
[all so raw Before Part Numbers].PARTNO,
[Part Number List].PartName, ***(this is the new column )****
[all so raw Before Part Numbers].SERIAL,
[all so raw Before Part Numbers].FAILCD,
[all so raw Before Part Numbers].REPCDE,
[all so raw Before Part Numbers].MONTH,
[all so raw Before Part Numbers].YEAR,
[all so raw Before Part Numbers].MODEL,
[all so raw Before Part Numbers].CO,
[all so raw Before Part Numbers].BILLTO,
[all so raw Before Part Numbers].CUSNAM,
[all so raw Before Part Numbers].CUSCTY, [all so raw Before Part
Numbers].CUSST,
[all so raw Before Part Numbers].SDATE,
[all so raw Before Part Numbers].EDATE,
[all so raw Before Part Numbers].LBRHRS
INTO [all so raw]
***here is the join statement******
FROM [all so raw Before Part Numbers] LEFT JOIN [Part Number List] ON [all
so raw Before Part Numbers].PARTNO = [Part Number List].PARTNO;

:

Show us the SQL. Open the query in design view. Next go to View, SQL View
and copy and past it here. Information on primary keys and relationships
would be a nice touch too.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

:

Morning,
I have created a make tabke query to join 2 table (say table 1 and table
2) to make a third (table 3). I have selected option 2 in the join
properties ("include ALL records from table one and only those records from
table 2 where the joined fields are equal.")

When I run the query in end up ~5000 rows short on my final table (origional
table (1) had 273737 new table (3) has 268787).
Why is this happening if all the rows from table 1 are supposed to be
included?
If this is normal is there any way around this?

Notes: I am using Access2003 and am still new to Access in general.

I can supply more detaisl if needed. thanks for your help