union query

[lmiller]

I am trying to develop a union query for my 2 inventory balances. the query
comes back with the first 2 fields correctly but when it comes to the
balances it isn't taking into accoutn the balance from my 2nd query.

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)))
UNION
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])>OR=([INVENTORY R&D 7TH].[END
BALANCE])));

When I hit run it comes back with just results from my Inventory r&d 7th.
Any suggestions on how to get the query to unite or add together?

 

[KARL DEWEY]

Now reads -- UNION SELECT [INVENTORY R&D].[PART #],
Change to UNION ALL SELECT [INVENTORY R&D].[PART #],

 

[lmiller]

thank you but I have tried that and result is still the same.

Now reads -- UNION SELECT [INVENTORY R&D].[PART #],
Change to UNION ALL SELECT [INVENTORY R&D].[PART #],
--
Build a little, test a little.

I am trying to develop a union query for my 2 inventory balances. the query
comes back with the first 2 fields correctly but when it comes to the
balances it isn't taking into accoutn the balance from my 2nd query.

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)))
UNION
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])>OR=([INVENTORY R&D 7TH].[END
BALANCE])));

When I hit run it comes back with just results from my Inventory r&d 7th.
Any suggestions on how to get the query to unite or add together?

 

[KARL DEWEY]

Incorrect >OR=
Correct >=
UNION ALL 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])>=([INVENTORY R&D 7TH].[END
BALANCE])));

--
Build a little, test a little.

thank you but I have tried that and result is still the same.

Now reads -- UNION SELECT [INVENTORY R&D].[PART #],
Change to UNION ALL SELECT [INVENTORY R&D].[PART #],
--
Build a little, test a little.

I am trying to develop a union query for my 2 inventory balances. the query
comes back with the first 2 fields correctly but when it comes to the
balances it isn't taking into accoutn the balance from my 2nd query.

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)))
UNION
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])>OR=([INVENTORY R&D 7TH].[END
BALANCE])));

When I hit run it comes back with just results from my Inventory r&d 7th.
Any suggestions on how to get the query to unite or add together?

 

[KARL DEWEY]

Your where is comparing two table but you do not have the second table in the
FROM statement.

UNION ALL SELECT [INVENTORY R&D].[PART #], [INVENTORY R&D].[PART NAME],
[INVENTORY R&D].[END BALANCE]
FROM [INVENTORY R&D] LEFT JOIN [INVENTORY R&D 7TH] ON [INVENTORY R&D] =
[INVENTORY R&D 7TH]
WHERE ((([INVENTORY R&D].[END BALANCE])>=([INVENTORY R&D 7TH].[END
BALANCE])));

--
Build a little, test a little.

thank you but I have tried that and result is still the same.

Now reads -- UNION SELECT [INVENTORY R&D].[PART #],
Change to UNION ALL SELECT [INVENTORY R&D].[PART #],
--
Build a little, test a little.

I am trying to develop a union query for my 2 inventory balances. the query
comes back with the first 2 fields correctly but when it comes to the
balances it isn't taking into accoutn the balance from my 2nd query.

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)))
UNION
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])>OR=([INVENTORY R&D 7TH].[END
BALANCE])));

When I hit run it comes back with just results from my Inventory r&d 7th.
Any suggestions on how to get the query to unite or add together?

 

[John Spencer]

You cannot refer to [INVENTORY R&D 7TH] in the second query since you have not
included any reference to the table in the FROM clause of the second query.

Also, the following is incorrectly formatted
WHERE ((([INVENTORY R&D].[END BALANCE])> OR =([INVENTORY R&D 7TH].[ENDBALANCE])));

It should look more like
WHERE [INVENTORY R&D].[END BALANCE]>=[INVENTORY R&D 7TH].[END BALANCE]

