A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

AT WITS END ON QUERY TOTAL



 
 
Thread Tools Display Modes
  #31  
Old February 16th, 2010, 05:38 PM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default AT WITS END ON QUERY TOTAL

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

--
Build a little, test a little.


"lmiller" wrote:


i keep playing with these queries.... very frustrating... i changed what you
suggested just to see what would happen I put in the 8th term items first
in your query to combine with the 7th Now when I run all I get is the
8th term data and no 7th. It has to be in the way they are joined.

"KARL DEWEY" wrote:

It is NOT pulling my 8th term inventory balance. Yes the query for it

works as a stand alone and I get my data
The data you posted showed NO DATA for END BALANCE 8 at all.

I see an error in the union query --
[INVENTORY RD 8TH]/[END BALANCE 8]
FROM [inventory rd 8th]
WHERE ((([inventory rd 8th].[END BALANCE 8])0));

There should be a period instead of a slash in [INVENTORY RD 8TH].[END
BALANCE 8] to make it work.

--
Build a little, test a little.


"lmiller" wrote:

Karl,

I tried these queries again see below:

PART # PART NAME END BALANCE 7 END BALANCE 8
1018 "OD 12.7MM X 24"" LONG" 27 0
11509671 BOLT-METRIX HEX FLANGE 93 0
11515781 GEAR BOX ASSEMBLY STEERING FIXING BOLT 25 0
11588324 NUT WELD HEX FLANGE (M10X1.5) 172 0
11588325 NUT WELD HEX FLANGE (M12X1.75) 55 0
11589252 GMT319 L4 BOLT 6 0
15226899-03-01 BODY MOUNT FRT LWR LH 14 0

SQL is:

