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?





0
Utf
2/9/2010 4:26:01 PM
access.queries 6343 articles. 1 followers. Follow

8 Replies
1026 Views

Similar Articles

[PageSpeed] 46

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?
> 
> 
> 
> 
> 
0
Utf
2/9/2010 6:37:01 PM
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?
> 
> 
> 
> 
> 
0
Utf
2/10/2010 4:42:01 PM
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?
> > 
> > 
> > 
> > 
> > 
0
Utf
2/11/2010 4:52:01 PM
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?
> > 
> > 
> > 
> > 
> > 
0
Utf
2/11/2010 5:03:03 PM
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?
> > 
> > 
> > 
> > 
> > 
0
Utf
2/11/2010 8:03:01 PM
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?
> > 
> > 
> > 
> > 
> > 
0
Utf
2/12/2010 6:38:05 PM
>> 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?
> > > 
> > > 
> > > 
> > > 
> > > 
0
Utf
2/12/2010 8:51:01 PM
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?
> > > > 
> > > > 
> > > > 
> > > > 
> > > > 
0
Utf
2/15/2010 1:25:01 PM
Reply:

Similar Artilces:

Using the sum function in a 3 dimension environment
How to Use the sum function in a 3 dimension environment in Microsoft Excel? Need a detailed description for a research paper I am doing, ASAP!!!! Is this what you need? =SUM(Sheet1:Sheet3!C1) If sheet names have spaces use =SUM('sheet x:sheet y'!C1) -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Paul Hargreaves" <Paul Hargreaves@discussions.microsoft.com> wrote in message news:24F4CEEA-3A12-4922-B72B-416A9584EDD6@microsoft.com... > How to Use the sum function in a 3 dimension environment in Microsoft > Excel? > Need a detailed d...

IMF Filter #3
Ok, I know this depends on your environment, but what level is most commonly set for the IMF controls. As far as archiving/deleting and then for the junk folder settings. --John On Mon, 6 Feb 2006 06:40:30 -0800, "John" <John@discussions.microsoft.com> wrote: >Ok, I know this depends on your environment, but what level is most commonly >set for the IMF controls. As far as archiving/deleting and then for the junk >folder settings. --John I would set the SCLs high with No Action on the Gateway and test and play with the settings as your testing goes over time. ...

Blank E-mail #3
When I send an e-mail it comes through blank to the recipient. How do I fix this Try typing a message in the body before you send it. Ray at work "Edward Moalem" <moalem@pacbell.net> wrote in message news:017e01c3a303$4eb4b180$a401280a@phx.gbl... > When I send an e-mail it comes through blank to the > recipient. > > How do I fix this > > If your using SpamEnder it causes a conflict. And yes Ray I did type a message in the body of the e- mail LOL >-----Original Message----- >Try typing a message in the body before you send it. > >Ray at ...

Mailbox Creation #3
I have always created a mailbox for Exchange whenever I have created a new user in Active Directory. However, recently, I receive confirmation that the mailbox has been created, but, in the Exchange System Manager, the mailbox is not there. I have checked for a possible licensing issue, but everything is in order. Any help on this matter would be deeply appreciated. Thank you. On Wed, 22 Dec 2004 17:13:02 -0800, Alboni <Alboni@discussions.microsoft.com> wrote: >I have always created a mailbox for Exchange whenever I have created a new >user in Active Directory. However, r...

Query Result Incorrect
I have a query based on a membership table in which I need to pull out all the people who do not have an email address. When I design the query and put "is null" in the criteria field for email address, it still returns names with email addresses. How can I get this query to pull out only the members who do not have an email address? Well what do you have in the columns with no email address? You should have a null but its possible your code puts either an empty string or a space in there. Since an email address should contain an '@' character you could try WHE...

reason: 554 5.7.1 real time blacklist
A user complained that they are not able to send messages to someone and got a returned message ----- The following addresses had permanent fatal errors ----- <xxxxxx@andrews.edu> (reason: 554 5.7.1 x.x.x.x: on real-time blacklist dnsbl.sorbs.net) ----- Transcript of session follows ----- ... while talking to outbox.andrews.edu.: >>> DATA <<< 554 5.7.1 x.x.x.x: on real-time blacklist dnsbl.sorbs.net 554 5.0.0 Service unavailable <<< 503 5.0.0 Need RCPT (recipient) any idea? Your domain is listed on sorbs. http://www.us.sorbs.net/ Go find out...

