how to do query

  • Thread starter Thread starter Bill H.
  • Start date Start date
B

Bill H.

I've been able to get my query (which uses two table) down to results that
look like this:


ID Lead
1 AA
2 BB
2 None
3 CC
4 None
5 DD
5 DD

What I need now is to have the query return a "Lead" if there is one, or if
"None" to return "None" but not to return both "Lead" and "None" as it does
for IDs 2 and 5.

Thanks.
 
Try designing a totals query(View/Totals on the main menu in query design
view). Add the ID field and the Lead field to the query grid. In the Totals
row under ID set it to Group By. Under Lead set it to Max. Think this is
what you're looking for. If not post back.
 
well...I don't know.

The query is really formed from three tables and one summation query, so
don't know exactly how to implement your idea.

I'll keep working on it for a bit.

Thanks.

Reggie said:
Try designing a totals query(View/Totals on the main menu in query design
view). Add the ID field and the Lead field to the query grid. In the Totals
row under ID set it to Group By. Under Lead set it to Max. Think this is
what you're looking for. If not post back.
Click to expand...
 
Well, I think I set up a test query, to try to understand what is going on
with your idea, but it doesn't work as I need.

For example, if ID 2 had both a set of initials and a "none", then only the
"none" shows up. I need the reverse. :-)

And I don't understand what I'm getting the "max" of.

And of course, the initials could be anything, including "NO" for example,
or "ZD"


Reggie said:
Try designing a totals query(View/Totals on the main menu in query design
view). Add the ID field and the Lead field to the query grid. In the Totals
row under ID set it to Group By. Under Lead set it to Max. Think this is
what you're looking for. If not post back.
Click to expand...
 
My misunderstanding. When you wrote None I thought you were meaning that
the field was empty/blank. I also thought that with each ID it had a(1)
lead initials or it was blank. Therefore if you grouped by ID and set the
Lead to Max it would find the first occurrence of the initials. If there
wasn't a max returned that meant that the lead initials for that entire
group was blank and that would have been shown in the result as well. Sorry
I can't help further. Don't know how to solve this one. Maybe someone else
can help.

--
Reggie

www.smittysinet.com
----------
Bill H. said:
Well, I think I set up a test query, to try to understand what is going on
with your idea, but it doesn't work as I need.

For example, if ID 2 had both a set of initials and a "none", then only the
"none" shows up. I need the reverse. :-)

And I don't understand what I'm getting the "max" of.

And of course, the initials could be anything, including "NO" for example,
or "ZD"
Click to expand...
 
Thanks.

I guess what I would like to have is this: Where ID numbers are the same,
show only the initials and not the "none" value.
 
Sorry, but don't know how to solve that one. Just thought of something
though. What if you were to run an update query to change all the None's to
Null. Simply add the Lead field to the update query grid. Set the criteria
to ="None" and set the Update To line to Null. Then the query I described
would work. You could then design a query based using the query I described
as the Source Data for the query. Type the following in the Field line to
show None for those records that are blank if you would like.

Lead: IIf(Nz([Lead],"")="","None",[Lead])

Just a clumsy thought!!

--
Reggie

www.smittysinet.com
----------
Bill H. said:
Thanks.

I guess what I would like to have is this: Where ID numbers are the same,
show only the initials and not the "none" value.
Click to expand...
 
Try using a sub-query with a Top clause to identify the record you need. This
could be slow.

SELECT Main.ID, Main.Lead
FROM TheTable as Main
WHERE Main.Lead In (
Select Top 1 Lead
From TheTable as T
WHERE T.ID = Main.ID
ORDER BY Lead = "None")



That should get
 
Back
Top