Null Values Problem

[DrStephen]

I have two tables: 'T Drugs' and 'T Removed'. 'T Drugs' is a list of 100 drugs that can be used. 'T Removed' is a table that records drugs actually used. This table contains a date field.

I want a report of all drugs used between two entered dates. The report, however, has to include all drugs available (i.e., even if a drug was not used, it should be listed in the report). I'm going to sum a quantity used field in a later report; The quantity of a non-used drug would be zero, of course.

I created a select query that joins 'T Drugs.ID' and 'T Removed.Drug Used'. Drug used is, in fact, the ID field from T Drugs. I set join properties to include ALL records from T Drugs and only those records from T Removed where the joined fields are equal. So far, so good. I added criteria to the query to select all records in which the date is between two entered dates (i.e., Between [Enter Start Date] and [Enter End Date])OR the date is null (because it's a drug that wasn't used). I am expected to see a return that includes every drug used during the two dates plus all the drugs that were not used. Excluded only should be records where the dates are outside of the two entered dates.

What is returned is the drugs actually used plus a small subset of non-used drugs. Basically, the query is behaving as if the value of the date field for non-used drugs is not null. So...two questions:

(1) why are some of the 'non-used drug date' fields null and other act as if they are not null?
(2) how do I make it work?

Thanks.

 

[Hmadyson]

What I would do is create 2 queries for your report. The first one would only grab records from T Removed that fall into your date range. You can ignore the null, because you do not want any to have null in that table. From this query, you can also create an aggregate query that will give you your counts.

Now the second query is built upon the first query and the T drugs table. Here you will do the left join in order to get all drugs from the T drugs table.

The reason that the prior report was not working was because some of the drugs that were null in this month were not null last month, so the join occurred first which gave you null records and non null records, and then the records were later reduced by looking at the date ranges. Does this make sense?

Please let me know if I can provide more assistance.

 

[DrStephen]

...worked like a charm. I was leaning in that direction, but probably would have used a make-table query rather than a select (for no good reason)...

...thanks.