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 |
#31
|
|||
|
|||
AT WITS END ON QUERY TOTAL
I just noticed you put a semicolon at the end of the firt part of the union
query. -- Build a little, test a little. "lmiller" wrote: i keep playing with these queries.... very frustrating... i changed what you suggested just to see what would happen I put in the 8th term items first in your query to combine with the 7th Now when I run all I get is the 8th term data and no 7th. It has to be in the way they are joined. "KARL DEWEY" wrote: It is NOT pulling my 8th term inventory balance. Yes the query for it works as a stand alone and I get my data The data you posted showed NO DATA for END BALANCE 8 at all. I see an error in the union query -- [INVENTORY RD 8TH]/[END BALANCE 8] FROM [inventory rd 8th] WHERE ((([inventory rd 8th].[END BALANCE 8])0)); There should be a period instead of a slash in [INVENTORY RD 8TH].[END BALANCE 8] to make it work. -- Build a little, test a little. "lmiller" wrote: Karl, I tried these queries again see below: PART # PART NAME END BALANCE 7 END BALANCE 8 1018 "OD 12.7MM X 24"" LONG" 27 0 11509671 BOLT-METRIX HEX FLANGE 93 0 11515781 GEAR BOX ASSEMBLY STEERING FIXING BOLT 25 0 11588324 NUT WELD HEX FLANGE (M10X1.5) 172 0 11588325 NUT WELD HEX FLANGE (M12X1.75) 55 0 11589252 GMT319 L4 BOLT 6 0 15226899-03-01 BODY MOUNT FRT LWR LH 14 0 SQL is: SELECT [INVENTORY RD 7th].[PART #], [INVENTORY RD 7th].[PART NAME], [INVENTORY RD 7th].[END BALANCE 7], 0 AS [END BALANCE 8] FROM [INVENTORY RD 7th] WHERE ((([INVENTORY RD 7th].[END BALANCE 7])0)) UNION SELECT [inventory rd 8th].[PART #], [inventory rd 8th].[PART NAME], 0 AS [END BALANCE 7], [INVENTORY RD 8TH]/[END BALANCE 8] FROM [inventory rd 8th] WHERE ((([inventory rd 8th].[END BALANCE 8])0)); the 2nd query is as: PART # PART NAME END BALANCE 7 END BALANCE 8 FINAL BALANCE 1018 "OD 12.7MM X 24"" LONG" 27 0 27 11509671 BOLT-METRIX HEX FLANGE 93 0 93 11515781 GEAR BOX ASSEMBLY STEERING FIXING BOLT 25 0 25 11588324 NUT WELD HEX FLANGE (M10X1.5) 172 0 172 11588325 NUT WELD HEX FLANGE (M12X1.75) 55 0 55 sql is as follows: SELECT [PART #],[PART NAME],[END BALANCE 7],[END BALANCE 8], SUM(nz([END BALANCE 7],0) + nz([END BALANCE 8],0)) AS [FINAL BALANCE] FROM [FINAL BALANCE] GROUP BY [PART #],[PART NAME],[END BALANCE 7],[END BALANCE 8] I keep having the same trouble, It is NOT pulling my 8th term inventory balance. Yes the query for it works as a stand alone and I get my data but when I try to combine the 7 and 8th nothing seems to work. "KARL DEWEY" wrote: Try these queries -- qryINVENTORY_RD_7_8 SELECT [INVENTORY RD 7th].[PART #], [INVENTORY RD 7th].[PART NAME], [INVENTORY RD 7th].[END BALANCE 7], 0 AS [END BALANCE 8] FROM [INVENTORY RD 7th] WHERE ((([INVENTORY RD 7th].[END BALANCE 7])0)); UNION ALLSELECT [inventory rd 8th].[PART #], [inventory rd 8th].[PART NAME], 0 AS [END BALANCE 7], [inventory rd 8th].[END BALANCE 8] FROM [inventory rd 8th] WHERE ((([inventory rd 8th].[END BALANCE 8])0)); SELECT [PART #], [PART NAME], [END BALANCE 7], [END BALANCE 8], Sum(Nz([END BALANCE 7], 0) + Nz([END BALANCE 8], 0)) AS Combined_Balance FROM qryINVENTORY_RD_7_8 GROUP BY [PART #], [PART NAME], [END BALANCE 7], [END BALANCE 8]; -- Build a little, test a little. "lmiller" wrote: SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME], [BEGINNING INVENTORY BALANCE 8TH].[END BALANCE 7], [inventory rd 8th].[END BALANCE 8], nz([END BALANCE 7],0)+nz([END BALANCE 8],0) AS [SUM OF BALANCES] FROM ( [BEGINNING INVENTORY BALANCE 8TH] RIGHT JOIN [COMBINED PARTS] ON [BEGINNING INVENTORY BALANCE 8TH].[PART#] = [COMBINED PARTS].[PART #]) RIGHT JOIN [INVENTORY RD 8TH] ON [COMBINED PARTS].[PART #] = [INVENTORY RD 8TH].[PART #]; I am trying but it keeps giving me an error on the [BEGINNING INVENTORY BALANCE 8TH].[PART #] can refer to more than one table in my SQL statement...... "Daryl S" wrote: Lmiller - Try this one (I switched the outer joins to a way I am more used to - it works in my db): SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME], [BEGINNING INVENTORY BALANCE 8TH].[END BALANCE 7], [INVENTORY RD 8TH].[END BALANCE 8], NZ([END BALANCE 7],0)+NZ([END BALANCE 8],0) AS [SUM OF BALANCES] FROM ([BEGINNING INVENTORY BALANCE 8TH] RIGHT JOIN [COMBINED PARTS] ON [BEGINNING INVENTORY BALANCE 8TH].[PART #] = [COMBINED PARTS].[PART #]) RIGHT JOIN [INVENTORY RD 8TH] ON [COMBINED PARTS].[PART #] = [INVENTORY RD 8TH].[PART #]; -- Daryl S "lmiller" wrote: Thanks Daryl, but I had already tried that when John suggested it - same result: SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME], [BEGINNING INVENTORY BALANCE 8TH].[END BALANCE 7], [inventory rd 8th].[END BALANCE 8], nz([BEGINNING INVENTORY BALANCE 8TH].[END BALANCE 7],0)+nz([inventory rd 8th].[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 rd 8th] ON [COMBINED PARTS].[PART #]=[inventory rd 8th].[PART #]; PART # PART NAME END BALANCE 7 END BALANCE 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 "Daryl S" wrote: Lmiller - John Vinson actually caught this error - change the ampersand to an equal sign in the first join. Try this: SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME], [BEGINNING INVENTORY BALANCE 8TH].[END BALANCE 7], [inventory rd 8th].[END BALANCE 8], nz([BEGINNING INVENTORY BALANCE 8TH].[END BALANCE 7],0)+nz([inventory rd 8th].[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 rd 8th] ON [COMBINED PARTS].[PART #]=[inventory rd 8th].[PART #]; -- Daryl S "lmiller" wrote: I want to thank anyone in advance for any insight into this. I have posted this question a couple of times and have been working on developing this query for 2 weeks and to no avial. I have a query that list my 7th term inventory balances as: PART # PRODUCT DESCRIPTION END BALANCE 7 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 SELECT [INVENTORY RD 7th].[PART #], [INVENTORY RD 7th].[PART NAME], [INVENTORY RD 7th].[END BALANCE 7] AS [END BALANCE 7] FROM [INVENTORY RD 7th] WHERE ((([INVENTORY RD 7th].[END BALANCE 7])0)); my 8th term inventory balances as follows: PART # PRODUCT DESCRIPTION END BALANCE 8 1018 "OD 12.7MM X 24"" LONG" 2 1234 test 1 SELECT [inventory rd 8th].[PART #], [inventory rd 8th].[PART NAME], [inventory rd 8th].[END BALANCE 8] FROM [inventory rd 8th] WHERE ((([inventory rd 8th].[END BALANCE 8])0)); I am trying to combine the 2 queries into 1 so that all part #'s list and if they are like part # to list on just one line. Also I would like the balances from each term to be added together. I made a union query for the Part # and name and it works great. I am trying to get the balances now to add or display correctly. Here is what I have come up with thus far : PART # PART NAME END BALANCE 7 END BALANCE 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 SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME], [BEGINNING INVENTORY BALANCE 8TH].[END BALANCE 7], [inventory rd 8th].[END BALANCE 8], nz([BEGINNING INVENTORY BALANCE 8TH].[END BALANCE 7],0)+nz([inventory rd 8th].[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 rd 8th] ON [COMBINED PARTS].[PART #]=[inventory rd 8th].[PART #]; Everything I have tried doesn't produce the balances. Any suggestion or ideas. Maybe I need a whole different query than the one I have come up with, I have played with many but as I said to no avail. THANKS!!! |
#32
|
|||
|
|||
AT WITS END ON QUERY TOTAL
Karl,
Is this suppose to be one long query or 2 separate? the semi-colon was in the first because I thought it was the end of the query and developed a 2nd as that is how your example said. when I take it out and rerun it comes back as it is the end of the first query. "KARL DEWEY" wrote: I just noticed you put a semicolon at the end of the firt part of the union query. -- Build a little, test a little. "lmiller" wrote: i keep playing with these queries.... very frustrating... i changed what you suggested just to see what would happen I put in the 8th term items first in your query to combine with the 7th Now when I run all I get is the 8th term data and no 7th. It has to be in the way they are joined. "KARL DEWEY" wrote: It is NOT pulling my 8th term inventory balance. Yes the query for it works as a stand alone and I get my data The data you posted showed NO DATA for END BALANCE 8 at all. I see an error in the union query -- [INVENTORY RD 8TH]/[END BALANCE 8] FROM [inventory rd 8th] WHERE ((([inventory rd 8th].[END BALANCE 8])0)); There should be a period instead of a slash in [INVENTORY RD 8TH].[END BALANCE 8] to make it work. -- Build a little, test a little. "lmiller" wrote: Karl, I tried these queries again see below: PART # PART NAME END BALANCE 7 END BALANCE 8 1018 "OD 12.7MM X 24"" LONG" 27 0 11509671 BOLT-METRIX HEX FLANGE 93 0 11515781 GEAR BOX ASSEMBLY STEERING FIXING BOLT 25 0 11588324 NUT WELD HEX FLANGE (M10X1.5) 172 0 11588325 NUT WELD HEX FLANGE (M12X1.75) 55 0 11589252 GMT319 L4 BOLT 6 0 15226899-03-01 BODY MOUNT FRT LWR LH 14 0 SQL is: SELECT [INVENTORY RD 7th].[PART #], [INVENTORY RD 7th].[PART NAME], [INVENTORY RD 7th].[END BALANCE 7], 0 AS [END BALANCE 8] FROM [INVENTORY RD 7th] WHERE ((([INVENTORY RD 7th].[END BALANCE 7])0)) UNION SELECT [inventory rd 8th].[PART #], [inventory rd 8th].[PART NAME], 0 AS [END BALANCE 7], [INVENTORY RD 8TH]/[END BALANCE 8] FROM [inventory rd 8th] WHERE ((([inventory rd 8th].[END BALANCE 8])0)); the 2nd query is as: PART # PART NAME END BALANCE 7 END BALANCE 8 FINAL BALANCE 1018 "OD 12.7MM X 24"" LONG" 27 0 27 11509671 BOLT-METRIX HEX FLANGE 93 0 93 11515781 GEAR BOX ASSEMBLY STEERING FIXING BOLT 25 0 25 11588324 NUT WELD HEX FLANGE (M10X1.5) 172 0 172 11588325 NUT WELD HEX FLANGE (M12X1.75) 55 0 55 sql is as follows: SELECT [PART #],[PART NAME],[END BALANCE 7],[END BALANCE 8], SUM(nz([END BALANCE 7],0) + nz([END BALANCE 8],0)) AS [FINAL BALANCE] FROM [FINAL BALANCE] GROUP BY [PART #],[PART NAME],[END BALANCE 7],[END BALANCE 8] I keep having the same trouble, It is NOT pulling my 8th term inventory balance. Yes the query for it works as a stand alone and I get my data but when I try to combine the 7 and 8th nothing seems to work. "KARL DEWEY" wrote: Try these queries -- qryINVENTORY_RD_7_8 SELECT [INVENTORY RD 7th].[PART #], [INVENTORY RD 7th].[PART NAME], [INVENTORY RD 7th].[END BALANCE 7], 0 AS [END BALANCE 8] FROM [INVENTORY RD 7th] WHERE ((([INVENTORY RD 7th].[END BALANCE 7])0)); UNION ALLSELECT [inventory rd 8th].[PART #], [inventory rd 8th].[PART NAME], 0 AS [END BALANCE 7], [inventory rd 8th].[END BALANCE 8] FROM [inventory rd 8th] WHERE ((([inventory rd 8th].[END BALANCE 8])0)); SELECT [PART #], [PART NAME], [END BALANCE 7], [END BALANCE 8], Sum(Nz([END BALANCE 7], 0) + Nz([END BALANCE 8], 0)) AS Combined_Balance FROM qryINVENTORY_RD_7_8 GROUP BY [PART #], [PART NAME], [END BALANCE 7], [END BALANCE 8]; -- Build a little, test a little. "lmiller" wrote: SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME], [BEGINNING INVENTORY BALANCE 8TH].[END BALANCE 7], [inventory rd 8th].[END BALANCE 8], nz([END BALANCE 7],0)+nz([END BALANCE 8],0) AS [SUM OF BALANCES] FROM ( [BEGINNING INVENTORY BALANCE 8TH] RIGHT JOIN [COMBINED PARTS] ON [BEGINNING INVENTORY BALANCE 8TH].[PART#] = [COMBINED PARTS].[PART #]) RIGHT JOIN [INVENTORY RD 8TH] ON [COMBINED PARTS].[PART #] = [INVENTORY RD 8TH].[PART #]; I am trying but it keeps giving me an error on the [BEGINNING INVENTORY BALANCE 8TH].[PART #] can refer to more than one table in my SQL statement...... "Daryl S" wrote: Lmiller - Try this one (I switched the outer joins to a way I am more used to - it works in my db): SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME], [BEGINNING INVENTORY BALANCE 8TH].[END BALANCE 7], [INVENTORY RD 8TH].[END BALANCE 8], NZ([END BALANCE 7],0)+NZ([END BALANCE 8],0) AS [SUM OF BALANCES] FROM ([BEGINNING INVENTORY BALANCE 8TH] RIGHT JOIN [COMBINED PARTS] ON [BEGINNING INVENTORY BALANCE 8TH].[PART #] = [COMBINED PARTS].[PART #]) RIGHT JOIN [INVENTORY RD 8TH] ON [COMBINED PARTS].[PART #] = [INVENTORY RD 8TH].[PART #]; -- Daryl S "lmiller" wrote: Thanks Daryl, but I had already tried that when John suggested it - same result: SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME], [BEGINNING INVENTORY BALANCE 8TH].[END BALANCE 7], [inventory rd 8th].[END BALANCE 8], nz([BEGINNING INVENTORY BALANCE 8TH].[END BALANCE 7],0)+nz([inventory rd 8th].[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 rd 8th] ON [COMBINED PARTS].[PART #]=[inventory rd 8th].[PART #]; PART # PART NAME END BALANCE 7 END BALANCE 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 "Daryl S" wrote: Lmiller - John Vinson actually caught this error - change the ampersand to an equal sign in the first join. Try this: SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME], [BEGINNING INVENTORY BALANCE 8TH].[END BALANCE 7], [inventory rd 8th].[END BALANCE 8], nz([BEGINNING INVENTORY BALANCE 8TH].[END BALANCE 7],0)+nz([inventory rd 8th].[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 rd 8th] ON [COMBINED PARTS].[PART #]=[inventory rd 8th].[PART #]; -- Daryl S "lmiller" wrote: I want to thank anyone in advance for any insight into this. I have posted this question a couple of times and have been working on developing this query for 2 weeks and to no avial. I have a query that list my 7th term inventory balances as: PART # PRODUCT DESCRIPTION END BALANCE 7 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 SELECT [INVENTORY RD 7th].[PART #], [INVENTORY RD 7th].[PART NAME], [INVENTORY RD 7th].[END BALANCE 7] AS [END BALANCE 7] FROM [INVENTORY RD 7th] WHERE ((([INVENTORY RD 7th].[END BALANCE 7])0)); my 8th term inventory balances as follows: PART # PRODUCT DESCRIPTION END BALANCE 8 1018 "OD 12.7MM X 24"" LONG" 2 1234 test 1 SELECT [inventory rd 8th].[PART #], [inventory rd 8th].[PART NAME], [inventory rd 8th].[END BALANCE 8] FROM [inventory rd 8th] WHERE ((([inventory rd 8th].[END BALANCE 8])0)); I am trying to combine the 2 queries into 1 so that all part #'s list and if they are like part # to list on just one line. Also I would like the balances from each term to be added together. I made a union query for the Part # and name and it works great. I am trying to get the balances now to add or display correctly. Here is what I have come up with thus far : PART # PART NAME END BALANCE 7 END BALANCE 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 SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME], [BEGINNING INVENTORY BALANCE 8TH].[END BALANCE 7], [inventory rd 8th].[END BALANCE 8], nz([BEGINNING INVENTORY BALANCE 8TH].[END BALANCE 7],0)+nz([inventory rd 8th].[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 rd 8th] ON [COMBINED PARTS].[PART #]=[inventory rd 8th].[PART #]; Everything I have tried doesn't produce the balances. Any suggestion or ideas. Maybe I need a whole different query than the one I have come up with, I have played with many but as I said to no avail. THANKS!!! |
#33
|
|||
|
|||
AT WITS END ON QUERY TOTAL
Please remember we are all volunteering our time - none of us are paid to
provide answers or help out. We have other commitments and lives, too. -- Daryl S "lmiller" wrote: Did everyone give up? can this be done in access? |
#34
|
|||
|
|||
AT WITS END ON QUERY TOTAL
Sorry I know you are and I do greatly appreciate all your time and effort!!!!! I didn't mean to come across so bad....just getting frustrated. "Daryl S" wrote: Please remember we are all volunteering our time - none of us are paid to provide answers or help out. We have other commitments and lives, too. -- Daryl S "lmiller" wrote: Did everyone give up? can this be done in access? |
#35
|
|||
|
|||
AT WITS END ON QUERY TOTAL
A union query combines more than one query into a single SQL statement that
can not be viewed in design view. All fields from the different parts must match in datatype and number of fields. That is why I put an [INVENTORY RD 8TH].[END BALANCE 8] in the 7 part and vice a versus. -- Build a little, test a little. "lmiller" wrote: Karl, Is this suppose to be one long query or 2 separate? the semi-colon was in the first because I thought it was the end of the query and developed a 2nd as that is how your example said. when I take it out and rerun it comes back as it is the end of the first query. "KARL DEWEY" wrote: I just noticed you put a semicolon at the end of the firt part of the union query. -- Build a little, test a little. "lmiller" wrote: i keep playing with these queries.... very frustrating... i changed what you suggested just to see what would happen I put in the 8th term items first in your query to combine with the 7th Now when I run all I get is the 8th term data and no 7th. It has to be in the way they are joined. "KARL DEWEY" wrote: It is NOT pulling my 8th term inventory balance. Yes the query for it works as a stand alone and I get my data The data you posted showed NO DATA for END BALANCE 8 at all. I see an error in the union query -- [INVENTORY RD 8TH]/[END BALANCE 8] FROM [inventory rd 8th] WHERE ((([inventory rd 8th].[END BALANCE 8])0)); There should be a period instead of a slash in [INVENTORY RD 8TH].[END BALANCE 8] to make it work. -- Build a little, test a little. "lmiller" wrote: Karl, I tried these queries again see below: PART # PART NAME END BALANCE 7 END BALANCE 8 1018 "OD 12.7MM X 24"" LONG" 27 0 11509671 BOLT-METRIX HEX FLANGE 93 0 11515781 GEAR BOX ASSEMBLY STEERING FIXING BOLT 25 0 11588324 NUT WELD HEX FLANGE (M10X1.5) 172 0 11588325 NUT WELD HEX FLANGE (M12X1.75) 55 0 11589252 GMT319 L4 BOLT 6 0 15226899-03-01 BODY MOUNT FRT LWR LH 14 0 SQL is: SELECT [INVENTORY RD 7th].[PART #], [INVENTORY RD 7th].[PART NAME], [INVENTORY RD 7th].[END BALANCE 7], 0 AS [END BALANCE 8] FROM [INVENTORY RD 7th] WHERE ((([INVENTORY RD 7th].[END BALANCE 7])0)) UNION SELECT [inventory rd 8th].[PART #], [inventory rd 8th].[PART NAME], 0 AS [END BALANCE 7], [INVENTORY RD 8TH]/[END BALANCE 8] FROM [inventory rd 8th] WHERE ((([inventory rd 8th].[END BALANCE 8])0)); the 2nd query is as: PART # PART NAME END BALANCE 7 END BALANCE 8 FINAL BALANCE 1018 "OD 12.7MM X 24"" LONG" 27 0 27 11509671 BOLT-METRIX HEX FLANGE 93 0 93 11515781 GEAR BOX ASSEMBLY STEERING FIXING BOLT 25 0 25 11588324 NUT WELD HEX FLANGE (M10X1.5) 172 0 172 11588325 NUT WELD HEX FLANGE (M12X1.75) 55 0 55 sql is as follows: SELECT [PART #],[PART NAME],[END BALANCE 7],[END BALANCE 8], SUM(nz([END BALANCE 7],0) + nz([END BALANCE 8],0)) AS [FINAL BALANCE] FROM [FINAL BALANCE] GROUP BY [PART #],[PART NAME],[END BALANCE 7],[END BALANCE 8] I keep having the same trouble, It is NOT pulling my 8th term inventory balance. Yes the query for it works as a stand alone and I get my data but when I try to combine the 7 and 8th nothing seems to work. "KARL DEWEY" wrote: Try these queries -- qryINVENTORY_RD_7_8 SELECT [INVENTORY RD 7th].[PART #], [INVENTORY RD 7th].[PART NAME], [INVENTORY RD 7th].[END BALANCE 7], 0 AS [END BALANCE 8] FROM [INVENTORY RD 7th] WHERE ((([INVENTORY RD 7th].[END BALANCE 7])0)); UNION ALLSELECT [inventory rd 8th].[PART #], [inventory rd 8th].[PART NAME], 0 AS [END BALANCE 7], [inventory rd 8th].[END BALANCE 8] FROM [inventory rd 8th] WHERE ((([inventory rd 8th].[END BALANCE 8])0)); SELECT [PART #], [PART NAME], [END BALANCE 7], [END BALANCE 8], Sum(Nz([END BALANCE 7], 0) + Nz([END BALANCE 8], 0)) AS Combined_Balance FROM qryINVENTORY_RD_7_8 GROUP BY [PART #], [PART NAME], [END BALANCE 7], [END BALANCE 8]; -- Build a little, test a little. "lmiller" wrote: SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME], [BEGINNING INVENTORY BALANCE 8TH].[END BALANCE 7], [inventory rd 8th].[END BALANCE 8], nz([END BALANCE 7],0)+nz([END BALANCE 8],0) AS [SUM OF BALANCES] FROM ( [BEGINNING INVENTORY BALANCE 8TH] RIGHT JOIN [COMBINED PARTS] ON [BEGINNING INVENTORY BALANCE 8TH].[PART#] = [COMBINED PARTS].[PART #]) RIGHT JOIN [INVENTORY RD 8TH] ON [COMBINED PARTS].[PART #] = [INVENTORY RD 8TH].[PART #]; I am trying but it keeps giving me an error on the [BEGINNING INVENTORY BALANCE 8TH].[PART #] can refer to more than one table in my SQL statement...... "Daryl S" wrote: Lmiller - Try this one (I switched the outer joins to a way I am more used to - it works in my db): SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME], [BEGINNING INVENTORY BALANCE 8TH].[END BALANCE 7], [INVENTORY RD 8TH].[END BALANCE 8], NZ([END BALANCE 7],0)+NZ([END BALANCE 8],0) AS [SUM OF BALANCES] FROM ([BEGINNING INVENTORY BALANCE 8TH] RIGHT JOIN [COMBINED PARTS] ON [BEGINNING INVENTORY BALANCE 8TH].[PART #] = [COMBINED PARTS].[PART #]) RIGHT JOIN [INVENTORY RD 8TH] ON [COMBINED PARTS].[PART #] = [INVENTORY RD 8TH].[PART #]; -- Daryl S "lmiller" wrote: Thanks Daryl, but I had already tried that when John suggested it - same result: SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME], [BEGINNING INVENTORY BALANCE 8TH].[END BALANCE 7], [inventory rd 8th].[END BALANCE 8], nz([BEGINNING INVENTORY BALANCE 8TH].[END BALANCE 7],0)+nz([inventory rd 8th].[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 rd 8th] ON [COMBINED PARTS].[PART #]=[inventory rd 8th].[PART #]; PART # PART NAME END BALANCE 7 END BALANCE 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 "Daryl S" wrote: Lmiller - John Vinson actually caught this error - change the ampersand to an equal sign in the first join. Try this: SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME], [BEGINNING INVENTORY BALANCE 8TH].[END BALANCE 7], [inventory rd 8th].[END BALANCE 8], nz([BEGINNING INVENTORY BALANCE 8TH].[END BALANCE 7],0)+nz([inventory rd 8th].[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 rd 8th] ON [COMBINED PARTS].[PART #]=[inventory rd 8th].[PART #]; -- Daryl S "lmiller" wrote: I want to thank anyone in advance for any insight into this. I have posted this question a couple of times and have been working on developing this query for 2 weeks and to no avial. I have a query that list my 7th term inventory balances as: PART # PRODUCT DESCRIPTION END BALANCE 7 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 SELECT [INVENTORY RD 7th].[PART #], [INVENTORY RD 7th].[PART NAME], [INVENTORY RD 7th].[END BALANCE 7] AS [END BALANCE 7] FROM [INVENTORY RD 7th] WHERE ((([INVENTORY RD 7th].[END BALANCE 7])0)); my 8th term inventory balances as follows: PART # PRODUCT DESCRIPTION END BALANCE 8 1018 "OD 12.7MM X 24"" LONG" 2 1234 test 1 SELECT [inventory rd 8th].[PART #], [inventory rd 8th].[PART NAME], [inventory rd 8th].[END BALANCE 8] FROM [inventory rd 8th] WHERE ((([inventory rd 8th].[END BALANCE 8])0)); I am trying to combine the 2 queries into 1 so that all part #'s list and if they are like part # to list on just one line. Also I would like the balances from each term to be added together. I made a union query for the Part # and name and it works great. I am trying to get the balances now to add or display correctly. Here is what I have come up with thus far : PART # PART NAME END BALANCE 7 END BALANCE 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 SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME], [BEGINNING INVENTORY BALANCE 8TH].[END BALANCE 7], [inventory rd 8th].[END BALANCE 8], nz([BEGINNING INVENTORY BALANCE 8TH].[END BALANCE 7],0)+nz([inventory rd 8th].[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 rd 8th] ON [COMBINED PARTS].[PART #]=[inventory rd 8th].[PART #]; Everything I have tried doesn't produce the balances. Any suggestion or ideas. Maybe I need a whole different query than the one I have come up with, I have played with many but as I said to no avail. THANKS!!! |
Thread Tools | |
Display Modes | |
|
|