Your query might need to look like the following.

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
UNION
SELECT [INVENTORY R&D].[PART #]
, [INVENTORY R&D].[PART NAME]
, [INVENTORY R&D].[END BALANCE]
FROM [INVENTORY R&D] INNER JOIN [INVENTORY R&D 7TH]
ON [INVENTORY R&D].[Part #] = [INVENTORY R&D 7TH].[Part #]
WHERE [INVENTORY R&D].[END BALANCE])>=[INVENTORY R&D 7TH].[END BALANCE]


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[lmiller]

Thanks again, I tried that but telling me the join statement is not supported

Your where is comparing two table but you do not have the second table in the
FROM statement.

UNION ALL SELECT [INVENTORY R&D].[PART #], [INVENTORY R&D].[PART NAME],
[INVENTORY R&D].[END BALANCE]
FROM [INVENTORY R&D] LEFT JOIN [INVENTORY R&D 7TH] ON [INVENTORY R&D] =
[INVENTORY R&D 7TH]
WHERE ((([INVENTORY R&D].[END BALANCE])>=([INVENTORY R&D 7TH].[END
BALANCE])));

--
Build a little, test a little.

thank you but I have tried that and result is still the same.

Now reads -- UNION SELECT [INVENTORY R&D].[PART #],
Change to UNION ALL SELECT [INVENTORY R&D].[PART #],
--
Build a little, test a little.


:

I am trying to develop a union query for my 2 inventory balances. the query
comes back with the first 2 fields correctly but when it comes to the
balances it isn't taking into accoutn the balance from my 2nd query.

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)))
UNION
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])>OR=([INVENTORY R&D 7TH].[END
BALANCE])));

When I hit run it comes back with just results from my Inventory r&d 7th.
Any suggestions on how to get the query to unite or add together?

 

[lmiller]

Thanks John,
I did try that but didn't work. I realize the one error I had > or= wasn't
correct. However still not getting the results I am looking for.

You cannot refer to [INVENTORY R&D 7TH] in the second query since you have not
included any reference to the table in the FROM clause of the second query.

Also, the following is incorrectly formatted
WHERE ((([INVENTORY R&D].[END BALANCE])> OR =([INVENTORY R&D 7TH].[ENDBALANCE])));

It should look more like
WHERE [INVENTORY R&D].[END BALANCE]>=[INVENTORY R&D 7TH].[END BALANCE]

Your query might need to look like the following.

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
UNION
SELECT [INVENTORY R&D].[PART #]
, [INVENTORY R&D].[PART NAME]
, [INVENTORY R&D].[END BALANCE]
FROM [INVENTORY R&D] INNER JOIN [INVENTORY R&D 7TH]
ON [INVENTORY R&D].[Part #] = [INVENTORY R&D 7TH].[Part #]
WHERE [INVENTORY R&D].[END BALANCE])>=[INVENTORY R&D 7TH].[END BALANCE]


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

I am trying to develop a union query for my 2 inventory balances. the query
comes back with the first 2 fields correctly but when it comes to the
balances it isn't taking into accoutn the balance from my 2nd query.

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)))
UNION
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])>OR=([INVENTORY R&D 7TH].[END
BALANCE])));

When I hit run it comes back with just results from my Inventory r&d 7th.
Any suggestions on how to get the query to unite or add together?

.

 

[KARL DEWEY]