Reconcile #3
After balancing I bring up all unreconciled transactions. How can you select all the transactions and tag them reconciled all at once, instead of one at a time? In microsoft.public.money, Randy wrote: >After balancing I bring up all unreconciled transactions. >How can you select all the transactions and tag them >reconciled all at once, instead of one at a time? Select to show only unreconciled transactions. Sort by date, and go to the oldest transaction. Hold down Cntl+Shift+M and let auto-repeat work thru the transactions until none are left showing. ...

Counting coloured cells #3
I have a list of percentages that are conditionally formatted and I want to be able to count how many cells are each different colour. I don't think you can do this with count if as it only looks for numbers or text and not formatting but essentially I want to count the number of cells in a range which are red/gold or green. Use the following UDF Function CountColor(rng As Range, colorRng As Range) For Each cl In rng If cl.Interior.ColorIndex = colorRng.Interior.ColorIndex Then ' use this for background color CountColor = CountColor + 1 End If ...

Importing Customizations and Workflows from 3.0
Hi, I've exported some customizations and workflows from CRM 3.0 and want to import to CRM 4.0. I'm trying at the CRM 4.0 interface but it's saying "Either the file could not be uploaded, or this is not a valid Customization file." How should I do that? It isn't possible? tks, Alex Unfortunately you can't export from CRM 3.0 and import into CRM 4.0 Your only option is to have your customisations on a CRM 3.0 server, upgrade the server to CRM 4.0, then export and import the customisations and workflows -- David Jennaway - Microsoft Dynamics CRM MVP Web: h...

Multiple Hyperlinks in 1 cell?
I have some spreadsheets that have a column with some text (4 numbers) that link to a webpage, where the URL ends with the four numbers (eg.1234). Sometimes I place an additional 4-number reference to another Webpage in the same cell, so it looks like (eg.1234/5678). I have to get rid of the link since I only know how to set it for one Webpage or the other. Is there a way for users to click 1234 and go to that page, or click 5678 (in the same cell) and go to that selected page? Thanks! Not that I'm aware of. "davidemile" <davidemile@hotmail.com> wrote in message news:...

Publisher Clip art #3
I have Microsoft Publisher 98. I have lost my disk! that contains all my clip-art. I can preview the pictures but not insert them. I have the Clipart file number (i.e. DD01658.wmf) but cannot find the design on Microsoft online Clipart and Media. There are several clip art images I am looking at right now that are that way. Is there anyway I can search Microsoft for them of get them somewhere else? Microsoft changes the clipart now and then. Much of the legacy clipart is gone from the Office web site. If you have a list of images you need, let me know and I will put them on a web pag...

CRM 3.0 Faxing
Apart from OmniRush, does anyone know of any other Faxing software which is compatible with CRM 3.0 ? Thanks Nicos Papaioannou ...

