AT WITS END ON QUERY TOTAL

lmiller · Feb 15, 2010

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

Daryl S · Feb 15, 2010

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

lmiller · Feb 15, 2010

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

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

Click to expand...

Daryl S · Feb 15, 2010

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

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

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

Click to expand...

Click to expand...

lmiller · Feb 15, 2010

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

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

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

:

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

Click to expand...

Click to expand...

Daryl S · Feb 15, 2010

Lmiller -

Are you building this final query in design mode? That should make it easy.
Add the three source queries. You will need to adjust the joins by
double-clicking on them, and selecting the "All from COMBINED" option for the
joins between the COMBINED query and the other two sources. Delete any join
between the other two queries.

Then, add the fields to the query grid by double-clicking on the [PART #]
and [PART NAME] fields in the COMBINED query. Then add the [END BALANCE 7]
and [END BALANCE 8] fields in the same manner. Finally, in the last column,
type in:

SUM OF BALANCES: =nz([END BALANCE 7],0) + nz([END BALANCE 8],0)

--
Daryl S

lmiller said:
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 said:

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

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

:

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

:

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

Click to expand...

Click to expand...

KARL DEWEY · Feb 15, 2010

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

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

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

:

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

:

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

Click to expand...

Click to expand...

lmiller · Feb 15, 2010

Tried what you suggested, this is what it comes up as now in SQL

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 ([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 #];

Now the query only list the part #'s that were in my 8th term with the 7th
term balance

PART # PART NAME END BALANCE 7 END BALANCE 8 SUM OF BALANCES
1018 "OD 12.7MM X 24"" LONG" 27 27
1234 test 0

what happened to all my other part # and their balances?

Daryl S said:
Lmiller -

Are you building this final query in design mode? That should make it easy.
Add the three source queries. You will need to adjust the joins by
double-clicking on them, and selecting the "All from COMBINED" option for the
joins between the COMBINED query and the other two sources. Delete any join
between the other two queries.

Then, add the fields to the query grid by double-clicking on the [PART #]
and [PART NAME] fields in the COMBINED query. Then add the [END BALANCE 7]
and [END BALANCE 8] fields in the same manner. Finally, in the last column,
type in:

SUM OF BALANCES: =nz([END BALANCE 7],0) + nz([END BALANCE 8],0)

--
Daryl S

lmiller said:

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

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

:

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

:

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

:

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

Click to expand...

Click to expand...

lmiller · Feb 15, 2010

Karl,

The first query "qryINVENTORY_RD7_8" resulted in nothing coming back: when
I ran it had no data

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 7TH].[END BALANCE 7])>0)); UNION ALL
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));

I went ahead and tried the 2nd query but kept getting a syntax error in the
from statement.....

KARL DEWEY said:
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 said:

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

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

:

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

:

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

:

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

Click to expand...

Click to expand...

Daryl S · Feb 15, 2010

LMiller -

Progress! I think I see values for END BALANCE 7 (the 27 and the null),
and the sums, but nothing for the END BALANCE 8. I would check the source
query to see if you really have something in [END BALANCE 8]. Maybe there is
a typo? The query is pulling the correct records, and you are getting one
from each of the source records, but the [END BALANCE 8] is not coming
through. Can you check your [INVENTORY BALANCE 8TH] query to make sure you
are getting data in the [END BALANCE 8] column?

--
Daryl S

