Append query for this?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I need to append all of the records from table A to table B. Any help would
be appreciated.
 
Two possibilities.

a) You answered your own question.
Solution: Use an append query.

b) You didn't describe your problem.
Solution: Describe your problem.
 
Jason,
Sorry for the lack of details.

I want all of the rows in tblZeroTrans, appended to table
tblWeeklySalesReportDtl.
I'm not very SQL savvy. I created a query using the design view:

INSERT INTO tblWeeklySalesReportDtl ( PartNumber, QuantityOrdered,
ScheduledShipDate )
SELECT tblZeroTrans.IPartNumber, tblZeroTrans.IQtyOrdered,
tblZeroTrans.ICustPrice
FROM tblWeeklySalesReportDtl, tblZeroTrans;

When I run this, the result I want is the 156 rows in tblZeroTrans added.
The actual result is that 3,293,004 rows are added. There are already 21,109
rows in tblWeeklySalesReportDtl. 21,109 x 156 = 3,293,004.

Is there a way to just add the 156 rows?
--
Thanks.
Chuck M.


Jason Lepack said:
Two possibilities.

a) You answered your own question.
Solution: Use an append query.

b) You didn't describe your problem.
Solution: Describe your problem.
Click to expand...
 
See comments inline.

Jason,
Sorry for the lack of details.
Click to expand...

No worries here, just my being a bit of a smartass... sorry if it
bugged you, but you did much better this time.
I want all of the rows in tblZeroTrans, appended to table
tblWeeklySalesReportDtl.
I'm not very SQL savvy. I created a query using the design view:
Click to expand...

Don't worry about that, if you keep wokring at it, you'll get better.
INSERT INTO tblWeeklySalesReportDtl ( PartNumber, QuantityOrdered,
ScheduledShipDate )
SELECT tblZeroTrans.IPartNumber, tblZeroTrans.IQtyOrdered,
tblZeroTrans.ICustPrice
FROM tblWeeklySalesReportDtl, tblZeroTrans;

When I run this, the result I want is the 156 rows in tblZeroTrans added.
The actual result is that 3,293,004 rows are added. There are already 21,109
rows in tblWeeklySalesReportDtl. 21,109 x 156 = 3,293,004.

Is there a way to just add the 156 rows?
Click to expand...

By including both tables in the SELECT statement you have created what
is called a "Cartesian Join" or "Cross Product". Each record in table
A is matched up with each record in Table B or rather in your case,
21109*156 = 3293004 records.

All you need to do is remove tblWeeklySalesReportDtl from your Query
Designer View and you should just have 156 records inserted.

You're welcome.

Cheers,
Jason Lepack
 
Jason,

This worked perfectly, thanks! You didn't bug me with you comment. When you
think about it, if I had taken my car to a mechanic and only said 'my car
doesn't move when I press on the gas pedal what do you think the problem is?'
I think he would be looking for some details as well :-).
 
Cheers!

Jason

Jason,

This worked perfectly, thanks! You didn't bug me with you comment. When you
think about it, if I had taken my car to a mechanic and only said 'my car
doesn't move when I press on the gas pedal what do you think the problem is?'
I think he would be looking for some details as well :-).
--
Thanks again!
Chuck M.



Jason Lepack said:
See comments inline.
Click to expand...
No worries here, just my being a bit of a smartass... sorry if it
bugged you, but you did much better this time.
Click to expand...
Don't worry about that, if you keep wokring at it, you'll get better.
Click to expand...
By including both tables in the SELECT statement you have created what
is called a "Cartesian Join" or "Cross Product". Each record in table
A is matched up with each record in Table B or rather in your case,
21109*156 = 3293004 records.
Click to expand...
All you need to do is remove tblWeeklySalesReportDtl from your Query
Designer View and you should just have 156 records inserted.
Click to expand...
You're welcome.
Click to expand...
Cheers,
Jason Lepack
Click to expand...

- Show quoted text -
Click to expand...
 
Back
Top