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  

Query only showing specific records - i need all - please help



 
 
Thread Tools Display Modes
  #1  
Old July 8th, 2008, 01:55 PM posted to microsoft.public.access.queries
Yula
external usenet poster
 
Posts: 33
Default Query only showing specific records - i need all - please help

I have a combo box in a form (Based on a query) that looks up values in the
Finish Lookup table. The table has the following info:
Finish Description Finish Rate Finish Sq Ft
Coil .45
Cutting 0
Foam 3.50 X
Folded 0
Lamintated .218 X

The next text box has
length
Width

To get the finish cost I have the following formula:
=IIf([Finish Square Foot]="X",[Finish Rate]*[Sq Ft]*[Copies
Requested],[Finish Rate]*[Copies Requested])

After I add new records, I only see the records where I choice a finish
description that has a finish rate associated with it, I do not see all
records with the zeros for the finish rate. What confuses me is that I see
all the tables being populated with all the info that I enter (0 rate
descriptions and actual rate descriptions)

I have to present all this info today and I have no clue why the query is
only showing those records with the rates associated with them. I don't have
any criterias set up.

Thanks so much,
Yula


  #2  
Old July 8th, 2008, 02:18 PM posted to microsoft.public.access.queries
Michel Walsh
external usenet poster
 
Posts: 2,404
Default Query only showing specific records - i need all - please help

If there is no finish rate defined, what would be the result?

You can use:

=Nz( [Finish Rate] * [Copies Requested] * iif( [Finish Square Foot]="X", [Sq
Ft], 1), "undefined" )


which will display "undefined" (without quote) if any of the three fields
is not defined.


You could also have use a Format to format the NULL values as displaying
"undefined" instead of the blank you should see at the moment when a null is
involved in the computation.




Vanderghast, Access MVP



"Yula" wrote in message
news
I have a combo box in a form (Based on a query) that looks up values in the
Finish Lookup table. The table has the following info:
Finish Description Finish Rate Finish Sq Ft
Coil .45
Cutting 0
Foam 3.50 X
Folded 0
Lamintated .218 X

The next text box has
length
Width

To get the finish cost I have the following formula:
=IIf([Finish Square Foot]="X",[Finish Rate]*[Sq Ft]*[Copies
Requested],[Finish Rate]*[Copies Requested])

After I add new records, I only see the records where I choice a finish
description that has a finish rate associated with it, I do not see all
records with the zeros for the finish rate. What confuses me is that I see
all the tables being populated with all the info that I enter (0 rate
descriptions and actual rate descriptions)

I have to present all this info today and I have no clue why the query is
only showing those records with the rates associated with them. I don't
have
any criterias set up.

Thanks so much,
Yula




  #3  
Old July 8th, 2008, 06:15 PM posted to microsoft.public.access.queries
Yula
external usenet poster
 
Posts: 33
Default Query only showing specific records - i need all - please help

Michael, Thank you, but I am still lost. I substituted your first formula
into my nish field and I am getting a calculation. The problem is that I want
to see all the data that I entered into the database. I am missing 7 records
because they did not require me to enter anything into the Length/Width
fields. I see all that data in the tables, bit not in the form/query. Is it
possible to fix? or should I erase them from my tables and reenter those
records?
"Michel Walsh" wrote:

If there is no finish rate defined, what would be the result?

You can use:

=Nz( [Finish Rate] * [Copies Requested] * iif( [Finish Square Foot]="X", [Sq
Ft], 1), "undefined" )


which will display "undefined" (without quote) if any of the three fields
is not defined.


You could also have use a Format to format the NULL values as displaying
"undefined" instead of the blank you should see at the moment when a null is
involved in the computation.




Vanderghast, Access MVP



"Yula" wrote in message
news
I have a combo box in a form (Based on a query) that looks up values in the
Finish Lookup table. The table has the following info:
Finish Description Finish Rate Finish Sq Ft
Coil .45
Cutting 0
Foam 3.50 X
Folded 0
Lamintated .218 X

The next text box has
length
Width

To get the finish cost I have the following formula:
=IIf([Finish Square Foot]="X",[Finish Rate]*[Sq Ft]*[Copies
Requested],[Finish Rate]*[Copies Requested])

