combining queries into 1

Daryl S,

Here is the query, I believe you suggested:

SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME], [BEGINNING
INVENTORY BALANCE 8TH].[END BALANCE 7], [INVENTORY R&D].[END BALANCE 8],
nz([BEGINNING INVENTORY BALANCE 8TH].[END BALANCE 7],0)+nz([INVENTORY
R&D].[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 R&D] ON [COMBINED PARTS].[PART #]=[INVENTORY R&D].[PART #];

PART # PART NAME END BALANCE 7 END BALANCE 8 SUM OF BALANCES
1018 "OD 12.7MM X 24"" LONG" 0
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

Any other suggestions? I also tried Karl's and with no success
I did change to End Balance 7, and End Balance 8 in the other tables. The
data still is coming back as 0 blances:



"Daryl S" wrote:

Lmiller -

Karl's idea is good, but you don't have the balances in the UNION query, so
it won't work quite right.

I don't see anything wrong with how the SQL is is currently coded (other
than the use of special characters in the field names), but let's try
changing the [END BALANCE] names in the two source queries, and then in the
combined query.

I changed the [END BALANCE] to [END BALANCE 7] in the first query, [END
BALANCE] to [END BALANCE 8] in the second query, and then updated the
Combined query to use them. See if these work.


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


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


SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME],[BEGINNING
INVENTORY BALANCE 8TH].[END BALANCE 7],[INVENTORY R&D].[END
BALANCE 8], (nz([BEGINNING INVENTORY BALANCE 8TH].[END BALANCE 8],0) +
nz([INVENTORY
R&D].[END BALANCE 7],0)) AS [SUM OF BALANCES]
FROM ([COMBINED PARTS] LEFT JOIN [BEGINNING INVENTORY BALANCE 8TH] ON
[COMBINED PARTS].[PART #] & [BEGINNING INVENTORY BALANCE 8TH].[PART #]) LEFT
JOIN [INVENTORY R&D] ON [COMBINED PARTS].[PART #] = [INVENTORY R&D].[PART #]

--
Daryl S


"lmiller" wrote:

Hello, I am going to try this again starting fresh from my other posts. I am
trying to develop an all inclusive query on our inventory. Each fical term
we start with new tables. I would like to take our Ending Inventory from our
prior fiscal term and combine it with our current inventory balances. In
years past I always took our ending inventory and posted them in our purchase
order table to have our beginning balances. I thought it would be less time
consuming to have a query to combine the 2. Below is a list of data and the
query designs.
PART # PRODUCT DESCRIPTION END BALANCE
1018 "OD 12.7MM X 24"" LONG" 27
11509671 BOLT-METRIX HEX FLANGE 93
11515781 GEAR BOX ASSEMBLY STEERING FIXING BOLT 25
11588324 NUT WELD HEX FLANGE (M10X1.5) 172
11588325 NUT WELD HEX FLANGE (M12X1.75) 55
11589252 GMT319 L4 BOLT 6


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


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

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

PART # PART NAME
1018 "OD 12.7MM X 24"" LONG"
11509671 BOLT-METRIX HEX FLANGE
11515781 GEAR BOX ASSEMBLY STEERING FIXING BOLT
11588324 NUT WELD HEX FLANGE (M10X1.5)
11588325 NUT WELD HEX FLANGE (M12X1.75)
11589252 GMT319 L4 BOLT
1234 test

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

PART # PART NAME TERM 7 TERM 8 SUM OF BALANCES
1018 "OD 12.7MM X 24"" LONG" 0
11509671 BOLT-METRIX HEX FLANGE 0
11515781 GEAR BOX ASSEMBLY STEERING FIXING BOLT 0
11588324 NUT WELD HEX FLANGE (M10X1.5) 0
11588325 NUT WELD HEX FLANGE (M12X1.75) 0
11589252 GMT319 L4 BOLT 0
1234 test 0
15226899-03-01 BODY MOUNT FRT LWR LH 0

SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME],[BEGINNING
INVENTORY BALANCE 8TH].[END BALANCE] AS [TERM 7],[INVENTORY R&D].[END
BALANCE] AS [TERM 8],nz
([BEGINNING INVENTORY BALANCE 8TH].[END BALANCE],0) +nz ([INVENTORY
R&D].[END BALANCE],0) AS [SUM OF BALANCES]
FROM ([COMBINED PARTS] LEFT JOIN [BEGINNING INVENTORY BALANCE 8TH] ON
[COMBINED PARTS].[PART #] & [BEGINNING INVENTORY BALANCE 8TH].[PART #]) LEFT
JOIN [INVENTORY R&D] ON [COMBINED PARTS].[PART #] = [INVENTORY R&D].[PART #]

the last table, as you can see isn't pulling the balances of my inventory.
Do I need a where statement or any suggestions?

Lmiller -

If you run the two 'new' queries separately (e.g. [Beginning Inventory
Balance 8th] and [Inventory R&D], do you get the values for [End Balance 8]
and [End Balance 7] that you expect? If so, try changing the query name from
[Inventory R&D] to [Inventory RD] to see if removing the ampersand helps. It
bugs me that in the join query it looks like only the [Sum of Balances] field
is populated. This is what makes me ask if the two 'source' queries are
returning values in the End Balance fields...

--
Daryl S


"lmiller" wrote:

Daryl S,

Here is the query, I believe you suggested:

SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME], [BEGINNING
INVENTORY BALANCE 8TH].[END BALANCE 7], [INVENTORY R&D].[END BALANCE 8],
nz([BEGINNING INVENTORY BALANCE 8TH].[END BALANCE 7],0)+nz([INVENTORY
R&D].[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 R&D] ON [COMBINED PARTS].[PART #]=[INVENTORY R&D].[PART #];

PART # PART NAME END BALANCE 7 END BALANCE 8 SUM OF BALANCES
1018 "OD 12.7MM X 24"" LONG" 0
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

Any other suggestions? I also tried Karl's and with no success
I did change to End Balance 7, and End Balance 8 in the other tables. The
data still is coming back as 0 blances:



"Daryl S" wrote:

Lmiller -

Karl's idea is good, but you don't have the balances in the UNION query, so
it won't work quite right.

I don't see anything wrong with how the SQL is is currently coded (other
than the use of special characters in the field names), but let's try
changing the [END BALANCE] names in the two source queries, and then in the
combined query.

I changed the [END BALANCE] to [END BALANCE 7] in the first query, [END
BALANCE] to [END BALANCE 8] in the second query, and then updated the
Combined query to use them. See if these work.


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


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


SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME],[BEGINNING
INVENTORY BALANCE 8TH].[END BALANCE 7],[INVENTORY R&D].[END
BALANCE 8], (nz([BEGINNING INVENTORY BALANCE 8TH].[END BALANCE 8],0) +
nz([INVENTORY
R&D].[END BALANCE 7],0)) AS [SUM OF BALANCES]
FROM ([COMBINED PARTS] LEFT JOIN [BEGINNING INVENTORY BALANCE 8TH] ON
[COMBINED PARTS].[PART #] & [BEGINNING INVENTORY BALANCE 8TH].[PART #]) LEFT
JOIN [INVENTORY R&D] ON [COMBINED PARTS].[PART #] = [INVENTORY R&D].[PART #]

--
Daryl S


"lmiller" wrote:

Hello, I am going to try this again starting fresh from my other posts. I am
trying to develop an all inclusive query on our inventory. Each fical term
we start with new tables. I would like to take our Ending Inventory from our
prior fiscal term and combine it with our current inventory balances. In
years past I always took our ending inventory and posted them in our purchase
order table to have our beginning balances. I thought it would be less time
consuming to have a query to combine the 2. Below is a list of data and the
query designs.
PART # PRODUCT DESCRIPTION END BALANCE
1018 "OD 12.7MM X 24"" LONG" 27
11509671 BOLT-METRIX HEX FLANGE 93
11515781 GEAR BOX ASSEMBLY STEERING FIXING BOLT 25
11588324 NUT WELD HEX FLANGE (M10X1.5) 172
11588325 NUT WELD HEX FLANGE (M12X1.75) 55
11589252 GMT319 L4 BOLT 6


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


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

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

PART # PART NAME
1018 "OD 12.7MM X 24"" LONG"
11509671 BOLT-METRIX HEX FLANGE
11515781 GEAR BOX ASSEMBLY STEERING FIXING BOLT
11588324 NUT WELD HEX FLANGE (M10X1.5)
11588325 NUT WELD HEX FLANGE (M12X1.75)
11589252 GMT319 L4 BOLT
1234 test

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

PART # PART NAME TERM 7 TERM 8 SUM OF BALANCES
1018 "OD 12.7MM X 24"" LONG" 0
11509671 BOLT-METRIX HEX FLANGE 0
11515781 GEAR BOX ASSEMBLY STEERING FIXING BOLT 0
11588324 NUT WELD HEX FLANGE (M10X1.5) 0
11588325 NUT WELD HEX FLANGE (M12X1.75) 0
11589252 GMT319 L4 BOLT 0
1234 test 0
15226899-03-01 BODY MOUNT FRT LWR LH 0

SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME],[BEGINNING
INVENTORY BALANCE 8TH].[END BALANCE] AS [TERM 7],[INVENTORY R&D].[END
BALANCE] AS [TERM 8],nz
([BEGINNING INVENTORY BALANCE 8TH].[END BALANCE],0) +nz ([INVENTORY
R&D].[END BALANCE],0) AS [SUM OF BALANCES]
FROM ([COMBINED PARTS] LEFT JOIN [BEGINNING INVENTORY BALANCE 8TH] ON
[COMBINED PARTS].[PART #] & [BEGINNING INVENTORY BALANCE 8TH].[PART #]) LEFT
JOIN [INVENTORY R&D] ON [COMBINED PARTS].[PART #] = [INVENTORY R&D].[PART #]

the last table, as you can see isn't pulling the balances of my inventory.
Do I need a where statement or any suggestions?

Daryl,

Yes they work beautifully as stand alones. They were copied below in my
original question but I did just now rename them taking out the "&" in R & D.
I reran the stand alone queries and they work fine but the combined query I
am still working on still result in 0 for my final balances. All the past
queries I have tried It just doesn't seem like it is pulling the values for
my Term 8.

"Daryl S" wrote:

Lmiller -

If you run the two 'new' queries separately (e.g. [Beginning Inventory
Balance 8th] and [Inventory R&D], do you get the values for [End Balance 8]
and [End Balance 7] that you expect? If so, try changing the query name from
[Inventory R&D] to [Inventory RD] to see if removing the ampersand helps. It
bugs me that in the join query it looks like only the [Sum of Balances] field
is populated. This is what makes me ask if the two 'source' queries are
returning values in the End Balance fields...

--
Daryl S


"lmiller" wrote:

Daryl S,

Here is the query, I believe you suggested:

SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME], [BEGINNING
INVENTORY BALANCE 8TH].[END BALANCE 7], [INVENTORY R&D].[END BALANCE 8],
nz([BEGINNING INVENTORY BALANCE 8TH].[END BALANCE 7],0)+nz([INVENTORY
R&D].[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 R&D] ON [COMBINED PARTS].[PART #]=[INVENTORY R&D].[PART #];

PART # PART NAME END BALANCE 7 END BALANCE 8 SUM OF BALANCES
1018 "OD 12.7MM X 24"" LONG" 0
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

Any other suggestions? I also tried Karl's and with no success
I did change to End Balance 7, and End Balance 8 in the other tables. The
data still is coming back as 0 blances:



"Daryl S" wrote:

Lmiller -

Karl's idea is good, but you don't have the balances in the UNION query, so
it won't work quite right.

I don't see anything wrong with how the SQL is is currently coded (other
than the use of special characters in the field names), but let's try
changing the [END BALANCE] names in the two source queries, and then in the
combined query.

I changed the [END BALANCE] to [END BALANCE 7] in the first query, [END
BALANCE] to [END BALANCE 8] in the second query, and then updated the
Combined query to use them. See if these work.


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


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


SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME],[BEGINNING
INVENTORY BALANCE 8TH].[END BALANCE 7],[INVENTORY R&D].[END
BALANCE 8], (nz([BEGINNING INVENTORY BALANCE 8TH].[END BALANCE 8],0) +
nz([INVENTORY
R&D].[END BALANCE 7],0)) AS [SUM OF BALANCES]
FROM ([COMBINED PARTS] LEFT JOIN [BEGINNING INVENTORY BALANCE 8TH] ON
[COMBINED PARTS].[PART #] & [BEGINNING INVENTORY BALANCE 8TH].[PART #]) LEFT
JOIN [INVENTORY R&D] ON [COMBINED PARTS].[PART #] = [INVENTORY R&D].[PART #]

--
Daryl S


"lmiller" wrote:

Hello, I am going to try this again starting fresh from my other posts. I am
trying to develop an all inclusive query on our inventory. Each fical term
we start with new tables. I would like to take our Ending Inventory from our
prior fiscal term and combine it with our current inventory balances. In
years past I always took our ending inventory and posted them in our purchase
order table to have our beginning balances. I thought it would be less time
consuming to have a query to combine the 2. Below is a list of data and the
query designs.
PART # PRODUCT DESCRIPTION END BALANCE
1018 "OD 12.7MM X 24"" LONG" 27
11509671 BOLT-METRIX HEX FLANGE 93
11515781 GEAR BOX ASSEMBLY STEERING FIXING BOLT 25
11588324 NUT WELD HEX FLANGE (M10X1.5) 172
11588325 NUT WELD HEX FLANGE (M12X1.75) 55
11589252 GMT319 L4 BOLT 6


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


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

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

PART # PART NAME
1018 "OD 12.7MM X 24"" LONG"
11509671 BOLT-METRIX HEX FLANGE
11515781 GEAR BOX ASSEMBLY STEERING FIXING BOLT
11588324 NUT WELD HEX FLANGE (M10X1.5)
11588325 NUT WELD HEX FLANGE (M12X1.75)
11589252 GMT319 L4 BOLT
1234 test

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

PART # PART NAME TERM 7 TERM 8 SUM OF BALANCES
1018 "OD 12.7MM X 24"" LONG" 0
11509671 BOLT-METRIX HEX FLANGE 0
11515781 GEAR BOX ASSEMBLY STEERING FIXING BOLT 0
11588324 NUT WELD HEX FLANGE (M10X1.5) 0
11588325 NUT WELD HEX FLANGE (M12X1.75) 0
11589252 GMT319 L4 BOLT 0
1234 test 0
15226899-03-01 BODY MOUNT FRT LWR LH 0

SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME],[BEGINNING
INVENTORY BALANCE 8TH].[END BALANCE] AS [TERM 7],[INVENTORY R&D].[END
BALANCE] AS [TERM 8],nz
([BEGINNING INVENTORY BALANCE 8TH].[END BALANCE],0) +nz ([INVENTORY
R&D].[END BALANCE],0) AS [SUM OF BALANCES]
FROM ([COMBINED PARTS] LEFT JOIN [BEGINNING INVENTORY BALANCE 8TH] ON
[COMBINED PARTS].[PART #] & [BEGINNING INVENTORY BALANCE 8TH].[PART #]) LEFT
JOIN [INVENTORY R&D] ON [COMBINED PARTS].[PART #] = [INVENTORY R&D].[PART #]

the last table, as you can see isn't pulling the balances of my inventory.
Do I need a where statement or any suggestions?

Does a being a replicated query affect the outcome?

"lmiller" wrote:

Daryl,

Yes they work beautifully as stand alones. They were copied below in my
original question but I did just now rename them taking out the "&" in R & D.
I reran the stand alone queries and they work fine but the combined query I
am still working on still result in 0 for my final balances. All the past
queries I have tried It just doesn't seem like it is pulling the values for
my Term 8.

"Daryl S" wrote:

Lmiller -

If you run the two 'new' queries separately (e.g. [Beginning Inventory
Balance 8th] and [Inventory R&D], do you get the values for [End Balance 8]
and [End Balance 7] that you expect? If so, try changing the query name from
[Inventory R&D] to [Inventory RD] to see if removing the ampersand helps. It
bugs me that in the join query it looks like only the [Sum of Balances] field
is populated. This is what makes me ask if the two 'source' queries are
returning values in the End Balance fields...

--
Daryl S


"lmiller" wrote:

Daryl S,

Here is the query, I believe you suggested:

SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME], [BEGINNING
INVENTORY BALANCE 8TH].[END BALANCE 7], [INVENTORY R&D].[END BALANCE 8],
nz([BEGINNING INVENTORY BALANCE 8TH].[END BALANCE 7],0)+nz([INVENTORY
R&D].[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 R&D] ON [COMBINED PARTS].[PART #]=[INVENTORY R&D].[PART #];

PART # PART NAME END BALANCE 7 END BALANCE 8 SUM OF BALANCES
1018 "OD 12.7MM X 24"" LONG" 0
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

Any other suggestions? I also tried Karl's and with no success
I did change to End Balance 7, and End Balance 8 in the other tables. The
data still is coming back as 0 blances:



"Daryl S" wrote:

Lmiller -

Karl's idea is good, but you don't have the balances in the UNION query, so
it won't work quite right.

I don't see anything wrong with how the SQL is is currently coded (other
than the use of special characters in the field names), but let's try
changing the [END BALANCE] names in the two source queries, and then in the
combined query.

I changed the [END BALANCE] to [END BALANCE 7] in the first query, [END
BALANCE] to [END BALANCE 8] in the second query, and then updated the
Combined query to use them. See if these work.


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


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


SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME],[BEGINNING
INVENTORY BALANCE 8TH].[END BALANCE 7],[INVENTORY R&D].[END
BALANCE 8], (nz([BEGINNING INVENTORY BALANCE 8TH].[END BALANCE 8],0) +
nz([INVENTORY
R&D].[END BALANCE 7],0)) AS [SUM OF BALANCES]
FROM ([COMBINED PARTS] LEFT JOIN [BEGINNING INVENTORY BALANCE 8TH] ON
[COMBINED PARTS].[PART #] & [BEGINNING INVENTORY BALANCE 8TH].[PART #]) LEFT
JOIN [INVENTORY R&D] ON [COMBINED PARTS].[PART #] = [INVENTORY R&D].[PART #]

--
Daryl S


"lmiller" wrote:

Hello, I am going to try this again starting fresh from my other posts. I am
trying to develop an all inclusive query on our inventory. Each fical term
we start with new tables. I would like to take our Ending Inventory from our
prior fiscal term and combine it with our current inventory balances. In
years past I always took our ending inventory and posted them in our purchase
order table to have our beginning balances. I thought it would be less time
consuming to have a query to combine the 2. Below is a list of data and the
query designs.
PART # PRODUCT DESCRIPTION END BALANCE
1018 "OD 12.7MM X 24"" LONG" 27
11509671 BOLT-METRIX HEX FLANGE 93
11515781 GEAR BOX ASSEMBLY STEERING FIXING BOLT 25
11588324 NUT WELD HEX FLANGE (M10X1.5) 172
11588325 NUT WELD HEX FLANGE (M12X1.75) 55
11589252 GMT319 L4 BOLT 6


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


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

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

PART # PART NAME
1018 "OD 12.7MM X 24"" LONG"
11509671 BOLT-METRIX HEX FLANGE
11515781 GEAR BOX ASSEMBLY STEERING FIXING BOLT
11588324 NUT WELD HEX FLANGE (M10X1.5)
11588325 NUT WELD HEX FLANGE (M12X1.75)
11589252 GMT319 L4 BOLT
1234 test

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

PART # PART NAME TERM 7 TERM 8 SUM OF BALANCES
1018 "OD 12.7MM X 24"" LONG" 0
11509671 BOLT-METRIX HEX FLANGE 0
11515781 GEAR BOX ASSEMBLY STEERING FIXING BOLT 0
11588324 NUT WELD HEX FLANGE (M10X1.5) 0
11588325 NUT WELD HEX FLANGE (M12X1.75) 0
11589252 GMT319 L4 BOLT 0
1234 test 0
15226899-03-01 BODY MOUNT FRT LWR LH 0

SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME],[BEGINNING
INVENTORY BALANCE 8TH].[END BALANCE] AS [TERM 7],[INVENTORY R&D].[END
BALANCE] AS [TERM 8],nz
([BEGINNING INVENTORY BALANCE 8TH].[END BALANCE],0) +nz ([INVENTORY
R&D].[END BALANCE],0) AS [SUM OF BALANCES]
FROM ([COMBINED PARTS] LEFT JOIN [BEGINNING INVENTORY BALANCE 8TH] ON
[COMBINED PARTS].[PART #] & [BEGINNING INVENTORY BALANCE 8TH].[PART #]) LEFT
JOIN [INVENTORY R&D] ON [COMBINED PARTS].[PART #] = [INVENTORY R&D].[PART #]

the last table, as you can see isn't pulling the balances of my inventory.
Do I need a where statement or any suggestions?

aNY OTHER SUGGESTIONS?

"Daryl S" wrote:

Lmiller -

If you run the two 'new' queries separately (e.g. [Beginning Inventory
Balance 8th] and [Inventory R&D], do you get the values for [End Balance 8]
and [End Balance 7] that you expect? If so, try changing the query name from
[Inventory R&D] to [Inventory RD] to see if removing the ampersand helps. It
bugs me that in the join query it looks like only the [Sum of Balances] field
is populated. This is what makes me ask if the two 'source' queries are
returning values in the End Balance fields...

--
Daryl S


"lmiller" wrote:

Daryl S,

Here is the query, I believe you suggested:

SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME], [BEGINNING
INVENTORY BALANCE 8TH].[END BALANCE 7], [INVENTORY R&D].[END BALANCE 8],
nz([BEGINNING INVENTORY BALANCE 8TH].[END BALANCE 7],0)+nz([INVENTORY
R&D].[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 R&D] ON [COMBINED PARTS].[PART #]=[INVENTORY R&D].[PART #];

PART # PART NAME END BALANCE 7 END BALANCE 8 SUM OF BALANCES
1018 "OD 12.7MM X 24"" LONG" 0
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

Any other suggestions? I also tried Karl's and with no success
I did change to End Balance 7, and End Balance 8 in the other tables. The
data still is coming back as 0 blances:



"Daryl S" wrote:

Lmiller -

Karl's idea is good, but you don't have the balances in the UNION query, so
it won't work quite right.

I don't see anything wrong with how the SQL is is currently coded (other
than the use of special characters in the field names), but let's try
changing the [END BALANCE] names in the two source queries, and then in the
combined query.

I changed the [END BALANCE] to [END BALANCE 7] in the first query, [END
BALANCE] to [END BALANCE 8] in the second query, and then updated the
Combined query to use them. See if these work.


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


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


SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME],[BEGINNING
INVENTORY BALANCE 8TH].[END BALANCE 7],[INVENTORY R&D].[END
BALANCE 8], (nz([BEGINNING INVENTORY BALANCE 8TH].[END BALANCE 8],0) +
nz([INVENTORY
R&D].[END BALANCE 7],0)) AS [SUM OF BALANCES]
FROM ([COMBINED PARTS] LEFT JOIN [BEGINNING INVENTORY BALANCE 8TH] ON
[COMBINED PARTS].[PART #] & [BEGINNING INVENTORY BALANCE 8TH].[PART #]) LEFT
JOIN [INVENTORY R&D] ON [COMBINED PARTS].[PART #] = [INVENTORY R&D].[PART #]

--
Daryl S


"lmiller" wrote:

Hello, I am going to try this again starting fresh from my other posts. I am
trying to develop an all inclusive query on our inventory. Each fical term
we start with new tables. I would like to take our Ending Inventory from our
prior fiscal term and combine it with our current inventory balances. In
years past I always took our ending inventory and posted them in our purchase
order table to have our beginning balances. I thought it would be less time
consuming to have a query to combine the 2. Below is a list of data and the
query designs.
PART # PRODUCT DESCRIPTION END BALANCE
1018 "OD 12.7MM X 24"" LONG" 27
11509671 BOLT-METRIX HEX FLANGE 93
11515781 GEAR BOX ASSEMBLY STEERING FIXING BOLT 25
11588324 NUT WELD HEX FLANGE (M10X1.5) 172
11588325 NUT WELD HEX FLANGE (M12X1.75) 55
11589252 GMT319 L4 BOLT 6


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


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

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

PART # PART NAME
1018 "OD 12.7MM X 24"" LONG"
11509671 BOLT-METRIX HEX FLANGE
11515781 GEAR BOX ASSEMBLY STEERING FIXING BOLT
11588324 NUT WELD HEX FLANGE (M10X1.5)
11588325 NUT WELD HEX FLANGE (M12X1.75)
11589252 GMT319 L4 BOLT
1234 test

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

PART # PART NAME TERM 7 TERM 8 SUM OF BALANCES
1018 "OD 12.7MM X 24"" LONG" 0
11509671 BOLT-METRIX HEX FLANGE 0
11515781 GEAR BOX ASSEMBLY STEERING FIXING BOLT 0
11588324 NUT WELD HEX FLANGE (M10X1.5) 0
11588325 NUT WELD HEX FLANGE (M12X1.75) 0
11589252 GMT319 L4 BOLT 0
1234 test 0
15226899-03-01 BODY MOUNT FRT LWR LH 0

SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME],[BEGINNING
INVENTORY BALANCE 8TH].[END BALANCE] AS [TERM 7],[INVENTORY R&D].[END
BALANCE] AS [TERM 8],nz
([BEGINNING INVENTORY BALANCE 8TH].[END BALANCE],0) +nz ([INVENTORY
R&D].[END BALANCE],0) AS [SUM OF BALANCES]
FROM ([COMBINED PARTS] LEFT JOIN [BEGINNING INVENTORY BALANCE 8TH] ON
[COMBINED PARTS].[PART #] & [BEGINNING INVENTORY BALANCE 8TH].[PART #]) LEFT
JOIN [INVENTORY R&D] ON [COMBINED PARTS].[PART #] = [INVENTORY R&D].[PART #]

the last table, as you can see isn't pulling the balances of my inventory.
Do I need a where statement or any suggestions?

LMiller -

I don't know anything about replication.

I would suggest making a copy of the final query and taking out one of the
two 'source' queries (but keep the UNION query in there) at a time and see if
you get any of the End Balance data to show up. If it works with each of the
two sources individually, but not together, you could add an additional query
to combine them.

Let us know!
--
Daryl S


"lmiller" wrote:

aNY OTHER SUGGESTIONS?

"Daryl S" wrote:

Lmiller -

If you run the two 'new' queries separately (e.g. [Beginning Inventory
Balance 8th] and [Inventory R&D], do you get the values for [End Balance 8]
and [End Balance 7] that you expect? If so, try changing the query name from
[Inventory R&D] to [Inventory RD] to see if removing the ampersand helps. It
bugs me that in the join query it looks like only the [Sum of Balances] field
is populated. This is what makes me ask if the two 'source' queries are
returning values in the End Balance fields...

--
Daryl S


"lmiller" wrote:

Daryl S,

Here is the query, I believe you suggested:

SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME], [BEGINNING
INVENTORY BALANCE 8TH].[END BALANCE 7], [INVENTORY R&D].[END BALANCE 8],
nz([BEGINNING INVENTORY BALANCE 8TH].[END BALANCE 7],0)+nz([INVENTORY
R&D].[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 R&D] ON [COMBINED PARTS].[PART #]=[INVENTORY R&D].[PART #];

PART # PART NAME END BALANCE 7 END BALANCE 8 SUM OF BALANCES
1018 "OD 12.7MM X 24"" LONG" 0
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

Any other suggestions? I also tried Karl's and with no success
I did change to End Balance 7, and End Balance 8 in the other tables. The
data still is coming back as 0 blances:



"Daryl S" wrote:

Lmiller -

Karl's idea is good, but you don't have the balances in the UNION query, so
it won't work quite right.

I don't see anything wrong with how the SQL is is currently coded (other
than the use of special characters in the field names), but let's try
changing the [END BALANCE] names in the two source queries, and then in the
combined query.

I changed the [END BALANCE] to [END BALANCE 7] in the first query, [END
BALANCE] to [END BALANCE 8] in the second query, and then updated the
Combined query to use them. See if these work.


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


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


SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME],[BEGINNING
INVENTORY BALANCE 8TH].[END BALANCE 7],[INVENTORY R&D].[END
BALANCE 8], (nz([BEGINNING INVENTORY BALANCE 8TH].[END BALANCE 8],0) +
nz([INVENTORY
R&D].[END BALANCE 7],0)) AS [SUM OF BALANCES]
FROM ([COMBINED PARTS] LEFT JOIN [BEGINNING INVENTORY BALANCE 8TH] ON
[COMBINED PARTS].[PART #] & [BEGINNING INVENTORY BALANCE 8TH].[PART #]) LEFT
JOIN [INVENTORY R&D] ON [COMBINED PARTS].[PART #] = [INVENTORY R&D].[PART #]

--
Daryl S


"lmiller" wrote:

Hello, I am going to try this again starting fresh from my other posts. I am
trying to develop an all inclusive query on our inventory. Each fical term
we start with new tables. I would like to take our Ending Inventory from our
prior fiscal term and combine it with our current inventory balances. In
years past I always took our ending inventory and posted them in our purchase
order table to have our beginning balances. I thought it would be less time
consuming to have a query to combine the 2. Below is a list of data and the
query designs.
PART # PRODUCT DESCRIPTION END BALANCE
1018 "OD 12.7MM X 24"" LONG" 27
11509671 BOLT-METRIX HEX FLANGE 93
11515781 GEAR BOX ASSEMBLY STEERING FIXING BOLT 25
11588324 NUT WELD HEX FLANGE (M10X1.5) 172
11588325 NUT WELD HEX FLANGE (M12X1.75) 55
11589252 GMT319 L4 BOLT 6


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


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

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

PART # PART NAME
1018 "OD 12.7MM X 24"" LONG"
11509671 BOLT-METRIX HEX FLANGE
11515781 GEAR BOX ASSEMBLY STEERING FIXING BOLT
11588324 NUT WELD HEX FLANGE (M10X1.5)
11588325 NUT WELD HEX FLANGE (M12X1.75)
11589252 GMT319 L4 BOLT
1234 test

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

PART # PART NAME TERM 7 TERM 8 SUM OF BALANCES
1018 "OD 12.7MM X 24"" LONG" 0
11509671 BOLT-METRIX HEX FLANGE 0
11515781 GEAR BOX ASSEMBLY STEERING FIXING BOLT 0
11588324 NUT WELD HEX FLANGE (M10X1.5) 0
11588325 NUT WELD HEX FLANGE (M12X1.75) 0
11589252 GMT319 L4 BOLT 0
1234 test 0
15226899-03-01 BODY MOUNT FRT LWR LH 0

SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME],[BEGINNING
INVENTORY BALANCE 8TH].[END BALANCE] AS [TERM 7],[INVENTORY R&D].[END
BALANCE] AS [TERM 8],nz
([BEGINNING INVENTORY BALANCE 8TH].[END BALANCE],0) +nz ([INVENTORY
R&D].[END BALANCE],0) AS [SUM OF BALANCES]
FROM ([COMBINED PARTS] LEFT JOIN [BEGINNING INVENTORY BALANCE 8TH] ON
[COMBINED PARTS].[PART #] & [BEGINNING INVENTORY BALANCE 8TH].[PART #]) LEFT
JOIN [INVENTORY R&D] ON [COMBINED PARTS].[PART #] = [INVENTORY R&D].[PART #]

the last table, as you can see isn't pulling the balances of my inventory.
Do I need a where statement or any suggestions?

Thank you for all you suggestions Daryl they are greatly appreciated. I
tried running the final without 1 of the source queries but kept getting an
error in the FROM statement.

"Daryl S" wrote:

LMiller -

I don't know anything about replication.

I would suggest making a copy of the final query and taking out one of the
two 'source' queries (but keep the UNION query in there) at a time and see if
you get any of the End Balance data to show up. If it works with each of the
two sources individually, but not together, you could add an additional query
to combine them.

Let us know!
--
Daryl S


"lmiller" wrote:

aNY OTHER SUGGESTIONS?

"Daryl S" wrote:

Lmiller -

If you run the two 'new' queries separately (e.g. [Beginning Inventory
Balance 8th] and [Inventory R&D], do you get the values for [End Balance 8]
and [End Balance 7] that you expect? If so, try changing the query name from
[Inventory R&D] to [Inventory RD] to see if removing the ampersand helps. It
bugs me that in the join query it looks like only the [Sum of Balances] field
is populated. This is what makes me ask if the two 'source' queries are
returning values in the End Balance fields...

--
Daryl S


"lmiller" wrote:

Daryl S,

Here is the query, I believe you suggested:

SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME], [BEGINNING
INVENTORY BALANCE 8TH].[END BALANCE 7], [INVENTORY R&D].[END BALANCE 8],
nz([BEGINNING INVENTORY BALANCE 8TH].[END BALANCE 7],0)+nz([INVENTORY
R&D].[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 R&D] ON [COMBINED PARTS].[PART #]=[INVENTORY R&D].[PART #];

PART # PART NAME END BALANCE 7 END BALANCE 8 SUM OF BALANCES
1018 "OD 12.7MM X 24"" LONG" 0
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

Any other suggestions? I also tried Karl's and with no success
I did change to End Balance 7, and End Balance 8 in the other tables. The
data still is coming back as 0 blances:



"Daryl S" wrote:

Lmiller -

Karl's idea is good, but you don't have the balances in the UNION query, so
it won't work quite right.

I don't see anything wrong with how the SQL is is currently coded (other
than the use of special characters in the field names), but let's try
changing the [END BALANCE] names in the two source queries, and then in the
combined query.

I changed the [END BALANCE] to [END BALANCE 7] in the first query, [END
BALANCE] to [END BALANCE 8] in the second query, and then updated the
Combined query to use them. See if these work.


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


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


SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME],[BEGINNING
INVENTORY BALANCE 8TH].[END BALANCE 7],[INVENTORY R&D].[END
BALANCE 8], (nz([BEGINNING INVENTORY BALANCE 8TH].[END BALANCE 8],0) +
nz([INVENTORY
R&D].[END BALANCE 7],0)) AS [SUM OF BALANCES]
FROM ([COMBINED PARTS] LEFT JOIN [BEGINNING INVENTORY BALANCE 8TH] ON
[COMBINED PARTS].[PART #] & [BEGINNING INVENTORY BALANCE 8TH].[PART #]) LEFT
JOIN [INVENTORY R&D] ON [COMBINED PARTS].[PART #] = [INVENTORY R&D].[PART #]

--
Daryl S


"lmiller" wrote:

Hello, I am going to try this again starting fresh from my other posts. I am
trying to develop an all inclusive query on our inventory. Each fical term
we start with new tables. I would like to take our Ending Inventory from our
prior fiscal term and combine it with our current inventory balances. In
years past I always took our ending inventory and posted them in our purchase
order table to have our beginning balances. I thought it would be less time
consuming to have a query to combine the 2. Below is a list of data and the
query designs.
PART # PRODUCT DESCRIPTION END BALANCE
1018 "OD 12.7MM X 24"" LONG" 27
11509671 BOLT-METRIX HEX FLANGE 93
11515781 GEAR BOX ASSEMBLY STEERING FIXING BOLT 25
11588324 NUT WELD HEX FLANGE (M10X1.5) 172
11588325 NUT WELD HEX FLANGE (M12X1.75) 55
11589252 GMT319 L4 BOLT 6


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


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

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

PART # PART NAME
1018 "OD 12.7MM X 24"" LONG"
11509671 BOLT-METRIX HEX FLANGE
11515781 GEAR BOX ASSEMBLY STEERING FIXING BOLT
11588324 NUT WELD HEX FLANGE (M10X1.5)
11588325 NUT WELD HEX FLANGE (M12X1.75)
11589252 GMT319 L4 BOLT
1234 test

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

PART # PART NAME TERM 7 TERM 8 SUM OF BALANCES
1018 "OD 12.7MM X 24"" LONG" 0
11509671 BOLT-METRIX HEX FLANGE 0
11515781 GEAR BOX ASSEMBLY STEERING FIXING BOLT 0
11588324 NUT WELD HEX FLANGE (M10X1.5) 0
11588325 NUT WELD HEX FLANGE (M12X1.75) 0
11589252 GMT319 L4 BOLT 0
1234 test 0
15226899-03-01 BODY MOUNT FRT LWR LH 0

SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME],[BEGINNING
INVENTORY BALANCE 8TH].[END BALANCE] AS [TERM 7],[INVENTORY R&D].[END
BALANCE] AS [TERM 8],nz
([BEGINNING INVENTORY BALANCE 8TH].[END BALANCE],0) +nz ([INVENTORY
R&D].[END BALANCE],0) AS [SUM OF BALANCES]
FROM ([COMBINED PARTS] LEFT JOIN [BEGINNING INVENTORY BALANCE 8TH] ON
[COMBINED PARTS].[PART #] & [BEGINNING INVENTORY BALANCE 8TH].[PART #]) LEFT
JOIN [INVENTORY R&D] ON [COMBINED PARTS].[PART #] = [INVENTORY R&D].[PART #]

the last table, as you can see isn't pulling the balances of my inventory.
Do I need a where statement or any suggestions?

On Fri, 12 Feb 2010 13:06:02 -0800, lmiller
wrote:

Thank you for all you suggestions Daryl they are greatly appreciated. I
tried running the final without 1 of the source queries but kept getting an
error in the FROM statement.


Your FROM statement reads:

FROM ([COMBINED PARTS] LEFT JOIN [BEGINNING INVENTORY BALANCE 8TH] ON
[COMBINED PARTS].[PART #] & [BEGINNING INVENTORY BALANCE 8TH].[PART #]) LEFT
JOIN [INVENTORY R&D] ON [COMBINED PARTS].[PART #]=[INVENTORY R&D].[PART #];

The & in the second line is a string concatenation character and is simply
wrong in this context; I think it needs to be an = rather than an &.
--

John W. Vinson [MVP]

Thanks John but that still didn't work. the balances still come up to 0. I
have yet to find out how to combine these 2 queries correctly.

"John W. Vinson" wrote:

On Fri, 12 Feb 2010 13:06:02 -0800, lmiller
wrote:

Thank you for all you suggestions Daryl they are greatly appreciated. I
tried running the final without 1 of the source queries but kept getting an
error in the FROM statement.


Your FROM statement reads:

FROM ([COMBINED PARTS] LEFT JOIN [BEGINNING INVENTORY BALANCE 8TH] ON
[COMBINED PARTS].[PART #] & [BEGINNING INVENTORY BALANCE 8TH].[PART #]) LEFT
JOIN [INVENTORY R&D] ON [COMBINED PARTS].[PART #]=[INVENTORY R&D].[PART #];

The & in the second line is a string concatenation character and is simply
wrong in this context; I think it needs to be an = rather than an &.
--

John W. Vinson [MVP]
.