2 tables to combine into one by a query

[ashley]

Please help!
I have been trying to get the 2 tables to combine into one by a query. Here
are sample of my table1 and table2.

Table1
ITEM LOC1 LOC2 QTYTABLE1
1 01 01 8
1 01 140 148

Table2
ITEM LOC1 LOC2 QTYTABLE2
1 01 140 148

How can I get them to look like below?
ITEM LOC1 LOC2 QTYTABLE1 QTYTABLE2
1 01 01 8
1 01 140 148 148

Please reply to ashley.nguyen(removethis)@comcast.net

 

[Jeff Boyce]

Ashley

Open a new query in design mode. Select both tables. Drag the common
field(s) from one table to the corresponding field in the other table.
Select the fields you wish to display. Run the query.

 

[ashley]

I drag the item, loc1, loc2 from one table to another. It only show
ITEM LOC1 LOC2 QTYTABLE1 QTYTABLE2
1 01 140 148 148

How can I also get the other record?
ITEM LOC1 LOC2 QTYTABLE1 QTYTABLE2
1 01 01 8

I tried to change the Joint properties. I got ambiguous outer joints warning
message.
--
Thanks
Ashley

Ashley

Open a new query in design mode. Select both tables. Drag the common
field(s) from one table to the corresponding field in the other table.
Select the fields you wish to display. Run the query.

 

[Chris2]

Please help!
I have been trying to get the 2 tables to combine into one by a query. Here
are sample of my table1 and table2.

Table1
ITEM LOC1 LOC2 QTYTABLE1
1 01 01 8
1 01 140 148

Table2
ITEM LOC1 LOC2 QTYTABLE2
1 01 140 148

How can I get them to look like below?
ITEM LOC1 LOC2 QTYTABLE1 QTYTABLE2
1 01 01 8
1 01 140 148 148

Please reply to ashley.nguyen(removethis)@comcast.net

Ashley,

Please forgive the dates appended to the table names.

Tables:

My apologies, I could not determine what the Primary Key was, and so I
did not add one to either table.

CREATE TABLE Table1_11022005_1
(ITEM INTEGER
,LOC1 INTEGER
,LOC2 INTEGER
,QTYTABLE1 INTEGER
)

CREATE TABLE Table2_11022005_1
(ITEM INTEGER
,LOC1 INTEGER
,LOC2 INTEGER
,QTYTABLE2 INTEGER
)


Sample Data:

Table1_11022005_1
1, 1, 1, 8
1, 1, 140, 148


Table1_11022005_2
1, 1, 140, 148


Query:

SELECT T1.ITEM
,FORMAT(T1.LOC1, "0#")
,FORMAT(T1.LOC2, "0#")
,T1.QTYTABLE1
,T2.QTYTABLE2
FROM Table1_11022005_1 AS T1
LEFT JOIN
Table2_11022005_1 AS T2
ON T1.ITEM = T2.ITEM
AND T1.LOC1 = T2.LOC1
AND T1.LOC2 = T2.LOC2
AND T1.QTYTABLE1 = T2.QTYTABLE2

Results:

ITEM LOC1 LOC2 QTYTABLE1 QTYTABLE2
1 01 01 8
1 01 140 148 148


Sincerely,

Chris O.

 

[ashley]

I used this and it works!

SELECT T1.ITEM
,FORMAT(T1.LOC1, "0#")
,FORMAT(T1.LOC2, "0#")
,T1.QTYTABLE1
,T2.QTYTABLE2
FROM Table1_11022005_1 AS T1
LEFT JOIN
Table2_11022005_1 AS T2
ON T1.ITEM = T2.ITEM
AND T1.LOC1 = T2.LOC1
AND T1.LOC2 = T2.LOC2
AND T1.QTYTABLE1 = T2.QTYTABLE2

Old Result
ITEM LOC1 LOC2 QTYTABLE1 QTYTABLE2
1 01 01 8
1 01 140 148 148

But instead of blank, I need to add a zero to the first row under qtytable2.
So it look like new result. Could you or some help me with this criteria.
New result
ITEM LOC1 LOC2 QTYTABLE1 QTYTABLE2
1 01 01 8 0
1 01 140 148 148

 

[Chris2]

I used this and it works!

Old Result
ITEM LOC1 LOC2 QTYTABLE1 QTYTABLE2
1 01 01 8
1 01 140 148 148

But instead of blank, I need to add a zero to the first row under qtytable2.
So it look like new result. Could you or some help me with this criteria.
New result
ITEM LOC1 LOC2 QTYTABLE1 QTYTABLE2
1 01 01 8 0
1 01 140 148 148

Ashley,

Query:

I added an Nz() function to the last column.

Syntax Nz(<expression>, <value>) <expression> = sql column or valid
piece of code.
-- If the <expression> evaluates as NULL, <value> is returned.
-- If the <expression> evaluates as anything other than NULL, the
results of the <expression> are returned.

SELECT T1.ITEM
,FORMAT(T1.LOC1, "0#")
,FORMAT(T1.LOC2, "0#")
,T1.QTYTABLE1
,Nz(T2.QTYTABLE2, 0)
FROM Table1_11022005_1 AS T1
LEFT JOIN
Table2_11022005_1 AS T2
ON T1.ITEM = T2.ITEM
AND T1.LOC1 = T2.LOC1
AND T1.LOC2 = T2.LOC2
AND T1.QTYTABLE1 = T2.QTYTABLE2

Results:

ITEM LOC1 LOC2 QTYTABLE1 QTYTABLE2
1 01 01 8 0
1 01 140 148 148


Sincerely,

Chris O.