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 |
#21
|
|||
|
|||
AT WITS END ON QUERY TOTAL
This is very frustrating... figured out my error but still same results 0
below is what I got: SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME], [BEGINNING INVENTORY BALANCE 8TH].[END BALANCE 7] FROM [COMBINED PARTS] LEFT JOIN [BEGINNING INVENTORY BALANCE 8TH] ON [COMBINED PARTS].[PART #]=[BEGINNING INVENTORY BALANCE 8TH].[PART #]; this is the "first half" I named it test. Here are the results (no balances pulled) PART # PART NAME END BALANCE 7 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 15226899-03-01 BODY MOUNT FRT LWR LH here is the other query: SELECT [TEST].[PART #], [TEST].[PART NAME], [TEST].[END BALANCE 7], [INVENTORY RD 8TH].[END BALANCE 8], nz([END BALANCE 7],0) + nz([END BALANCE 8],0) AS [SUM OF BALANCES] FROM [TEST] LEFT JOIN [INVENTORY RD 8TH] ON [TEST].[PART#] = [INVENTORY RD 8TH].[PART #] Same results big old 0 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 - Annoying... The way to get around that issue is to change the name of [PART #] in the source query to something like [PART Num]... -- Daryl S "lmiller" wrote: SELECT [COMBINED PARTS].[PART #],[COMBINED PARTS].[PART NAME],[BEGINNING INVENTORY BALANCE 8TH].[END BALANCE 7] FROM ([COMBINED PARTS] LEFT JOIN [BEGINNING INVENTORY BALANCE 8TH] ON [COMBINED PARTS].[PART #] = [BEGINNING INVENTORY BALANCE 8TH].[PART#]); The query "first half" that you suggest above won't work, giving me an error that the [beginning inventory balance 8th].[part #] can refer to more than one table in my from statement. "Daryl S" wrote: Lmiller - This should be easy - it works fine on my machine. Anyway, here is another thing to try. Join only the [COMBINED PARTS] and the [BEGINNING INVENTORY BALANCE 8TH] sources like this: You should have the END BALANCE 7 column filled where there is data. SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME], [BEGINNING INVENTORY BALANCE 8TH].[END BALANCE 7], FROM ([COMBINED PARTS] LEFT JOIN [BEGINNING INVENTORY BALANCE 8TH] ON [COMBINED PARTS].[PART #] = [BEGINNING INVENTORY BALANCE 8TH].[PART #]); Now join that query with the [INVENTORY RD 8TH] like this (change the [FIRST HALF] to the name you gave the query above: SELECT [FIRST HALF].[PART #], [FIRST HALF].[PART NAME], [FIRST HALFH].[END BALANCE 7], [INVENTORY RD 8TH].[END BALANCE 8], nz([END BALANCE 7],0)+nz([END BALANCE 8],0) AS [SUM OF BALANCES] FROM [FIRST HALF] LEFT JOIN [INVENTORY RD 8TH] ON [FIRST HALF].[PART #] = [INVENTORY RD 8TH].[PART #]; -- Daryl S "lmiller" wrote: Same results. it's just not combining the balances or giving me any of the balances. I know there probably is an easy solution to this, seems like it's just a matter of comparing part #s if they are the same add the two balances together if not just list..... "Daryl S" wrote: LMiller - OK, let's try something else. In your source query (and in the final query), change the [END BALANCE 8] to something like [END_BALANCE_8]. Run each and see what you get. -- Daryl S "lmiller" wrote: PART # PRODUCT DESCRIPTION END BALANCE 8 1018 "OD 12.7MM X 24"" LONG" 2 1234 test 1 Yes Daryl, notice above, this is the result from running my 8th term. I do have balances. You are coming up with the same conclusion I have. No matter what I have tried my 8th term isn't pulling. "Daryl S" wrote: LMiller - Progress! I think I see values for END BALANCE 7 (the 27 and the null), and the sums, but nothing for the END BALANCE 8. I would check the source query to see if you really have something in [END BALANCE 8]. Maybe there is a typo? The query is pulling the correct records, and you are getting one from each of the source records, but the [END BALANCE 8] is not coming through. Can you check your [INVENTORY BALANCE 8TH] query to make sure you are getting data in the [END BALANCE 8] column? -- Daryl S "lmiller" wrote: Tried what you suggested, this is what it comes up as now in SQL 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 ([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 #]; Now the query only list the part #'s that were in my 8th term with the 7th term balance PART # PART NAME END BALANCE 7 END BALANCE 8 SUM OF BALANCES 1018 "OD 12.7MM X 24"" LONG" 27 27 1234 test 0 what happened to all my other part # and their balances? "Daryl S" wrote: Lmiller - Are you building this final query in design mode? That should make it easy. Add the three source queries. You will need to adjust the joins by double-clicking on them, and selecting the "All from COMBINED" option for the joins between the COMBINED query and the other two sources. Delete any join between the other two queries. Then, add the fields to the query grid by double-clicking on the [PART #] and [PART NAME] fields in the COMBINED query. Then add the [END BALANCE 7] and [END BALANCE 8] fields in the same manner. Finally, in the last column, type in: SUM OF BALANCES: =nz([END BALANCE 7],0) + nz([END BALANCE 8],0) -- Daryl S "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!!! |
#22
|
|||
|
|||
AT WITS END ON QUERY TOTAL
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!!! |
#23
|
|||
|
|||
AT WITS END ON QUERY TOTAL
Thanks Karl, I fixed the error but the results are the same still isn't
pulling any balances from the 8th term or the 8th term parts 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 15226899-04-00 BODY MOUNT FRT UPR RH 14 0 15226899-05-00 BODY MOUNT FRT LWR RH 14 0 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)); 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 11589252 GMT319 L4 BOLT 6 0 6 15226899-03-01 BODY MOUNT FRT LWR LH 14 0 14 15226899-04-00 BODY MOUNT FRT UPR RH 14 0 14 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] "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!!! |
#24
|
|||
|
|||
AT WITS END ON QUERY TOTAL
do I need an if statement in there? if ([inventory rd 7].[part #] = [inventory rd 8].[part #] than add the 2 balances together? "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!!! |
#25
|
|||
|
|||
AT WITS END ON QUERY TOTAL
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!!! |
#26
|
|||
|
|||
AT WITS END ON QUERY TOTAL
Lmiller -
The problem has to be with [BEGINNING INVENTORY BALANCE 8TH]. What do you get when you run this: SELECT * from [BEGINNING INVENTORY BALANCE 8TH]? -- Daryl S "lmiller" wrote: This is very frustrating... figured out my error but still same results 0 below is what I got: SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME], [BEGINNING INVENTORY BALANCE 8TH].[END BALANCE 7] FROM [COMBINED PARTS] LEFT JOIN [BEGINNING INVENTORY BALANCE 8TH] ON [COMBINED PARTS].[PART #]=[BEGINNING INVENTORY BALANCE 8TH].[PART #]; this is the "first half" I named it test. Here are the results (no balances pulled) PART # PART NAME END BALANCE 7 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 15226899-03-01 BODY MOUNT FRT LWR LH here is the other query: SELECT [TEST].[PART #], [TEST].[PART NAME], [TEST].[END BALANCE 7], [INVENTORY RD 8TH].[END BALANCE 8], nz([END BALANCE 7],0) + nz([END BALANCE 8],0) AS [SUM OF BALANCES] FROM [TEST] LEFT JOIN [INVENTORY RD 8TH] ON [TEST].[PART#] = [INVENTORY RD 8TH].[PART #] Same results big old 0 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 - Annoying... The way to get around that issue is to change the name of [PART #] in the source query to something like [PART Num]... -- Daryl S "lmiller" wrote: SELECT [COMBINED PARTS].[PART #],[COMBINED PARTS].[PART NAME],[BEGINNING INVENTORY BALANCE 8TH].[END BALANCE 7] FROM ([COMBINED PARTS] LEFT JOIN [BEGINNING INVENTORY BALANCE 8TH] ON [COMBINED PARTS].[PART #] = [BEGINNING INVENTORY BALANCE 8TH].[PART#]); The query "first half" that you suggest above won't work, giving me an error that the [beginning inventory balance 8th].[part #] can refer to more than one table in my from statement. "Daryl S" wrote: Lmiller - This should be easy - it works fine on my machine. Anyway, here is another thing to try. Join only the [COMBINED PARTS] and the [BEGINNING INVENTORY BALANCE 8TH] sources like this: You should have the END BALANCE 7 column filled where there is data. SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME], [BEGINNING INVENTORY BALANCE 8TH].[END BALANCE 7], FROM ([COMBINED PARTS] LEFT JOIN [BEGINNING INVENTORY BALANCE 8TH] ON [COMBINED PARTS].[PART #] = [BEGINNING INVENTORY BALANCE 8TH].[PART #]); Now join that query with the [INVENTORY RD 8TH] like this (change the [FIRST HALF] to the name you gave the query above: SELECT [FIRST HALF].[PART #], [FIRST HALF].[PART NAME], [FIRST HALFH].[END BALANCE 7], [INVENTORY RD 8TH].[END BALANCE 8], nz([END BALANCE 7],0)+nz([END BALANCE 8],0) AS [SUM OF BALANCES] FROM [FIRST HALF] LEFT JOIN [INVENTORY RD 8TH] ON [FIRST HALF].[PART #] = [INVENTORY RD 8TH].[PART #]; -- Daryl S "lmiller" wrote: Same results. it's just not combining the balances or giving me any of the balances. I know there probably is an easy solution to this, seems like it's just a matter of comparing part #s if they are the same add the two balances together if not just list..... "Daryl S" wrote: LMiller - OK, let's try something else. In your source query (and in the final query), change the [END BALANCE 8] to something like [END_BALANCE_8]. Run each and see what you get. -- Daryl S "lmiller" wrote: PART # PRODUCT DESCRIPTION END BALANCE 8 1018 "OD 12.7MM X 24"" LONG" 2 1234 test 1 Yes Daryl, notice above, this is the result from running my 8th term. I do have balances. You are coming up with the same conclusion I have. No matter what I have tried my 8th term isn't pulling. "Daryl S" wrote: LMiller - Progress! I think I see values for END BALANCE 7 (the 27 and the null), and the sums, but nothing for the END BALANCE 8. I would check the source query to see if you really have something in [END BALANCE 8]. Maybe there is a typo? The query is pulling the correct records, and you are getting one from each of the source records, but the [END BALANCE 8] is not coming through. Can you check your [INVENTORY BALANCE 8TH] query to make sure you are getting data in the [END BALANCE 8] column? -- Daryl S "lmiller" wrote: Tried what you suggested, this is what it comes up as now in SQL 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 ([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 #]; Now the query only list the part #'s that were in my 8th term with the 7th term balance PART # PART NAME END BALANCE 7 END BALANCE 8 SUM OF BALANCES 1018 "OD 12.7MM X 24"" LONG" 27 27 1234 test 0 what happened to all my other part # and their balances? "Daryl S" wrote: Lmiller - Are you building this final query in design mode? That should make it easy. Add the three source queries. You will need to adjust the joins by double-clicking on them, and selecting the "All from COMBINED" option for the joins between the COMBINED query and the other two sources. Delete any join between the other two queries. Then, add the fields to the query grid by double-clicking on the [PART #] and [PART NAME] fields in the COMBINED query. Then add the [END BALANCE 7] and [END BALANCE 8] fields in the same manner. Finally, in the last column, type in: SUM OF BALANCES: =nz([END BALANCE 7],0) + nz([END BALANCE 8],0) -- Daryl S "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 |
#27
|
|||
|
|||
AT WITS END ON QUERY TOTAL
I get the 8th term balances as they should be. I could be wrong but I have been playing with these and I think it's in the way they are joined. I reversed the query a little bit and put the 8th term items first than the 7th term. Now all the 8th term balances come up but no 7th. I am not familar with "if" statments but seems like if the the 7th and 8th part # are = than they need to be added together if not than their balances should just list. If I run just the part # and names together in a combined query they combine just great when i try to add the balances it gets all confused......I put the 7th term balance in SQL first it pulls just those items if I put the 8th first it just pulls those items. i know this is hard trying to relay. "Daryl S" wrote: Lmiller - The problem has to be with [BEGINNING INVENTORY BALANCE 8TH]. What do you get when you run this: SELECT * from [BEGINNING INVENTORY BALANCE 8TH]? -- Daryl S "lmiller" wrote: This is very frustrating... figured out my error but still same results 0 below is what I got: SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME], [BEGINNING INVENTORY BALANCE 8TH].[END BALANCE 7] FROM [COMBINED PARTS] LEFT JOIN [BEGINNING INVENTORY BALANCE 8TH] ON [COMBINED PARTS].[PART #]=[BEGINNING INVENTORY BALANCE 8TH].[PART #]; this is the "first half" I named it test. Here are the results (no balances pulled) PART # PART NAME END BALANCE 7 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 15226899-03-01 BODY MOUNT FRT LWR LH here is the other query: SELECT [TEST].[PART #], [TEST].[PART NAME], [TEST].[END BALANCE 7], [INVENTORY RD 8TH].[END BALANCE 8], nz([END BALANCE 7],0) + nz([END BALANCE 8],0) AS [SUM OF BALANCES] FROM [TEST] LEFT JOIN [INVENTORY RD 8TH] ON [TEST].[PART#] = [INVENTORY RD 8TH].[PART #] Same results big old 0 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 - Annoying... The way to get around that issue is to change the name of [PART #] in the source query to something like [PART Num]... -- Daryl S "lmiller" wrote: SELECT [COMBINED PARTS].[PART #],[COMBINED PARTS].[PART NAME],[BEGINNING INVENTORY BALANCE 8TH].[END BALANCE 7] FROM ([COMBINED PARTS] LEFT JOIN [BEGINNING INVENTORY BALANCE 8TH] ON [COMBINED PARTS].[PART #] = [BEGINNING INVENTORY BALANCE 8TH].[PART#]); The query "first half" that you suggest above won't work, giving me an error that the [beginning inventory balance 8th].[part #] can refer to more than one table in my from statement. "Daryl S" wrote: Lmiller - This should be easy - it works fine on my machine. Anyway, here is another thing to try. Join only the [COMBINED PARTS] and the [BEGINNING INVENTORY BALANCE 8TH] sources like this: You should have the END BALANCE 7 column filled where there is data. SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME], [BEGINNING INVENTORY BALANCE 8TH].[END BALANCE 7], FROM ([COMBINED PARTS] LEFT JOIN [BEGINNING INVENTORY BALANCE 8TH] ON [COMBINED PARTS].[PART #] = [BEGINNING INVENTORY BALANCE 8TH].[PART #]); Now join that query with the [INVENTORY RD 8TH] like this (change the [FIRST HALF] to the name you gave the query above: SELECT [FIRST HALF].[PART #], [FIRST HALF].[PART NAME], [FIRST HALFH].[END BALANCE 7], [INVENTORY RD 8TH].[END BALANCE 8], nz([END BALANCE 7],0)+nz([END BALANCE 8],0) AS [SUM OF BALANCES] FROM [FIRST HALF] LEFT JOIN [INVENTORY RD 8TH] ON [FIRST HALF].[PART #] = [INVENTORY RD 8TH].[PART #]; -- Daryl S "lmiller" wrote: Same results. it's just not combining the balances or giving me any of the balances. I know there probably is an easy solution to this, seems like it's just a matter of comparing part #s if they are the same add the two balances together if not just list..... "Daryl S" wrote: LMiller - OK, let's try something else. In your source query (and in the final query), change the [END BALANCE 8] to something like [END_BALANCE_8]. Run each and see what you get. -- Daryl S "lmiller" wrote: PART # PRODUCT DESCRIPTION END BALANCE 8 1018 "OD 12.7MM X 24"" LONG" 2 1234 test 1 Yes Daryl, notice above, this is the result from running my 8th term. I do have balances. You are coming up with the same conclusion I have. No matter what I have tried my 8th term isn't pulling. "Daryl S" wrote: LMiller - Progress! I think I see values for END BALANCE 7 (the 27 and the null), and the sums, but nothing for the END BALANCE 8. I would check the source query to see if you really have something in [END BALANCE 8]. Maybe there is a typo? The query is pulling the correct records, and you are getting one from each of the source records, but the [END BALANCE 8] is not coming through. Can you check your [INVENTORY BALANCE 8TH] query to make sure you are getting data in the [END BALANCE 8] column? -- Daryl S "lmiller" wrote: Tried what you suggested, this is what it comes up as now in SQL 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 ([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 #]; Now the query only list the part #'s that were in my 8th term with the 7th term balance PART # PART NAME END BALANCE 7 END BALANCE 8 SUM OF BALANCES 1018 "OD 12.7MM X 24"" LONG" 27 27 1234 test 0 what happened to all my other part # and their balances? "Daryl S" wrote: Lmiller - Are you building this final query in design mode? That should make it easy. Add the three source queries. You will need to adjust the joins by double-clicking on them, and selecting the "All from COMBINED" option for the joins between the COMBINED query and the other two sources. Delete any join between the other two queries. Then, add the fields to the query grid by double-clicking on the [PART #] and [PART NAME] fields in the COMBINED query. Then add the [END BALANCE 7] and [END BALANCE 8] fields in the same manner. Finally, in the last column, type in: SUM OF BALANCES: =nz([END BALANCE 7],0) + nz([END BALANCE 8],0) -- Daryl S "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)); |
#28
|
|||
|
|||
AT WITS END ON QUERY TOTAL
Did everyone give up? can this be done in access?
"Daryl S" wrote: Lmiller - The problem has to be with [BEGINNING INVENTORY BALANCE 8TH]. What do you get when you run this: SELECT * from [BEGINNING INVENTORY BALANCE 8TH]? -- Daryl S "lmiller" wrote: This is very frustrating... figured out my error but still same results 0 below is what I got: SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME], [BEGINNING INVENTORY BALANCE 8TH].[END BALANCE 7] FROM [COMBINED PARTS] LEFT JOIN [BEGINNING INVENTORY BALANCE 8TH] ON [COMBINED PARTS].[PART #]=[BEGINNING INVENTORY BALANCE 8TH].[PART #]; this is the "first half" I named it test. Here are the results (no balances pulled) PART # PART NAME END BALANCE 7 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 15226899-03-01 BODY MOUNT FRT LWR LH here is the other query: SELECT [TEST].[PART #], [TEST].[PART NAME], [TEST].[END BALANCE 7], [INVENTORY RD 8TH].[END BALANCE 8], nz([END BALANCE 7],0) + nz([END BALANCE 8],0) AS [SUM OF BALANCES] FROM [TEST] LEFT JOIN [INVENTORY RD 8TH] ON [TEST].[PART#] = [INVENTORY RD 8TH].[PART #] Same results big old 0 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 - Annoying... The way to get around that issue is to change the name of [PART #] in the source query to something like [PART Num]... -- Daryl S "lmiller" wrote: SELECT [COMBINED PARTS].[PART #],[COMBINED PARTS].[PART NAME],[BEGINNING INVENTORY BALANCE 8TH].[END BALANCE 7] FROM ([COMBINED PARTS] LEFT JOIN [BEGINNING INVENTORY BALANCE 8TH] ON [COMBINED PARTS].[PART #] = [BEGINNING INVENTORY BALANCE 8TH].[PART#]); The query "first half" that you suggest above won't work, giving me an error that the [beginning inventory balance 8th].[part #] can refer to more than one table in my from statement. "Daryl S" wrote: Lmiller - This should be easy - it works fine on my machine. Anyway, here is another thing to try. Join only the [COMBINED PARTS] and the [BEGINNING INVENTORY BALANCE 8TH] sources like this: You should have the END BALANCE 7 column filled where there is data. SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME], [BEGINNING INVENTORY BALANCE 8TH].[END BALANCE 7], FROM ([COMBINED PARTS] LEFT JOIN [BEGINNING INVENTORY BALANCE 8TH] ON [COMBINED PARTS].[PART #] = [BEGINNING INVENTORY BALANCE 8TH].[PART #]); Now join that query with the [INVENTORY RD 8TH] like this (change the [FIRST HALF] to the name you gave the query above: SELECT [FIRST HALF].[PART #], [FIRST HALF].[PART NAME], [FIRST HALFH].[END BALANCE 7], [INVENTORY RD 8TH].[END BALANCE 8], nz([END BALANCE 7],0)+nz([END BALANCE 8],0) AS [SUM OF BALANCES] FROM [FIRST HALF] LEFT JOIN [INVENTORY RD 8TH] ON [FIRST HALF].[PART #] = [INVENTORY RD 8TH].[PART #]; -- Daryl S "lmiller" wrote: Same results. it's just not combining the balances or giving me any of the balances. I know there probably is an easy solution to this, seems like it's just a matter of comparing part #s if they are the same add the two balances together if not just list..... "Daryl S" wrote: LMiller - OK, let's try something else. In your source query (and in the final query), change the [END BALANCE 8] to something like [END_BALANCE_8]. Run each and see what you get. -- Daryl S "lmiller" wrote: PART # PRODUCT DESCRIPTION END BALANCE 8 1018 "OD 12.7MM X 24"" LONG" 2 1234 test 1 Yes Daryl, notice above, this is the result from running my 8th term. I do have balances. You are coming up with the same conclusion I have. No matter what I have tried my 8th term isn't pulling. "Daryl S" wrote: LMiller - Progress! I think I see values for END BALANCE 7 (the 27 and the null), and the sums, but nothing for the END BALANCE 8. I would check the source query to see if you really have something in [END BALANCE 8]. Maybe there is a typo? The query is pulling the correct records, and you are getting one from each of the source records, but the [END BALANCE 8] is not coming through. Can you check your [INVENTORY BALANCE 8TH] query to make sure you are getting data in the [END BALANCE 8] column? -- Daryl S "lmiller" wrote: Tried what you suggested, this is what it comes up as now in SQL 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 ([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 #]; Now the query only list the part #'s that were in my 8th term with the 7th term balance PART # PART NAME END BALANCE 7 END BALANCE 8 SUM OF BALANCES 1018 "OD 12.7MM X 24"" LONG" 27 27 1234 test 0 what happened to all my other part # and their balances? "Daryl S" wrote: Lmiller - Are you building this final query in design mode? That should make it easy. Add the three source queries. You will need to adjust the joins by double-clicking on them, and selecting the "All from COMBINED" option for the joins between the COMBINED query and the other two sources. Delete any join between the other two queries. Then, add the fields to the query grid by double-clicking on the [PART #] and [PART NAME] fields in the COMBINED query. Then add the [END BALANCE 7] and [END BALANCE 8] fields in the same manner. Finally, in the last column, type in: SUM OF BALANCES: =nz([END BALANCE 7],0) + nz([END BALANCE 8],0) -- Daryl S "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)); |
#29
|
|||
|
|||
AT WITS END ON QUERY TOTAL
Lmiller -
I understand what you are saying about the need to add the values only if they both exist, but we resolve that issue by using the nz(value,0), which gives a zero to the columns that have no data, allowing them to be added to the columns with data. It works on my machine, giving me these results (I am adding spaces so they line up visually): PART # PART NAME END BALANCE 7 END BALANCE 8 SUM OF BALANCES 1018 "OD 12.7MM X 24"" Long" 27 2 29 1234 TEST 1 1 1 Notice there is no END BALANCE 7 for TEST 1, but the SUM OF BALANCES comes out right because of the nz function. So we don't have to worry about adding only when they both exist - we assign any that don't exist a zero in the sum calculation. What we have to fix the problem with the [BEGINNING INVENTORY BALANCE 8TH] query. Can you please post that SQL? I know running it stand-alone gives you correct answers, but please post a few of those records also. -- Daryl S "lmiller" wrote: I get the 8th term balances as they should be. I could be wrong but I have been playing with these and I think it's in the way they are joined. I reversed the query a little bit and put the 8th term items first than the 7th term. Now all the 8th term balances come up but no 7th. I am not familar with "if" statments but seems like if the the 7th and 8th part # are = than they need to be added together if not than their balances should just list. If I run just the part # and names together in a combined query they combine just great when i try to add the balances it gets all confused......I put the 7th term balance in SQL first it pulls just those items if I put the 8th first it just pulls those items. i know this is hard trying to relay. "Daryl S" wrote: Lmiller - The problem has to be with [BEGINNING INVENTORY BALANCE 8TH]. What do you get when you run this: SELECT * from [BEGINNING INVENTORY BALANCE 8TH]? -- Daryl S "lmiller" wrote: This is very frustrating... figured out my error but still same results 0 below is what I got: SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME], [BEGINNING INVENTORY BALANCE 8TH].[END BALANCE 7] FROM [COMBINED PARTS] LEFT JOIN [BEGINNING INVENTORY BALANCE 8TH] ON [COMBINED PARTS].[PART #]=[BEGINNING INVENTORY BALANCE 8TH].[PART #]; this is the "first half" I named it test. Here are the results (no balances pulled) PART # PART NAME END BALANCE 7 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 15226899-03-01 BODY MOUNT FRT LWR LH here is the other query: SELECT [TEST].[PART #], [TEST].[PART NAME], [TEST].[END BALANCE 7], [INVENTORY RD 8TH].[END BALANCE 8], nz([END BALANCE 7],0) + nz([END BALANCE 8],0) AS [SUM OF BALANCES] FROM [TEST] LEFT JOIN [INVENTORY RD 8TH] ON [TEST].[PART#] = [INVENTORY RD 8TH].[PART #] Same results big old 0 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 - Annoying... The way to get around that issue is to change the name of [PART #] in the source query to something like [PART Num]... -- Daryl S "lmiller" wrote: SELECT [COMBINED PARTS].[PART #],[COMBINED PARTS].[PART NAME],[BEGINNING INVENTORY BALANCE 8TH].[END BALANCE 7] FROM ([COMBINED PARTS] LEFT JOIN [BEGINNING INVENTORY BALANCE 8TH] ON [COMBINED PARTS].[PART #] = [BEGINNING INVENTORY BALANCE 8TH].[PART#]); The query "first half" that you suggest above won't work, giving me an error that the [beginning inventory balance 8th].[part #] can refer to more than one table in my from statement. "Daryl S" wrote: Lmiller - This should be easy - it works fine on my machine. Anyway, here is another thing to try. Join only the [COMBINED PARTS] and the [BEGINNING INVENTORY BALANCE 8TH] sources like this: You should have the END BALANCE 7 column filled where there is data. SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME], [BEGINNING INVENTORY BALANCE 8TH].[END BALANCE 7], FROM ([COMBINED PARTS] LEFT JOIN [BEGINNING INVENTORY BALANCE 8TH] ON [COMBINED PARTS].[PART #] = [BEGINNING INVENTORY BALANCE 8TH].[PART #]); Now join that query with the [INVENTORY RD 8TH] like this (change the [FIRST HALF] to the name you gave the query above: SELECT [FIRST HALF].[PART #], [FIRST HALF].[PART NAME], [FIRST HALFH].[END BALANCE 7], [INVENTORY RD 8TH].[END BALANCE 8], nz([END BALANCE 7],0)+nz([END BALANCE 8],0) AS [SUM OF BALANCES] FROM [FIRST HALF] LEFT JOIN [INVENTORY RD 8TH] ON [FIRST HALF].[PART #] = [INVENTORY RD 8TH].[PART #]; -- Daryl S "lmiller" wrote: Same results. it's just not combining the balances or giving me any of the balances. I know there probably is an easy solution to this, seems like it's just a matter of comparing part #s if they are the same add the two balances together if not just list..... "Daryl S" wrote: LMiller - OK, let's try something else. In your source query (and in the final query), change the [END BALANCE 8] to something like [END_BALANCE_8]. Run each and see what you get. -- Daryl S "lmiller" wrote: PART # PRODUCT DESCRIPTION END BALANCE 8 1018 "OD 12.7MM X 24"" LONG" 2 1234 test 1 Yes Daryl, notice above, this is the result from running my 8th term. I do have balances. You are coming up with the same conclusion I have. No matter what I have tried my 8th term isn't pulling. "Daryl S" wrote: LMiller - Progress! I think I see values for END BALANCE 7 (the 27 and the null), and the sums, but nothing for the END BALANCE 8. I would check the source query to see if you really have something in [END BALANCE 8]. Maybe there is a typo? The query is pulling the correct records, and you are getting one from each of the source records, but the [END BALANCE 8] is not coming through. Can you check your [INVENTORY BALANCE 8TH] query to make sure you are getting data in the [END BALANCE 8] column? -- Daryl S "lmiller" wrote: Tried what you suggested, this is what it comes up as now in SQL 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 ([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 #]; Now the query only list the part #'s that were in my 8th term with the 7th term balance PART # PART NAME END BALANCE 7 END BALANCE 8 SUM OF BALANCES 1018 "OD 12.7MM X 24"" LONG" 27 27 1234 test 0 what happened to all my other part # and their balances? "Daryl S" wrote: Lmiller - Are you building this final query in design mode? That should make it easy. Add the three source queries. You will need to adjust the joins by double-clicking on them, and selecting the "All from COMBINED" option for the joins between the COMBINED query and the other two sources. Delete any join between the other two queries. Then, add the fields to the query grid by double-clicking on the [PART #] and [PART NAME] fields in the COMBINED query. Then add the [END BALANCE 7] and [END BALANCE 8] fields in the same manner. Finally, in the last column, type in: SUM OF BALANCES: =nz([END BALANCE 7],0) + nz([END BALANCE 8],0) -- Daryl S "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)); |
#30
|
|||
|
|||
AT WITS END ON QUERY TOTAL
Daryl here is the results from the beginning inventory... and the SQL
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 11589252 GMT319 L4 BOLT 6 15226899-03-01 BODY MOUNT FRT LWR LH 14 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)); "Daryl S" wrote: Lmiller - I understand what you are saying about the need to add the values only if they both exist, but we resolve that issue by using the nz(value,0), which gives a zero to the columns that have no data, allowing them to be added to the columns with data. It works on my machine, giving me these results (I am adding spaces so they line up visually): PART # PART NAME END BALANCE 7 END BALANCE 8 SUM OF BALANCES 1018 "OD 12.7MM X 24"" Long" 27 2 29 1234 TEST 1 1 1 Notice there is no END BALANCE 7 for TEST 1, but the SUM OF BALANCES comes out right because of the nz function. So we don't have to worry about adding only when they both exist - we assign any that don't exist a zero in the sum calculation. What we have to fix the problem with the [BEGINNING INVENTORY BALANCE 8TH] query. Can you please post that SQL? I know running it stand-alone gives you correct answers, but please post a few of those records also. -- Daryl S "lmiller" wrote: I get the 8th term balances as they should be. I could be wrong but I have been playing with these and I think it's in the way they are joined. I reversed the query a little bit and put the 8th term items first than the 7th term. Now all the 8th term balances come up but no 7th. I am not familar with "if" statments but seems like if the the 7th and 8th part # are = than they need to be added together if not than their balances should just list. If I run just the part # and names together in a combined query they combine just great when i try to add the balances it gets all confused......I put the 7th term balance in SQL first it pulls just those items if I put the 8th first it just pulls those items. i know this is hard trying to relay. "Daryl S" wrote: Lmiller - The problem has to be with [BEGINNING INVENTORY BALANCE 8TH]. What do you get when you run this: SELECT * from [BEGINNING INVENTORY BALANCE 8TH]? -- Daryl S "lmiller" wrote: This is very frustrating... figured out my error but still same results 0 below is what I got: SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME], [BEGINNING INVENTORY BALANCE 8TH].[END BALANCE 7] FROM [COMBINED PARTS] LEFT JOIN [BEGINNING INVENTORY BALANCE 8TH] ON [COMBINED PARTS].[PART #]=[BEGINNING INVENTORY BALANCE 8TH].[PART #]; this is the "first half" I named it test. Here are the results (no balances pulled) PART # PART NAME END BALANCE 7 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 15226899-03-01 BODY MOUNT FRT LWR LH here is the other query: SELECT [TEST].[PART #], [TEST].[PART NAME], [TEST].[END BALANCE 7], [INVENTORY RD 8TH].[END BALANCE 8], nz([END BALANCE 7],0) + nz([END BALANCE 8],0) AS [SUM OF BALANCES] FROM [TEST] LEFT JOIN [INVENTORY RD 8TH] ON [TEST].[PART#] = [INVENTORY RD 8TH].[PART #] Same results big old 0 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 - Annoying... The way to get around that issue is to change the name of [PART #] in the source query to something like [PART Num]... -- Daryl S "lmiller" wrote: SELECT [COMBINED PARTS].[PART #],[COMBINED PARTS].[PART NAME],[BEGINNING INVENTORY BALANCE 8TH].[END BALANCE 7] FROM ([COMBINED PARTS] LEFT JOIN [BEGINNING INVENTORY BALANCE 8TH] ON [COMBINED PARTS].[PART #] = [BEGINNING INVENTORY BALANCE 8TH].[PART#]); The query "first half" that you suggest above won't work, giving me an error that the [beginning inventory balance 8th].[part #] can refer to more than one table in my from statement. "Daryl S" wrote: Lmiller - This should be easy - it works fine on my machine. Anyway, here is another thing to try. Join only the [COMBINED PARTS] and the [BEGINNING INVENTORY BALANCE 8TH] sources like this: You should have the END BALANCE 7 column filled where there is data. SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME], [BEGINNING INVENTORY BALANCE 8TH].[END BALANCE 7], FROM ([COMBINED PARTS] LEFT JOIN [BEGINNING INVENTORY BALANCE 8TH] ON [COMBINED PARTS].[PART #] = [BEGINNING INVENTORY BALANCE 8TH].[PART #]); Now join that query with the [INVENTORY RD 8TH] like this (change the [FIRST HALF] to the name you gave the query above: SELECT [FIRST HALF].[PART #], [FIRST HALF].[PART NAME], [FIRST HALFH].[END BALANCE 7], [INVENTORY RD 8TH].[END BALANCE 8], nz([END BALANCE 7],0)+nz([END BALANCE 8],0) AS [SUM OF BALANCES] FROM [FIRST HALF] LEFT JOIN [INVENTORY RD 8TH] ON [FIRST HALF].[PART #] = [INVENTORY RD 8TH].[PART #]; -- Daryl S "lmiller" wrote: Same results. it's just not combining the balances or giving me any of the balances. I know there probably is an easy solution to this, seems like it's just a matter of comparing part #s if they are the same add the two balances together if not just list..... "Daryl S" wrote: LMiller - OK, let's try something else. In your source query (and in the final query), change the [END BALANCE 8] to something like [END_BALANCE_8]. Run each and see what you get. -- Daryl S "lmiller" wrote: PART # PRODUCT DESCRIPTION END BALANCE 8 1018 "OD 12.7MM X 24"" LONG" 2 1234 test 1 Yes Daryl, notice above, this is the result from running my 8th term. I do have balances. You are coming up with the same conclusion I have. No matter what I have tried my 8th term isn't pulling. "Daryl S" wrote: LMiller - Progress! I think I see values for END BALANCE 7 (the 27 and the null), and the sums, but nothing for the END BALANCE 8. I would check the source query to see if you really have something in [END BALANCE 8]. Maybe there is a typo? The query is pulling the correct records, and you are getting one from each of the source records, but the [END BALANCE 8] is not coming through. Can you check your [INVENTORY BALANCE 8TH] query to make sure you are getting data in the [END BALANCE 8] column? -- Daryl S "lmiller" wrote: Tried what you suggested, this is what it comes up as now in SQL 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 ([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 #]; Now the query only list the part #'s that were in my 8th term with the 7th term balance PART # PART NAME END BALANCE 7 END BALANCE 8 SUM OF BALANCES 1018 "OD 12.7MM X 24"" LONG" 27 27 1234 test 0 what happened to all my other part # and their balances? "Daryl S" wrote: Lmiller - Are you building this final query in design mode? That should make it easy. Add the three source queries. You will need to adjust the joins by double-clicking on them, and selecting the "All from COMBINED" option for the joins between the COMBINED query and the other two sources. Delete any join between the other two queries. Then, add the fields to the query grid by double-clicking on the [PART #] and [PART NAME] fields in the COMBINED query. Then add the [END BALANCE 7] and [END BALANCE 8] fields in the same manner. Finally, in the last column, type in: SUM OF BALANCES: =nz([END BALANCE 7],0) + nz([END BALANCE 8],0) -- Daryl S "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] |
Thread Tools | |
Display Modes | |
|
|