A union query combines more than one query into a single SQL statement that
can not be viewed in design view.
All fields from the different parts must match in datatype and number of
fields. That is why I put an [INVENTORY RD 8TH].[END BALANCE 8] in the 7
part and vice a versus.

--
Build a little, test a little.


"lmiller" wrote:

Karl,

Is this suppose to be one long query or 2 separate? the semi-colon was in
the first because I thought it was the end of the query and developed a 2nd
as that is how your example said. when I take it out and rerun it comes
back as it is the end of the first query.

"KARL DEWEY" wrote:

I just noticed you put a semicolon at the end of the firt part of the union
query.

--
Build a little, test a little.


"lmiller" wrote:


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