If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
COMBINING QUERIES INTO 1
I have posted a similar question earlier today, I am trying to combine a
couple of queries into one. I keep getting a syntax error in the joined operation. SELECT [COMBINED PARTS].[PART #], nz([BEGINNING INVENTORY BALANCE 8TH].[PART NAME], [INVENTORY R&D].[PART NAME]),[BEGINNING INVENTORY BALANCE 8TH].[END BALANCE] AS [TERM 7 END BALANCE], [INVENTORY R&D].[END BALANCE] AS [TERM 8 BALANCE], nz([BEGINNING INVENTORY BALANCE 8TH].[END BALANCE],0) + nz([INVENTORY R&D].[END BALANCE],0) AS [SUM OF BALANCES] FROM ([COMBINED PARTS] LEFT JOIN [BEGINNING INVENTORY BALANCE 8TH] ON [COMBINED PARTS].[PART #] =([BEGINNINING INVENTORY BALANCE 8TH].[PART #]) LEFT JOIN [INVENTORY R&D] ON ([COMBINED PARTS].[PART#] = [INVENTORY R&D].[PARTS #]) any suggestions on what I am doing wrong? |
#2
|
|||
|
|||
COMBINING QUERIES INTO 1
Note the misspelling in line 9 below BEGINNINING should be BEGINNING in the
table reference [BEGINNINING INVENTORY BALANCE 8TH] Also, you have unbalanced parentheses with an extra parenthesis before that table reference SELECT [COMBINED PARTS].[PART #] , nz([BEGINNING INVENTORY BALANCE 8TH].[PART NAME] , [INVENTORY R&D].[PART NAME]) ,[BEGINNING INVENTORY BALANCE 8TH].[END BALANCE] AS [TERM 7 END BALANCE] , [INVENTORY R&D].[END BALANCE] AS [TERM 8 BALANCE] , nz([BEGINNING INVENTORY BALANCE 8TH].[END BALANCE],0) + nz([INVENTORY R&D].[END BALANCE],0) AS [SUM OF BALANCES] FROM ([COMBINED PARTS] LEFT JOIN [BEGINNING INVENTORY BALANCE 8TH] ON [COMBINED PARTS].[PART #] =([BEGINNINING INVENTORY BALANCE 8TH].[PART #]) LEFT JOIN [INVENTORY R&D] ON ([COMBINED PARTS].[PART#] = [INVENTORY R&D].[PARTS #]) Try rewriting this as SELECT [COMBINED PARTS].[PART #] , nz([BEGINNING INVENTORY BALANCE 8TH].[PART NAME] , [INVENTORY R&D].[PART NAME]) ,[BEGINNING INVENTORY BALANCE 8TH].[END BALANCE] AS [TERM 7 END BALANCE] , [INVENTORY R&D].[END BALANCE] AS [TERM 8 BALANCE] , nz([BEGINNING INVENTORY BALANCE 8TH].[END BALANCE],0) + nz([INVENTORY R&D].[END BALANCE],0) AS [SUM OF BALANCES] FROM ( [COMBINED PARTS] LEFT JOIN [BEGINNING INVENTORY BALANCE 8TH] ON [COMBINED PARTS].[PART #] =[BEGINNING INVENTORY BALANCE 8TH].[PART #] ) LEFT JOIN [INVENTORY R&D] ON [COMBINED PARTS].[PART#] = [INVENTORY R&D].[PARTS #] John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County lmiller wrote: I have posted a similar question earlier today, I am trying to combine a couple of queries into one. I keep getting a syntax error in the joined operation. SELECT [COMBINED PARTS].[PART #], nz([BEGINNING INVENTORY BALANCE 8TH].[PART NAME], [INVENTORY R&D].[PART NAME]),[BEGINNING INVENTORY BALANCE 8TH].[END BALANCE] AS [TERM 7 END BALANCE], [INVENTORY R&D].[END BALANCE] AS [TERM 8 BALANCE], nz([BEGINNING INVENTORY BALANCE 8TH].[END BALANCE],0) + nz([INVENTORY R&D].[END BALANCE],0) AS [SUM OF BALANCES] FROM ([COMBINED PARTS] LEFT JOIN [BEGINNING INVENTORY BALANCE 8TH] ON [COMBINED PARTS].[PART #] =([BEGINNINING INVENTORY BALANCE 8TH].[PART #]) LEFT JOIN [INVENTORY R&D] ON ([COMBINED PARTS].[PART#] = [INVENTORY R&D].[PARTS #]) any suggestions on what I am doing wrong? |
#3
|
|||
|
|||
COMBINING QUERIES INTO 1
LMiller -
There was an extra opening parenthesis. Try this: SELECT [COMBINED PARTS].[PART #], nz([BEGINNING INVENTORY BALANCE 8TH].[PART NAME], [INVENTORY R&D].[PART NAME]),[BEGINNING INVENTORY BALANCE 8TH].[END BALANCE] AS [TERM 7 END BALANCE], [INVENTORY R&D].[END BALANCE] AS [TERM 8 BALANCE], nz([BEGINNING INVENTORY BALANCE 8TH].[END BALANCE],0) + nz([INVENTORY R&D].[END BALANCE],0) AS [SUM OF BALANCES] FROM ([COMBINED PARTS] LEFT JOIN [BEGINNING INVENTORY BALANCE 8TH] ON [COMBINED PARTS].[PART #] = [BEGINNINING INVENTORY BALANCE 8TH].[PART #]) LEFT JOIN [INVENTORY R&D] ON ([COMBINED PARTS].[PART#] = [INVENTORY R&D].[PARTS #]) -- Daryl S "lmiller" wrote: I have posted a similar question earlier today, I am trying to combine a couple of queries into one. I keep getting a syntax error in the joined operation. SELECT [COMBINED PARTS].[PART #], nz([BEGINNING INVENTORY BALANCE 8TH].[PART NAME], [INVENTORY R&D].[PART NAME]),[BEGINNING INVENTORY BALANCE 8TH].[END BALANCE] AS [TERM 7 END BALANCE], [INVENTORY R&D].[END BALANCE] AS [TERM 8 BALANCE], nz([BEGINNING INVENTORY BALANCE 8TH].[END BALANCE],0) + nz([INVENTORY R&D].[END BALANCE],0) AS [SUM OF BALANCES] FROM ([COMBINED PARTS] LEFT JOIN [BEGINNING INVENTORY BALANCE 8TH] ON [COMBINED PARTS].[PART #] =([BEGINNINING INVENTORY BALANCE 8TH].[PART #]) LEFT JOIN [INVENTORY R&D] ON ([COMBINED PARTS].[PART#] = [INVENTORY R&D].[PARTS #]) any suggestions on what I am doing wrong? |
#4
|
|||
|
|||
COMBINING QUERIES INTO 1
Thank you John, but the balances come up as 0 for all part #. any other
suggestions? "John Spencer" wrote: Note the misspelling in line 9 below BEGINNINING should be BEGINNING in the table reference [BEGINNINING INVENTORY BALANCE 8TH] Also, you have unbalanced parentheses with an extra parenthesis before that table reference SELECT [COMBINED PARTS].[PART #] , nz([BEGINNING INVENTORY BALANCE 8TH].[PART NAME] , [INVENTORY R&D].[PART NAME]) ,[BEGINNING INVENTORY BALANCE 8TH].[END BALANCE] AS [TERM 7 END BALANCE] , [INVENTORY R&D].[END BALANCE] AS [TERM 8 BALANCE] , nz([BEGINNING INVENTORY BALANCE 8TH].[END BALANCE],0) + nz([INVENTORY R&D].[END BALANCE],0) AS [SUM OF BALANCES] FROM ([COMBINED PARTS] LEFT JOIN [BEGINNING INVENTORY BALANCE 8TH] ON [COMBINED PARTS].[PART #] =([BEGINNINING INVENTORY BALANCE 8TH].[PART #]) LEFT JOIN [INVENTORY R&D] ON ([COMBINED PARTS].[PART#] = [INVENTORY R&D].[PARTS #]) Try rewriting this as SELECT [COMBINED PARTS].[PART #] , nz([BEGINNING INVENTORY BALANCE 8TH].[PART NAME] , [INVENTORY R&D].[PART NAME]) ,[BEGINNING INVENTORY BALANCE 8TH].[END BALANCE] AS [TERM 7 END BALANCE] , [INVENTORY R&D].[END BALANCE] AS [TERM 8 BALANCE] , nz([BEGINNING INVENTORY BALANCE 8TH].[END BALANCE],0) + nz([INVENTORY R&D].[END BALANCE],0) AS [SUM OF BALANCES] FROM ( [COMBINED PARTS] LEFT JOIN [BEGINNING INVENTORY BALANCE 8TH] ON [COMBINED PARTS].[PART #] =[BEGINNING INVENTORY BALANCE 8TH].[PART #] ) LEFT JOIN [INVENTORY R&D] ON [COMBINED PARTS].[PART#] = [INVENTORY R&D].[PARTS #] John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County lmiller wrote: I have posted a similar question earlier today, I am trying to combine a couple of queries into one. I keep getting a syntax error in the joined operation. SELECT [COMBINED PARTS].[PART #], nz([BEGINNING INVENTORY BALANCE 8TH].[PART NAME], [INVENTORY R&D].[PART NAME]),[BEGINNING INVENTORY BALANCE 8TH].[END BALANCE] AS [TERM 7 END BALANCE], [INVENTORY R&D].[END BALANCE] AS [TERM 8 BALANCE], nz([BEGINNING INVENTORY BALANCE 8TH].[END BALANCE],0) + nz([INVENTORY R&D].[END BALANCE],0) AS [SUM OF BALANCES] FROM ([COMBINED PARTS] LEFT JOIN [BEGINNING INVENTORY BALANCE 8TH] ON [COMBINED PARTS].[PART #] =([BEGINNINING INVENTORY BALANCE 8TH].[PART #]) LEFT JOIN [INVENTORY R&D] ON ([COMBINED PARTS].[PART#] = [INVENTORY R&D].[PARTS #]) any suggestions on what I am doing wrong? . |
Thread Tools | |
Display Modes | |
|
|