COMBINING QUERIES INTO 1

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

John Spencer

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
 
D

Daryl S

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 #])
 
L

lmiller

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

combining queries into 1 8
combine like items in query 1
3 queries into 1 8
AT WITS END ON QUERY TOTAL 34
COMBINING 2 QUERIES INTO 1 12
union query 19
Combining 3 queries 2
calculating results within a query 1

Top