COMBINING 2 QUERIES INTO 1

L

lmiller

I have developed this query for my 7th term inventory balances:
SELECT [INVENTORY R&D 7th].[PART #], [INVENTORY R&D 7th].[PART NAME],
[INVENTORY R&D 7th].[END BALANCE]
FROM [INVENTORY R&D 7th]
WHERE ((([INVENTORY R&D 7th].[END BALANCE])>0));


This query for my 8th term balances:

SELECT [INVENTORY R&D].[PART #], [INVENTORY R&D].[PART NAME], [INVENTORY
R&D].[END BALANCE]
FROM [INVENTORY R&D]
WHERE ((([INVENTORY R&D].[END BALANCE])>0));


I would like to combine them into 1 compelte inventory list. I would like
to list all part # that have a balance and if there are the same part # in
each of the 2 queries, I would like their balances added together . I
greatly appreciate any help you can give. I have this union query thus far:

SELECT [PART #],[PART NAME],[END BALANCE]
FROM [BEGINNING INVENTORY BALANCE 8TH]
UNION SELECT [PART #],[PART NAME],[END BALANCE]
FROM [INVENTORY R&D

when I hit run the part# and names list correctly but the balances for the
8th term do not list nor do the add together with the 7th.

Thank you in advance for you help!!!!!!
 
D

Daryl S

Lmiller -

A UNION query will append two sets of rows together if done properly, but it
cannot add records from one query to another. You need a SELECT query using
both of the queries you initially created. You may need a third query before
you join them depending on the data. If all of the records you need to see
are in the 8th term balances query, (or all are in the 7th term balances
query), then you don't need the third query.

If there are some records that will be returned in the 7th term balances
query that are not in the 8th term query, and vice versa, and you need to see
ALL of the records that had balances in either 7th or 8th term, then you need
another query (the UNION query) to get the right records. This query should
only contain the key values, and look like this:

SELECT [INVENTORY R&D 7th].[PART #]
FROM [INVENTORY R&D 7th]
WHERE ((([INVENTORY R&D 7th].[END BALANCE])>0));
UNION SELECT [PART #]
FROM [INVENTORY R&D]
WHERE ((([INVENTORY R&D].[END BALANCE])>0))
ORDER BY [PART #];

Then to get the results you want, you need to join the three queries. If
the query names were qryInv7, qryInv8, and qryInvUnion, the SQL would look
like this:

SELECT qryInvUnion.[PART #], nz(qryInv7.[PART NAME],qryInv8.[PART NAME]),
qryInv7.[END BALANCE] As [Term 7 End Balance], qryInv8.[END BALANCE] As [Term
8 Balance], nz(qryInv7.[END BALANCE],0) + nz(qryInv8.[END BALANCE],0) AS [Sum
Of Balances]
FROM (qryInvUnion LEFT JOIN qryInv7 ON qryInvUnion.[PART #] = qryInv7.[PART
#]) LEFT JOIN qryInv8 ON qryInvUnion.[PART #] = qryInv8.[PART #];

(This is untested, so I could have a typo. You need to substitute your
query names of course.)

--
Daryl S


lmiller said:
I have developed this query for my 7th term inventory balances:
SELECT [INVENTORY R&D 7th].[PART #], [INVENTORY R&D 7th].[PART NAME],
[INVENTORY R&D 7th].[END BALANCE]
FROM [INVENTORY R&D 7th]
WHERE ((([INVENTORY R&D 7th].[END BALANCE])>0));


This query for my 8th term balances:

SELECT [INVENTORY R&D].[PART #], [INVENTORY R&D].[PART NAME], [INVENTORY
R&D].[END BALANCE]
FROM [INVENTORY R&D]
WHERE ((([INVENTORY R&D].[END BALANCE])>0));


I would like to combine them into 1 compelte inventory list. I would like
to list all part # that have a balance and if there are the same part # in
each of the 2 queries, I would like their balances added together . I
greatly appreciate any help you can give. I have this union query thus far:

SELECT [PART #],[PART NAME],[END BALANCE]
FROM [BEGINNING INVENTORY BALANCE 8TH]
UNION SELECT [PART #],[PART NAME],[END BALANCE]
FROM [INVENTORY R&D

when I hit run the part# and names list correctly but the balances for the
8th term do not list nor do the add together with the 7th.

Thank you in advance for you help!!!!!!
Click to expand...
 
L

lmiller

Thank you Daryl,

ok that makes sense. My union query that I made does append the part #
together properly now I know why the total won't come out. A union query
cannot add totals. I am trying to develop a query combining all 3 of these
but I have way to many syntax errors. But thank you again for clearing my
confusion up why the total weren't coming out.

Daryl S said:
Lmiller -

A UNION query will append two sets of rows together if done properly, but it
cannot add records from one query to another. You need a SELECT query using
both of the queries you initially created. You may need a third query before
you join them depending on the data. If all of the records you need to see
are in the 8th term balances query, (or all are in the 7th term balances
query), then you don't need the third query.

If there are some records that will be returned in the 7th term balances
query that are not in the 8th term query, and vice versa, and you need to see
ALL of the records that had balances in either 7th or 8th term, then you need
another query (the UNION query) to get the right records. This query should
only contain the key values, and look like this:

SELECT [INVENTORY R&D 7th].[PART #]
FROM [INVENTORY R&D 7th]
WHERE ((([INVENTORY R&D 7th].[END BALANCE])>0));
UNION SELECT [PART #]
FROM [INVENTORY R&D]
WHERE ((([INVENTORY R&D].[END BALANCE])>0))
ORDER BY [PART #];

Then to get the results you want, you need to join the three queries. If
the query names were qryInv7, qryInv8, and qryInvUnion, the SQL would look
like this:

SELECT qryInvUnion.[PART #], nz(qryInv7.[PART NAME],qryInv8.[PART NAME]),
qryInv7.[END BALANCE] As [Term 7 End Balance], qryInv8.[END BALANCE] As [Term
8 Balance], nz(qryInv7.[END BALANCE],0) + nz(qryInv8.[END BALANCE],0) AS [Sum
Of Balances]
FROM (qryInvUnion LEFT JOIN qryInv7 ON qryInvUnion.[PART #] = qryInv7.[PART
#]) LEFT JOIN qryInv8 ON qryInvUnion.[PART #] = qryInv8.[PART #];

(This is untested, so I could have a typo. You need to substitute your
query names of course.)

--
Daryl S


lmiller said:
I have developed this query for my 7th term inventory balances:
SELECT [INVENTORY R&D 7th].[PART #], [INVENTORY R&D 7th].[PART NAME],
[INVENTORY R&D 7th].[END BALANCE]
FROM [INVENTORY R&D 7th]
WHERE ((([INVENTORY R&D 7th].[END BALANCE])>0));


This query for my 8th term balances:

SELECT [INVENTORY R&D].[PART #], [INVENTORY R&D].[PART NAME], [INVENTORY
R&D].[END BALANCE]
FROM [INVENTORY R&D]
WHERE ((([INVENTORY R&D].[END BALANCE])>0));


I would like to combine them into 1 compelte inventory list. I would like
to list all part # that have a balance and if there are the same part # in
each of the 2 queries, I would like their balances added together . I
greatly appreciate any help you can give. I have this union query thus far:

SELECT [PART #],[PART NAME],[END BALANCE]
FROM [BEGINNING INVENTORY BALANCE 8TH]
UNION SELECT [PART #],[PART NAME],[END BALANCE]
FROM [INVENTORY R&D

when I hit run the part# and names list correctly but the balances for the
8th term do not list nor do the add together with the 7th.

Thank you in advance for you help!!!!!!
Click to expand...
Click to expand...
 
L

lmiller

Thanks again Daryl for your help but this must be something access can't do

Daryl S said:
Lmiller -

A UNION query will append two sets of rows together if done properly, but it
cannot add records from one query to another. You need a SELECT query using
both of the queries you initially created. You may need a third query before
you join them depending on the data. If all of the records you need to see
are in the 8th term balances query, (or all are in the 7th term balances
query), then you don't need the third query.

If there are some records that will be returned in the 7th term balances
query that are not in the 8th term query, and vice versa, and you need to see
ALL of the records that had balances in either 7th or 8th term, then you need
another query (the UNION query) to get the right records. This query should
only contain the key values, and look like this:

SELECT [INVENTORY R&D 7th].[PART #]
FROM [INVENTORY R&D 7th]
WHERE ((([INVENTORY R&D 7th].[END BALANCE])>0));
UNION SELECT [PART #]
FROM [INVENTORY R&D]
WHERE ((([INVENTORY R&D].[END BALANCE])>0))
ORDER BY [PART #];

Then to get the results you want, you need to join the three queries. If
the query names were qryInv7, qryInv8, and qryInvUnion, the SQL would look
like this:

SELECT qryInvUnion.[PART #], nz(qryInv7.[PART NAME],qryInv8.[PART NAME]),
qryInv7.[END BALANCE] As [Term 7 End Balance], qryInv8.[END BALANCE] As [Term
8 Balance], nz(qryInv7.[END BALANCE],0) + nz(qryInv8.[END BALANCE],0) AS [Sum
Of Balances]
FROM (qryInvUnion LEFT JOIN qryInv7 ON qryInvUnion.[PART #] = qryInv7.[PART
#]) LEFT JOIN qryInv8 ON qryInvUnion.[PART #] = qryInv8.[PART #];

(This is untested, so I could have a typo. You need to substitute your
query names of course.)

--
Daryl S


lmiller said:
I have developed this query for my 7th term inventory balances:
SELECT [INVENTORY R&D 7th].[PART #], [INVENTORY R&D 7th].[PART NAME],
[INVENTORY R&D 7th].[END BALANCE]
FROM [INVENTORY R&D 7th]
WHERE ((([INVENTORY R&D 7th].[END BALANCE])>0));


This query for my 8th term balances:

SELECT [INVENTORY R&D].[PART #], [INVENTORY R&D].[PART NAME], [INVENTORY
R&D].[END BALANCE]
FROM [INVENTORY R&D]
WHERE ((([INVENTORY R&D].[END BALANCE])>0));


I would like to combine them into 1 compelte inventory list. I would like
to list all part # that have a balance and if there are the same part # in
each of the 2 queries, I would like their balances added together . I
greatly appreciate any help you can give. I have this union query thus far:

SELECT [PART #],[PART NAME],[END BALANCE]
FROM [BEGINNING INVENTORY BALANCE 8TH]
UNION SELECT [PART #],[PART NAME],[END BALANCE]
FROM [INVENTORY R&D

when I hit run the part# and names list correctly but the balances for the
8th term do not list nor do the add together with the 7th.

Thank you in advance for you help!!!!!!
Click to expand...
Click to expand...
 
J

John W. Vinson

My union query that I made does append the part #
together properly now I know why the total won't come out. A union query
cannot add totals.
Click to expand...

true... but you can create a UNION query to retrieve the records, and then
base a Totals query *on that UNION query* to do the totals.
 
P

PieterLinden via AccessMonster.com

lmiller said:
Thanks again Daryl for your help but this must be something access can't do
Click to expand...

You sure about that? What is the structure of the underlying table you're
trying to query? SQL won't work well (if at all) on improperly structured
tables. I know, I've had to fix them.

Access can't work well with completely screwed up tables. If you structure
your tables with an understanding of what SQL *can* work with, you get a lot
farther.
 
L

lmiller

I believe the tables are just fine have been using them for over 10 years now
from one of our fiscal terms to another.
 
L

lmiller

John,

I have developed a union query to retrieve the data and a totals query, but
the totals query only takes into account the totals where the part numbers
match exactly, no matter if i left join, right join, or inner I would like
to combine all part # that have a balance no matter if they are in 1 table or
both.
 
L

lmiller

here is the query i am trying to developt to capture the totals, but there
is a syntax in the joined operation

SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME], [BEGINNING
INVENTORY BALANCE 8TH].[END BALANCE] AS [TERM 7],[INVENTORY R&D].[END
BALANCE] AS [TERM 8],
nz([BEGINNING INVENTORY BALANCE 8TH].[END BALANCE],0) + nz([INVENTORY
R&D].[END BALANCE],0) AS [SUM OF BALANCES]
FROM ([COMPINED 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].[PART #])
 
L

lmiller

Hello John,

I have developed the totals query as follows:
SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME], [BEGINNING
INVENTORY BALANCE 8TH].[END BALANCE] AS [TERM 7],[INVENTORY R&D].[END
BALANCE] AS [TERM 8],
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].[PART #]

The query runs with no errors however none of the balances are coming over.
all the parts show 0 in inventory:
PART # PART NAME TERM 7 TERM 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
15226899-03-01 BODY MOUNT FRT LWR LH 0
 
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 #])
--
Daryl S


lmiller said:
here is the query i am trying to developt to capture the totals, but there
is a syntax in the joined operation

SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME], [BEGINNING
INVENTORY BALANCE 8TH].[END BALANCE] AS [TERM 7],[INVENTORY R&D].[END
BALANCE] AS [TERM 8],
nz([BEGINNING INVENTORY BALANCE 8TH].[END BALANCE],0) + nz([INVENTORY
R&D].[END BALANCE],0) AS [SUM OF BALANCES]
FROM ([COMPINED 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].[PART #])

lmiller said:
John,

I have developed a union query to retrieve the data and a totals query, but
the totals query only takes into account the totals where the part numbers
match exactly, no matter if i left join, right join, or inner I would like
to combine all part # that have a balance no matter if they are in 1 table or
both.
Click to expand...
Click to expand...
 
L

lmiller

thanks for the tip, but I lost all my names doing that, here is the query I
have developed:
SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME],[BEGINNING
INVENTORY BALANCE 8TH].[END BALANCE] AS [TERM 7],[INVENTORY R&D].[END
BALANCE] AS [TERM 8],
([BEGINNING INVENTORY BALANCE 8TH].[END BALANCE]) + ([INVENTORY R&D].[END
BALANCE]) 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].[PART #]

here is the data:

PART # PART NAME TERM 7 TERM 8 SUM OF BALANCES
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


Part# and names pull just fine but now there are no balances from either
query it is pulling from.


Daryl S said:
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 said:
here is the query i am trying to developt to capture the totals, but there
is a syntax in the joined operation

SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME], [BEGINNING
INVENTORY BALANCE 8TH].[END BALANCE] AS [TERM 7],[INVENTORY R&D].[END
BALANCE] AS [TERM 8],
nz([BEGINNING INVENTORY BALANCE 8TH].[END BALANCE],0) + nz([INVENTORY
R&D].[END BALANCE],0) AS [SUM OF BALANCES]
FROM ([COMPINED 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].[PART #])

lmiller said:
John,

I have developed a union query to retrieve the data and a totals query, but
the totals query only takes into account the totals where the part numbers
match exactly, no matter if i left join, right join, or inner I would like
to combine all part # that have a balance no matter if they are in 1 table or
both.

:

My union query that I made does append the part #
together properly now I know why the total won't come out. A union query
cannot add totals.

true... but you can create a UNION query to retrieve the records, and then
base a Totals query *on that UNION query* to do the totals.
Click to expand...
Click to expand...
Click to expand...
 
D

Daryl S

Lmiller -

You need the nz(fieldname,0) when adding the two fields together for the
balance, because if one of the two fields is null, the answer will be null.
The nz construct changes nulls to zeros, so they can be added. Since I don't
see anything in the [TERM 7] or [TERM 8] columns, I would check the source
queries and make sure they have data in them (that is, check [BEGINNING
INVENTORY BALANCE 8TH] and [INVENTORY R&D] to make sure they have data in the
[END BALANCE] columns...

SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME],[BEGINNING
INVENTORY BALANCE 8TH].[END BALANCE] AS [TERM 7],[INVENTORY R&D].[END
BALANCE] AS [TERM 8],
(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].[PART #]

--
Daryl S


lmiller said:
thanks for the tip, but I lost all my names doing that, here is the query I
have developed:
SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME],[BEGINNING
INVENTORY BALANCE 8TH].[END BALANCE] AS [TERM 7],[INVENTORY R&D].[END
BALANCE] AS [TERM 8],
([BEGINNING INVENTORY BALANCE 8TH].[END BALANCE]) + ([INVENTORY R&D].[END
BALANCE]) 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].[PART #]

here is the data:

PART # PART NAME TERM 7 TERM 8 SUM OF BALANCES
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


Part# and names pull just fine but now there are no balances from either
query it is pulling from.


Daryl S said:
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 said:
here is the query i am trying to developt to capture the totals, but there
is a syntax in the joined operation

SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME], [BEGINNING
INVENTORY BALANCE 8TH].[END BALANCE] AS [TERM 7],[INVENTORY R&D].[END
BALANCE] AS [TERM 8],
nz([BEGINNING INVENTORY BALANCE 8TH].[END BALANCE],0) + nz([INVENTORY
R&D].[END BALANCE],0) AS [SUM OF BALANCES]
FROM ([COMPINED 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].[PART #])

:

John,

I have developed a union query to retrieve the data and a totals query, but
the totals query only takes into account the totals where the part numbers
match exactly, no matter if i left join, right join, or inner I would like
to combine all part # that have a balance no matter if they are in 1 table or
both.

:

My union query that I made does append the part #
together properly now I know why the total won't come out. A union query
cannot add totals.

true... but you can create a UNION query to retrieve the records, and then
base a Totals query *on that UNION query* to do the totals.
Click to expand...
Click to expand...
Click to expand...
 
L

lmiller

They do have data in the fields when ran separatly, that is how we know wha
we have in our inventory. but when I try to combine than everything comes up
0

Daryl S said:
Lmiller -

You need the nz(fieldname,0) when adding the two fields together for the
balance, because if one of the two fields is null, the answer will be null.
The nz construct changes nulls to zeros, so they can be added. Since I don't
see anything in the [TERM 7] or [TERM 8] columns, I would check the source
queries and make sure they have data in them (that is, check [BEGINNING
INVENTORY BALANCE 8TH] and [INVENTORY R&D] to make sure they have data in the
[END BALANCE] columns...

SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME],[BEGINNING
INVENTORY BALANCE 8TH].[END BALANCE] AS [TERM 7],[INVENTORY R&D].[END
BALANCE] AS [TERM 8],
(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].[PART #]

--
Daryl S


lmiller said:
thanks for the tip, but I lost all my names doing that, here is the query I
have developed:
SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME],[BEGINNING
INVENTORY BALANCE 8TH].[END BALANCE] AS [TERM 7],[INVENTORY R&D].[END
BALANCE] AS [TERM 8],
([BEGINNING INVENTORY BALANCE 8TH].[END BALANCE]) + ([INVENTORY R&D].[END
BALANCE]) 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].[PART #]

here is the data:

PART # PART NAME TERM 7 TERM 8 SUM OF BALANCES
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


Part# and names pull just fine but now there are no balances from either
query it is pulling from.


Daryl S said:
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


:

here is the query i am trying to developt to capture the totals, but there
is a syntax in the joined operation

SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME], [BEGINNING
INVENTORY BALANCE 8TH].[END BALANCE] AS [TERM 7],[INVENTORY R&D].[END
BALANCE] AS [TERM 8],
nz([BEGINNING INVENTORY BALANCE 8TH].[END BALANCE],0) + nz([INVENTORY
R&D].[END BALANCE],0) AS [SUM OF BALANCES]
FROM ([COMPINED 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].[PART #])

:

John,

I have developed a union query to retrieve the data and a totals query, but
the totals query only takes into account the totals where the part numbers
match exactly, no matter if i left join, right join, or inner I would like
to combine all part # that have a balance no matter if they are in 1 table or
both.

:

My union query that I made does append the part #
together properly now I know why the total won't come out. A union query
cannot add totals.

true... but you can create a UNION query to retrieve the records, and then
base a Totals query *on that UNION query* to do the totals.
Click to expand...
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

union query 19
combining queries into 1 8
3 queries into 1 8
COMBINING QUERIES INTO 1 3
AT WITS END ON QUERY TOTAL 34
combine like items in query 1
How to create a "point in time" calculation that is date stamped 1
Query Running Balance 6

Top