SQL Union Query help

[Guest]

I am a novice with Access. I have a table that keeps track of leads for many
people in the same group. Every person has a goal to try to meet during the
year. I need to keeps track of if they gave a lead, how many left to meet
their goal and the status of the lead (contacted, not contacted, sold to, did
not sell to). The table only holds the information of submitted leads, lead
info and the status. The status is displayed with only numbers for the above
fields (contacted 4, sold 2, etc.) I had to create 2 queries for each because
I had it sum the person's totals, but it counted only by date. So I created
another query to add those numbers together. (If there is an easier way to do
this, please let me know.) I need to compile a report with these multiple
queries. I have NO CLUE how to write SQL statements. I need the report to
display everyone, even if they didn't submit anything, but I don't know how
to set up a union query to do this for the report.

 

[Guest]

You really only need one query structured correctly. An easy way to do this
is to use Calculated Fields. That is, rather than useing a field name in the
query, you make one up and use some basic logic to determine what value you
want returned. In this case, you will want to use a Totals query. If you are
unfamiliar with what that is, on the tool bar, you will see an icon that
looks a lot like the greek letter Zeta (I think), anyway its tool tipe says
Totals.
Now for separating the different status conditions (contacted, not
contacted, sold to, did not sell to), each, as you said, has a value. We can
create a column for each status, and put a value in it like this. In the
Field row of the query builder enter somthing like this:

Sold: IIf([MyTable]![Status] = 2, 1, 0) | Contacted: IIf([MyTable]![Status]
=4, 1, 0)

What happens at this point is that in the calculated field Sold, If the
field that has the status code = 2, then this row will have the value 1 in
the Sold field and 0 in the other Calculated status fields.

Now in you Totals query, you will see a row named Total. It will default to
Group By. This means that all the records in the table where the field for
this column are the same will be combined into one record. If you have two
fields that Say Group By, it will combine all records where both fields are
the same. For example, say you want your report to be by Person and Month.
You would have a field for Person and a field for Month. Let's say Jim was
there from January to May. You would see five records for Jim, once for each
month he was there.

So use the Group by to Control how the records will be rolled up together.
Then, instead of Group By for the Status fields, use Sum. Since we have
assigned a value of 1 for each time the status in the table is equal to the
column in our query, the sum will actually count the number of time the
Status was Sold and the number of times the status was Contacted.

Hope this gives you a start. Post back if you need more info.

 

[Guest]

Thank you for helping. More questions: my colums don't have numbers in them
(I forgot to tell this). They will either have a date or a text [yes (type of
product), or No]. Will this format still work? Also, what does Status and the
numbers do in the expression?

You really only need one query structured correctly. An easy way to do this
is to use Calculated Fields. That is, rather than useing a field name in the
query, you make one up and use some basic logic to determine what value you
want returned. In this case, you will want to use a Totals query. If you are
unfamiliar with what that is, on the tool bar, you will see an icon that
looks a lot like the greek letter Zeta (I think), anyway its tool tipe says
Totals.
Now for separating the different status conditions (contacted, not
contacted, sold to, did not sell to), each, as you said, has a value. We can
create a column for each status, and put a value in it like this. In the
Field row of the query builder enter somthing like this:

Sold: IIf([MyTable]![Status] = 2, 1, 0) | Contacted: IIf([MyTable]![Status]
=4, 1, 0)

What happens at this point is that in the calculated field Sold, If the
field that has the status code = 2, then this row will have the value 1 in
the Sold field and 0 in the other Calculated status fields.

Now in you Totals query, you will see a row named Total. It will default to
Group By. This means that all the records in the table where the field for
this column are the same will be combined into one record. If you have two
fields that Say Group By, it will combine all records where both fields are
the same. For example, say you want your report to be by Person and Month.
You would have a field for Person and a field for Month. Let's say Jim was
there from January to May. You would see five records for Jim, once for each
month he was there.

So use the Group by to Control how the records will be rolled up together.
Then, instead of Group By for the Status fields, use Sum. Since we have
assigned a value of 1 for each time the status in the table is equal to the
column in our query, the sum will actually count the number of time the
Status was Sold and the number of times the status was Contacted.

Hope this gives you a start. Post back if you need more info.

I am a novice with Access. I have a table that keeps track of leads for many
people in the same group. Every person has a goal to try to meet during the
year. I need to keeps track of if they gave a lead, how many left to meet
their goal and the status of the lead (contacted, not contacted, sold to, did
not sell to). The table only holds the information of submitted leads, lead
info and the status. The status is displayed with only numbers for the above
fields (contacted 4, sold 2, etc.) I had to create 2 queries for each because
I had it sum the person's totals, but it counted only by date. So I created
another query to add those numbers together. (If there is an easier way to do
this, please let me know.) I need to compile a report with these multiple
queries. I have NO CLUE how to write SQL statements. I need the report to
display everyone, even if they didn't submit anything, but I don't know how
to set up a union query to do this for the report.