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 » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Select only negative numbered query results



 
 
Thread Tools Display Modes
  #1  
Old February 21st, 2006, 07:52 PM posted to microsoft.public.access.gettingstarted
external usenet poster
 
Posts: n/a
Default Select only negative numbered query results

Hello, just hoping for a bit of help on this one:

I have built a query which is linked via an ODBC database connection. I have
added 3 linked tables to my query. In my query I have a field called "NET
AVAIL". This field is a calculated field based upon the following expression:

Field Input is NET AVAIL: [ON HAND] - [REQRD]
Table Input is -- Blank --
Total Input is Expression

The way I have this written now it querys and displays all of my records
accurately, but it displays all my numbers which result in a positive "NET
AVAIL" as well as a negative. I would like to filter this calculation to
display on the results of this calculation which are negative.

I've tried many, many different things and just can't get it.
Any help is greatly appreciated! Thanks.
  #2  
Old February 21st, 2006, 08:17 PM posted to microsoft.public.access.gettingstarted
external usenet poster
 
Posts: n/a
Default Select only negative numbered query results

You didn't post a copy of the SQL of your query. I suspect what you are
looking for is a selection criterion. If you are working in query design
mode, just add "0" (without the quotes) in the selection under that field.

If you are working against a SQL statement in code, you'll need to look into
using a WHERE clause.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"la knight" wrote in message
...
Hello, just hoping for a bit of help on this one:

I have built a query which is linked via an ODBC database connection. I
have
added 3 linked tables to my query. In my query I have a field called "NET
AVAIL". This field is a calculated field based upon the following
expression:

Field Input is NET AVAIL: [ON HAND] - [REQRD]
Table Input is -- Blank --
Total Input is Expression

The way I have this written now it querys and displays all of my records
accurately, but it displays all my numbers which result in a positive "NET
AVAIL" as well as a negative. I would like to filter this calculation to
display on the results of this calculation which are negative.

I've tried many, many different things and just can't get it.
Any help is greatly appreciated! Thanks.



  #3  
Old February 21st, 2006, 08:36 PM posted to microsoft.public.access.gettingstarted
external usenet poster
 
Posts: n/a
Default Select only negative numbered query results

I am using the Query Design Mode to enter fields, table, input, etc... And I
have tried the "0" without the quotes for criteria and when I Run the query
it pops up with an Input Data Paramater" box. Which I'm not sure what that is
for. I just want to weed out the positives.

Here's the SQL: (I also have many other columns as you will notice)

