Append query for this?

[Guest]

Hi,

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

 

[Jason Lepack]

Two possibilities.

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

b) You didn't describe your problem.
Solution: Describe your problem.

 

[Guest]

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.

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 Lepack]

See comments inline.

Jason,
Sorry for the lack of details.

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:

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?

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

 

[Guest]

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 .

 

[Jason Lepack]

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.

See comments inline.

No worries here, just my being a bit of a smartass... sorry if it
bugged you, but you did much better this time.

Don't worry about that, if you keep wokring at it, you'll get better.

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

- Show quoted text -