Show entire record (1 Viewer)

[jayme27]

Using a query that pulls data from multiple sources (tables and other queries), if just one of the columns is null that entire row does not show in the query. How can I have the value "0" display in that field in order for the record to fully display?

Example of columns in the query:

ProjectName SupplyMonth SupplyCost POLMonth POLCost

A dialog box form prompts the user for the criteria which is the month datepart to the fields in the SupplyMonth and POLMonth columns.
Everything works wonderful, except if the user enters a month when there are no entries. No entries (null) makes the whole row not display in the query even if the other columns meet the criteria.
GOAL: To have the query display the entire record and display "0" or empty values in those columns that do not have a cost associated to that month.

TMR 6 $0 6 $45
STK 6 $3 6 $12

row one scenerio would not show up on my query due to not having a month matching the criteria entered by the user at the prompt.

 

[jayme27]

If it matters, the Supply and POL month and cost come from another table/query.
The main query pulls this data in order to SUM TOTALS for these Costs. SumSupply and SumPOL. Cannot have a sum if I don't have a matching month in the column.
What is the best approach to take for accomplishing what I need to display?
Thank you

 

[Rich@ITTC]

Hi jayme27

Have you thought about not allowing the key fields in the tables (e.g. Supply Cost and/or POLCost) to have a null or zero length entry (i.e. in the table design set the field "Required" = Yes and "Allow Zero Length" = No). You could also put in a default value for these key fields as $0.00.

Alternatively couldn't you set up your form where this data is entered to have a default value for the key fields.

Another possibility is to use a query that has a left join rather than an internal join. A left join brings up all the records in Table A (whether or not there are values in Table B) and the data from Table B for those records that do have a value.

Another thing to try is, if you are using criteria to select records, to put in "Is Null" under the specified criteria (in an Or row when in the Design View of the query). There is always that well-known problem of having for example 100 records and setting up a query of all those with a value of over $100 returning 56 and all those equal to or less than $100 returning 42 - total 98 records (as two records had missing or Null data in the field concerned).

HTH

Rich Gorvin



[This message has been edited by Rich@ITTC (edited 07-20-2001).]

 

[jayme27]

I tried the Is Null in the criteria for the SupplyMonth and POLMonth so that when the user types: 6 in the dialog box form to see all records for the month of June.
Again, if Supply had no entries(costs) for that month, but POL did have entries for that month, the whole record did not show in the query or report.
How would a query know to Sum all entries for that month if there are no entries made for that month yet?

I could go to one of my main forms and under Supply and POL Costs, type every month of the year in, put a 0 value under each cost and it works fine. However, that is a real pain to do that for every year for every record.

I understand in the query that the computer is trying to match all records with the month that the user wants to see, however, for a given column it should display a 0 cost and display that month for those columns that do not have a matching record.

 

[pcs]

not sure about your design...
but, could you replace the query fields in question with an expression?

like:
Xmonth6: Nz([month6],0)

or, the problem may also stem from when you do the datepart,
maybe Nz() it there...probably the easiest fix.

hth,
al


[This message has been edited by pcs (edited 07-21-2001).]

 

[jayme27]

The Nz function failed to show me all the records like before.
Someone had mentioned the best approach might be to create a recordset and check for EOF. How do you do this?

My situation is as follows:

Dialog box used as prompt for criteria query...asks user for two things:
Example
FY: 00
Month: 6

There are multiple fields in the query that sum different costs (supply, transportation, gas, etc.) for only the period of time specified by the criteria given at the prompt. Unfortunately, if there are no costs for that Fiscal year (FY) and month for any ONE of those fields, then the entire record fails to display in the query. I do not want this "all or nothing" to happen.
Each of these costs are pulled from another table or query with FY and month and cost fields.
Everything works fine except the main query cannot sum costs for a record when I do not have a matching FY and month in the other table.
GOAL: If the criteria finds no matching FY and month in order to sum the cost field, then the entire record should display but with a blank entry or 0 in the sum cost field.

I am thinking when the user is prompted for the criteria maybe the query can do the following:
Regardless of FY, show null values too
Regardless of month, show null values too
Regardless of cost, show null values too
THen the criteria would show those fields with no value and those that the user wants?

Anyone got any ideas. Thanks for the advice so far.

 

[pcs]

are you familiar with the term 'Null propagation' ? if not post again.

al

 

[jayme27]

Using the dialog box form, that prompt the user for the query's criteria for Fiscal Year and Month,
I was able to make the fiscal year field manditory entry and any entry in the Month field not matching
what the user put in the prompt defaulted to a Month = 0 which I used in the query criteria under "or"
part so that all field sums had a value even if there were no cost entries for that month so that the
whole record would display using a 0 month and 0 cost or the desired month and its total cost.
Yes I did use the Nz(datepart(m,[field]),0)
For same field in criteria I had the dialog box prompt field of the form and in the "or" I had the value
0.
All costs were then summed in the cost field.

Thank you