Left off .[PART #] --
UNION ALL SELECT [INVENTORY R&D].[PART #], [INVENTORY R&D].[PART NAME],
[INVENTORY R&D].[END BALANCE]
FROM [INVENTORY R&D] LEFT JOIN [INVENTORY R&D 7TH] ON [INVENTORY R&D].[PART
#] = [INVENTORY R&D 7TH].[PART #]
WHERE ((([INVENTORY R&D].[END BALANCE])>=([INVENTORY R&D 7TH].[END
BALANCE])));

--
Build a little, test a little.

Thanks again, I tried that but telling me the join statement is not supported

Your where is comparing two table but you do not have the second table in the
FROM statement.

UNION ALL SELECT [INVENTORY R&D].[PART #], [INVENTORY R&D].[PART NAME],
[INVENTORY R&D].[END BALANCE]
FROM [INVENTORY R&D] LEFT JOIN [INVENTORY R&D 7TH] ON [INVENTORY R&D] =
[INVENTORY R&D 7TH]
WHERE ((([INVENTORY R&D].[END BALANCE])>=([INVENTORY R&D 7TH].[END
BALANCE])));

--
Build a little, test a little.

thank you but I have tried that and result is still the same.

:

Now reads -- UNION SELECT [INVENTORY R&D].[PART #],
Change to UNION ALL SELECT [INVENTORY R&D].[PART #],
--
Build a little, test a little.


:

I am trying to develop a union query for my 2 inventory balances. the query
comes back with the first 2 fields correctly but when it comes to the
balances it isn't taking into accoutn the balance from my 2nd query.

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)))
UNION
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])>OR=([INVENTORY R&D 7TH].[END
BALANCE])));

When I hit run it comes back with just results from my Inventory r&d 7th.
Any suggestions on how to get the query to unite or add together?

 

[lmiller]

thank you but nothing has worked thus far must be something access can't do

Thanks again, I tried that but telling me the join statement is not supported

Your where is comparing two table but you do not have the second table in the
FROM statement.

UNION ALL SELECT [INVENTORY R&D].[PART #], [INVENTORY R&D].[PART NAME],
[INVENTORY R&D].[END BALANCE]
FROM [INVENTORY R&D] LEFT JOIN [INVENTORY R&D 7TH] ON [INVENTORY R&D] =
[INVENTORY R&D 7TH]
WHERE ((([INVENTORY R&D].[END BALANCE])>=([INVENTORY R&D 7TH].[END
BALANCE])));

--
Build a little, test a little.

thank you but I have tried that and result is still the same.

:

Now reads -- UNION SELECT [INVENTORY R&D].[PART #],
Change to UNION ALL SELECT [INVENTORY R&D].[PART #],
--
Build a little, test a little.


:

I am trying to develop a union query for my 2 inventory balances. the query
comes back with the first 2 fields correctly but when it comes to the
balances it isn't taking into accoutn the balance from my 2nd query.

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)))
UNION
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])>OR=([INVENTORY R&D 7TH].[END
BALANCE])));

When I hit run it comes back with just results from my Inventory r&d 7th.
Any suggestions on how to get the query to unite or add together?

 

[lmiller]

thanks for your help but nothing has worked must be something access can't do

You cannot refer to [INVENTORY R&D 7TH] in the second query since you have not
included any reference to the table in the FROM clause of the second query.

Also, the following is incorrectly formatted
WHERE ((([INVENTORY R&D].[END BALANCE])> OR =([INVENTORY R&D 7TH].[ENDBALANCE])));

It should look more like
WHERE [INVENTORY R&D].[END BALANCE]>=[INVENTORY R&D 7TH].[END BALANCE]

Your query might need to look like the following.

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
UNION
SELECT [INVENTORY R&D].[PART #]
, [INVENTORY R&D].[PART NAME]
, [INVENTORY R&D].[END BALANCE]
FROM [INVENTORY R&D] INNER JOIN [INVENTORY R&D 7TH]
ON [INVENTORY R&D].[Part #] = [INVENTORY R&D 7TH].[Part #]
WHERE [INVENTORY R&D].[END BALANCE])>=[INVENTORY R&D 7TH].[END BALANCE]


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

I am trying to develop a union query for my 2 inventory balances. the query
comes back with the first 2 fields correctly but when it comes to the
balances it isn't taking into accoutn the balance from my 2nd query.

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)))
UNION
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])>OR=([INVENTORY R&D 7TH].[END
BALANCE])));

When I hit run it comes back with just results from my Inventory r&d 7th.
Any suggestions on how to get the query to unite or add together?

.

 