SELECT V_INVENTORY_MSTR.PRODUCT_LINE AS [PR LINE], V_INVENTORY_MSTR.PART,
V_INVENTORY_MSTR.QTY_YTD_USAGE AS [PREV YTD],
V_INVENTORY_MSTR.QTY_CURRENT_USAGE AS [QTY YTD], V_INVENTORY_MSTR.QTY_ONHAND
AS [ON HAND], V_INVENTORY_MSTR.QTY_REQUIRED AS REQRD, [ON HAND]-[REQRD] AS
[NET AVAIL], Sum(V_ORDER_LINES.QTY_ORDERED) AS [SO QTY],
V_INVENTORY_MSTR.QTY_ONORDER_WO AS [WO QTY],
First(V_ORDER_LINES.DATE_ITEM_PROM) AS [SHIP DUE],
First(V_JOB_HEADER.DATE_DUE) AS [WO DATE], V_JOB_HEADER.DATE_START AS [WO
START], V_JOB_HEADER.QTY_COMPLETED AS [QTY DONE], Sum(V_ORDER_LINES.QTY_BO)
AS [BACK ORD]
FROM (V_INVENTORY_MSTR LEFT JOIN V_ORDER_LINES ON V_INVENTORY_MSTR.PART =
V_ORDER_LINES.PART) LEFT JOIN V_JOB_HEADER ON V_INVENTORY_MSTR.PART =
V_JOB_HEADER.PART
GROUP BY V_INVENTORY_MSTR.PRODUCT_LINE, V_INVENTORY_MSTR.PART,
V_INVENTORY_MSTR.QTY_YTD_USAGE, V_INVENTORY_MSTR.QTY_CURRENT_USAGE,
V_INVENTORY_MSTR.QTY_ONHAND, V_INVENTORY_MSTR.QTY_REQUIRED,
V_INVENTORY_MSTR.QTY_ONORDER_WO, V_JOB_HEADER.DATE_START,
V_JOB_HEADER.QTY_COMPLETED
HAVING (((V_INVENTORY_MSTR.PRODUCT_LINE)="20" Or
(V_INVENTORY_MSTR.PRODUCT_LINE)="17" Or (V_INVENTORY_MSTR.PRODUCT_LINE)="19"
Or (V_INVENTORY_MSTR.PRODUCT_LINE)="50" Or
(V_INVENTORY_MSTR.PRODUCT_LINE)="52") AND
((First(V_ORDER_LINES.DATE_ITEM_PROM))#1/1/1900#))
ORDER BY V_INVENTORY_MSTR.PRODUCT_LINE, V_INVENTORY_MSTR.PART;


"Jeff Boyce" wrote:

You didn't post a copy of the SQL of your query. I suspect what you are
looking for is a selection criterion. If you are working in query design
mode, just add "0" (without the quotes) in the selection under that field.

If you are working against a SQL statement in code, you'll need to look into
using a WHERE clause.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"la knight" wrote in message
...
Hello, just hoping for a bit of help on this one:

I have built a query which is linked via an ODBC database connection. I
have
added 3 linked tables to my query. In my query I have a field called "NET
AVAIL". This field is a calculated field based upon the following
expression:

Field Input is NET AVAIL: [ON HAND] - [REQRD]
Table Input is -- Blank --
Total Input is Expression

The way I have this written now it querys and displays all of my records
accurately, but it displays all my numbers which result in a positive "NET
AVAIL" as well as a negative. I would like to filter this calculation to
display on the results of this calculation which are negative.

I've tried many, many different things and just can't get it.
Any help is greatly appreciated! Thanks.




  #4  
Old February 22nd, 2006, 01:14 AM posted to microsoft.public.access.gettingstarted
external usenet poster
 
Posts: n/a
Default Select only negative numbered query results

I may have missed it, but I didn't see a clause in your SQL statement that
looks like:

WHERE xxxx 0

(where xxxx is the calculated value you first mentioned).

Where exactly are you putting the selection criterion?

Regards

Jeff Boyce
Microsoft Office/Access MVP


"la knight" wrote in message
...
I am using the Query Design Mode to enter fields, table, input, etc... And
I
have tried the "0" without the quotes for criteria and when I Run the
query
it pops up with an Input Data Paramater" box. Which I'm not sure what that
is
for. I just want to weed out the positives.

Here's the SQL: (I also have many other columns as you will notice)

SELECT V_INVENTORY_MSTR.PRODUCT_LINE AS [PR LINE], V_INVENTORY_MSTR.PART,
V_INVENTORY_MSTR.QTY_YTD_USAGE AS [PREV YTD],
V_INVENTORY_MSTR.QTY_CURRENT_USAGE AS [QTY YTD],
V_INVENTORY_MSTR.QTY_ONHAND
AS [ON HAND], V_INVENTORY_MSTR.QTY_REQUIRED AS REQRD, [ON HAND]-[REQRD] AS
[NET AVAIL], Sum(V_ORDER_LINES.QTY_ORDERED) AS [SO QTY],
V_INVENTORY_MSTR.QTY_ONORDER_WO AS [WO QTY],
First(V_ORDER_LINES.DATE_ITEM_PROM) AS [SHIP DUE],
First(V_JOB_HEADER.DATE_DUE) AS [WO DATE], V_JOB_HEADER.DATE_START AS [WO
START], V_JOB_HEADER.QTY_COMPLETED AS [QTY DONE],
Sum(V_ORDER_LINES.QTY_BO)
AS [BACK ORD]
FROM (V_INVENTORY_MSTR LEFT JOIN V_ORDER_LINES ON V_INVENTORY_MSTR.PART =
V_ORDER_LINES.PART) LEFT JOIN V_JOB_HEADER ON V_INVENTORY_MSTR.PART =
V_JOB_HEADER.PART
GROUP BY V_INVENTORY_MSTR.PRODUCT_LINE, V_INVENTORY_MSTR.PART,
V_INVENTORY_MSTR.QTY_YTD_USAGE, V_INVENTORY_MSTR.QTY_CURRENT_USAGE,
V_INVENTORY_MSTR.QTY_ONHAND, V_INVENTORY_MSTR.QTY_REQUIRED,
V_INVENTORY_MSTR.QTY_ONORDER_WO, V_JOB_HEADER.DATE_START,
V_JOB_HEADER.QTY_COMPLETED
HAVING (((V_INVENTORY_MSTR.PRODUCT_LINE)="20" Or
(V_INVENTORY_MSTR.PRODUCT_LINE)="17" Or
(V_INVENTORY_MSTR.PRODUCT_LINE)="19"
Or (V_INVENTORY_MSTR.PRODUCT_LINE)="50" Or
(V_INVENTORY_MSTR.PRODUCT_LINE)="52") AND
((First(V_ORDER_LINES.DATE_ITEM_PROM))#1/1/1900#))
ORDER BY V_INVENTORY_MSTR.PRODUCT_LINE, V_INVENTORY_MSTR.PART;


"Jeff Boyce" wrote:

You didn't post a copy of the SQL of your query. I suspect what you are
looking for is a selection criterion. If you are working in query design
mode, just add "0" (without the quotes) in the selection under that
field.

If you are working against a SQL statement in code, you'll need to look
into
using a WHERE clause.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"la knight" wrote in message
...
Hello, just hoping for a bit of help on this one:

I have built a query which is linked via an ODBC database connection. I
have
added 3 linked tables to my query. In my query I have a field called
"NET
AVAIL". This field is a calculated field based upon the following
expression:

Field Input is NET AVAIL: [ON HAND] - [REQRD]
Table Input is -- Blank --
Total Input is Expression

The way I have this written now it querys and displays all of my
records
accurately, but it displays all my numbers which result in a positive
"NET
AVAIL" as well as a negative. I would like to filter this calculation
to
display on the results of this calculation which are negative.

I've tried many, many different things and just can't get it.
Any help is greatly appreciated! Thanks.






  #5  
Old February 22nd, 2006, 03:29 PM posted to microsoft.public.access.gettingstarted
external usenet poster
 
Posts: n/a
Default Select only negative numbered query results

Here's the SQL again with a Where clause for the criteria. I kept taking out
all the ones I tried because I kept getting error messages or pop up windows
asking for paramter input. The code this way gives me a error message which
says "Data type mismatch". I've also received syntax errors too for other
configurations.

SQL:

SELECT V_INVENTORY_MSTR.PRODUCT_LINE AS [PR LINE], V_INVENTORY_MSTR.PART,
V_INVENTORY_MSTR.QTY_YTD_USAGE AS [PREV YTD],
V_INVENTORY_MSTR.QTY_CURRENT_USAGE AS [QTY YTD], V_INVENTORY_MSTR.QTY_ONHAND
AS [ON HAND], V_INVENTORY_MSTR.QTY_REQUIRED AS REQRD,
Sum(V_ORDER_LINES.QTY_ORDERED) AS [SO QTY], V_INVENTORY_MSTR.QTY_ONORDER_WO
AS [WO QTY], First(V_ORDER_LINES.DATE_ITEM_PROM) AS [SHIP DUE],
First(V_JOB_HEADER.DATE_DUE) AS [WO DATE], V_JOB_HEADER.DATE_START AS [WO
START], V_JOB_HEADER.QTY_COMPLETED AS [QTY DONE], Sum(V_ORDER_LINES.QTY_BO)
AS [BACK ORD]
FROM (V_INVENTORY_MSTR LEFT JOIN V_ORDER_LINES ON V_INVENTORY_MSTR.PART =
V_ORDER_LINES.PART) LEFT JOIN V_JOB_HEADER ON V_INVENTORY_MSTR.PART =
V_JOB_HEADER.PART
GROUP BY V_INVENTORY_MSTR.PRODUCT_LINE, V_INVENTORY_MSTR.PART,
V_INVENTORY_MSTR.QTY_YTD_USAGE, V_INVENTORY_MSTR.QTY_CURRENT_USAGE,
V_INVENTORY_MSTR.QTY_ONHAND, V_INVENTORY_MSTR.QTY_REQUIRED,
V_INVENTORY_MSTR.QTY_ONORDER_WO, V_JOB_HEADER.DATE_START,
V_JOB_HEADER.QTY_COMPLETED
HAVING (((V_INVENTORY_MSTR.PRODUCT_LINE)="20" Or
(V_INVENTORY_MSTR.PRODUCT_LINE)="17" Or (V_INVENTORY_MSTR.PRODUCT_LINE)="19"
Or (V_INVENTORY_MSTR.PRODUCT_LINE)="50" Or
(V_INVENTORY_MSTR.PRODUCT_LINE)="52") AND ("«Expr» Where [NET AVAIL]"0) AND
((First(V_ORDER_LINES.DATE_ITEM_PROM))#1/1/1900#))
ORDER BY V_INVENTORY_MSTR.PRODUCT_LINE, V_INVENTORY_MSTR.PART;


"Jeff Boyce" wrote:

I may have missed it, but I didn't see a clause in your SQL statement that
looks like:

WHERE xxxx 0

(where xxxx is the calculated value you first mentioned).

Where exactly are you putting the selection criterion?

Regards

Jeff Boyce
Microsoft Office/Access MVP


"la knight" wrote in message
...
I am using the Query Design Mode to enter fields, table, input, etc... And
I
have tried the "0" without the quotes for criteria and when I Run the
query
it pops up with an Input Data Paramater" box. Which I'm not sure what that
is
for. I just want to weed out the positives.

Here's the SQL: (I also have many other columns as you will notice)

SELECT V_INVENTORY_MSTR.PRODUCT_LINE AS [PR LINE], V_INVENTORY_MSTR.PART,
V_INVENTORY_MSTR.QTY_YTD_USAGE AS [PREV YTD],
V_INVENTORY_MSTR.QTY_CURRENT_USAGE AS [QTY YTD],
V_INVENTORY_MSTR.QTY_ONHAND
AS [ON HAND], V_INVENTORY_MSTR.QTY_REQUIRED AS REQRD, [ON HAND]-[REQRD] AS
[NET AVAIL], Sum(V_ORDER_LINES.QTY_ORDERED) AS [SO QTY],
V_INVENTORY_MSTR.QTY_ONORDER_WO AS [WO QTY],
First(V_ORDER_LINES.DATE_ITEM_PROM) AS [SHIP DUE],
First(V_JOB_HEADER.DATE_DUE) AS [WO DATE], V_JOB_HEADER.DATE_START AS [WO
START], V_JOB_HEADER.QTY_COMPLETED AS [QTY DONE],
Sum(V_ORDER_LINES.QTY_BO)
AS [BACK ORD]
FROM (V_INVENTORY_MSTR LEFT JOIN V_ORDER_LINES ON V_INVENTORY_MSTR.PART =
V_ORDER_LINES.PART) LEFT JOIN V_JOB_HEADER ON V_INVENTORY_MSTR.PART =
V_JOB_HEADER.PART
GROUP BY V_INVENTORY_MSTR.PRODUCT_LINE, V_INVENTORY_MSTR.PART,
V_INVENTORY_MSTR.QTY_YTD_USAGE, V_INVENTORY_MSTR.QTY_CURRENT_USAGE,
V_INVENTORY_MSTR.QTY_ONHAND, V_INVENTORY_MSTR.QTY_REQUIRED,
V_INVENTORY_MSTR.QTY_ONORDER_WO, V_JOB_HEADER.DATE_START,
V_JOB_HEADER.QTY_COMPLETED
HAVING (((V_INVENTORY_MSTR.PRODUCT_LINE)="20" Or
(V_INVENTORY_MSTR.PRODUCT_LINE)="17" Or
(V_INVENTORY_MSTR.PRODUCT_LINE)="19"
Or (V_INVENTORY_MSTR.PRODUCT_LINE)="50" Or
(V_INVENTORY_MSTR.PRODUCT_LINE)="52") AND
((First(V_ORDER_LINES.DATE_ITEM_PROM))#1/1/1900#))
ORDER BY V_INVENTORY_MSTR.PRODUCT_LINE, V_INVENTORY_MSTR.PART;


"Jeff Boyce" wrote:

You didn't post a copy of the SQL of your query. I suspect what you are
looking for is a selection criterion. If you are working in query design
mode, just add "0" (without the quotes) in the selection under that
field.

If you are working against a SQL statement in code, you'll need to look
into
using a WHERE clause.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"la knight" wrote in message
...
Hello, just hoping for a bit of help on this one:

I have built a query which is linked via an ODBC database connection. I
have
added 3 linked tables to my query. In my query I have a field called
"NET
AVAIL". This field is a calculated field based upon the following
expression:

Field Input is NET AVAIL: [ON HAND] - [REQRD]
Table Input is -- Blank --
Total Input is Expression

The way I have this written now it querys and displays all of my
records
accurately, but it displays all my numbers which result in a positive
"NET
AVAIL" as well as a negative. I would like to filter this calculation
to
display on the results of this calculation which are negative.

I've tried many, many different things and just can't get it.
Any help is greatly appreciated! Thanks.






  #6  
Old February 22nd, 2006, 05:54 PM posted to microsoft.public.access.gettingstarted
external usenet poster
 
Posts: n/a
Default Select only negative numbered query results

Your SQL statement includes something extra, the "Expr".

Try this ...

Start with a new (empty) query in design mode. Add the (?single) table that
has the data you need to use to calculate the value you mentioned. Add a
new field that calculates that value. In the Selection Criterion "cell",
type in your criterion (i.e., 0).

Run the query. Does it return a list of negative numbers?

Open the query in design mode and add some more pieces. Does it still run?

Keep building up until your query has all the pieces you have now, in your
SQL statement. Does it still run? If you need to use the SQL statement
rather than the query, change the view in design mode to SQL and copy the
SQL Access generates from what you've built in design mode.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"la knight" wrote in message
...
Here's the SQL again with a Where clause for the criteria. I kept taking
out
all the ones I tried because I kept getting error messages or pop up
windows
asking for paramter input. The code this way gives me a error message
which
says "Data type mismatch". I've also received syntax errors too for other
configurations.

SQL:

SELECT V_INVENTORY_MSTR.PRODUCT_LINE AS [PR LINE], V_INVENTORY_MSTR.PART,
V_INVENTORY_MSTR.QTY_YTD_USAGE AS [PREV YTD],
V_INVENTORY_MSTR.QTY_CURRENT_USAGE AS [QTY YTD],
V_INVENTORY_MSTR.QTY_ONHAND
AS [ON HAND], V_INVENTORY_MSTR.QTY_REQUIRED AS REQRD,
Sum(V_ORDER_LINES.QTY_ORDERED) AS [SO QTY],
V_INVENTORY_MSTR.QTY_ONORDER_WO
AS [WO QTY], First(V_ORDER_LINES.DATE_ITEM_PROM) AS [SHIP DUE],
First(V_JOB_HEADER.DATE_DUE) AS [WO DATE], V_JOB_HEADER.DATE_START AS [WO
START], V_JOB_HEADER.QTY_COMPLETED AS [QTY DONE],
Sum(V_ORDER_LINES.QTY_BO)
AS [BACK ORD]
FROM (V_INVENTORY_MSTR LEFT JOIN V_ORDER_LINES ON V_INVENTORY_MSTR.PART =
V_ORDER_LINES.PART) LEFT JOIN V_JOB_HEADER ON V_INVENTORY_MSTR.PART =
V_JOB_HEADER.PART
GROUP BY V_INVENTORY_MSTR.PRODUCT_LINE, V_INVENTORY_MSTR.PART,
V_INVENTORY_MSTR.QTY_YTD_USAGE, V_INVENTORY_MSTR.QTY_CURRENT_USAGE,
V_INVENTORY_MSTR.QTY_ONHAND, V_INVENTORY_MSTR.QTY_REQUIRED,
V_INVENTORY_MSTR.QTY_ONORDER_WO, V_JOB_HEADER.DATE_START,
V_JOB_HEADER.QTY_COMPLETED
HAVING (((V_INVENTORY_MSTR.PRODUCT_LINE)="20" Or
(V_INVENTORY_MSTR.PRODUCT_LINE)="17" Or
(V_INVENTORY_MSTR.PRODUCT_LINE)="19"
Or (V_INVENTORY_MSTR.PRODUCT_LINE)="50" Or
(V_INVENTORY_MSTR.PRODUCT_LINE)="52") AND ("«Expr» Where [NET AVAIL]"0)
AND
((First(V_ORDER_LINES.DATE_ITEM_PROM))#1/1/1900#))
ORDER BY V_INVENTORY_MSTR.PRODUCT_LINE, V_INVENTORY_MSTR.PART;


"Jeff Boyce" wrote:

I may have missed it, but I didn't see a clause in your SQL statement
that
looks like:

WHERE xxxx 0

(where xxxx is the calculated value you first mentioned).

Where exactly are you putting the selection criterion?

Regards

Jeff Boyce
Microsoft Office/Access MVP


"la knight" wrote in message
...
I am using the Query Design Mode to enter fields, table, input, etc...
And
I
have tried the "0" without the quotes for criteria and when I Run the
query
it pops up with an Input Data Paramater" box. Which I'm not sure what
that
is
for. I just want to weed out the positives.

Here's the SQL: (I also have many other columns as you will notice)

SELECT V_INVENTORY_MSTR.PRODUCT_LINE AS [PR LINE],
V_INVENTORY_MSTR.PART,
V_INVENTORY_MSTR.QTY_YTD_USAGE AS [PREV YTD],
V_INVENTORY_MSTR.QTY_CURRENT_USAGE AS [QTY YTD],
V_INVENTORY_MSTR.QTY_ONHAND
AS [ON HAND], V_INVENTORY_MSTR.QTY_REQUIRED AS REQRD, [ON HAND]-[REQRD]
AS
[NET AVAIL], Sum(V_ORDER_LINES.QTY_ORDERED) AS [SO QTY],
V_INVENTORY_MSTR.QTY_ONORDER_WO AS [WO QTY],
First(V_ORDER_LINES.DATE_ITEM_PROM) AS [SHIP DUE],
First(V_JOB_HEADER.DATE_DUE) AS [WO DATE], V_JOB_HEADER.DATE_START AS
[WO
START], V_JOB_HEADER.QTY_COMPLETED AS [QTY DONE],
Sum(V_ORDER_LINES.QTY_BO)
AS [BACK ORD]
FROM (V_INVENTORY_MSTR LEFT JOIN V_ORDER_LINES ON V_INVENTORY_MSTR.PART
=
V_ORDER_LINES.PART) LEFT JOIN V_JOB_HEADER ON V_INVENTORY_MSTR.PART =
V_JOB_HEADER.PART
GROUP BY V_INVENTORY_MSTR.PRODUCT_LINE, V_INVENTORY_MSTR.PART,
V_INVENTORY_MSTR.QTY_YTD_USAGE, V_INVENTORY_MSTR.QTY_CURRENT_USAGE,
V_INVENTORY_MSTR.QTY_ONHAND, V_INVENTORY_MSTR.QTY_REQUIRED,
V_INVENTORY_MSTR.QTY_ONORDER_WO, V_JOB_HEADER.DATE_START,
V_JOB_HEADER.QTY_COMPLETED
HAVING (((V_INVENTORY_MSTR.PRODUCT_LINE)="20" Or
(V_INVENTORY_MSTR.PRODUCT_LINE)="17" Or
(V_INVENTORY_MSTR.PRODUCT_LINE)="19"
Or (V_INVENTORY_MSTR.PRODUCT_LINE)="50" Or
(V_INVENTORY_MSTR.PRODUCT_LINE)="52") AND
((First(V_ORDER_LINES.DATE_ITEM_PROM))#1/1/1900#))
ORDER BY V_INVENTORY_MSTR.PRODUCT_LINE, V_INVENTORY_MSTR.PART;


"Jeff Boyce" wrote:

You didn't post a copy of the SQL of your query. I suspect what you
are
looking for is a selection criterion. If you are working in query
design
mode, just add "0" (without the quotes) in the selection under that
field.

If you are working against a SQL statement in code, you'll need to
look
into
using a WHERE clause.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"la knight" wrote in message
...
Hello, just hoping for a bit of help on this one:

I have built a query which is linked via an ODBC database
connection. I
have
added 3 linked tables to my query. In my query I have a field called
"NET
AVAIL". This field is a calculated field based upon the following
expression:

Field Input is NET AVAIL: [ON HAND] - [REQRD]
Table Input is -- Blank --
Total Input is Expression

The way I have this written now it querys and displays all of my
records
accurately, but it displays all my numbers which result in a
positive
"NET
AVAIL" as well as a negative. I would like to filter this
calculation
to
display on the results of this calculation which are negative.

I've tried many, many different things and just can't get it.
Any help is greatly appreciated! Thanks.








  #7  
Old February 23rd, 2006, 07:46 PM posted to microsoft.public.access.gettingstarted
external usenet poster
 
Posts: n/a
Default Select only negative numbered query results

Jeff:

Thanks for all your help. I did take your advice by starting with a blank
query and building it up piece by piece until I was returned the results I
was looking for. I guess the best advice I can give other new users would be
to do it this way also. It did take some trial and error to get the columns
of queries in the correct order, totaled, group and sorted to achieve my
results, but once I did - it was a beautiful thing!

One other question if your still listening - Is there a way to change the
display text of a query result to read as something else? Meaning this:

If I query a "PRODUCT_ID" which has a value of "20", can I change the
display of the result in the field to read as "lamp" instead of "20"?

Hope that makes sense. I've looked for an answer for this one too, but I'm
not sure I'm using the correct terminology to find the answer.


  #8  
Old February 24th, 2006, 12:03 AM posted to microsoft.public.access.gettingstarted
external usenet poster
 
Posts: n/a
Default Select only negative numbered query results

Do you have a table somewhere that lists
20 Lamp
30 Desk
40 Chair

If so, in your query, add that table, and join on the PRODUCT_ID. Then,
instead of showing PRODUCT_ID, select the field from the (lookup) table that
has "Lamp", "Desk", "...

Regards

Jeff Boyce
Microsoft Office/Access MVP

"la knight" wrote in message
news
Jeff:

Thanks for all your help. I did take your advice by starting with a blank
query and building it up piece by piece until I was returned the results I
was looking for. I guess the best advice I can give other new users would
be
to do it this way also. It did take some trial and error to get the
columns
of queries in the correct order, totaled, group and sorted to achieve my
results, but once I did - it was a beautiful thing!

One other question if your still listening - Is there a way to change the
display text of a query result to read as something else? Meaning this:

If I query a "PRODUCT_ID" which has a value of "20", can I change the
display of the result in the field to read as "lamp" instead of "20"?

Hope that makes sense. I've looked for an answer for this one too, but I'm
not sure I'm using the correct terminology to find the answer.




 




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

Similar Threads
Thread Thread Starter Forum Replies Last Post
Visio Shortcuts [email protected] Visio 1 December 29th, 2006 12:28 AM
Query criteria that references values of column in another table TyzonZ Running & Setting Up Queries 11 February 6th, 2006 01:57 AM
Combining records from two queries JohnB Running & Setting Up Queries 9 January 25th, 2006 02:35 PM
adding column with numbers in query Giz Running & Setting Up Queries 15 February 11th, 2005 12:09 PM
Big number gives error! Sara Mellen Running & Setting Up Queries 8 October 11th, 2004 02:48 AM


All times are GMT +1. The time now is 11:44 AM.


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