After I add new records, I only see the records where I choice a finish
description that has a finish rate associated with it, I do not see all
records with the zeros for the finish rate. What confuses me is that I see
all the tables being populated with all the info that I enter (0 rate
descriptions and actual rate descriptions)

I have to present all this info today and I have no clue why the query is
only showing those records with the rates associated with them. I don't
have
any criterias set up.

Thanks so much,
Yula





  #4  
Old July 8th, 2008, 06:32 PM posted to microsoft.public.access.queries
Michel Walsh
external usenet poster
 
Posts: 2,404
Default Query only showing specific records - i need all - please help

If the data is in the table but not in the form, you may have a filter on
the form. If the form is based on a query, then, when you look at the
query, in data view, do you see the records? if not, then, probably, you
have a criteria in the query that filter out those records. Remove the
criteria if that is the case.

Sure, the easier way is to edit the data in the table, add the required data
in the field (so you will be able to see the records in the form), but that
won't avoid the problem to re-occur. You can always design the table to NOT
ALLOW NULL under your field , if that makes sense. But knot having all the
details, I strongly suspect you used a filter or a criteria which eliminates
the records. A little bit if you have a criteria under a quantity field that
said: 10 then only records having their quantity value 10 would be
picked up.

So start with the query. Is your records are available there? if not, remove
the criteria which makes no sense.
If the data is there, then open the form, and remove the form filter, if
there is one.



Vanderghast, Access MVP.


"Yula" wrote in message
...
Michael, Thank you, but I am still lost. I substituted your first formula
into my nish field and I am getting a calculation. The problem is that I
want
to see all the data that I entered into the database. I am missing 7
records
because they did not require me to enter anything into the Length/Width
fields. I see all that data in the tables, bit not in the form/query. Is
it
possible to fix? or should I erase them from my tables and reenter those
records?
"Michel Walsh" wrote:

If there is no finish rate defined, what would be the result?

You can use:

=Nz( [Finish Rate] * [Copies Requested] * iif( [Finish Square Foot]="X",
[Sq
Ft], 1), "undefined" )


which will display "undefined" (without quote) if any of the three
fields
is not defined.


You could also have use a Format to format the NULL values as displaying
"undefined" instead of the blank you should see at the moment when a null
is
involved in the computation.




Vanderghast, Access MVP



"Yula" wrote in message
news
I have a combo box in a form (Based on a query) that looks up values in
the
Finish Lookup table. The table has the following info:
Finish Description Finish Rate Finish Sq Ft
Coil .45
Cutting 0
Foam 3.50 X
Folded 0
Lamintated .218 X

The next text box has
length
Width

To get the finish cost I have the following formula:
=IIf([Finish Square Foot]="X",[Finish Rate]*[Sq Ft]*[Copies
Requested],[Finish Rate]*[Copies Requested])

After I add new records, I only see the records where I choice a finish
description that has a finish rate associated with it, I do not see all
records with the zeros for the finish rate. What confuses me is that I
see
all the tables being populated with all the info that I enter (0 rate
descriptions and actual rate descriptions)

I have to present all this info today and I have no clue why the query
is
only showing those records with the rates associated with them. I don't
have
any criterias set up.

Thanks so much,
Yula







  #5  
Old July 9th, 2008, 08:51 PM posted to microsoft.public.access.queries
Yula
external usenet poster
 
Posts: 33
Default Query only showing specific records - i need all - please help

Michel - I am going to loose my mind with this stuff. The form is based on a
Query. I already erased the previous records and entered a new record that
did not have length and width filled out. All the tables are now populated
with the info I entered, except for the Size table (the one that has the
length/width field).

I checked the query for any criteria and unless there is some hidden rows, I
do not see any criteria. All I see are the records that have length and width
filled in. Is there other criteria that is not visable in a query design
view? Not all records require the user to enter length and width, why is it
not showing the records with nothing in the length/Width fields????

My last thought, before I complety loose it, is that maybe I was trying to
put some code that was suggested to me into an event and I didn't erase
everthing.

I wanted the user to be required to enter data in the length/width field if
there is an X in the Sq ft coloumn of the paper lookup table and the finish
lookup table

Paper
Description Rate Sq ft
Gloss .50 X