SELECT [INVENTORY RD 7th].[PART #], [INVENTORY RD 7th].[PART NAME],
[INVENTORY RD 7th].[END BALANCE 7], 0 AS [END BALANCE 8]
FROM [INVENTORY RD 7th]
WHERE ((([INVENTORY RD 7th].[END BALANCE 7])0))
UNION SELECT [inventory rd 8th].[PART #], [inventory rd 8th].[PART NAME], 0
AS [END BALANCE 7], [INVENTORY RD 8TH]/[END BALANCE 8]
FROM [inventory rd 8th]
WHERE ((([inventory rd 8th].[END BALANCE 8])0));

the 2nd query is as:

PART # PART NAME END BALANCE 7 END BALANCE 8 FINAL BALANCE
1018 "OD 12.7MM X 24"" LONG" 27 0 27
11509671 BOLT-METRIX HEX FLANGE 93 0 93
11515781 GEAR BOX ASSEMBLY STEERING FIXING BOLT 25 0 25
11588324 NUT WELD HEX FLANGE (M10X1.5) 172 0 172
11588325 NUT WELD HEX FLANGE (M12X1.75) 55 0 55

sql is as follows:
SELECT [PART #],[PART NAME],[END BALANCE 7],[END BALANCE 8], SUM(nz([END
BALANCE 7],0) + nz([END BALANCE 8],0)) AS [FINAL BALANCE]
FROM [FINAL BALANCE]
GROUP BY [PART #],[PART NAME],[END BALANCE 7],[END BALANCE 8]

I keep having the same trouble, It is NOT pulling my 8th term inventory
balance. Yes the query for it works as a stand alone and I get my data but
when I try to combine the 7 and 8th nothing seems to work.


"KARL DEWEY" wrote:

Try these queries --
qryINVENTORY_RD_7_8
SELECT [INVENTORY RD 7th].[PART #], [INVENTORY RD 7th].[PART NAME],
[INVENTORY RD 7th].[END BALANCE 7], 0 AS [END BALANCE 8]
FROM [INVENTORY RD 7th]
WHERE ((([INVENTORY RD 7th].[END BALANCE 7])0));
UNION ALLSELECT [inventory rd 8th].[PART #], [inventory rd 8th].[PART NAME],
0 AS [END BALANCE 7], [inventory rd 8th].[END BALANCE 8]
FROM [inventory rd 8th]
WHERE ((([inventory rd 8th].[END BALANCE 8])0));

SELECT [PART #], [PART NAME], [END BALANCE 7], [END BALANCE 8], Sum(Nz([END
BALANCE 7], 0) + Nz([END BALANCE 8], 0)) AS Combined_Balance
FROM qryINVENTORY_RD_7_8
GROUP BY [PART #], [PART NAME], [END BALANCE 7], [END BALANCE 8];

--
Build a little, test a little.


"lmiller" wrote:

SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME], [BEGINNING
INVENTORY BALANCE 8TH].[END BALANCE 7], [inventory rd 8th].[END BALANCE 8],
nz([END BALANCE 7],0)+nz([END BALANCE 8],0) AS [SUM OF BALANCES]
FROM ( [BEGINNING INVENTORY BALANCE 8TH] RIGHT JOIN [COMBINED PARTS] ON
[BEGINNING INVENTORY BALANCE 8TH].[PART#] = [COMBINED PARTS].[PART #]) RIGHT
JOIN [INVENTORY RD 8TH] ON [COMBINED PARTS].[PART #] = [INVENTORY RD
8TH].[PART #];

I am trying but it keeps giving me an error on the [BEGINNING INVENTORY
BALANCE 8TH].[PART #] can refer to more than one table in my SQL
statement......

"Daryl S" wrote:

Lmiller -

Try this one (I switched the outer joins to a way I am more used to - it
works in my db):

SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME], [BEGINNING
INVENTORY BALANCE 8TH].[END BALANCE 7], [INVENTORY RD 8TH].[END BALANCE 8],
NZ([END BALANCE 7],0)+NZ([END BALANCE 8],0) AS [SUM OF BALANCES]
FROM ([BEGINNING INVENTORY BALANCE 8TH] RIGHT JOIN [COMBINED PARTS] ON
[BEGINNING INVENTORY BALANCE 8TH].[PART #] = [COMBINED PARTS].[PART #]) RIGHT
JOIN [INVENTORY RD 8TH] ON [COMBINED PARTS].[PART #] = [INVENTORY RD
8TH].[PART #];


--
Daryl S


"lmiller" wrote:

Thanks Daryl,

but I had already tried that when John suggested it - same result:

SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME], [BEGINNING
INVENTORY BALANCE 8TH].[END BALANCE 7], [inventory rd 8th].[END BALANCE 8],
nz([BEGINNING INVENTORY BALANCE 8TH].[END BALANCE 7],0)+nz([inventory rd
8th].[END BALANCE 8],0) AS [SUM OF BALANCES]
FROM ([COMBINED PARTS] LEFT JOIN [BEGINNING INVENTORY BALANCE 8TH] ON
[COMBINED PARTS].[PART #] = [BEGINNING INVENTORY BALANCE 8TH].[PART #]) LEFT
JOIN [inventory rd 8th] ON [COMBINED PARTS].[PART #]=[inventory rd 8th].[PART
#];
PART # PART NAME END BALANCE 7 END BALANCE 8 SUM OF BALANCES
1018 "OD 12.7MM X 24"" LONG" 0
11509671 BOLT-METRIX HEX FLANGE 0
11515781 GEAR BOX ASSEMBLY STEERING FIXING BOLT 0
11588324 NUT WELD HEX FLANGE (M10X1.5) 0
11588325 NUT WELD HEX FLANGE (M12X1.75) 0
11589252 GMT319 L4 BOLT 0
1234 test 0


"Daryl S" wrote:

Lmiller -

John Vinson actually caught this error - change the ampersand to an equal
sign in the first join. Try this:

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

--
Daryl S


"lmiller" wrote:

I want to thank anyone in advance for any insight into this. I have posted
this question a couple of times and have been working on developing this
query for 2 weeks and to no avial. I have a query that list my 7th term
inventory balances as:

PART # PRODUCT DESCRIPTION END BALANCE 7
1018 "OD 12.7MM X 24"" LONG" 27
11509671 BOLT-METRIX HEX FLANGE 93
11515781 GEAR BOX ASSEMBLY STEERING FIXING BOLT 25
11588324 NUT WELD HEX FLANGE (M10X1.5) 172
11588325 NUT WELD HEX FLANGE (M12X1.75) 55

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


my 8th term inventory balances as follows:

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

SELECT [inventory rd 8th].[PART #], [inventory rd 8th].[PART NAME],
[inventory rd 8th].[END BALANCE 8]
FROM [inventory rd 8th]
WHERE ((([inventory rd 8th].[END BALANCE 8])0));


I am trying to combine the 2 queries into 1 so that all part #'s list and if
they are like part # to list on just one line. Also I would like the
balances from each term to be added together. I made a union query for the
Part # and name and it works great. I am trying to get the balances now to
add or display correctly.

Here is what I have come up with thus far :

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

SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME], [BEGINNING
INVENTORY BALANCE 8TH].[END BALANCE 7], [inventory rd 8th].[END BALANCE 8],
nz([BEGINNING INVENTORY BALANCE 8TH].[END BALANCE 7],0)+nz([inventory rd
8th].[END BALANCE 8],0) AS [SUM OF BALANCES]
FROM ([COMBINED PARTS] LEFT JOIN [BEGINNING INVENTORY BALANCE 8TH] ON
[COMBINED PARTS].[PART #] & [BEGINNING INVENTORY BALANCE 8TH].[PART #]) LEFT
JOIN [inventory rd 8th] ON [COMBINED PARTS].[PART #]=[inventory rd 8th].[PART
#];
Everything I have tried doesn't produce the balances. Any suggestion or
ideas. Maybe I need a whole different query than the one I have come up
with, I have played with many but as I said to no avail.
THANKS!!!

  #32  
Old February 16th, 2010, 05:45 PM posted to microsoft.public.access.queries
LMiller
external usenet poster
 
Posts: 77
Default AT WITS END ON QUERY TOTAL

Karl,

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

"KARL DEWEY" wrote:

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

--
Build a little, test a little.


"lmiller" wrote:


i keep playing with these queries.... very frustrating... i changed what you
suggested just to see what would happen I put in the 8th term items first
in your query to combine with the 7th Now when I run all I get is the
8th term data and no 7th. It has to be in the way they are joined.

"KARL DEWEY" wrote:

It is NOT pulling my 8th term inventory balance. Yes the query for it
works as a stand alone and I get my data
The data you posted showed NO DATA for END BALANCE 8 at all.

I see an error in the union query --
[INVENTORY RD 8TH]/[END BALANCE 8]
FROM [inventory rd 8th]
WHERE ((([inventory rd 8th].[END BALANCE 8])0));

There should be a period instead of a slash in [INVENTORY RD 8TH].[END
BALANCE 8] to make it work.

--
Build a little, test a little.


"lmiller" wrote:

Karl,

I tried these queries again see below:

PART # PART NAME END BALANCE 7 END BALANCE 8
1018 "OD 12.7MM X 24"" LONG" 27 0
11509671 BOLT-METRIX HEX FLANGE 93 0
11515781 GEAR BOX ASSEMBLY STEERING FIXING BOLT 25 0
11588324 NUT WELD HEX FLANGE (M10X1.5) 172 0
11588325 NUT WELD HEX FLANGE (M12X1.75) 55 0
11589252 GMT319 L4 BOLT 6 0
15226899-03-01 BODY MOUNT FRT LWR LH 14 0

SQL is:

SELECT [INVENTORY RD 7th].[PART #], [INVENTORY RD 7th].[PART NAME],
[INVENTORY RD 7th].[END BALANCE 7], 0 AS [END BALANCE 8]
FROM [INVENTORY RD 7th]
WHERE ((([INVENTORY RD 7th].[END BALANCE 7])0))
UNION SELECT [inventory rd 8th].[PART #], [inventory rd 8th].[PART NAME], 0
AS [END BALANCE 7], [INVENTORY RD 8TH]/[END BALANCE 8]
FROM [inventory rd 8th]
WHERE ((([inventory rd 8th].[END BALANCE 8])0));

the 2nd query is as:

PART # PART NAME END BALANCE 7 END BALANCE 8 FINAL BALANCE
1018 "OD 12.7MM X 24"" LONG" 27 0 27
11509671 BOLT-METRIX HEX FLANGE 93 0 93
11515781 GEAR BOX ASSEMBLY STEERING FIXING BOLT 25 0 25
11588324 NUT WELD HEX FLANGE (M10X1.5) 172 0 172
11588325 NUT WELD HEX FLANGE (M12X1.75) 55 0 55

sql is as follows:
SELECT [PART #],[PART NAME],[END BALANCE 7],[END BALANCE 8], SUM(nz([END
BALANCE 7],0) + nz([END BALANCE 8],0)) AS [FINAL BALANCE]
FROM [FINAL BALANCE]
GROUP BY [PART #],[PART NAME],[END BALANCE 7],[END BALANCE 8]

I keep having the same trouble, It is NOT pulling my 8th term inventory
balance. Yes the query for it works as a stand alone and I get my data but
when I try to combine the 7 and 8th nothing seems to work.


"KARL DEWEY" wrote:

Try these queries --
qryINVENTORY_RD_7_8
SELECT [INVENTORY RD 7th].[PART #], [INVENTORY RD 7th].[PART NAME],
[INVENTORY RD 7th].[END BALANCE 7], 0 AS [END BALANCE 8]
FROM [INVENTORY RD 7th]
WHERE ((([INVENTORY RD 7th].[END BALANCE 7])0));
UNION ALLSELECT [inventory rd 8th].[PART #], [inventory rd 8th].[PART NAME],
0 AS [END BALANCE 7], [inventory rd 8th].[END BALANCE 8]
FROM [inventory rd 8th]
WHERE ((([inventory rd 8th].[END BALANCE 8])0));

SELECT [PART #], [PART NAME], [END BALANCE 7], [END BALANCE 8], Sum(Nz([END
BALANCE 7], 0) + Nz([END BALANCE 8], 0)) AS Combined_Balance
FROM qryINVENTORY_RD_7_8
GROUP BY [PART #], [PART NAME], [END BALANCE 7], [END BALANCE 8];

--
Build a little, test a little.


"lmiller" wrote:

SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME], [BEGINNING
INVENTORY BALANCE 8TH].[END BALANCE 7], [inventory rd 8th].[END BALANCE 8],
nz([END BALANCE 7],0)+nz([END BALANCE 8],0) AS [SUM OF BALANCES]
FROM ( [BEGINNING INVENTORY BALANCE 8TH] RIGHT JOIN [COMBINED PARTS] ON
[BEGINNING INVENTORY BALANCE 8TH].[PART#] = [COMBINED PARTS].[PART #]) RIGHT
JOIN [INVENTORY RD 8TH] ON [COMBINED PARTS].[PART #] = [INVENTORY RD
8TH].[PART #];

I am trying but it keeps giving me an error on the [BEGINNING INVENTORY
BALANCE 8TH].[PART #] can refer to more than one table in my SQL
statement......

"Daryl S" wrote:

Lmiller -

Try this one (I switched the outer joins to a way I am more used to - it
works in my db):

SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME], [BEGINNING
INVENTORY BALANCE 8TH].[END BALANCE 7], [INVENTORY RD 8TH].[END BALANCE 8],
NZ([END BALANCE 7],0)+NZ([END BALANCE 8],0) AS [SUM OF BALANCES]
FROM ([BEGINNING INVENTORY BALANCE 8TH] RIGHT JOIN [COMBINED PARTS] ON
[BEGINNING INVENTORY BALANCE 8TH].[PART #] = [COMBINED PARTS].[PART #]) RIGHT
JOIN [INVENTORY RD 8TH] ON [COMBINED PARTS].[PART #] = [INVENTORY RD
8TH].[PART #];


--
Daryl S


"lmiller" wrote:

Thanks Daryl,

but I had already tried that when John suggested it - same result:

SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME], [BEGINNING
INVENTORY BALANCE 8TH].[END BALANCE 7], [inventory rd 8th].[END BALANCE 8],
nz([BEGINNING INVENTORY BALANCE 8TH].[END BALANCE 7],0)+nz([inventory rd
8th].[END BALANCE 8],0) AS [SUM OF BALANCES]
FROM ([COMBINED PARTS] LEFT JOIN [BEGINNING INVENTORY BALANCE 8TH] ON
[COMBINED PARTS].[PART #] = [BEGINNING INVENTORY BALANCE 8TH].[PART #]) LEFT
JOIN [inventory rd 8th] ON [COMBINED PARTS].[PART #]=[inventory rd 8th].[PART
#];
PART # PART NAME END BALANCE 7 END BALANCE 8 SUM OF BALANCES
1018 "OD 12.7MM X 24"" LONG" 0
11509671 BOLT-METRIX HEX FLANGE 0
11515781 GEAR BOX ASSEMBLY STEERING FIXING BOLT 0
11588324 NUT WELD HEX FLANGE (M10X1.5) 0
11588325 NUT WELD HEX FLANGE (M12X1.75) 0
11589252 GMT319 L4 BOLT 0
1234 test 0


"Daryl S" wrote:

Lmiller -

John Vinson actually caught this error - change the ampersand to an equal
sign in the first join. Try this:

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

--
Daryl S


"lmiller" wrote:

I want to thank anyone in advance for any insight into this. I have posted
this question a couple of times and have been working on developing this
query for 2 weeks and to no avial. I have a query that list my 7th term
inventory balances as:

PART # PRODUCT DESCRIPTION END BALANCE 7
1018 "OD 12.7MM X 24"" LONG" 27
11509671 BOLT-METRIX HEX FLANGE 93
11515781 GEAR BOX ASSEMBLY STEERING FIXING BOLT 25
11588324 NUT WELD HEX FLANGE (M10X1.5) 172
11588325 NUT WELD HEX FLANGE (M12X1.75) 55

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


my 8th term inventory balances as follows:

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

SELECT [inventory rd 8th].[PART #], [inventory rd 8th].[PART NAME],
[inventory rd 8th].[END BALANCE 8]
FROM [inventory rd 8th]
WHERE ((([inventory rd 8th].[END BALANCE 8])0));


I am trying to combine the 2 queries into 1 so that all part #'s list and if
they are like part # to list on just one line. Also I would like the
balances from each term to be added together. I made a union query for the
Part # and name and it works great. I am trying to get the balances now to
add or display correctly.

Here is what I have come up with thus far :

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

SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME], [BEGINNING
INVENTORY BALANCE 8TH].[END BALANCE 7], [inventory rd 8th].[END BALANCE 8],
nz([BEGINNING INVENTORY BALANCE 8TH].[END BALANCE 7],0)+nz([inventory rd
8th].[END BALANCE 8],0) AS [SUM OF BALANCES]
FROM ([COMBINED PARTS] LEFT JOIN [BEGINNING INVENTORY BALANCE 8TH] ON
[COMBINED PARTS].[PART #] & [BEGINNING INVENTORY BALANCE 8TH].[PART #]) LEFT
JOIN [inventory rd 8th] ON [COMBINED PARTS].[PART #]=[inventory rd 8th].[PART
#];
Everything I have tried doesn't produce the balances. Any suggestion or
ideas. Maybe I need a whole different query than the one I have come up
with, I have played with many but as I said to no avail.
THANKS!!!

  #33  
Old February 16th, 2010, 06:22 PM posted to microsoft.public.access.queries
Daryl S[_2_]
external usenet poster
 
Posts: 881
Default AT WITS END ON QUERY TOTAL

Please remember we are all volunteering our time - none of us are paid to
provide answers or help out. We have other commitments and lives, too.

--
Daryl S


"lmiller" wrote:

Did everyone give up? can this be done in access?


  #34  
Old February 16th, 2010, 06:34 PM posted to microsoft.public.access.queries
LMiller
external usenet poster
 
Posts: 77
Default AT WITS END ON QUERY TOTAL


Sorry I know you are and I do greatly appreciate all your time and
effort!!!!! I didn't mean to come across so bad....just getting frustrated.
"Daryl S" wrote:

Please remember we are all volunteering our time - none of us are paid to
provide answers or help out. We have other commitments and lives, too.

--
Daryl S


"lmiller" wrote:

Did everyone give up? can this be done in access?


  #35  
Old February 16th, 2010, 11:15 PM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default AT WITS END ON QUERY TOTAL

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

--
Build a little, test a little.


"lmiller" wrote:

Karl,

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

"KARL DEWEY" wrote:

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

--
Build a little, test a little.


"lmiller" wrote:


i keep playing with these queries.... very frustrating... i changed what you
suggested just to see what would happen I put in the 8th term items first
in your query to combine with the 7th Now when I run all I get is the
8th term data and no 7th. It has to be in the way they are joined.

"KARL DEWEY" wrote:

It is NOT pulling my 8th term inventory balance. Yes the query for it
works as a stand alone and I get my data
The data you posted showed NO DATA for END BALANCE 8 at all.

I see an error in the union query --
[INVENTORY RD 8TH]/[END BALANCE 8]
FROM [inventory rd 8th]
WHERE ((([inventory rd 8th].[END BALANCE 8])0));

There should be a period instead of a slash in [INVENTORY RD 8TH].[END
BALANCE 8] to make it work.

--
Build a little, test a little.


"lmiller" wrote:

Karl,

I tried these queries again see below:

PART # PART NAME END BALANCE 7 END BALANCE 8
1018 "OD 12.7MM X 24"" LONG" 27 0
11509671 BOLT-METRIX HEX FLANGE 93 0
11515781 GEAR BOX ASSEMBLY STEERING FIXING BOLT 25 0
11588324 NUT WELD HEX FLANGE (M10X1.5) 172 0
11588325 NUT WELD HEX FLANGE (M12X1.75) 55 0
11589252 GMT319 L4 BOLT 6 0
15226899-03-01 BODY MOUNT FRT LWR LH 14 0

SQL is:

SELECT [INVENTORY RD 7th].[PART #], [INVENTORY RD 7th].[PART NAME],
[INVENTORY RD 7th].[END BALANCE 7], 0 AS [END BALANCE 8]
FROM [INVENTORY RD 7th]
WHERE ((([INVENTORY RD 7th].[END BALANCE 7])0))
UNION SELECT [inventory rd 8th].[PART #], [inventory rd 8th].[PART NAME], 0
AS [END BALANCE 7], [INVENTORY RD 8TH]/[END BALANCE 8]
FROM [inventory rd 8th]
WHERE ((([inventory rd 8th].[END BALANCE 8])0));

the 2nd query is as:

PART # PART NAME END BALANCE 7 END BALANCE 8 FINAL BALANCE
1018 "OD 12.7MM X 24"" LONG" 27 0 27
11509671 BOLT-METRIX HEX FLANGE 93 0 93
11515781 GEAR BOX ASSEMBLY STEERING FIXING BOLT 25 0 25
11588324 NUT WELD HEX FLANGE (M10X1.5) 172 0 172
11588325 NUT WELD HEX FLANGE (M12X1.75) 55 0 55

sql is as follows:
SELECT [PART #],[PART NAME],[END BALANCE 7],[END BALANCE 8], SUM(nz([END
BALANCE 7],0) + nz([END BALANCE 8],0)) AS [FINAL BALANCE]
FROM [FINAL BALANCE]
GROUP BY [PART #],[PART NAME],[END BALANCE 7],[END BALANCE 8]

I keep having the same trouble, It is NOT pulling my 8th term inventory
balance. Yes the query for it works as a stand alone and I get my data but
when I try to combine the 7 and 8th nothing seems to work.


"KARL DEWEY" wrote:

Try these queries --
qryINVENTORY_RD_7_8
SELECT [INVENTORY RD 7th].[PART #], [INVENTORY RD 7th].[PART NAME],
[INVENTORY RD 7th].[END BALANCE 7], 0 AS [END BALANCE 8]
FROM [INVENTORY RD 7th]
WHERE ((([INVENTORY RD 7th].[END BALANCE 7])0));
UNION ALLSELECT [inventory rd 8th].[PART #], [inventory rd 8th].[PART NAME],
0 AS [END BALANCE 7], [inventory rd 8th].[END BALANCE 8]
FROM [inventory rd 8th]
WHERE ((([inventory rd 8th].[END BALANCE 8])0));

SELECT [PART #], [PART NAME], [END BALANCE 7], [END BALANCE 8], Sum(Nz([END
BALANCE 7], 0) + Nz([END BALANCE 8], 0)) AS Combined_Balance
FROM qryINVENTORY_RD_7_8
GROUP BY [PART #], [PART NAME], [END BALANCE 7], [END BALANCE 8];

--
Build a little, test a little.


"lmiller" wrote:

SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME], [BEGINNING
INVENTORY BALANCE 8TH].[END BALANCE 7], [inventory rd 8th].[END BALANCE 8],
nz([END BALANCE 7],0)+nz([END BALANCE 8],0) AS [SUM OF BALANCES]
FROM ( [BEGINNING INVENTORY BALANCE 8TH] RIGHT JOIN [COMBINED PARTS] ON
[BEGINNING INVENTORY BALANCE 8TH].[PART#] = [COMBINED PARTS].[PART #]) RIGHT
JOIN [INVENTORY RD 8TH] ON [COMBINED PARTS].[PART #] = [INVENTORY RD
8TH].[PART #];

I am trying but it keeps giving me an error on the [BEGINNING INVENTORY
BALANCE 8TH].[PART #] can refer to more than one table in my SQL
statement......

"Daryl S" wrote:

Lmiller -

Try this one (I switched the outer joins to a way I am more used to - it
works in my db):

SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME], [BEGINNING
INVENTORY BALANCE 8TH].[END BALANCE 7], [INVENTORY RD 8TH].[END BALANCE 8],
NZ([END BALANCE 7],0)+NZ([END BALANCE 8],0) AS [SUM OF BALANCES]
FROM ([BEGINNING INVENTORY BALANCE 8TH] RIGHT JOIN [COMBINED PARTS] ON
[BEGINNING INVENTORY BALANCE 8TH].[PART #] = [COMBINED PARTS].[PART #]) RIGHT
JOIN [INVENTORY RD 8TH] ON [COMBINED PARTS].[PART #] = [INVENTORY RD
8TH].[PART #];


--
Daryl S


"lmiller" wrote:

Thanks Daryl,

but I had already tried that when John suggested it - same result:

SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME], [BEGINNING
INVENTORY BALANCE 8TH].[END BALANCE 7], [inventory rd 8th].[END BALANCE 8],
nz([BEGINNING INVENTORY BALANCE 8TH].[END BALANCE 7],0)+nz([inventory rd
8th].[END BALANCE 8],0) AS [SUM OF BALANCES]
FROM ([COMBINED PARTS] LEFT JOIN [BEGINNING INVENTORY BALANCE 8TH] ON
[COMBINED PARTS].[PART #] = [BEGINNING INVENTORY BALANCE 8TH].[PART #]) LEFT
JOIN [inventory rd 8th] ON [COMBINED PARTS].[PART #]=[inventory rd 8th].[PART
#];
PART # PART NAME END BALANCE 7 END BALANCE 8 SUM OF BALANCES
1018 "OD 12.7MM X 24"" LONG" 0
11509671 BOLT-METRIX HEX FLANGE 0
11515781 GEAR BOX ASSEMBLY STEERING FIXING BOLT 0
11588324 NUT WELD HEX FLANGE (M10X1.5) 0
11588325 NUT WELD HEX FLANGE (M12X1.75) 0
11589252 GMT319 L4 BOLT 0
1234 test 0


"Daryl S" wrote:

Lmiller -

John Vinson actually caught this error - change the ampersand to an equal
sign in the first join. Try this:

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

--
Daryl S


"lmiller" wrote:

I want to thank anyone in advance for any insight into this. I have posted
this question a couple of times and have been working on developing this
query for 2 weeks and to no avial. I have a query that list my 7th term
inventory balances as:

PART # PRODUCT DESCRIPTION END BALANCE 7
1018 "OD 12.7MM X 24"" LONG" 27
11509671 BOLT-METRIX HEX FLANGE 93
11515781 GEAR BOX ASSEMBLY STEERING FIXING BOLT 25
11588324 NUT WELD HEX FLANGE (M10X1.5) 172
11588325 NUT WELD HEX FLANGE (M12X1.75) 55

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


my 8th term inventory balances as follows:

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

SELECT [inventory rd 8th].[PART #], [inventory rd 8th].[PART NAME],
[inventory rd 8th].[END BALANCE 8]
FROM [inventory rd 8th]
WHERE ((([inventory rd 8th].[END BALANCE 8])0));


I am trying to combine the 2 queries into 1 so that all part #'s list and if
they are like part # to list on just one line. Also I would like the
balances from each term to be added together. I made a union query for the
Part # and name and it works great. I am trying to get the balances now to
add or display correctly.

Here is what I have come up with thus far :

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

SELECT [COMBINED PARTS].[PART #], [COMBINED PARTS].[PART NAME], [BEGINNING
INVENTORY BALANCE 8TH].[END BALANCE 7], [inventory rd 8th].[END BALANCE 8],
nz([BEGINNING INVENTORY BALANCE 8TH].[END BALANCE 7],0)+nz([inventory rd
8th].[END BALANCE 8],0) AS [SUM OF BALANCES]
FROM ([COMBINED PARTS] LEFT JOIN [BEGINNING INVENTORY BALANCE 8TH] ON
[COMBINED PARTS].[PART #] & [BEGINNING INVENTORY BALANCE 8TH].[PART #]) LEFT
JOIN [inventory rd 8th] ON [COMBINED PARTS].[PART #]=[inventory rd 8th].[PART
#];
Everything I have tried doesn't produce the balances. Any suggestion or
ideas. Maybe I need a whole different query than the one I have come up
with, I have played with many but as I said to no avail.
THANKS!!!

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:33 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.