[KARL DEWEY]

thank you but nothing has worked thus far
Post some number examples of what it is doing and what you expect it to do.
Are you expecting it to combine the 2 'end balance' into a single number?

--
Build a little, test a little.

thank you but nothing has worked thus far must be something access can't do

Thanks again, I tried that but telling me the join statement is not supported

Your where is comparing two table but you do not have the second table in the
FROM statement.

UNION ALL SELECT [INVENTORY R&D].[PART #], [INVENTORY R&D].[PART NAME],
[INVENTORY R&D].[END BALANCE]
FROM [INVENTORY R&D] LEFT JOIN [INVENTORY R&D 7TH] ON [INVENTORY R&D] =
[INVENTORY R&D 7TH]
WHERE ((([INVENTORY R&D].[END BALANCE])>=([INVENTORY R&D 7TH].[END
BALANCE])));

--
Build a little, test a little.


:

thank you but I have tried that and result is still the same.

:

Now reads -- UNION SELECT [INVENTORY R&D].[PART #],
Change to UNION ALL SELECT [INVENTORY R&D].[PART #],
--
Build a little, test a little.


:

I am trying to develop a union query for my 2 inventory balances. the query
comes back with the first 2 fields correctly but when it comes to the
balances it isn't taking into accoutn the balance from my 2nd query.

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)))
UNION
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])>OR=([INVENTORY R&D 7TH].[END
BALANCE])));

When I hit run it comes back with just results from my Inventory r&d 7th.
Any suggestions on how to get the query to unite or add together?

 

[lmiller]

Yes, I realize a union query won't do sums. Therefore I have developed
yet another query trying to combine the 3. But the numbers still aren't
coming out correctly. I realize hard for you to tell when you don't have my
data in front of you.

Post some number examples of what it is doing and what you expect it to do.
Are you expecting it to combine the 2 'end balance' into a single number?

--
Build a little, test a little.

thank you but nothing has worked thus far must be something access can't do

Thanks again, I tried that but telling me the join statement is not supported

:

Your where is comparing two table but you do not have the second table in the
FROM statement.

UNION ALL SELECT [INVENTORY R&D].[PART #], [INVENTORY R&D].[PART NAME],
[INVENTORY R&D].[END BALANCE]
FROM [INVENTORY R&D] LEFT JOIN [INVENTORY R&D 7TH] ON [INVENTORY R&D] =
[INVENTORY R&D 7TH]
WHERE ((([INVENTORY R&D].[END BALANCE])>=([INVENTORY R&D 7TH].[END
BALANCE])));

--
Build a little, test a little.


:

thank you but I have tried that and result is still the same.

:

Now reads -- UNION SELECT [INVENTORY R&D].[PART #],
Change to UNION ALL SELECT [INVENTORY R&D].[PART #],
--
Build a little, test a little.


:

I am trying to develop a union query for my 2 inventory balances. the query
comes back with the first 2 fields correctly but when it comes to the
balances it isn't taking into accoutn the balance from my 2nd query.

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)))
UNION
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])>OR=([INVENTORY R&D 7TH].[END
BALANCE])));

When I hit run it comes back with just results from my Inventory r&d 7th.
Any suggestions on how to get the query to unite or add together?

 

[John W. Vinson]

I realize hard for you to tell when you don't have my
data in front of you.

Exactly... that's why Karl asked you to post some sample data.

You chose not to do so.

 

[John W. Vinson]

thanks for your help but nothing has worked must be something access can't do

Certainly not if you don't write your query correctly.

"A good workman never blames his tools".

John and Karl are trying to help, but you're certainly not providing them with
the information that would enable them to do so! Samples of (phony, if it's
confidential) input data and your desired result would certainly help.
Remember, *we cannot see your screen and we cannot read your mind*.

 

[lmiller]

I didn't see where he asked to post sample data. I would of been happy to do
so!!!