Finish
Description Rate Sq ft
Laminate .25 X

Thank you so much for your help!!1





I just have no clue what to do next, this is driving me Nuts!!

"Michel Walsh" wrote:

If the data is in the table but not in the form, you may have a filter on
the form. If the form is based on a query, then, when you look at the
query, in data view, do you see the records? if not, then, probably, you
have a criteria in the query that filter out those records. Remove the
criteria if that is the case.

Sure, the easier way is to edit the data in the table, add the required data
in the field (so you will be able to see the records in the form), but that
won't avoid the problem to re-occur. You can always design the table to NOT
ALLOW NULL under your field , if that makes sense. But knot having all the
details, I strongly suspect you used a filter or a criteria which eliminates
the records. A little bit if you have a criteria under a quantity field that
said: 10 then only records having their quantity value 10 would be
picked up.

So start with the query. Is your records are available there? if not, remove
the criteria which makes no sense.
If the data is there, then open the form, and remove the form filter, if
there is one.



Vanderghast, Access MVP.


"Yula" wrote in message
...
Michael, Thank you, but I am still lost. I substituted your first formula
into my nish field and I am getting a calculation. The problem is that I
want
to see all the data that I entered into the database. I am missing 7
records
because they did not require me to enter anything into the Length/Width
fields. I see all that data in the tables, bit not in the form/query. Is
it
possible to fix? or should I erase them from my tables and reenter those
records?
"Michel Walsh" wrote:

If there is no finish rate defined, what would be the result?

You can use:

=Nz( [Finish Rate] * [Copies Requested] * iif( [Finish Square Foot]="X",
[Sq
Ft], 1), "undefined" )


which will display "undefined" (without quote) if any of the three
fields
is not defined.


You could also have use a Format to format the NULL values as displaying
"undefined" instead of the blank you should see at the moment when a null
is
involved in the computation.




Vanderghast, Access MVP



"Yula" wrote in message
news I have a combo box in a form (Based on a query) that looks up values in
the
Finish Lookup table. The table has the following info:
Finish Description Finish Rate Finish Sq Ft
Coil .45
Cutting 0
Foam 3.50 X
Folded 0
Lamintated .218 X

The next text box has
length
Width

To get the finish cost I have the following formula:
=IIf([Finish Square Foot]="X",[Finish Rate]*[Sq Ft]*[Copies
Requested],[Finish Rate]*[Copies Requested])

After I add new records, I only see the records where I choice a finish
description that has a finish rate associated with it, I do not see all
records with the zeros for the finish rate. What confuses me is that I
see
all the tables being populated with all the info that I enter (0 rate
descriptions and actual rate descriptions)

I have to present all this info today and I have no clue why the query
is
only showing those records with the rates associated with them. I don't
have
any criterias set up.

Thanks so much,
Yula








  #6  
Old July 9th, 2008, 09:18 PM posted to microsoft.public.access.queries
Michel Walsh
external usenet poster
 
Posts: 2,404
Default Query only showing specific records - i need all - please help

When you say you don't see the record, you mean you don't see the row at
all, OR you see the row but under the computed expression column, you see no
result.

If you see no row at all, then this seems to be a criteria in the query, OR
a (dynamic) filter in the form. Sure, if you don't see the row at all in the
query view (as data view), then it is a criteria in the query. A criteria in
the query can be as a criteria, OR as an INNER JOIN. An inner join acts like
a criteria too. Can you edit the SQL statement (in SQL view of the query,
select all the text, then Ctrl_V will push it in the clipboard, and Ctrl-V
here will paste it back, here, as example).


If you see the row BUT NOT a result under a compute column, that is because
NULL propagates through most arithmetic operations.



Second 'question'. To impose the end user to fill the field length/Width
when there is an X under the Sq ft column, you have to add a Table
Validation Rule in the Table Design.

( [Sq ft] = "X") IMP ( NOT ( [length/Width] IS NULL ))

can be the formulation of the rule, add some nice description for that rule
(such as: "must supplied length/Width when [Sq ft] has an X" )


Note: I assume [Sq Ft] field is a TEXT field, NOT A BOOLEAN field
formatted to show a check, or an X. If [Sq ft] is Boolean value type, the
table validation rule becomes:

