Still stuck on the null issue

[Thompyt]

If a field is null, how do I get it to print out the missing string and place a value of 0 for the count?



SELECT Count(Inquires.Actual_ID) AS CountOfActual_ID, Inquires.Actual_ID
FROM Inquires
WHERE (((Inquires.[Date Sent]) Between DateSerial(Year(Date()),Month(Date())-3,0) And DateSerial(Year(Date()),Month(Date())-2,0)))
GROUP BY Inquires.Actual_ID
ORDER BY Inquires.Actual_ID;


Actual Table has 11 rows of data with 2 columns

Actual_ID
Actual

Actual_ID is linked by a 1 to many relationship to the Actual_ID field in the Inquries Table.

[June7]

Did you try Nz(Count(Inquires.Actual_ID),0)?

[Thompyt]

June7,
I tried this with the same outcome as before:

SELECT Inquires.Actual_ID, Nz(Count([Inquires].[Actual_ID]),0) AS Actual
FROM Inquires INNER JOIN Actual ON Inquires.Actual_ID = Actual.Actual_ID
WHERE (((Inquires.[Date Sent]) Between DateSerial(Year(Date()),Month(Date())-1,0) And DateSerial(Year(Date()),Month(Date())+1,0)))
GROUP BY Inquires.Actual_ID;

[June7]

I just did a test of aggregate query.

The records with null fields show 0 for the count. Did not use Nz().

What do you mean by 'print out missing string'? If something is missing it can't 'print'.

[Thompyt]

Data_Exp7.zip

Lets say the following is given:

Table: Actual

Actual_ID	Actual
1	Instructions sent
2	No show
3	Case in queue
4	M/C approved/issued
5	Waiver approved
6	Sent for revocation
7	Another Post
8	Not received
9	No Record Found
10	Purged
11	Under review

Table: Inquires

Inq_ID	Inq_Type	Section	Date Sent	Purp_ID	Actual_ID
1	Attorney	NID	3/11/2014	Consideration	Instructions sent
2	Baker	IV	3/9/2014	Denial	No show
3	UPS	ABC	3/9/2014	Approved	Case in queue
4	Metzger	NID	3/12/2014	Denial	M/C approved/issued
5	Pet	IV	3/13/2014	Asked	Waiver approved
7	Third Party	IV	3/14/2014	Zeroed	No Record Found
9	Judge	ABC	3/15/2014	Quit	Purged
10	Metzger	NID	3/30/2014	Applied	Under review
11	Baker	ABC	3/11/2014	Action Verb	Instructions sent

I am having the Actual_ID counted where it fits in certain date ranges. Say month 1 is the month of October. If there is nothing within that month for Actual.Actual_ID = 6 or Actual.Actual=“
Sent for revocation”, there will not be anything for the system to count. If it isn’t tin there then the entire row would disappear in the report when I have it listed by Actual.Actual.

Report Example

Header	Month-6	Month-5	Month-4	Month-3	Month-2	Month-1
Actual.Actual	CountofMonth-6	CountofMonth-5	CountofMonth-4	CountofMonth-3	CountofMonth-2	CountofMonth-1
Footer	SumofMonth-6	SumofMonth-5	SumofMonth-4	SumofMonth-3	SumofMonth-2	SumofMonth-1

[June7]

There are no records in the Inquires for Actual_IDs 6 and 8 but you still want them to show as lines in the report?

Are you filtering the report by date range or some other parameter?

Post the report RecordSource. Or better, provide db.

[Thompyt]

June7, that's correct. I want all lines to display even if it isn't there or is not in the time specified. The dB is at the top of post 5.

I am filtering by the Inquires.Date Sent field.

[ssanfu]

Actual_ID is linked by a 1 to many relationship to the Actual_ID field in the Inquries Table.

Well, one problem is that Actual.Actual_ID is type Long and Inquries.Actual_ID is type String (Text) . So the tables cannot be linked on these two fields.

This also means that your tables are not normalized. You are storing the same text from Actual.Actual in Inquries.Actual_ID.

And you have lookup fields in your tables. See http://access.mvps.org/access/lookupfields.htm
I never use lookup fields. To me, setting up a lookup field is redundant (and a waste if time) - you have to do the same set up when you put a combo box on a form.

[Thompyt]

Before I posted the dB i unlinked the actual table to simplify and changed it to text. I read up on the lookup

[June7]

If you save the descriptive text into Inquires then the Actual_ID field in Actual table serves no purpose. Didn't really help us any to remove the link and change field. I also never build lookups in table. It only causes confusion when viewing tables and queries. The only purpose it serves is when fields are added to form or report from Field List, the properties set in table will be adopted.

One way to include lines for all Actual values in the report requires a dataset of all possible combinations of the fields used to filter report and the Actual values then join that dataset to the Inquires table join type "Include all records from {master dataset} and only those from {Inquires} that match". When the filter is applied, there will be records for all Actual values, even the ones with no data in Inquires.

A variation on the above may be to do a query that does all the filtering and/or aggregate calcs of Inquires then join that query to the Actual table - join type "Include all records from Actual and only those from {query} that match".

Another approach is using domain aggregate calcs on report. I do this for a report that does an annual summation of data. I want a row for each month even if there are no records for a month. Twelve textboxes with DSum() expression.

[Thompyt]

OK Gents, I have eliminated the lookup tables in the Inquires Table. But it still doesn't let me lose a data in that month and keep the row across for all the rest in my reports. For some reason, I get a circular argument when I try something like =Nz([Attorney),0) in the Inquirent by Month Report.

Attached is the updated version.Data_Exp7.zip

[June7]

Circular reference because textbox has same name as the field.

My comments in previous post still apply.

[Thompyt]

June7,
Thanks, I fixed the circular reference. I did do the former to get the counts and sums to equal 0. The issue I see is that the 6 month window broken into months is revolving. I am trying to figure a way to run an update query with a macro so that there's records update automatically when running the reports. The latter part of idea 1 I don't quite understand, include all records from actual. Inquires is the main table and actual provides the choices for tha field. Remember I'm a novice and only started playing with access two-three months ago.

Why does access provide lookup options in a table where most seem to disapprove of that option?

[June7]

Lookups in table because it is possible to build and use db without forms and if you are the only user, then do what you want for yourself. But if developing for others and multiple users, user friendly is the key phrase. Working directly with forms and queries from the navigation pane is not user friendly.

If you want an Actual category to show in report even when there is no data for that category in Inquires, the 3 options described are the only ones I know. Two of them involve including the Actual table in the report RecordSource.