PART # PRODUCT DESCRIPTION END BALANCE
1018 "OD 12.7MM X 24"" LONG" 2
1234 test 1

this is from our 8th term. The query is as follows
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));

this is sample data from our 7th term with query design following:

PART # PRODUCT DESCRIPTION END BALANCE
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

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));

I developed a union query as:

PART # PART NAME
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

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

I was told a union query can not add or combine the total balances. I have
tried to develop a 3rd query to combine the part numbers from both tables
with their balances. When one term ends(7th) of course we have a beginning
inventory for the start of our 8th term. I was trying to develop 1 query to
take into account this beginning balance inventory along with the purchases
we make during this term. Normally i enter each part number and balance into
our purchase order table and when i run that term's query for inventory
everything works great. I was trying to alleviate this process by just
combining queries. The part numbers combine just fine but I haven't figured
out how to combine the total balances.

 

[lmiller]

I apologize, I forgot to add the query on the totals it is as follows:

PART # PART NAME END BALANCE
1018 "OD 12.7MM X 24"" LONG"
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
11589252 GMT319 L4 BOLT 6
1234 test

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


As you can see it is only taking the totals from the "beginning balance
query" and creating a 2nd line for any of the "inventory r&d". I have tried
different joins left, right, inner to no avail. I have even tried
developing a 3rd query for a just the balances.

I didn't see where he asked to post sample data. I would of been happy to do
so!!!

PART # PRODUCT DESCRIPTION END BALANCE
1018 "OD 12.7MM X 24"" LONG" 2
1234 test 1

this is from our 8th term. The query is as follows
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));

this is sample data from our 7th term with query design following:

PART # PRODUCT DESCRIPTION END BALANCE
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

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));

I developed a union query as:

PART # PART NAME
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

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

I was told a union query can not add or combine the total balances. I have
tried to develop a 3rd query to combine the part numbers from both tables
with their balances. When one term ends(7th) of course we have a beginning
inventory for the start of our 8th term. I was trying to develop 1 query to
take into account this beginning balance inventory along with the purchases
we make during this term. Normally i enter each part number and balance into
our purchase order table and when i run that term's query for inventory
everything works great. I was trying to alleviate this process by just
combining queries. The part numbers combine just fine but I haven't figured
out how to combine the total balances.

Exactly... that's why Karl asked you to post some sample data.

You chose not to do so.

 

[KARL DEWEY]

Use this simple totals query on the union query --
SELECT [PART #],[PART NAME], Sum([END BALANCE]) AS Final_Balance
FROM YourUnionQuery
GROUP BY [PART #],[PART NAME];

--
Build a little, test a little.

I apologize, I forgot to add the query on the totals it is as follows:

PART # PART NAME END BALANCE
1018 "OD 12.7MM X 24"" LONG"
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
11589252 GMT319 L4 BOLT 6
1234 test

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


As you can see it is only taking the totals from the "beginning balance
query" and creating a 2nd line for any of the "inventory r&d". I have tried
different joins left, right, inner to no avail. I have even tried
developing a 3rd query for a just the balances.

I didn't see where he asked to post sample data. I would of been happy to do
so!!!

PART # PRODUCT DESCRIPTION END BALANCE
1018 "OD 12.7MM X 24"" LONG" 2
1234 test 1

this is from our 8th term. The query is as follows
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));

this is sample data from our 7th term with query design following:

PART # PRODUCT DESCRIPTION END BALANCE
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

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));

I developed a union query as:

PART # PART NAME
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

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

I was told a union query can not add or combine the total balances. I have
tried to develop a 3rd query to combine the part numbers from both tables
with their balances. When one term ends(7th) of course we have a beginning
inventory for the start of our 8th term. I was trying to develop 1 query to
take into account this beginning balance inventory along with the purchases
we make during this term. Normally i enter each part number and balance into
our purchase order table and when i run that term's query for inventory
everything works great. I was trying to alleviate this process by just
combining queries. The part numbers combine just fine but I haven't figured
out how to combine the total balances.