[Sq ft] IMP ( NOT ( [length/Width] IS NULL ))

and a more appropriate description:

"must supplied length/Width when [Sq ft] is checked"



If you wonder about what is the operator IMP, it is the logical operator
IMPlies, a kind of electrical switch: if the left side of IMP is false,
every thing is as if no rule was there; if the left side of IMP is true,
then the right side MUST be true for the record be considered acceptable to
the database.





Vanderghast, Access MVP



"Yula" wrote in message
...
Michel - I am going to loose my mind with this stuff. The form is based on
a
Query. I already erased the previous records and entered a new record that
did not have length and width filled out. All the tables are now populated
with the info I entered, except for the Size table (the one that has the
length/width field).

I checked the query for any criteria and unless there is some hidden rows,
I
do not see any criteria. All I see are the records that have length and
width
filled in. Is there other criteria that is not visable in a query design
view? Not all records require the user to enter length and width, why is
it
not showing the records with nothing in the length/Width fields????

My last thought, before I complety loose it, is that maybe I was trying to
put some code that was suggested to me into an event and I didn't erase
everthing.

I wanted the user to be required to enter data in the length/width field
if
there is an X in the Sq ft coloumn of the paper lookup table and the
finish
lookup table

Paper
Description Rate Sq ft
Gloss .50 X

Finish
Description Rate Sq ft
Laminate .25 X

Thank you so much for your help!!1





I just have no clue what to do next, this is driving me Nuts!!

"Michel Walsh" wrote:

If the data is in the table but not in the form, you may have a filter on
the form. If the form is based on a query, then, when you look at the
query, in data view, do you see the records? if not, then, probably, you
have a criteria in the query that filter out those records. Remove the
criteria if that is the case.

Sure, the easier way is to edit the data in the table, add the required
data
in the field (so you will be able to see the records in the form), but
that
won't avoid the problem to re-occur. You can always design the table to
NOT
ALLOW NULL under your field , if that makes sense. But knot having all
the
details, I strongly suspect you used a filter or a criteria which
eliminates
the records. A little bit if you have a criteria under a quantity field
that
said: 10 then only records having their quantity value 10 would be
picked up.

So start with the query. Is your records are available there? if not,
remove
the criteria which makes no sense.
If the data is there, then open the form, and remove the form filter, if
there is one.



Vanderghast, Access MVP.


"Yula" wrote in message
...
Michael, Thank you, but I am still lost. I substituted your first
formula
into my nish field and I am getting a calculation. The problem is that
I
want
to see all the data that I entered into the database. I am missing 7
records
because they did not require me to enter anything into the Length/Width
fields. I see all that data in the tables, bit not in the form/query.
Is
it
possible to fix? or should I erase them from my tables and reenter
those
records?
"Michel Walsh" wrote:

If there is no finish rate defined, what would be the result?

You can use:

=Nz( [Finish Rate] * [Copies Requested] * iif( [Finish Square
Foot]="X",
[Sq
Ft], 1), "undefined" )


which will display "undefined" (without quote) if any of the three
fields
is not defined.


You could also have use a Format to format the NULL values as
displaying
"undefined" instead of the blank you should see at the moment when a
null
is
involved in the computation.




Vanderghast, Access MVP



"Yula" wrote in message
news I have a combo box in a form (Based on a query) that looks up values
in
the
Finish Lookup table. The table has the following info:
Finish Description Finish Rate Finish Sq Ft
Coil .45
Cutting 0
Foam 3.50 X
Folded 0
Lamintated .218 X

The next text box has
length
Width

To get the finish cost I have the following formula:
=IIf([Finish Square Foot]="X",[Finish Rate]*[Sq Ft]*[Copies
Requested],[Finish Rate]*[Copies Requested])

After I add new records, I only see the records where I choice a
finish
description that has a finish rate associated with it, I do not see
all
records with the zeros for the finish rate. What confuses me is that
I
see
all the tables being populated with all the info that I enter (0
rate
descriptions and actual rate descriptions)

I have to present all this info today and I have no clue why the
query
is
only showing those records with the rates associated with them. I
don't
have
any criterias set up.

Thanks so much,
Yula










 




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 12:34 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.