Joining 3 Queries That Count
I am trying to decipher the “status” of records in my table based on two date fields and the name of the agent assigned to each record. The fields are as follows: SUPPORT MGR (Name) DC (Date Completed) Follow-up Date I have 3 separate queries based on these fields that work as follows: a) Completed SELECT [Master Table].[SUPPORT MGR], Count([Master Table].[MERCHANT ID]) AS [# COMPLETED] FROM [Master Table] WHERE ((([Master Table].DC) Between [Start Date] And [End Date])) GROUP BY [Master Table].[SUPPORT MGR]; b) In Follow-Up SELECT [Master Table].[SUPPORT MGR], Count(...

Public Folders only synchronize 1-way
Yesterday I created a replica for a small part of my Public Folder tree. That part of my tree shows up on the store of the replica server. So far so good. But any changes made in the new replica don't get replicated back to the orginal Public Folder. Any changes made to the original folder do get replicated to the new. In short I know have 1-way public folder replication. Which is not what I want. Half of my clients now see different folder contents then the other half of my clients. Any thoughts ? I checked event logs. Nothing wrong there it seems. Greetz Richard ...

CRM 3.0 Reports Not Working in Outlook Client
Recently we had to republish all the reports on the SQL server (2005) in order for them to show up in the web client. They now do and can be run. However, none of our laptop clients are able to run a report. When a user clicks on the report link, they get the following error: Server error '/' application The request failed with HTTP status 401: unauthorized. These same users can run the reports through the web client. I'm at a complete loss and appreciate any and all help. Thanks, Jason Little more background: CRM 3.0 and SQL Server 2005 are on different servers OS: Windows ...

Program unable to link "PSAPI.lib" Query?
Hi Guys I have a downloaded a win32 API program that works for Win 95/98/NT. The download archive contained a project workspace for VC++ 6.0 When i opened the program with VC++ 6.0 & tried to compile the program it gave me the following error: LINK: fatal error LNK1181: cannot open input file "psapi.lib". Going through the Project settings i found out that in the Project Settings->Link Tab - General Category. In the Object/library modules following libraries were listed: comctl32.lib psapi.lib kernel32.lib user32.lib gdi32.lib winspool.lib comdlg32.lib advapi32.lib shell3...

Word as Email Editor #3
In order to use the Auto Signature function in outlook 97, we have had to disable the Word E-Mail Editor. We have found that this can be done through Tools | Options | Email or Tools | Use Word as the E-Mail Editor (in both instances, removing the tick out of the checkbox). However, the problem we are faced with is that everytime you resart a pc, the check boxes are again ticked and the auto signature doesn't work. Any Ideas??? ...

Scanning quantity items 1.2 x $3.00 ?
I have rms set up so that you can enter the number of items...say 2 packs of cigs times (x) scan the item, and you get the total. The problem is sometimes an employee will scan 1.2 by accident (rarely), and then inventory is off, so is the price etc, and they don't catch it either...I found out because I ran a report, and it showed up with a 1.2 sale of a pack of smokes. Since we only sell full packs I new this was an error...is there a way to prevent this? -- Thank You Vince :) ...

Money vs. Quicken #3
I've been doing a side-by-side comparison between Money 2005 Premier and Quicken 2005 Premier over the past week. I should also point out that I beta tested Money 2005 having been a loyal fan of Money since about 1999. The beta testers did point out most of the current issues and many others that did get fixed in time. Unfortunately, Quicken has problems that Money doesn't have. One, it's interface has always been rather poor compared to Money's. Quicken also seems to have severe problems with the process of accepting downloaded transactions. There appears to be no ...

CRM 3.0 Conveting Campaign Tasks into Tasks
I would like to convert Campaign Tasks into Tasks. The reason for this is that only Tasks are sync'ed with Outlook. Can it be done with the workflow manager. I had a look at it but without any result. Tnx ...

CButton #3
I have two questions about CButton, not in a dialog but simply amid various graphics in my SDI view. m_pButton = new CButton; m_pButtonRect = new CRect(500,400,600,450); m_pButton->Create(_T ("Get Going"), WS_CHILD | WS_VISIBLE | BS_PUSHBUTTON, *m_pButtonRect, this, 100); 1. Can I adjust the height of the font with my present method of creating the button? 2. Any idea how I can get rid of the flicker that occurs when I delete the button pointer? I either get a flicker or a brief "ghost" effect, depending on what graphics operations are going on....

conditional formatting for more than 3 conditions
Hi, Is there any solution for the following : I have six type of conditions, if any condition is met, cell colour should be yellow. For example, if cell value is any one of the following : DATA1 DATA2 DATA3 DATA4 DATA5 DATA6 All cells containing above should show yellow colour in the file. Please help. Thanks, Jai formula is =or(c1="data1",c1="data2",etc) or =or(c1={"data1","data2"}) -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "Jai" <Jai@discussions.microsoft.com> wrote i...

2 domain 2 forest but 1 exchange 2003
What is the best setup? I have 2 domains in 2 forest. Its 2 separate compagny but now i merge all. How can i merge 2 domain in 1 domain. After i setup exchange on this domain and its ok for all user. On 2 Jun 2006 19:39:04 -0700, "MeTa" <bmartinp@gmail.com> wrote: >What is the best setup? I have 2 domains in 2 forest. Its 2 separate >compagny but now i merge all. How can i merge 2 domain in 1 domain. >After i setup exchange on this domain and its ok for all user. Do you want to end up with one forest or do you need to retain the two? I prefer 1 forest for the glo...

Send Outgoing E-Mail from 1 Account
Hi, I would like to have all users share 1 account for outgoing mail so that they all share the same reply address, the mail should come back to the 1 account for forwarding back to the user. I set up a user and gave them permissions to create and read thinking that they could send via this. I get the following error "You do not have the permission to send the message on behalf of the specified user.txt" I have tried the 'send on behalf of' this works but shows the users '@name.local' address. and this is not the best look. Please Help Matthew It sound...

Adding #3
Hi, Does anyone have any suggestions for the following: I would like to add the last cell in column B (The row varies daily) o sheet 1 to the existing amount on Sheet 2 Cell A2. For example the last cell on Sheet 1 column B is $100. The amount o Sheet 2 Cell A2 is $5. I would like a Macro that would find the las cell on Sheet 1 Column B and add it to the existing value of Cell A Sheet 2 to create a new cell value of $105. Any help would be greatly appreciated -- STEVE ----------------------------------------------------------------------- STEVEB's Profile: http://www.excelforum.com...