If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
combining queries into 1
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? |
#2
|
|||
|
|||
combining queries into 1
Lmiller -
If you run the two 'new' queries separately (e.g. [Beginning Inventory Balance 8th] and [Inventory R&D], do you get the values for [End Balance 8] and [End Balance 7] that you expect? If so, try changing the query name from [Inventory R&D] to [Inventory RD] to see if removing the ampersand helps. It bugs me that in the join query it looks like only the [Sum of Balances] field is populated. This is what makes me ask if the two 'source' queries are returning values in the End Balance fields... -- Daryl S "lmiller" wrote: 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? |
#3
|
|||
|
|||
combining queries into 1
Daryl,
Yes they work beautifully as stand alones. They were copied below in my original question but I did just now rename them taking out the "&" in R & D. I reran the stand alone queries and they work fine but the combined query I am still working on still result in 0 for my final balances. All the past queries I have tried It just doesn't seem like it is pulling the values for my Term 8. "Daryl S" wrote: Lmiller - If you run the two 'new' queries separately (e.g. [Beginning Inventory Balance 8th] and [Inventory R&D], do you get the values for [End Balance 8] and [End Balance 7] that you expect? If so, try changing the query name from [Inventory R&D] to [Inventory RD] to see if removing the ampersand helps. It bugs me that in the join query it looks like only the [Sum of Balances] field is populated. This is what makes me ask if the two 'source' queries are returning values in the End Balance fields... -- Daryl S "lmiller" wrote: 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? |
#4
|
|||
|
|||
combining queries into 1
Does a being a replicated query affect the outcome?
"lmiller" wrote: Daryl, Yes they work beautifully as stand alones. They were copied below in my original question but I did just now rename them taking out the "&" in R & D. I reran the stand alone queries and they work fine but the combined query I am still working on still result in 0 for my final balances. All the past queries I have tried It just doesn't seem like it is pulling the values for my Term 8. "Daryl S" wrote: Lmiller - If you run the two 'new' queries separately (e.g. [Beginning Inventory Balance 8th] and [Inventory R&D], do you get the values for [End Balance 8] and [End Balance 7] that you expect? If so, try changing the query name from [Inventory R&D] to [Inventory RD] to see if removing the ampersand helps. It bugs me that in the join query it looks like only the [Sum of Balances] field is populated. This is what makes me ask if the two 'source' queries are returning values in the End Balance fields... -- Daryl S "lmiller" wrote: 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? |
#5
|
|||
|
|||
combining queries into 1
aNY OTHER SUGGESTIONS?
"Daryl S" wrote: Lmiller - If you run the two 'new' queries separately (e.g. [Beginning Inventory Balance 8th] and [Inventory R&D], do you get the values for [End Balance 8] and [End Balance 7] that you expect? If so, try changing the query name from [Inventory R&D] to [Inventory RD] to see if removing the ampersand helps. It bugs me that in the join query it looks like only the [Sum of Balances] field is populated. This is what makes me ask if the two 'source' queries are returning values in the End Balance fields... -- Daryl S "lmiller" wrote: 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? |
#6
|
|||
|
|||
combining queries into 1
LMiller -
I don't know anything about replication. I would suggest making a copy of the final query and taking out one of the two 'source' queries (but keep the UNION query in there) at a time and see if you get any of the End Balance data to show up. If it works with each of the two sources individually, but not together, you could add an additional query to combine them. Let us know! -- Daryl S "lmiller" wrote: aNY OTHER SUGGESTIONS? "Daryl S" wrote: Lmiller - If you run the two 'new' queries separately (e.g. [Beginning Inventory Balance 8th] and [Inventory R&D], do you get the values for [End Balance 8] and [End Balance 7] that you expect? If so, try changing the query name from [Inventory R&D] to [Inventory RD] to see if removing the ampersand helps. It bugs me that in the join query it looks like only the [Sum of Balances] field is populated. This is what makes me ask if the two 'source' queries are returning values in the End Balance fields... -- Daryl S "lmiller" wrote: 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? |
#7
|
|||
|
|||
combining queries into 1
Thank you for all you suggestions Daryl they are greatly appreciated. I
tried running the final without 1 of the source queries but kept getting an error in the FROM statement. "Daryl S" wrote: LMiller - I don't know anything about replication. I would suggest making a copy of the final query and taking out one of the two 'source' queries (but keep the UNION query in there) at a time and see if you get any of the End Balance data to show up. If it works with each of the two sources individually, but not together, you could add an additional query to combine them. Let us know! -- Daryl S "lmiller" wrote: aNY OTHER SUGGESTIONS? "Daryl S" wrote: Lmiller - If you run the two 'new' queries separately (e.g. [Beginning Inventory Balance 8th] and [Inventory R&D], do you get the values for [End Balance 8] and [End Balance 7] that you expect? If so, try changing the query name from [Inventory R&D] to [Inventory RD] to see if removing the ampersand helps. It bugs me that in the join query it looks like only the [Sum of Balances] field is populated. This is what makes me ask if the two 'source' queries are returning values in the End Balance fields... -- Daryl S "lmiller" wrote: 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? |
#8
|
|||
|
|||
combining queries into 1
On Fri, 12 Feb 2010 13:06:02 -0800, lmiller
wrote: Thank you for all you suggestions Daryl they are greatly appreciated. I tried running the final without 1 of the source queries but kept getting an error in the FROM statement. Your FROM statement reads: 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 & in the second line is a string concatenation character and is simply wrong in this context; I think it needs to be an = rather than an &. -- John W. Vinson [MVP] |
#9
|
|||
|
|||
combining queries into 1
Thanks John but that still didn't work. the balances still come up to 0. I
have yet to find out how to combine these 2 queries correctly. "John W. Vinson" wrote: On Fri, 12 Feb 2010 13:06:02 -0800, lmiller wrote: Thank you for all you suggestions Daryl they are greatly appreciated. I tried running the final without 1 of the source queries but kept getting an error in the FROM statement. Your FROM statement reads: 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 & in the second line is a string concatenation character and is simply wrong in this context; I think it needs to be an = rather than an &. -- John W. Vinson [MVP] . |
Thread Tools | |
Display Modes | |
|
|