3 queries into 1

  • Thread starter Thread starter lmiller
  • Start date Start date
L

lmiller

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 said:
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?
Click to expand...
 
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 said:
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?
Click to expand...
 
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 said:
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 said:
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?
Click to expand...
Click to expand...
 
thanks Karl, but unfortuantly that didn't work

KARL DEWEY said:
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 said:
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?
Click to expand...
Click to expand...
 
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 said:
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 said:
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?
Click to expand...
Click to expand...
 
aNY OTHER SUGGESTIONS ON THIS?

KARL DEWEY said:
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 said:
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?
Click to expand...
Click to expand...
 
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 said:
aNY OTHER SUGGESTIONS ON THIS?

KARL DEWEY said:
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 said:
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?
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
aNY OTHER SUGGESTIONS ON THIS?

KARL DEWEY said:
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.


:

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?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top