lmiller said:
Tried what you suggested, this is what it comes up as now in SQL

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 ([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 #];

Now the query only list the part #'s that were in my 8th term with the 7th
term balance

PART # PART NAME END BALANCE 7 END BALANCE 8 SUM OF BALANCES
1018 "OD 12.7MM X 24"" LONG" 27 27
1234 test 0

what happened to all my other part # and their balances?

Daryl S said:

Lmiller -

Are you building this final query in design mode? That should make it easy.
Add the three source queries. You will need to adjust the joins by
double-clicking on them, and selecting the "All from COMBINED" option for the
joins between the COMBINED query and the other two sources. Delete any join
between the other two queries.

Then, add the fields to the query grid by double-clicking on the [PART #]
and [PART NAME] fields in the COMBINED query. Then add the [END BALANCE 7]
and [END BALANCE 8] fields in the same manner. Finally, in the last column,
type in:

SUM OF BALANCES: =nz([END BALANCE 7],0) + nz([END BALANCE 8],0)

--
Daryl S

lmiller said:

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

:

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

:

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

:

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

:

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

Click to expand...

Click to expand...

lmiller · Feb 15, 2010

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

Yes Daryl, notice above, this is the result from running my 8th term. I do
have balances. You are coming up with the same conclusion I have. No matter
what I have tried my 8th term isn't pulling.

Daryl S said:
LMiller -

Progress! I think I see values for END BALANCE 7 (the 27 and the null),
and the sums, but nothing for the END BALANCE 8. I would check the source
query to see if you really have something in [END BALANCE 8]. Maybe there is
a typo? The query is pulling the correct records, and you are getting one
from each of the source records, but the [END BALANCE 8] is not coming
through. Can you check your [INVENTORY BALANCE 8TH] query to make sure you
are getting data in the [END BALANCE 8] column?

--
Daryl S

lmiller said:

Tried what you suggested, this is what it comes up as now in SQL

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 ([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 #];

Now the query only list the part #'s that were in my 8th term with the 7th
term balance

PART # PART NAME END BALANCE 7 END BALANCE 8 SUM OF BALANCES
1018 "OD 12.7MM X 24"" LONG" 27 27
1234 test 0

what happened to all my other part # and their balances?

Daryl S said:

Lmiller -

Are you building this final query in design mode? That should make it easy.
Add the three source queries. You will need to adjust the joins by
double-clicking on them, and selecting the "All from COMBINED" option for the
joins between the COMBINED query and the other two sources. Delete any join
between the other two queries.

Then, add the fields to the query grid by double-clicking on the [PART #]
and [PART NAME] fields in the COMBINED query. Then add the [END BALANCE 7]
and [END BALANCE 8] fields in the same manner. Finally, in the last column,
type in:

SUM OF BALANCES: =nz([END BALANCE 7],0) + nz([END BALANCE 8],0)

--
Daryl S

:

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

:

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

:

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

:

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

:

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

Click to expand...

Click to expand...

Daryl S · Feb 15, 2010

LMiller -

OK, let's try something else. In your source query (and in the final
query), change the [END BALANCE 8] to something like [END_BALANCE_8]. Run
each and see what you get.

--
Daryl S

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

Yes Daryl, notice above, this is the result from running my 8th term. I do
have balances. You are coming up with the same conclusion I have. No matter
what I have tried my 8th term isn't pulling.

Daryl S said:

LMiller -

Progress! I think I see values for END BALANCE 7 (the 27 and the null),
and the sums, but nothing for the END BALANCE 8. I would check the source
query to see if you really have something in [END BALANCE 8]. Maybe there is
a typo? The query is pulling the correct records, and you are getting one
from each of the source records, but the [END BALANCE 8] is not coming
through. Can you check your [INVENTORY BALANCE 8TH] query to make sure you
are getting data in the [END BALANCE 8] column?

--
Daryl S

lmiller said:

Tried what you suggested, this is what it comes up as now in SQL

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 ([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 #];

Now the query only list the part #'s that were in my 8th term with the 7th
term balance

PART # PART NAME END BALANCE 7 END BALANCE 8 SUM OF BALANCES
1018 "OD 12.7MM X 24"" LONG" 27 27
1234 test 0

what happened to all my other part # and their balances?

:

Lmiller -

Are you building this final query in design mode? That should make it easy.
Add the three source queries. You will need to adjust the joins by
double-clicking on them, and selecting the "All from COMBINED" option for the
joins between the COMBINED query and the other two sources. Delete any join
between the other two queries.

Then, add the fields to the query grid by double-clicking on the [PART #]
and [PART NAME] fields in the COMBINED query. Then add the [END BALANCE 7]
and [END BALANCE 8] fields in the same manner. Finally, in the last column,
type in:

SUM OF BALANCES: =nz([END BALANCE 7],0) + nz([END BALANCE 8],0)

--
Daryl S

:

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

:

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

:

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

:

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

:

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

Click to expand...

Click to expand...

lmiller · Feb 15, 2010

Same results. it's just not combining the balances or giving me any of the
balances. I know there probably is an easy solution to this, seems like it's
just a matter of comparing part #s if they are the same add the two balances
together if not just list.....

Daryl S said:
LMiller -

OK, let's try something else. In your source query (and in the final
query), change the [END BALANCE 8] to something like [END_BALANCE_8]. Run
each and see what you get.

--
Daryl S

lmiller said:

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

Yes Daryl, notice above, this is the result from running my 8th term. I do
have balances. You are coming up with the same conclusion I have. No matter
what I have tried my 8th term isn't pulling.

Daryl S said:

LMiller -

Progress! I think I see values for END BALANCE 7 (the 27 and the null),
and the sums, but nothing for the END BALANCE 8. I would check the source
query to see if you really have something in [END BALANCE 8]. Maybe there is
a typo? The query is pulling the correct records, and you are getting one
from each of the source records, but the [END BALANCE 8] is not coming
through. Can you check your [INVENTORY BALANCE 8TH] query to make sure you
are getting data in the [END BALANCE 8] column?

--
Daryl S

:

Tried what you suggested, this is what it comes up as now in SQL

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 ([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 #];

Now the query only list the part #'s that were in my 8th term with the 7th
term balance

PART # PART NAME END BALANCE 7 END BALANCE 8 SUM OF BALANCES
1018 "OD 12.7MM X 24"" LONG" 27 27
1234 test 0

what happened to all my other part # and their balances?

:

Lmiller -

Are you building this final query in design mode? That should make it easy.
Add the three source queries. You will need to adjust the joins by
double-clicking on them, and selecting the "All from COMBINED" option for the
joins between the COMBINED query and the other two sources. Delete any join
between the other two queries.

Then, add the fields to the query grid by double-clicking on the [PART #]
and [PART NAME] fields in the COMBINED query. Then add the [END BALANCE 7]
and [END BALANCE 8] fields in the same manner. Finally, in the last column,
type in:

SUM OF BALANCES: =nz([END BALANCE 7],0) + nz([END BALANCE 8],0)

--
Daryl S

:

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

:

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

:

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

:

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

:

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

Click to expand...

Click to expand...

lmiller · Feb 15, 2010

Daryl,

seems like when I left join, the balances from my term 7 appear and when i
right join the balances from my 8th appear. Is there an expression that can
be used for if the part # = than they are to be added together, If they
don't equal than just list.

Daryl S said:
LMiller -

OK, let's try something else. In your source query (and in the final
query), change the [END BALANCE 8] to something like [END_BALANCE_8]. Run
each and see what you get.

--
Daryl S

lmiller said:

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

Yes Daryl, notice above, this is the result from running my 8th term. I do
have balances. You are coming up with the same conclusion I have. No matter
what I have tried my 8th term isn't pulling.

Daryl S said:

LMiller -

Progress! I think I see values for END BALANCE 7 (the 27 and the null),
and the sums, but nothing for the END BALANCE 8. I would check the source
query to see if you really have something in [END BALANCE 8]. Maybe there is
a typo? The query is pulling the correct records, and you are getting one
from each of the source records, but the [END BALANCE 8] is not coming
through. Can you check your [INVENTORY BALANCE 8TH] query to make sure you
are getting data in the [END BALANCE 8] column?

--
Daryl S

:

Tried what you suggested, this is what it comes up as now in SQL

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 ([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 #];

Now the query only list the part #'s that were in my 8th term with the 7th
term balance

PART # PART NAME END BALANCE 7 END BALANCE 8 SUM OF BALANCES
1018 "OD 12.7MM X 24"" LONG" 27 27
1234 test 0

what happened to all my other part # and their balances?

:

Lmiller -

Are you building this final query in design mode? That should make it easy.
Add the three source queries. You will need to adjust the joins by
double-clicking on them, and selecting the "All from COMBINED" option for the
joins between the COMBINED query and the other two sources. Delete any join
between the other two queries.

Then, add the fields to the query grid by double-clicking on the [PART #]
and [PART NAME] fields in the COMBINED query. Then add the [END BALANCE 7]
and [END BALANCE 8] fields in the same manner. Finally, in the last column,
type in:

SUM OF BALANCES: =nz([END BALANCE 7],0) + nz([END BALANCE 8],0)

--
Daryl S

:

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

:

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

:

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

:

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

:

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

Click to expand...

Click to expand...

Daryl S · Feb 15, 2010

Lmiller -

This should be easy - it works fine on my machine. Anyway, here is another
thing to try. Join only the [COMBINED PARTS] and the [BEGINNING INVENTORY
BALANCE 8TH] sources like this:

You should have the END BALANCE 7 column filled where there is data.

SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME], [BEGINNING
INVENTORY BALANCE 8TH].[END BALANCE 7],
FROM ([COMBINED PARTS] LEFT JOIN [BEGINNING INVENTORY BALANCE 8TH] ON
[COMBINED PARTS].[PART #] = [BEGINNING INVENTORY BALANCE 8TH].[PART #]);

Now join that query with the [INVENTORY RD 8TH] like this (change the [FIRST
HALF] to the name you gave the query above:

SELECT [FIRST HALF].[PART #], [FIRST HALF].[PART NAME], [FIRST HALFH].[END
BALANCE 7], [INVENTORY RD 8TH].[END BALANCE 8],
nz([END BALANCE 7],0)+nz([END BALANCE 8],0) AS [SUM OF BALANCES]
FROM [FIRST HALF] LEFT JOIN [INVENTORY RD 8TH] ON [FIRST HALF].[PART #] =
[INVENTORY RD 8TH].[PART #];

--
Daryl S

lmiller said:
Same results. it's just not combining the balances or giving me any of the
balances. I know there probably is an easy solution to this, seems like it's
just a matter of comparing part #s if they are the same add the two balances
together if not just list.....

Daryl S said:

LMiller -

OK, let's try something else. In your source query (and in the final
query), change the [END BALANCE 8] to something like [END_BALANCE_8]. Run
each and see what you get.

--
Daryl S

lmiller said:

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

Yes Daryl, notice above, this is the result from running my 8th term. I do
have balances. You are coming up with the same conclusion I have. No matter
what I have tried my 8th term isn't pulling.

:

LMiller -

Progress! I think I see values for END BALANCE 7 (the 27 and the null),
and the sums, but nothing for the END BALANCE 8. I would check the source
query to see if you really have something in [END BALANCE 8]. Maybe there is
a typo? The query is pulling the correct records, and you are getting one
from each of the source records, but the [END BALANCE 8] is not coming
through. Can you check your [INVENTORY BALANCE 8TH] query to make sure you
are getting data in the [END BALANCE 8] column?

--
Daryl S

:

Tried what you suggested, this is what it comes up as now in SQL

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 ([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 #];

Now the query only list the part #'s that were in my 8th term with the 7th
term balance

PART # PART NAME END BALANCE 7 END BALANCE 8 SUM OF BALANCES
1018 "OD 12.7MM X 24"" LONG" 27 27
1234 test 0

what happened to all my other part # and their balances?

:

Lmiller -

Are you building this final query in design mode? That should make it easy.
Add the three source queries. You will need to adjust the joins by
double-clicking on them, and selecting the "All from COMBINED" option for the
joins between the COMBINED query and the other two sources. Delete any join
between the other two queries.

Then, add the fields to the query grid by double-clicking on the [PART #]
and [PART NAME] fields in the COMBINED query. Then add the [END BALANCE 7]
and [END BALANCE 8] fields in the same manner. Finally, in the last column,
type in:

SUM OF BALANCES: =nz([END BALANCE 7],0) + nz([END BALANCE 8],0)

--
Daryl S

:

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

:

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

:

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

:

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

:

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

Click to expand...

Click to expand...

lmiller · Feb 15, 2010

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

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

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

:

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

:

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

:

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

Click to expand...

Click to expand...

Daryl S · Feb 15, 2010

Lmiller -

If the COMBINED is the first source listed, then they should both be LEFT
joins (e.g. ALL records from COMBINED, and any from 7 or 8 that match).

Did you try the two separate left joins?
--
Daryl S

lmiller said:
Daryl,

seems like when I left join, the balances from my term 7 appear and when i
right join the balances from my 8th appear. Is there an expression that can
be used for if the part # = than they are to be added together, If they
don't equal than just list.

Daryl S said:

LMiller -

OK, let's try something else. In your source query (and in the final
query), change the [END BALANCE 8] to something like [END_BALANCE_8]. Run
each and see what you get.

--
Daryl S

lmiller said:

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

Yes Daryl, notice above, this is the result from running my 8th term. I do
have balances. You are coming up with the same conclusion I have. No matter
what I have tried my 8th term isn't pulling.

:

LMiller -

Progress! I think I see values for END BALANCE 7 (the 27 and the null),
and the sums, but nothing for the END BALANCE 8. I would check the source
query to see if you really have something in [END BALANCE 8]. Maybe there is
a typo? The query is pulling the correct records, and you are getting one
from each of the source records, but the [END BALANCE 8] is not coming
through. Can you check your [INVENTORY BALANCE 8TH] query to make sure you
are getting data in the [END BALANCE 8] column?

--
Daryl S

:

Tried what you suggested, this is what it comes up as now in SQL

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 ([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 #];

Now the query only list the part #'s that were in my 8th term with the 7th
term balance

PART # PART NAME END BALANCE 7 END BALANCE 8 SUM OF BALANCES
1018 "OD 12.7MM X 24"" LONG" 27 27
1234 test 0

what happened to all my other part # and their balances?

:

Lmiller -

Are you building this final query in design mode? That should make it easy.
Add the three source queries. You will need to adjust the joins by
double-clicking on them, and selecting the "All from COMBINED" option for the
joins between the COMBINED query and the other two sources. Delete any join
between the other two queries.

Then, add the fields to the query grid by double-clicking on the [PART #]
and [PART NAME] fields in the COMBINED query. Then add the [END BALANCE 7]
and [END BALANCE 8] fields in the same manner. Finally, in the last column,
type in:

SUM OF BALANCES: =nz([END BALANCE 7],0) + nz([END BALANCE 8],0)

--
Daryl S

:

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

:

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

:

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

:

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

:

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

Click to expand...

Click to expand...

lmiller · Feb 15, 2010

SELECT [COMBINED PARTS].[PART #],[COMBINED PARTS].[PART NAME],[BEGINNING
INVENTORY BALANCE 8TH].[END BALANCE 7] FROM ([COMBINED PARTS] LEFT JOIN
[BEGINNING INVENTORY BALANCE 8TH] ON [COMBINED PARTS].[PART #] = [BEGINNING
INVENTORY BALANCE 8TH].[PART#]);

The query "first half" that you suggest above won't work, giving me an error
that the [beginning inventory balance 8th].[part #] can refer to more than
one table in my from statement.

Daryl S said:
Lmiller -

This should be easy - it works fine on my machine. Anyway, here is another
thing to try. Join only the [COMBINED PARTS] and the [BEGINNING INVENTORY
BALANCE 8TH] sources like this:

You should have the END BALANCE 7 column filled where there is data.

SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME], [BEGINNING
INVENTORY BALANCE 8TH].[END BALANCE 7],
FROM ([COMBINED PARTS] LEFT JOIN [BEGINNING INVENTORY BALANCE 8TH] ON
[COMBINED PARTS].[PART #] = [BEGINNING INVENTORY BALANCE 8TH].[PART #]);

Now join that query with the [INVENTORY RD 8TH] like this (change the [FIRST
HALF] to the name you gave the query above:

SELECT [FIRST HALF].[PART #], [FIRST HALF].[PART NAME], [FIRST HALFH].[END
BALANCE 7], [INVENTORY RD 8TH].[END BALANCE 8],
nz([END BALANCE 7],0)+nz([END BALANCE 8],0) AS [SUM OF BALANCES]
FROM [FIRST HALF] LEFT JOIN [INVENTORY RD 8TH] ON [FIRST HALF].[PART #] =
[INVENTORY RD 8TH].[PART #];

--
Daryl S

lmiller said:

Same results. it's just not combining the balances or giving me any of the
balances. I know there probably is an easy solution to this, seems like it's
just a matter of comparing part #s if they are the same add the two balances
together if not just list.....

Daryl S said:

LMiller -

OK, let's try something else. In your source query (and in the final
query), change the [END BALANCE 8] to something like [END_BALANCE_8]. Run
each and see what you get.

--
Daryl S

:

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

Yes Daryl, notice above, this is the result from running my 8th term. I do
have balances. You are coming up with the same conclusion I have. No matter
what I have tried my 8th term isn't pulling.

:

LMiller -

Progress! I think I see values for END BALANCE 7 (the 27 and the null),
and the sums, but nothing for the END BALANCE 8. I would check the source
query to see if you really have something in [END BALANCE 8]. Maybe there is
a typo? The query is pulling the correct records, and you are getting one
from each of the source records, but the [END BALANCE 8] is not coming
through. Can you check your [INVENTORY BALANCE 8TH] query to make sure you
are getting data in the [END BALANCE 8] column?

--
Daryl S

:

Tried what you suggested, this is what it comes up as now in SQL

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 ([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 #];

Now the query only list the part #'s that were in my 8th term with the 7th
term balance

PART # PART NAME END BALANCE 7 END BALANCE 8 SUM OF BALANCES
1018 "OD 12.7MM X 24"" LONG" 27 27
1234 test 0

what happened to all my other part # and their balances?

:

Lmiller -

Are you building this final query in design mode? That should make it easy.
Add the three source queries. You will need to adjust the joins by
double-clicking on them, and selecting the "All from COMBINED" option for the
joins between the COMBINED query and the other two sources. Delete any join
between the other two queries.

Then, add the fields to the query grid by double-clicking on the [PART #]
and [PART NAME] fields in the COMBINED query. Then add the [END BALANCE 7]
and [END BALANCE 8] fields in the same manner. Finally, in the last column,
type in:

SUM OF BALANCES: =nz([END BALANCE 7],0) + nz([END BALANCE 8],0)

--
Daryl S

:

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

:

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

:

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

:

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

:

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

Click to expand...

Click to expand...

lmiller · Feb 15, 2010

I have been trying to do the separate joins but in the "first half" I keep
getting an error that the [beginning inventory balance 8th].[part #] can
refer to more than one table in my from statement
SELECT [COMBINED PARTS].[PART #],[COMBINED PARTS].[PART NAME],[BEGINNING
INVENTORY BALANCE 8TH].[END BALANCE 7] FROM ([COMBINED PARTS] LEFT JOIN
[BEGINNING INVENTORY BALANCE 8TH] ON [COMBINED PARTS].[PART #] = [BEGINNING
INVENTORY BALANCE 8TH].[PART#])

Daryl S said:
Lmiller -

If the COMBINED is the first source listed, then they should both be LEFT
joins (e.g. ALL records from COMBINED, and any from 7 or 8 that match).

Did you try the two separate left joins?
--
Daryl S

lmiller said:

Daryl,

seems like when I left join, the balances from my term 7 appear and when i
right join the balances from my 8th appear. Is there an expression that can
be used for if the part # = than they are to be added together, If they
don't equal than just list.

Daryl S said:

LMiller -

OK, let's try something else. In your source query (and in the final
query), change the [END BALANCE 8] to something like [END_BALANCE_8]. Run
each and see what you get.

--
Daryl S

:

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

Yes Daryl, notice above, this is the result from running my 8th term. I do
have balances. You are coming up with the same conclusion I have. No matter
what I have tried my 8th term isn't pulling.

:

LMiller -

Progress! I think I see values for END BALANCE 7 (the 27 and the null),
and the sums, but nothing for the END BALANCE 8. I would check the source
query to see if you really have something in [END BALANCE 8]. Maybe there is
a typo? The query is pulling the correct records, and you are getting one
from each of the source records, but the [END BALANCE 8] is not coming
through. Can you check your [INVENTORY BALANCE 8TH] query to make sure you
are getting data in the [END BALANCE 8] column?

--
Daryl S

:

Tried what you suggested, this is what it comes up as now in SQL

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 ([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 #];

Now the query only list the part #'s that were in my 8th term with the 7th
term balance

PART # PART NAME END BALANCE 7 END BALANCE 8 SUM OF BALANCES
1018 "OD 12.7MM X 24"" LONG" 27 27
1234 test 0

what happened to all my other part # and their balances?

:

Lmiller -

Are you building this final query in design mode? That should make it easy.
Add the three source queries. You will need to adjust the joins by
double-clicking on them, and selecting the "All from COMBINED" option for the
joins between the COMBINED query and the other two sources. Delete any join
between the other two queries.

Then, add the fields to the query grid by double-clicking on the [PART #]
and [PART NAME] fields in the COMBINED query. Then add the [END BALANCE 7]
and [END BALANCE 8] fields in the same manner. Finally, in the last column,
type in:

SUM OF BALANCES: =nz([END BALANCE 7],0) + nz([END BALANCE 8],0)

--
Daryl S

:

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

:

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

:

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

:

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

:

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

Click to expand...

Click to expand...

Daryl S · Feb 15, 2010

Lmiller -

Annoying... The way to get around that issue is to change the name of [PART
#] in the source query to something like [PART Num]...

--
Daryl S

lmiller said:
SELECT [COMBINED PARTS].[PART #],[COMBINED PARTS].[PART NAME],[BEGINNING
INVENTORY BALANCE 8TH].[END BALANCE 7] FROM ([COMBINED PARTS] LEFT JOIN
[BEGINNING INVENTORY BALANCE 8TH] ON [COMBINED PARTS].[PART #] = [BEGINNING
INVENTORY BALANCE 8TH].[PART#]);

The query "first half" that you suggest above won't work, giving me an error
that the [beginning inventory balance 8th].[part #] can refer to more than
one table in my from statement.

Daryl S said:

Lmiller -

This should be easy - it works fine on my machine. Anyway, here is another
thing to try. Join only the [COMBINED PARTS] and the [BEGINNING INVENTORY
BALANCE 8TH] sources like this:

You should have the END BALANCE 7 column filled where there is data.

SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME], [BEGINNING
INVENTORY BALANCE 8TH].[END BALANCE 7],
FROM ([COMBINED PARTS] LEFT JOIN [BEGINNING INVENTORY BALANCE 8TH] ON
[COMBINED PARTS].[PART #] = [BEGINNING INVENTORY BALANCE 8TH].[PART #]);

Now join that query with the [INVENTORY RD 8TH] like this (change the [FIRST
HALF] to the name you gave the query above:

SELECT [FIRST HALF].[PART #], [FIRST HALF].[PART NAME], [FIRST HALFH].[END
BALANCE 7], [INVENTORY RD 8TH].[END BALANCE 8],
nz([END BALANCE 7],0)+nz([END BALANCE 8],0) AS [SUM OF BALANCES]
FROM [FIRST HALF] LEFT JOIN [INVENTORY RD 8TH] ON [FIRST HALF].[PART #] =
[INVENTORY RD 8TH].[PART #];

--
Daryl S

lmiller said:

Same results. it's just not combining the balances or giving me any of the
balances. I know there probably is an easy solution to this, seems like it's
just a matter of comparing part #s if they are the same add the two balances
together if not just list.....

:

LMiller -

OK, let's try something else. In your source query (and in the final
query), change the [END BALANCE 8] to something like [END_BALANCE_8]. Run
each and see what you get.

--
Daryl S

:

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

Yes Daryl, notice above, this is the result from running my 8th term. I do
have balances. You are coming up with the same conclusion I have. No matter
what I have tried my 8th term isn't pulling.

:

LMiller -

Progress! I think I see values for END BALANCE 7 (the 27 and the null),
and the sums, but nothing for the END BALANCE 8. I would check the source
query to see if you really have something in [END BALANCE 8]. Maybe there is
a typo? The query is pulling the correct records, and you are getting one
from each of the source records, but the [END BALANCE 8] is not coming
through. Can you check your [INVENTORY BALANCE 8TH] query to make sure you
are getting data in the [END BALANCE 8] column?

--
Daryl S

:

Tried what you suggested, this is what it comes up as now in SQL

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 ([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 #];

Now the query only list the part #'s that were in my 8th term with the 7th
term balance

PART # PART NAME END BALANCE 7 END BALANCE 8 SUM OF BALANCES
1018 "OD 12.7MM X 24"" LONG" 27 27
1234 test 0

what happened to all my other part # and their balances?

:

Lmiller -

Are you building this final query in design mode? That should make it easy.
Add the three source queries. You will need to adjust the joins by
double-clicking on them, and selecting the "All from COMBINED" option for the
joins between the COMBINED query and the other two sources. Delete any join
between the other two queries.

Then, add the fields to the query grid by double-clicking on the [PART #]
and [PART NAME] fields in the COMBINED query. Then add the [END BALANCE 7]
and [END BALANCE 8] fields in the same manner. Finally, in the last column,
type in:

SUM OF BALANCES: =nz([END BALANCE 7],0) + nz([END BALANCE 8],0)

--
Daryl S

:

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

:

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

:

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

:

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

:

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

Click to expand...

Click to expand...