How do I prepare a query where only the fields with information already added are included in the report?

How do I prepare a query where I will have a book-type index in alpha order, different from the record mentioned in 2. I have 4 fields that need to be indexed, not the rest of the fields. Do I need to prepare a field with a record number that I would populate it as I go along, so that I can then go back to the final report containing all the information.

This depends on several factors.

We'll start with simplest scenario and move to more complicated one.


If the data in question is numeric, then it is quite simple; we only have to allow null values to make it 'blank'. Therefore for a query that works across a column that may contain null values, we can exclude all null by doing this:

SELECT *
FROM aTable
WHERE aColumn IS NOT NULL;

We can also undergo the burden of excluding null by setting the field's "Required" property in table design to 'Yes' which means it cannot accept a null value. In case where we specify a default values (note that Access will assign a default value of zero to any numeric data type), that will be used in cases where user does not type in explicit value. To prevent that behavior, we would clear the Default Value property which has the effect of forcing the users to fill in the value before it even can be saved.


It gets dicey when we deal with textual data type because strings alternatively can accept a 'Zero-Length String', which is basically this: ""

But ZLS and null aren't same! To exclude both ZLS and null in a query, we must do something like this: \
SELECT *
FROM aTable
WHERE aColumn & "" <> "";

This basically concatenates the string in a column with ZLS. If it contained a string "Apple", "Apple" & "" = "Apple" (e.g. no change at all). Likewise, "" & "" = "" (and we know it's a ZLS and thus excluded). & operator works even with null values, so Null & "" = "" which still is a ZLS and thus excluded. There are several other way to test for both ZLS and null so this is only one of several possible approaches.

Fortunately, Access provides us a provide to exclude ZLS by specifying the property "Allow Zero Length Strings" for any textual data type in table design view, and thus freeing us from the burden if so desired.

Finally, it must be understood that any kind of query provides no guarantees of what order it may return the result in. The only reliable way is to explicitly declare ORDER BY in the query:

SELECT *
FROM aTable
ORDER BY aColumn

(or to get the result in descending order:)
This depends on several factors.

We'll start with simplest scenario and move to more complicated one.


If the data in question is numeric, then it is quite simple; we only have to allow null values to make it 'blank'. Therefore for a query that works across a column that may contain null values, we can exclude all null by doing this:

SELECT *
FROM aTable
WHERE aColumn IS NOT NULL;

We can also undergo the burden of excluding null by setting the field's "Required" property in table design to 'Yes' which means it cannot accept a null value. In case where we specify a default values (note that Access will assign a default value of zero to any numeric data type), that will be used in cases where user does not type in explicit value. To prevent that behavior, we would clear the Default Value property which has the effect of forcing the users to fill in the value before it even can be saved.


It gets dicey when we deal with textual data type because strings alternatively can accept a 'Zero-Length String', which is basically this: ""

But ZLS and null aren't same! To exclude both ZLS and null in a query, we must do something like this: \
SELECT *
FROM aTable
WHERE aColumn & "" <> "";

This basically concatenates the string in a column with ZLS. If it contained a string "Apple", "Apple" & "" = "Apple" (e.g. no change at all). Likewise, "" & "" = "" (and we know it's a ZLS and thus excluded). & operator works even with null values, so Null & "" = "" which still is a ZLS and thus excluded. There are several other way to test for both ZLS and null so this is only one of several possible approaches.

Fortunately, Access provides us a provide to exclude ZLS by specifying the property "Allow Zero Length Strings" for any textual data type in table design view, and thus freeing us from the burden if so desired.

Finally, it must be understood that any kind of query provides no guarantees of what order it may return the result in. The only reliable way is to explicitly declare ORDER BY in the query:
This depends on several factors.

We'll start with simplest scenario and move to more complicated one.


If the data in question is numeric, then it is quite simple; we only have to allow null values to make it 'blank'. Therefore for a query that works across a column that may contain null values, we can exclude all null by doing this:

SELECT *
FROM aTable
WHERE aColumn IS NOT NULL;

We can also undergo the burden of excluding null by setting the field's "Required" property in table design to 'Yes' which means it cannot accept a null value. In case where we specify a default values (note that Access will assign a default value of zero to any numeric data type), that will be used in cases where user does not type in explicit value. To prevent that behavior, we would clear the Default Value property which has the effect of forcing the users to fill in the value before it even can be saved.


It gets dicey when we deal with textual data type because strings alternatively can accept a 'Zero-Length String', which is basically this: ""

But ZLS and null aren't same! To exclude both ZLS and null in a query, we must do something like this: \
SELECT *
FROM aTable
WHERE aColumn & "" <> "";

This basically concatenates the string in a column with ZLS. If it contained a string "Apple", "Apple" & "" = "Apple" (e.g. no change at all). Likewise, "" & "" = "" (and we know it's a ZLS and thus excluded). & operator works even with null values, so Null & "" = "" which still is a ZLS and thus excluded. There are several other way to test for both ZLS and null so this is only one of several possible approaches.

Fortunately, Access provides us a provide to exclude ZLS by specifying the property "Allow Zero Length Strings" for any textual data type in table design view, and thus freeing us from the burden if so desired.

Finally, it must be understood that any kind of query provides no guarantees of what order it may return the result in. The only reliable way is to explicitly declare ORDER BY in the query:
SELECT *
FROM aTable
ORDER BY aColumn

(or to get it in descending order:)
SELECT *
FROM aTable
ORDER BY aColumn DESC;

Hope that gets you started.

Thanks very much for your thorough answer. It was just what I needed!