I realize hard for you to tell when you don't have my
data in front of you.

Exactly... that's why Karl asked you to post some sample data.

You chose not to do so.

 

[lmiller]

I have given them examples, here they are again,

7th Term inventory:

PART # PRODUCT DESCRIPTION END BALANCE
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

query design:

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));

8th term inventory balances
PART # PRODUCT DESCRIPTION Sum Of QTY RECVD Sum Of MEMO QTY SHIP END BALANCE
1018 "OD 12.7MM X 24"" LONG" 2 0 2
1234 test 1 0 1

query design

SELECT DISTINCTROW [PO'S FOR SALES 8TH].[PART #], [PO'S FOR SALES 8TH].[PART
NAME], Sum([PO'S FOR SALES 8TH].[QTY RECVD]) AS [Sum Of QTY RECVD], Sum([PO'S
FOR SALES 8TH].[MEMO QTY SHIP]) AS [Sum Of MEMO QTY SHIP], [Sum Of QTY
RECVD]-[Sum Of MEMO QTY SHIP] AS [END BALANCE]
FROM [PO'S FOR SALES 8TH]
GROUP BY [PO'S FOR SALES 8TH].[PART #], [PO'S FOR SALES 8TH].[PART NAME],
[Sum Of QTY RECVD]-[Sum Of MEMO QTY SHIP]
HAVING ((([PO'S FOR SALES 8TH].[PART NAME])>"0") AND ((Sum([PO'S FOR SALES
8TH].[QTY RECVD]))>0));


combined parts query
PART # PART NAME
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

query design:

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

The query I have developed to combine the inventory balances:

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

design:
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 #]

I realize I have done something wrong that is why it isn't working
correctly. It's only giving me 0 for my balances rather than totaling them.
I think it's in the joined fields. If the part # match, yes I would like the
two balances added together but if there is a part # with a balance in one
term and not the other, I still would like this listed as well. Thank you
again for any help you can give!!!!!!!

 

[lmiller]

Thank you Karl,

but unfortunatly that didn't work either. Still didn't pull the balances.

Use this simple totals query on the union query --
SELECT [PART #],[PART NAME], Sum([END BALANCE]) AS Final_Balance
FROM YourUnionQuery
GROUP BY [PART #],[PART NAME];

--
Build a little, test a little.

I apologize, I forgot to add the query on the totals it is as follows:

PART # PART NAME END BALANCE
1018 "OD 12.7MM X 24"" LONG"
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
11589252 GMT319 L4 BOLT 6
1234 test

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


As you can see it is only taking the totals from the "beginning balance
query" and creating a 2nd line for any of the "inventory r&d". I have tried
different joins left, right, inner to no avail. I have even tried
developing a 3rd query for a just the balances.

I didn't see where he asked to post sample data. I would of been happy to do
so!!!

PART # PRODUCT DESCRIPTION END BALANCE
1018 "OD 12.7MM X 24"" LONG" 2
1234 test 1

this is from our 8th term. The query is as follows
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));

this is sample data from our 7th term with query design following:

PART # PRODUCT DESCRIPTION END BALANCE
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

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));

I developed a union query as:

PART # PART NAME
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

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

I was told a union query can not add or combine the total balances. I have
tried to develop a 3rd query to combine the part numbers from both tables
with their balances. When one term ends(7th) of course we have a beginning
inventory for the start of our 8th term. I was trying to develop 1 query to
take into account this beginning balance inventory along with the purchases
we make during this term. Normally i enter each part number and balance into
our purchase order table and when i run that term's query for inventory
everything works great. I was trying to alleviate this process by just
combining queries. The part numbers combine just fine but I haven't figured
out how to combine the total balances.









:

I realize hard for you to tell when you don't have my
data in front of you.

Exactly... that's why Karl asked you to post some sample data.

You chose not to do so.