COMBINING QUERIES INTO 1

  • Thread starter Thread starter lmiller
  • Start date Start date
L

lmiller

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?
 
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 -

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 #])
 
Thank you John, but the balances come up as 0 for all part #. any other
suggestions?

John Spencer said:
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
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?
Click to expand...
.
Click to expand...
 
Back
Top