3 queries into 1

Hello, I am going to try this again starting fresh from my other posts. I am
trying to develop an all inclusive query on our inventory. Each fical term
we start with new tables. I would like to take our Ending Inventory from our
prior fiscal term and combine it with our current inventory balances. In
years past I always took our ending inventory and posted them in our purchase
order table to have our beginning balances. I thought it would be less time
consuming to have a query to combine the 2. Below is a list of data and the
query designs.
PART # PRODUCT DESCRIPTION END BALANCE
1018 "OD 12.7MM X 24"" LONG" 27
11509671 BOLT-METRIX HEX FLANGE 93
11515781 GEAR BOX ASSEMBLY STEERING FIXING BOLT 25
11588324 NUT WELD HEX FLANGE (M10X1.5) 172
11588325 NUT WELD HEX FLANGE (M12X1.75) 55
11589252 GMT319 L4 BOLT 6


SELECT [INVENTORY R&D 7th].[PART #], [INVENTORY R&D 7th].[PART NAME],
[INVENTORY R&D 7th].[END BALANCE]
FROM [INVENTORY R&D 7th]
WHERE ((([INVENTORY R&D 7th].[END BALANCE])0));


PART # PRODUCT DESCRIPTION Sum Of QTY RECVD Sum Of MEMO QTY SHIP END BALANCE
1018 "OD 12.7MM X 24"" LONG" 2 0 2
1234 test 1 0 1

SELECT DISTINCTROW [PO'S FOR SALES 8TH].[PART #], [PO'S FOR SALES 8TH].[PART
NAME], Sum([PO'S FOR SALES 8TH].[QTY RECVD]) AS [Sum Of QTY RECVD], Sum([PO'S
FOR SALES 8TH].[MEMO QTY SHIP]) AS [Sum Of MEMO QTY SHIP], [Sum Of QTY
RECVD]-[Sum Of MEMO QTY SHIP] AS [END BALANCE]
FROM [PO'S FOR SALES 8TH]
GROUP BY [PO'S FOR SALES 8TH].[PART #], [PO'S FOR SALES 8TH].[PART NAME],
[Sum Of QTY RECVD]-[Sum Of MEMO QTY SHIP]
HAVING ((([PO'S FOR SALES 8TH].[PART NAME])"0") AND ((Sum([PO'S FOR SALES
8TH].[QTY RECVD]))0));

PART # PART NAME
1018 "OD 12.7MM X 24"" LONG"
11509671 BOLT-METRIX HEX FLANGE
11515781 GEAR BOX ASSEMBLY STEERING FIXING BOLT
11588324 NUT WELD HEX FLANGE (M10X1.5)
11588325 NUT WELD HEX FLANGE (M12X1.75)
11589252 GMT319 L4 BOLT
1234 test

SELECT [PART #],[PART NAME]
FROM [BEGINNING INVENTORY BALANCE 8TH]
UNION SELECT [PART #],[PART NAME]
FROM [INVENTORY R&D];

PART # PART NAME TERM 7 TERM 8 SUM OF BALANCES
1018 "OD 12.7MM X 24"" LONG" 0
11509671 BOLT-METRIX HEX FLANGE 0
11515781 GEAR BOX ASSEMBLY STEERING FIXING BOLT 0
11588324 NUT WELD HEX FLANGE (M10X1.5) 0
11588325 NUT WELD HEX FLANGE (M12X1.75) 0
11589252 GMT319 L4 BOLT 0
1234 test 0
15226899-03-01 BODY MOUNT FRT LWR LH 0

SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME],[BEGINNING
INVENTORY BALANCE 8TH].[END BALANCE] AS [TERM 7],[INVENTORY R&D].[END
BALANCE] AS [TERM 8],nz
([BEGINNING INVENTORY BALANCE 8TH].[END BALANCE],0) +nz ([INVENTORY
R&D].[END BALANCE],0) AS [SUM OF BALANCES]
FROM ([COMBINED PARTS] LEFT JOIN [BEGINNING INVENTORY BALANCE 8TH] ON
[COMBINED PARTS].[PART #] & [BEGINNING INVENTORY BALANCE 8TH].[PART #]) LEFT
JOIN [INVENTORY R&D] ON [COMBINED PARTS].[PART #] = [INVENTORY R&D].[PART #]

the last table, as you can see isn't pulling the balances of my inventory.
Do I need a where statement or any suggestions?

Use this simple totals query on the union query --
SELECT [PART #],[PART NAME], Sum([END BALANCE]) AS Final_Balance
FROM YourUnionQuery
GROUP BY [PART #],[PART NAME];

--
Build a little, test a little.


"lmiller" wrote:

Hello, I am going to try this again starting fresh from my other posts. I am
trying to develop an all inclusive query on our inventory. Each fical term
we start with new tables. I would like to take our Ending Inventory from our
prior fiscal term and combine it with our current inventory balances. In
years past I always took our ending inventory and posted them in our purchase
order table to have our beginning balances. I thought it would be less time
consuming to have a query to combine the 2. Below is a list of data and the
query designs.
PART # PRODUCT DESCRIPTION END BALANCE
1018 "OD 12.7MM X 24"" LONG" 27
11509671 BOLT-METRIX HEX FLANGE 93
11515781 GEAR BOX ASSEMBLY STEERING FIXING BOLT 25
11588324 NUT WELD HEX FLANGE (M10X1.5) 172
11588325 NUT WELD HEX FLANGE (M12X1.75) 55
11589252 GMT319 L4 BOLT 6


SELECT [INVENTORY R&D 7th].[PART #], [INVENTORY R&D 7th].[PART NAME],
[INVENTORY R&D 7th].[END BALANCE]
FROM [INVENTORY R&D 7th]
WHERE ((([INVENTORY R&D 7th].[END BALANCE])0));


PART # PRODUCT DESCRIPTION Sum Of QTY RECVD Sum Of MEMO QTY SHIP END BALANCE
1018 "OD 12.7MM X 24"" LONG" 2 0 2
1234 test 1 0 1

SELECT DISTINCTROW [PO'S FOR SALES 8TH].[PART #], [PO'S FOR SALES 8TH].[PART
NAME], Sum([PO'S FOR SALES 8TH].[QTY RECVD]) AS [Sum Of QTY RECVD], Sum([PO'S
FOR SALES 8TH].[MEMO QTY SHIP]) AS [Sum Of MEMO QTY SHIP], [Sum Of QTY
RECVD]-[Sum Of MEMO QTY SHIP] AS [END BALANCE]
FROM [PO'S FOR SALES 8TH]
GROUP BY [PO'S FOR SALES 8TH].[PART #], [PO'S FOR SALES 8TH].[PART NAME],
[Sum Of QTY RECVD]-[Sum Of MEMO QTY SHIP]
HAVING ((([PO'S FOR SALES 8TH].[PART NAME])"0") AND ((Sum([PO'S FOR SALES
8TH].[QTY RECVD]))0));

PART # PART NAME
1018 "OD 12.7MM X 24"" LONG"
11509671 BOLT-METRIX HEX FLANGE
11515781 GEAR BOX ASSEMBLY STEERING FIXING BOLT
11588324 NUT WELD HEX FLANGE (M10X1.5)
11588325 NUT WELD HEX FLANGE (M12X1.75)
11589252 GMT319 L4 BOLT
1234 test

SELECT [PART #],[PART NAME]
FROM [BEGINNING INVENTORY BALANCE 8TH]
UNION SELECT [PART #],[PART NAME]
FROM [INVENTORY R&D];

PART # PART NAME TERM 7 TERM 8 SUM OF BALANCES
1018 "OD 12.7MM X 24"" LONG" 0
11509671 BOLT-METRIX HEX FLANGE 0
11515781 GEAR BOX ASSEMBLY STEERING FIXING BOLT 0
11588324 NUT WELD HEX FLANGE (M10X1.5) 0
11588325 NUT WELD HEX FLANGE (M12X1.75) 0
11589252 GMT319 L4 BOLT 0
1234 test 0
15226899-03-01 BODY MOUNT FRT LWR LH 0

SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME],[BEGINNING
INVENTORY BALANCE 8TH].[END BALANCE] AS [TERM 7],[INVENTORY R&D].[END
BALANCE] AS [TERM 8],nz
([BEGINNING INVENTORY BALANCE 8TH].[END BALANCE],0) +nz ([INVENTORY
R&D].[END BALANCE],0) AS [SUM OF BALANCES]
FROM ([COMBINED PARTS] LEFT JOIN [BEGINNING INVENTORY BALANCE 8TH] ON
[COMBINED PARTS].[PART #] & [BEGINNING INVENTORY BALANCE 8TH].[PART #]) LEFT
JOIN [INVENTORY R&D] ON [COMBINED PARTS].[PART #] = [INVENTORY R&D].[PART #]

the last table, as you can see isn't pulling the balances of my inventory.
Do I need a where statement or any suggestions?

Lmiller -

Karl's idea is good, but you don't have the balances in the UNION query, so
it won't work quite right.

I don't see anything wrong with how the SQL is is currently coded (other
than the use of special characters in the field names), but let's try
changing the [END BALANCE] names in the two source queries, and then in the
combined query.

I changed the [END BALANCE] to [END BALANCE 7] in the first query, [END
BALANCE] to [END BALANCE 8] in the second query, and then updated the
Combined query to use them. See if these work.


SELECT [INVENTORY R&D 7th].[PART #], [INVENTORY R&D 7th].[PART NAME],
[INVENTORY R&D 7th].[END BALANCE] AS [END BALANCE 7]
FROM [INVENTORY R&D 7th]
WHERE ((([INVENTORY R&D 7th].[END BALANCE])0));


SELECT DISTINCTROW [PO'S FOR SALES 8TH].[PART #], [PO'S FOR SALES 8TH].[PART
NAME], Sum([PO'S FOR SALES 8TH].[QTY RECVD]) AS [Sum Of QTY RECVD], Sum([PO'S
FOR SALES 8TH].[MEMO QTY SHIP]) AS [Sum Of MEMO QTY SHIP], [Sum Of QTY
RECVD]-[Sum Of MEMO QTY SHIP] AS [END BALANCE 8]
FROM [PO'S FOR SALES 8TH]
GROUP BY [PO'S FOR SALES 8TH].[PART #], [PO'S FOR SALES 8TH].[PART NAME],
[Sum Of QTY RECVD]-[Sum Of MEMO QTY SHIP]
HAVING ((([PO'S FOR SALES 8TH].[PART NAME])"0") AND ((Sum([PO'S FOR SALES
8TH].[QTY RECVD]))0));


SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME],[BEGINNING
INVENTORY BALANCE 8TH].[END BALANCE 7],[INVENTORY R&D].[END
BALANCE 8], (nz([BEGINNING INVENTORY BALANCE 8TH].[END BALANCE 8],0) +
nz([INVENTORY
R&D].[END BALANCE 7],0)) AS [SUM OF BALANCES]
FROM ([COMBINED PARTS] LEFT JOIN [BEGINNING INVENTORY BALANCE 8TH] ON
[COMBINED PARTS].[PART #] & [BEGINNING INVENTORY BALANCE 8TH].[PART #]) LEFT
JOIN [INVENTORY R&D] ON [COMBINED PARTS].[PART #] = [INVENTORY R&D].[PART #]

--
Daryl S


"lmiller" wrote:

Hello, I am going to try this again starting fresh from my other posts. I am
trying to develop an all inclusive query on our inventory. Each fical term
we start with new tables. I would like to take our Ending Inventory from our
prior fiscal term and combine it with our current inventory balances. In
years past I always took our ending inventory and posted them in our purchase
order table to have our beginning balances. I thought it would be less time
consuming to have a query to combine the 2. Below is a list of data and the
query designs.
PART # PRODUCT DESCRIPTION END BALANCE
1018 "OD 12.7MM X 24"" LONG" 27
11509671 BOLT-METRIX HEX FLANGE 93
11515781 GEAR BOX ASSEMBLY STEERING FIXING BOLT 25
11588324 NUT WELD HEX FLANGE (M10X1.5) 172
11588325 NUT WELD HEX FLANGE (M12X1.75) 55
11589252 GMT319 L4 BOLT 6


SELECT [INVENTORY R&D 7th].[PART #], [INVENTORY R&D 7th].[PART NAME],
[INVENTORY R&D 7th].[END BALANCE]
FROM [INVENTORY R&D 7th]
WHERE ((([INVENTORY R&D 7th].[END BALANCE])0));


PART # PRODUCT DESCRIPTION Sum Of QTY RECVD Sum Of MEMO QTY SHIP END BALANCE
1018 "OD 12.7MM X 24"" LONG" 2 0 2
1234 test 1 0 1

SELECT DISTINCTROW [PO'S FOR SALES 8TH].[PART #], [PO'S FOR SALES 8TH].[PART
NAME], Sum([PO'S FOR SALES 8TH].[QTY RECVD]) AS [Sum Of QTY RECVD], Sum([PO'S
FOR SALES 8TH].[MEMO QTY SHIP]) AS [Sum Of MEMO QTY SHIP], [Sum Of QTY
RECVD]-[Sum Of MEMO QTY SHIP] AS [END BALANCE]
FROM [PO'S FOR SALES 8TH]
GROUP BY [PO'S FOR SALES 8TH].[PART #], [PO'S FOR SALES 8TH].[PART NAME],
[Sum Of QTY RECVD]-[Sum Of MEMO QTY SHIP]
HAVING ((([PO'S FOR SALES 8TH].[PART NAME])"0") AND ((Sum([PO'S FOR SALES
8TH].[QTY RECVD]))0));

PART # PART NAME
1018 "OD 12.7MM X 24"" LONG"
11509671 BOLT-METRIX HEX FLANGE
11515781 GEAR BOX ASSEMBLY STEERING FIXING BOLT
11588324 NUT WELD HEX FLANGE (M10X1.5)
11588325 NUT WELD HEX FLANGE (M12X1.75)
11589252 GMT319 L4 BOLT
1234 test

SELECT [PART #],[PART NAME]
FROM [BEGINNING INVENTORY BALANCE 8TH]
UNION SELECT [PART #],[PART NAME]
FROM [INVENTORY R&D];

PART # PART NAME TERM 7 TERM 8 SUM OF BALANCES
1018 "OD 12.7MM X 24"" LONG" 0
11509671 BOLT-METRIX HEX FLANGE 0
11515781 GEAR BOX ASSEMBLY STEERING FIXING BOLT 0
11588324 NUT WELD HEX FLANGE (M10X1.5) 0
11588325 NUT WELD HEX FLANGE (M12X1.75) 0
11589252 GMT319 L4 BOLT 0
1234 test 0
15226899-03-01 BODY MOUNT FRT LWR LH 0

SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME],[BEGINNING
INVENTORY BALANCE 8TH].[END BALANCE] AS [TERM 7],[INVENTORY R&D].[END
BALANCE] AS [TERM 8],nz
([BEGINNING INVENTORY BALANCE 8TH].[END BALANCE],0) +nz ([INVENTORY
R&D].[END BALANCE],0) AS [SUM OF BALANCES]
FROM ([COMBINED PARTS] LEFT JOIN [BEGINNING INVENTORY BALANCE 8TH] ON
[COMBINED PARTS].[PART #] & [BEGINNING INVENTORY BALANCE 8TH].[PART #]) LEFT
JOIN [INVENTORY R&D] ON [COMBINED PARTS].[PART #] = [INVENTORY R&D].[PART #]

the last table, as you can see isn't pulling the balances of my inventory.
Do I need a where statement or any suggestions?

I just had a chance to try your suggestion, unfortunatly the result was the
same. It still isn't pulling any balances. It shows up as a 0 balance for
each part #.

"Daryl S" wrote:

Lmiller -

Karl's idea is good, but you don't have the balances in the UNION query, so
it won't work quite right.

I don't see anything wrong with how the SQL is is currently coded (other
than the use of special characters in the field names), but let's try
changing the [END BALANCE] names in the two source queries, and then in the
combined query.

I changed the [END BALANCE] to [END BALANCE 7] in the first query, [END
BALANCE] to [END BALANCE 8] in the second query, and then updated the
Combined query to use them. See if these work.


SELECT [INVENTORY R&D 7th].[PART #], [INVENTORY R&D 7th].[PART NAME],
[INVENTORY R&D 7th].[END BALANCE] AS [END BALANCE 7]
FROM [INVENTORY R&D 7th]
WHERE ((([INVENTORY R&D 7th].[END BALANCE])0));


SELECT DISTINCTROW [PO'S FOR SALES 8TH].[PART #], [PO'S FOR SALES 8TH].[PART
NAME], Sum([PO'S FOR SALES 8TH].[QTY RECVD]) AS [Sum Of QTY RECVD], Sum([PO'S
FOR SALES 8TH].[MEMO QTY SHIP]) AS [Sum Of MEMO QTY SHIP], [Sum Of QTY
RECVD]-[Sum Of MEMO QTY SHIP] AS [END BALANCE 8]
FROM [PO'S FOR SALES 8TH]
GROUP BY [PO'S FOR SALES 8TH].[PART #], [PO'S FOR SALES 8TH].[PART NAME],
[Sum Of QTY RECVD]-[Sum Of MEMO QTY SHIP]
HAVING ((([PO'S FOR SALES 8TH].[PART NAME])"0") AND ((Sum([PO'S FOR SALES
8TH].[QTY RECVD]))0));


SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME],[BEGINNING
INVENTORY BALANCE 8TH].[END BALANCE 7],[INVENTORY R&D].[END
BALANCE 8], (nz([BEGINNING INVENTORY BALANCE 8TH].[END BALANCE 8],0) +
nz([INVENTORY
R&D].[END BALANCE 7],0)) AS [SUM OF BALANCES]
FROM ([COMBINED PARTS] LEFT JOIN [BEGINNING INVENTORY BALANCE 8TH] ON
[COMBINED PARTS].[PART #] & [BEGINNING INVENTORY BALANCE 8TH].[PART #]) LEFT
JOIN [INVENTORY R&D] ON [COMBINED PARTS].[PART #] = [INVENTORY R&D].[PART #]

--
Daryl S


"lmiller" wrote:

Hello, I am going to try this again starting fresh from my other posts. I am
trying to develop an all inclusive query on our inventory. Each fical term
we start with new tables. I would like to take our Ending Inventory from our
prior fiscal term and combine it with our current inventory balances. In
years past I always took our ending inventory and posted them in our purchase
order table to have our beginning balances. I thought it would be less time
consuming to have a query to combine the 2. Below is a list of data and the
query designs.
PART # PRODUCT DESCRIPTION END BALANCE
1018 "OD 12.7MM X 24"" LONG" 27
11509671 BOLT-METRIX HEX FLANGE 93
11515781 GEAR BOX ASSEMBLY STEERING FIXING BOLT 25
11588324 NUT WELD HEX FLANGE (M10X1.5) 172
11588325 NUT WELD HEX FLANGE (M12X1.75) 55
11589252 GMT319 L4 BOLT 6


SELECT [INVENTORY R&D 7th].[PART #], [INVENTORY R&D 7th].[PART NAME],
[INVENTORY R&D 7th].[END BALANCE]
FROM [INVENTORY R&D 7th]
WHERE ((([INVENTORY R&D 7th].[END BALANCE])0));


PART # PRODUCT DESCRIPTION Sum Of QTY RECVD Sum Of MEMO QTY SHIP END BALANCE
1018 "OD 12.7MM X 24"" LONG" 2 0 2
1234 test 1 0 1

SELECT DISTINCTROW [PO'S FOR SALES 8TH].[PART #], [PO'S FOR SALES 8TH].[PART
NAME], Sum([PO'S FOR SALES 8TH].[QTY RECVD]) AS [Sum Of QTY RECVD], Sum([PO'S
FOR SALES 8TH].[MEMO QTY SHIP]) AS [Sum Of MEMO QTY SHIP], [Sum Of QTY
RECVD]-[Sum Of MEMO QTY SHIP] AS [END BALANCE]
FROM [PO'S FOR SALES 8TH]
GROUP BY [PO'S FOR SALES 8TH].[PART #], [PO'S FOR SALES 8TH].[PART NAME],
[Sum Of QTY RECVD]-[Sum Of MEMO QTY SHIP]
HAVING ((([PO'S FOR SALES 8TH].[PART NAME])"0") AND ((Sum([PO'S FOR SALES
8TH].[QTY RECVD]))0));

PART # PART NAME
1018 "OD 12.7MM X 24"" LONG"
11509671 BOLT-METRIX HEX FLANGE
11515781 GEAR BOX ASSEMBLY STEERING FIXING BOLT
11588324 NUT WELD HEX FLANGE (M10X1.5)
11588325 NUT WELD HEX FLANGE (M12X1.75)
11589252 GMT319 L4 BOLT
1234 test

SELECT [PART #],[PART NAME]
FROM [BEGINNING INVENTORY BALANCE 8TH]
UNION SELECT [PART #],[PART NAME]
FROM [INVENTORY R&D];

PART # PART NAME TERM 7 TERM 8 SUM OF BALANCES
1018 "OD 12.7MM X 24"" LONG" 0
11509671 BOLT-METRIX HEX FLANGE 0
11515781 GEAR BOX ASSEMBLY STEERING FIXING BOLT 0
11588324 NUT WELD HEX FLANGE (M10X1.5) 0
11588325 NUT WELD HEX FLANGE (M12X1.75) 0
11589252 GMT319 L4 BOLT 0
1234 test 0
15226899-03-01 BODY MOUNT FRT LWR LH 0

SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME],[BEGINNING
INVENTORY BALANCE 8TH].[END BALANCE] AS [TERM 7],[INVENTORY R&D].[END
BALANCE] AS [TERM 8],nz
([BEGINNING INVENTORY BALANCE 8TH].[END BALANCE],0) +nz ([INVENTORY
R&D].[END BALANCE],0) AS [SUM OF BALANCES]
FROM ([COMBINED PARTS] LEFT JOIN [BEGINNING INVENTORY BALANCE 8TH] ON
[COMBINED PARTS].[PART #] & [BEGINNING INVENTORY BALANCE 8TH].[PART #]) LEFT
JOIN [INVENTORY R&D] ON [COMBINED PARTS].[PART #] = [INVENTORY R&D].[PART #]

the last table, as you can see isn't pulling the balances of my inventory.
Do I need a where statement or any suggestions?

thanks Karl, but unfortuantly that didn't work

"KARL DEWEY" wrote:

Use this simple totals query on the union query --
SELECT [PART #],[PART NAME], Sum([END BALANCE]) AS Final_Balance
FROM YourUnionQuery
GROUP BY [PART #],[PART NAME];

--
Build a little, test a little.


"lmiller" wrote:

Hello, I am going to try this again starting fresh from my other posts. I am
trying to develop an all inclusive query on our inventory. Each fical term
we start with new tables. I would like to take our Ending Inventory from our
prior fiscal term and combine it with our current inventory balances. In
years past I always took our ending inventory and posted them in our purchase
order table to have our beginning balances. I thought it would be less time
consuming to have a query to combine the 2. Below is a list of data and the
query designs.
PART # PRODUCT DESCRIPTION END BALANCE
1018 "OD 12.7MM X 24"" LONG" 27
11509671 BOLT-METRIX HEX FLANGE 93
11515781 GEAR BOX ASSEMBLY STEERING FIXING BOLT 25
11588324 NUT WELD HEX FLANGE (M10X1.5) 172
11588325 NUT WELD HEX FLANGE (M12X1.75) 55
11589252 GMT319 L4 BOLT 6


SELECT [INVENTORY R&D 7th].[PART #], [INVENTORY R&D 7th].[PART NAME],
[INVENTORY R&D 7th].[END BALANCE]
FROM [INVENTORY R&D 7th]
WHERE ((([INVENTORY R&D 7th].[END BALANCE])0));


PART # PRODUCT DESCRIPTION Sum Of QTY RECVD Sum Of MEMO QTY SHIP END BALANCE
1018 "OD 12.7MM X 24"" LONG" 2 0 2
1234 test 1 0 1

SELECT DISTINCTROW [PO'S FOR SALES 8TH].[PART #], [PO'S FOR SALES 8TH].[PART
NAME], Sum([PO'S FOR SALES 8TH].[QTY RECVD]) AS [Sum Of QTY RECVD], Sum([PO'S
FOR SALES 8TH].[MEMO QTY SHIP]) AS [Sum Of MEMO QTY SHIP], [Sum Of QTY
RECVD]-[Sum Of MEMO QTY SHIP] AS [END BALANCE]
FROM [PO'S FOR SALES 8TH]
GROUP BY [PO'S FOR SALES 8TH].[PART #], [PO'S FOR SALES 8TH].[PART NAME],
[Sum Of QTY RECVD]-[Sum Of MEMO QTY SHIP]
HAVING ((([PO'S FOR SALES 8TH].[PART NAME])"0") AND ((Sum([PO'S FOR SALES
8TH].[QTY RECVD]))0));

PART # PART NAME
1018 "OD 12.7MM X 24"" LONG"
11509671 BOLT-METRIX HEX FLANGE
11515781 GEAR BOX ASSEMBLY STEERING FIXING BOLT
11588324 NUT WELD HEX FLANGE (M10X1.5)
11588325 NUT WELD HEX FLANGE (M12X1.75)
11589252 GMT319 L4 BOLT
1234 test

SELECT [PART #],[PART NAME]
FROM [BEGINNING INVENTORY BALANCE 8TH]
UNION SELECT [PART #],[PART NAME]
FROM [INVENTORY R&D];

PART # PART NAME TERM 7 TERM 8 SUM OF BALANCES
1018 "OD 12.7MM X 24"" LONG" 0
11509671 BOLT-METRIX HEX FLANGE 0
11515781 GEAR BOX ASSEMBLY STEERING FIXING BOLT 0
11588324 NUT WELD HEX FLANGE (M10X1.5) 0
11588325 NUT WELD HEX FLANGE (M12X1.75) 0
11589252 GMT319 L4 BOLT 0
1234 test 0
15226899-03-01 BODY MOUNT FRT LWR LH 0

SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME],[BEGINNING
INVENTORY BALANCE 8TH].[END BALANCE] AS [TERM 7],[INVENTORY R&D].[END
BALANCE] AS [TERM 8],nz
([BEGINNING INVENTORY BALANCE 8TH].[END BALANCE],0) +nz ([INVENTORY
R&D].[END BALANCE],0) AS [SUM OF BALANCES]
FROM ([COMBINED PARTS] LEFT JOIN [BEGINNING INVENTORY BALANCE 8TH] ON
[COMBINED PARTS].[PART #] & [BEGINNING INVENTORY BALANCE 8TH].[PART #]) LEFT
JOIN [INVENTORY R&D] ON [COMBINED PARTS].[PART #] = [INVENTORY R&D].[PART #]

the last table, as you can see isn't pulling the balances of my inventory.
Do I need a where statement or any suggestions?

Daryl S,

Here is the query, I believe you suggested:

SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME], [BEGINNING
INVENTORY BALANCE 8TH].[END BALANCE 7], [INVENTORY R&D].[END BALANCE 8],
nz([BEGINNING INVENTORY BALANCE 8TH].[END BALANCE 7],0)+nz([INVENTORY
R&D].[END BALANCE 8],0) AS [SUM OF BALANCES]
FROM ([COMBINED PARTS] LEFT JOIN [BEGINNING INVENTORY BALANCE 8TH] ON
[COMBINED PARTS].[PART #] & [BEGINNING INVENTORY BALANCE 8TH].[PART #]) LEFT
JOIN [INVENTORY R&D] ON [COMBINED PARTS].[PART #]=[INVENTORY R&D].[PART #];

PART # PART NAME END BALANCE 7 END BALANCE 8 SUM OF BALANCES
1018 "OD 12.7MM X 24"" LONG" 0
1018 "OD 12.7MM X 24"" LONG" 0
11509671 BOLT-METRIX HEX FLANGE 0
11515781 GEAR BOX ASSEMBLY STEERING FIXING BOLT 0
11588324 NUT WELD HEX FLANGE (M10X1.5) 0
11588325 NUT WELD HEX FLANGE (M12X1.75) 0
11589252 GMT319 L4 BOLT 0
1234 test 0

Any other suggestions? I also tried Karl's and with no success
I did change to End Balance 7, and End Balance 8 in the other tables. The
data still is coming back as 0 blances:



"Daryl S" wrote:

Lmiller -

Karl's idea is good, but you don't have the balances in the UNION query, so
it won't work quite right.

I don't see anything wrong with how the SQL is is currently coded (other
than the use of special characters in the field names), but let's try
changing the [END BALANCE] names in the two source queries, and then in the
combined query.

I changed the [END BALANCE] to [END BALANCE 7] in the first query, [END
BALANCE] to [END BALANCE 8] in the second query, and then updated the
Combined query to use them. See if these work.


SELECT [INVENTORY R&D 7th].[PART #], [INVENTORY R&D 7th].[PART NAME],
[INVENTORY R&D 7th].[END BALANCE] AS [END BALANCE 7]
FROM [INVENTORY R&D 7th]
WHERE ((([INVENTORY R&D 7th].[END BALANCE])0));


SELECT DISTINCTROW [PO'S FOR SALES 8TH].[PART #], [PO'S FOR SALES 8TH].[PART
NAME], Sum([PO'S FOR SALES 8TH].[QTY RECVD]) AS [Sum Of QTY RECVD], Sum([PO'S
FOR SALES 8TH].[MEMO QTY SHIP]) AS [Sum Of MEMO QTY SHIP], [Sum Of QTY
RECVD]-[Sum Of MEMO QTY SHIP] AS [END BALANCE 8]
FROM [PO'S FOR SALES 8TH]
GROUP BY [PO'S FOR SALES 8TH].[PART #], [PO'S FOR SALES 8TH].[PART NAME],
[Sum Of QTY RECVD]-[Sum Of MEMO QTY SHIP]
HAVING ((([PO'S FOR SALES 8TH].[PART NAME])"0") AND ((Sum([PO'S FOR SALES
8TH].[QTY RECVD]))0));


SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME],[BEGINNING
INVENTORY BALANCE 8TH].[END BALANCE 7],[INVENTORY R&D].[END
BALANCE 8], (nz([BEGINNING INVENTORY BALANCE 8TH].[END BALANCE 8],0) +
nz([INVENTORY
R&D].[END BALANCE 7],0)) AS [SUM OF BALANCES]
FROM ([COMBINED PARTS] LEFT JOIN [BEGINNING INVENTORY BALANCE 8TH] ON
[COMBINED PARTS].[PART #] & [BEGINNING INVENTORY BALANCE 8TH].[PART #]) LEFT
JOIN [INVENTORY R&D] ON [COMBINED PARTS].[PART #] = [INVENTORY R&D].[PART #]

--
Daryl S


"lmiller" wrote:

Hello, I am going to try this again starting fresh from my other posts. I am
trying to develop an all inclusive query on our inventory. Each fical term
we start with new tables. I would like to take our Ending Inventory from our
prior fiscal term and combine it with our current inventory balances. In
years past I always took our ending inventory and posted them in our purchase
order table to have our beginning balances. I thought it would be less time
consuming to have a query to combine the 2. Below is a list of data and the
query designs.
PART # PRODUCT DESCRIPTION END BALANCE
1018 "OD 12.7MM X 24"" LONG" 27
11509671 BOLT-METRIX HEX FLANGE 93
11515781 GEAR BOX ASSEMBLY STEERING FIXING BOLT 25
11588324 NUT WELD HEX FLANGE (M10X1.5) 172
11588325 NUT WELD HEX FLANGE (M12X1.75) 55
11589252 GMT319 L4 BOLT 6


SELECT [INVENTORY R&D 7th].[PART #], [INVENTORY R&D 7th].[PART NAME],
[INVENTORY R&D 7th].[END BALANCE]
FROM [INVENTORY R&D 7th]
WHERE ((([INVENTORY R&D 7th].[END BALANCE])0));


PART # PRODUCT DESCRIPTION Sum Of QTY RECVD Sum Of MEMO QTY SHIP END BALANCE
1018 "OD 12.7MM X 24"" LONG" 2 0 2
1234 test 1 0 1

SELECT DISTINCTROW [PO'S FOR SALES 8TH].[PART #], [PO'S FOR SALES 8TH].[PART
NAME], Sum([PO'S FOR SALES 8TH].[QTY RECVD]) AS [Sum Of QTY RECVD], Sum([PO'S
FOR SALES 8TH].[MEMO QTY SHIP]) AS [Sum Of MEMO QTY SHIP], [Sum Of QTY
RECVD]-[Sum Of MEMO QTY SHIP] AS [END BALANCE]
FROM [PO'S FOR SALES 8TH]
GROUP BY [PO'S FOR SALES 8TH].[PART #], [PO'S FOR SALES 8TH].[PART NAME],
[Sum Of QTY RECVD]-[Sum Of MEMO QTY SHIP]
HAVING ((([PO'S FOR SALES 8TH].[PART NAME])"0") AND ((Sum([PO'S FOR SALES
8TH].[QTY RECVD]))0));

PART # PART NAME
1018 "OD 12.7MM X 24"" LONG"
11509671 BOLT-METRIX HEX FLANGE
11515781 GEAR BOX ASSEMBLY STEERING FIXING BOLT
11588324 NUT WELD HEX FLANGE (M10X1.5)
11588325 NUT WELD HEX FLANGE (M12X1.75)
11589252 GMT319 L4 BOLT
1234 test

SELECT [PART #],[PART NAME]
FROM [BEGINNING INVENTORY BALANCE 8TH]
UNION SELECT [PART #],[PART NAME]
FROM [INVENTORY R&D];

PART # PART NAME TERM 7 TERM 8 SUM OF BALANCES
1018 "OD 12.7MM X 24"" LONG" 0
11509671 BOLT-METRIX HEX FLANGE 0
11515781 GEAR BOX ASSEMBLY STEERING FIXING BOLT 0
11588324 NUT WELD HEX FLANGE (M10X1.5) 0
11588325 NUT WELD HEX FLANGE (M12X1.75) 0
11589252 GMT319 L4 BOLT 0
1234 test 0
15226899-03-01 BODY MOUNT FRT LWR LH 0

SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME],[BEGINNING
INVENTORY BALANCE 8TH].[END BALANCE] AS [TERM 7],[INVENTORY R&D].[END
BALANCE] AS [TERM 8],nz
([BEGINNING INVENTORY BALANCE 8TH].[END BALANCE],0) +nz ([INVENTORY
R&D].[END BALANCE],0) AS [SUM OF BALANCES]
FROM ([COMBINED PARTS] LEFT JOIN [BEGINNING INVENTORY BALANCE 8TH] ON
[COMBINED PARTS].[PART #] & [BEGINNING INVENTORY BALANCE 8TH].[PART #]) LEFT
JOIN [INVENTORY R&D] ON [COMBINED PARTS].[PART #] = [INVENTORY R&D].[PART #]

the last table, as you can see isn't pulling the balances of my inventory.
Do I need a where statement or any suggestions?

aNY OTHER SUGGESTIONS ON THIS?

"KARL DEWEY" wrote:

Use this simple totals query on the union query --
SELECT [PART #],[PART NAME], Sum([END BALANCE]) AS Final_Balance
FROM YourUnionQuery
GROUP BY [PART #],[PART NAME];

--
Build a little, test a little.


"lmiller" wrote:

Hello, I am going to try this again starting fresh from my other posts. I am
trying to develop an all inclusive query on our inventory. Each fical term
we start with new tables. I would like to take our Ending Inventory from our
prior fiscal term and combine it with our current inventory balances. In
years past I always took our ending inventory and posted them in our purchase
order table to have our beginning balances. I thought it would be less time
consuming to have a query to combine the 2. Below is a list of data and the
query designs.
PART # PRODUCT DESCRIPTION END BALANCE
1018 "OD 12.7MM X 24"" LONG" 27
11509671 BOLT-METRIX HEX FLANGE 93
11515781 GEAR BOX ASSEMBLY STEERING FIXING BOLT 25
11588324 NUT WELD HEX FLANGE (M10X1.5) 172
11588325 NUT WELD HEX FLANGE (M12X1.75) 55
11589252 GMT319 L4 BOLT 6


SELECT [INVENTORY R&D 7th].[PART #], [INVENTORY R&D 7th].[PART NAME],
[INVENTORY R&D 7th].[END BALANCE]
FROM [INVENTORY R&D 7th]
WHERE ((([INVENTORY R&D 7th].[END BALANCE])0));


PART # PRODUCT DESCRIPTION Sum Of QTY RECVD Sum Of MEMO QTY SHIP END BALANCE
1018 "OD 12.7MM X 24"" LONG" 2 0 2
1234 test 1 0 1

SELECT DISTINCTROW [PO'S FOR SALES 8TH].[PART #], [PO'S FOR SALES 8TH].[PART
NAME], Sum([PO'S FOR SALES 8TH].[QTY RECVD]) AS [Sum Of QTY RECVD], Sum([PO'S
FOR SALES 8TH].[MEMO QTY SHIP]) AS [Sum Of MEMO QTY SHIP], [Sum Of QTY
RECVD]-[Sum Of MEMO QTY SHIP] AS [END BALANCE]
FROM [PO'S FOR SALES 8TH]
GROUP BY [PO'S FOR SALES 8TH].[PART #], [PO'S FOR SALES 8TH].[PART NAME],
[Sum Of QTY RECVD]-[Sum Of MEMO QTY SHIP]
HAVING ((([PO'S FOR SALES 8TH].[PART NAME])"0") AND ((Sum([PO'S FOR SALES
8TH].[QTY RECVD]))0));

PART # PART NAME
1018 "OD 12.7MM X 24"" LONG"
11509671 BOLT-METRIX HEX FLANGE
11515781 GEAR BOX ASSEMBLY STEERING FIXING BOLT
11588324 NUT WELD HEX FLANGE (M10X1.5)
11588325 NUT WELD HEX FLANGE (M12X1.75)
11589252 GMT319 L4 BOLT
1234 test

SELECT [PART #],[PART NAME]
FROM [BEGINNING INVENTORY BALANCE 8TH]
UNION SELECT [PART #],[PART NAME]
FROM [INVENTORY R&D];

PART # PART NAME TERM 7 TERM 8 SUM OF BALANCES
1018 "OD 12.7MM X 24"" LONG" 0
11509671 BOLT-METRIX HEX FLANGE 0
11515781 GEAR BOX ASSEMBLY STEERING FIXING BOLT 0
11588324 NUT WELD HEX FLANGE (M10X1.5) 0
11588325 NUT WELD HEX FLANGE (M12X1.75) 0
11589252 GMT319 L4 BOLT 0
1234 test 0
15226899-03-01 BODY MOUNT FRT LWR LH 0

SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME],[BEGINNING
INVENTORY BALANCE 8TH].[END BALANCE] AS [TERM 7],[INVENTORY R&D].[END
BALANCE] AS [TERM 8],nz
([BEGINNING INVENTORY BALANCE 8TH].[END BALANCE],0) +nz ([INVENTORY
R&D].[END BALANCE],0) AS [SUM OF BALANCES]
FROM ([COMBINED PARTS] LEFT JOIN [BEGINNING INVENTORY BALANCE 8TH] ON
[COMBINED PARTS].[PART #] & [BEGINNING INVENTORY BALANCE 8TH].[PART #]) LEFT
JOIN [INVENTORY R&D] ON [COMBINED PARTS].[PART #] = [INVENTORY R&D].[PART #]

the last table, as you can see isn't pulling the balances of my inventory.
Do I need a where statement or any suggestions?

unfortuantly that didn't work
What did it not do that you expected?
What did it do that was unwanted?
Any error messages?
--
Build a little, test a little.


"lmiller" wrote:

aNY OTHER SUGGESTIONS ON THIS?

"KARL DEWEY" wrote:

Use this simple totals query on the union query --
SELECT [PART #],[PART NAME], Sum([END BALANCE]) AS Final_Balance
FROM YourUnionQuery
GROUP BY [PART #],[PART NAME];

--
Build a little, test a little.


"lmiller" wrote:

Hello, I am going to try this again starting fresh from my other posts. I am
trying to develop an all inclusive query on our inventory. Each fical term
we start with new tables. I would like to take our Ending Inventory from our
prior fiscal term and combine it with our current inventory balances. In
years past I always took our ending inventory and posted them in our purchase
order table to have our beginning balances. I thought it would be less time
consuming to have a query to combine the 2. Below is a list of data and the
query designs.
PART # PRODUCT DESCRIPTION END BALANCE
1018 "OD 12.7MM X 24"" LONG" 27
11509671 BOLT-METRIX HEX FLANGE 93
11515781 GEAR BOX ASSEMBLY STEERING FIXING BOLT 25
11588324 NUT WELD HEX FLANGE (M10X1.5) 172
11588325 NUT WELD HEX FLANGE (M12X1.75) 55
11589252 GMT319 L4 BOLT 6


SELECT [INVENTORY R&D 7th].[PART #], [INVENTORY R&D 7th].[PART NAME],
[INVENTORY R&D 7th].[END BALANCE]
FROM [INVENTORY R&D 7th]
WHERE ((([INVENTORY R&D 7th].[END BALANCE])0));


PART # PRODUCT DESCRIPTION Sum Of QTY RECVD Sum Of MEMO QTY SHIP END BALANCE
1018 "OD 12.7MM X 24"" LONG" 2 0 2
1234 test 1 0 1

SELECT DISTINCTROW [PO'S FOR SALES 8TH].[PART #], [PO'S FOR SALES 8TH].[PART
NAME], Sum([PO'S FOR SALES 8TH].[QTY RECVD]) AS [Sum Of QTY RECVD], Sum([PO'S
FOR SALES 8TH].[MEMO QTY SHIP]) AS [Sum Of MEMO QTY SHIP], [Sum Of QTY
RECVD]-[Sum Of MEMO QTY SHIP] AS [END BALANCE]
FROM [PO'S FOR SALES 8TH]
GROUP BY [PO'S FOR SALES 8TH].[PART #], [PO'S FOR SALES 8TH].[PART NAME],
[Sum Of QTY RECVD]-[Sum Of MEMO QTY SHIP]
HAVING ((([PO'S FOR SALES 8TH].[PART NAME])"0") AND ((Sum([PO'S FOR SALES
8TH].[QTY RECVD]))0));

PART # PART NAME
1018 "OD 12.7MM X 24"" LONG"
11509671 BOLT-METRIX HEX FLANGE
11515781 GEAR BOX ASSEMBLY STEERING FIXING BOLT
11588324 NUT WELD HEX FLANGE (M10X1.5)
11588325 NUT WELD HEX FLANGE (M12X1.75)
11589252 GMT319 L4 BOLT
1234 test

SELECT [PART #],[PART NAME]
FROM [BEGINNING INVENTORY BALANCE 8TH]
UNION SELECT [PART #],[PART NAME]
FROM [INVENTORY R&D];

PART # PART NAME TERM 7 TERM 8 SUM OF BALANCES
1018 "OD 12.7MM X 24"" LONG" 0
11509671 BOLT-METRIX HEX FLANGE 0
11515781 GEAR BOX ASSEMBLY STEERING FIXING BOLT 0
11588324 NUT WELD HEX FLANGE (M10X1.5) 0
11588325 NUT WELD HEX FLANGE (M12X1.75) 0
11589252 GMT319 L4 BOLT 0
1234 test 0
15226899-03-01 BODY MOUNT FRT LWR LH 0

SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME],[BEGINNING
INVENTORY BALANCE 8TH].[END BALANCE] AS [TERM 7],[INVENTORY R&D].[END
BALANCE] AS [TERM 8],nz
([BEGINNING INVENTORY BALANCE 8TH].[END BALANCE],0) +nz ([INVENTORY
R&D].[END BALANCE],0) AS [SUM OF BALANCES]
FROM ([COMBINED PARTS] LEFT JOIN [BEGINNING INVENTORY BALANCE 8TH] ON
[COMBINED PARTS].[PART #] & [BEGINNING INVENTORY BALANCE 8TH].[PART #]) LEFT
JOIN [INVENTORY R&D] ON [COMBINED PARTS].[PART #] = [INVENTORY R&D].[PART #]

the last table, as you can see isn't pulling the balances of my inventory.
Do I need a where statement or any suggestions?

There was no error message. When I run the union query the part # and names
display fine, but the balances associated with them displays 0. I would like
the query to produce each of the balances associated with each part number.

for Example: I want the query to show:

1018 "OD 12.7MM X 24"" LONG" 29

this is just the first part number on my query but you will notice how it
took the total of 27 pieces from query 1 and added it to the total 2 pieces
from query 2.

"KARL DEWEY" wrote:

unfortuantly that didn't work
What did it not do that you expected?
What did it do that was unwanted?
Any error messages?
--
Build a little, test a little.


"lmiller" wrote:

aNY OTHER SUGGESTIONS ON THIS?

"KARL DEWEY" wrote:

Use this simple totals query on the union query --
SELECT [PART #],[PART NAME], Sum([END BALANCE]) AS Final_Balance
FROM YourUnionQuery
GROUP BY [PART #],[PART NAME];

--
Build a little, test a little.


"lmiller" wrote:

Hello, I am going to try this again starting fresh from my other posts. I am
trying to develop an all inclusive query on our inventory. Each fical term
we start with new tables. I would like to take our Ending Inventory from our
prior fiscal term and combine it with our current inventory balances. In
years past I always took our ending inventory and posted them in our purchase
order table to have our beginning balances. I thought it would be less time
consuming to have a query to combine the 2. Below is a list of data and the
query designs.
PART # PRODUCT DESCRIPTION END BALANCE
1018 "OD 12.7MM X 24"" LONG" 27
11509671 BOLT-METRIX HEX FLANGE 93
11515781 GEAR BOX ASSEMBLY STEERING FIXING BOLT 25
11588324 NUT WELD HEX FLANGE (M10X1.5) 172
11588325 NUT WELD HEX FLANGE (M12X1.75) 55
11589252 GMT319 L4 BOLT 6


SELECT [INVENTORY R&D 7th].[PART #], [INVENTORY R&D 7th].[PART NAME],
[INVENTORY R&D 7th].[END BALANCE]
FROM [INVENTORY R&D 7th]
WHERE ((([INVENTORY R&D 7th].[END BALANCE])0));


PART # PRODUCT DESCRIPTION Sum Of QTY RECVD Sum Of MEMO QTY SHIP END BALANCE
1018 "OD 12.7MM X 24"" LONG" 2 0 2
1234 test 1 0 1

SELECT DISTINCTROW [PO'S FOR SALES 8TH].[PART #], [PO'S FOR SALES 8TH].[PART
NAME], Sum([PO'S FOR SALES 8TH].[QTY RECVD]) AS [Sum Of QTY RECVD], Sum([PO'S
FOR SALES 8TH].[MEMO QTY SHIP]) AS [Sum Of MEMO QTY SHIP], [Sum Of QTY
RECVD]-[Sum Of MEMO QTY SHIP] AS [END BALANCE]
FROM [PO'S FOR SALES 8TH]
GROUP BY [PO'S FOR SALES 8TH].[PART #], [PO'S FOR SALES 8TH].[PART NAME],
[Sum Of QTY RECVD]-[Sum Of MEMO QTY SHIP]
HAVING ((([PO'S FOR SALES 8TH].[PART NAME])"0") AND ((Sum([PO'S FOR SALES
8TH].[QTY RECVD]))0));

PART # PART NAME
1018 "OD 12.7MM X 24"" LONG"
11509671 BOLT-METRIX HEX FLANGE
11515781 GEAR BOX ASSEMBLY STEERING FIXING BOLT
11588324 NUT WELD HEX FLANGE (M10X1.5)
11588325 NUT WELD HEX FLANGE (M12X1.75)
11589252 GMT319 L4 BOLT
1234 test

SELECT [PART #],[PART NAME]
FROM [BEGINNING INVENTORY BALANCE 8TH]
UNION SELECT [PART #],[PART NAME]
FROM [INVENTORY R&D];

PART # PART NAME TERM 7 TERM 8 SUM OF BALANCES
1018 "OD 12.7MM X 24"" LONG" 0
11509671 BOLT-METRIX HEX FLANGE 0
11515781 GEAR BOX ASSEMBLY STEERING FIXING BOLT 0
11588324 NUT WELD HEX FLANGE (M10X1.5) 0
11588325 NUT WELD HEX FLANGE (M12X1.75) 0
11589252 GMT319 L4 BOLT 0
1234 test 0
15226899-03-01 BODY MOUNT FRT LWR LH 0

SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME],[BEGINNING
INVENTORY BALANCE 8TH].[END BALANCE] AS [TERM 7],[INVENTORY R&D].[END
BALANCE] AS [TERM 8],nz
([BEGINNING INVENTORY BALANCE 8TH].[END BALANCE],0) +nz ([INVENTORY
R&D].[END BALANCE],0) AS [SUM OF BALANCES]
FROM ([COMBINED PARTS] LEFT JOIN [BEGINNING INVENTORY BALANCE 8TH] ON
[COMBINED PARTS].[PART #] & [BEGINNING INVENTORY BALANCE 8TH].[PART #]) LEFT
JOIN [INVENTORY R&D] ON [COMBINED PARTS].[PART #] = [INVENTORY R&D].[PART #]

the last table, as you can see isn't pulling the balances of my inventory.
Do I need a where statement or any suggestions?