combining queries into 1

L

lmiller

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 said:
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 said:
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?
Click to expand...
Click to expand...
 
D

Daryl S

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 said:
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 said:
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 said:
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?
Click to expand...
Click to expand...
Click to expand...
 
L

lmiller

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


:

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?
Click to expand...
Click to expand...
Click to expand...
 
L

lmiller

Does a being a replicated query affect the outcome?

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



:

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


:

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?
Click to expand...
Click to expand...
Click to expand...
 
L

lmiller

aNY OTHER SUGGESTIONS?

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


:

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?
Click to expand...
Click to expand...
Click to expand...
 
D

Daryl S

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 said:
aNY OTHER SUGGESTIONS?

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



:

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


:

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?
Click to expand...
Click to expand...
Click to expand...
 
L

lmiller

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 said:
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 said:
aNY OTHER SUGGESTIONS?

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


:

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:



:

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


:

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?
Click to expand...
Click to expand...
Click to expand...
 
J

John W. Vinson

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.
Click to expand...

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 #];
Click to expand...
Click to expand...
Click to expand...

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

lmiller

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 said:
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.
Click to expand...

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 #];
Click to expand...
Click to expand...

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 &.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

3 queries into 1 8
AT WITS END ON QUERY TOTAL 34
COMBINING QUERIES INTO 1 3
COMBINING 2 QUERIES INTO 1 13
combine like items in query 1
union query 19
Combining 3 queries 2
Excel Remains Open until Access is Closed... 2

Top