adding 2 fields including null entries

[Jesse]

..everything seems to work. i went through and changed the query, in Word, for
all the other fields in my table and everything seems to be working. one more
final question that might not be as difficult. Is there anyway i can set up a
query or report that searches every record in my Start Up table and groups
all the records in which a specific name is found in any field. Almost like a
Find function that if the name is anywhere in any field of the record, that
record is displayed. Thanks a ton for the query help. I can't tell you how
gracious i am.

 

[Paul Frey via AccessMonster.com]

Ted,

My name is Paul and I have a similar issue to Jesse's. I read your reply about using a union query but I am having trouble understanding the code. I have one table (Recipient Menu) and two of its columns (MainDish1) & (MainDish2) can have results of the same name in both. I need to do a name count for both columns with one total for each name. I would appreciate any assistance that you could provide.

Thanks,
Paul

 

[Ted Allen]

Hi Paul,

A basic union query should give you what you need. To put together a union
query, you just create two (or more) select queries, and then add the word
UNION between them. The union query will use the field names from the first
query, and the fields will be combined according to their order.

In your case, I think that all you would need to be able to get your summary
counts is a query that gives the original field name, and the field value.
You could then run any kind of summary query you wanted.

The trickiest thing about creating UNION queries when you are new to them is
learning the SQL syntax. But, as a tool, you can use the regular query
design window to set up the query, then switch to sql view to see the sql.
If you do that with the two queries, you can then create a third query,
define it as a union query, paste the first query sql, delete the ; at the
end, hit return to add a new line below, type "UNION ALL" (a regular UNION
will dump duplicates, UNION ALL will not), and then paste the sql of the
second query.

In your case, it would probably look something like this:

(Recipient Menu) and two of its columns (MainDish1) & (MainDish2)

SELECT "MainDish1" AS SourceField, RM.MainDish1 AS MainDish FROM [Recipient
Menu] AS RM
UNION ALL
SELECT "MainDish2", RM.MainDish2 FROM [Recipient Menu] AS RM
ORDER BY MainDish, SourceField;

You can try pasting this sql into the sql view of a new query, It should
work for you if I didn't make any typos (which I often do). If you wanted to
get the total of each type for each field, you could create a query based on
this query to group by SourceField and by MainDish, and count the MainDish
field, or if you wanted the total in both fields, you could just group by
MainDish and Count MainDish.

Of course, the query could be written in such a way to do all of this at
once, but it does get more complex and harder to follow the SQL if you aren't
used to SQL syntax. Post back if you really want to do it all at once and I
can help with that. If you do though, post a little more info on what you
want to see as far as the totals go (do you want one line per main dish, with
subtotal columns for each field, and then a total column, whether you want to
count or omit nulls, etc).

HTH, Ted Allen