Yes/No Fields, a Query, and a Macro

[ryguy7272]

I am new to using yes/no fields. I have two tables:
My ‘goals’ table has these fields:
IDKey, Employee, and Goals
My ‘Mapping’ table has these fields:
ID, Name, RVP, and Changed (this is the yes/no field).

I have a query, named ‘LookupChanges’ and the SQL is as such:
SELECT Goals.IDKey, Goals.Employee, Sum(Goals.Goals) AS SumOfGoals,
Mapping.RVP
FROM Goals LEFT JOIN Mapping ON Goals.IDKey = Mapping.ID
GROUP BY Goals.IDKey, Goals.Employee, Mapping.RVP;

Pretty simple stuff. Well, in all honest, the query may be irrelevant right
now. I was hoping to find a way of setting this up so a user, a colleague of
mine, could put checks in boxes in the yes/no field to indicate changes to
our other colleague’s goals. I was hoping to come up with a way of checking
a box next to a name, and linking this to some VBA code, so when the macro
fires, all the names with checks in the yes/no fields will be used in the
query. Then we will know which employees goals changed, as well as all
employees who report to these employees, so all goals will be updated
simultaneously. I hope this makes sense. I have lots of experience with
Access, but almost no experience with the yes/no fields. If there is a
sample DB out there somewhere, which may do what I am describing here, please
send me a link to it so I can see the structure of the DB, the logic, and the
code.

Regards,
Ryan---

 

[Ken Snell \(MVP\)]

Perhaps this type of query is what you seek:

SELECT Goals.IDKey, Goals.Employee,
Sum(Goals.Goals) AS SumOfGoals,
Mapping.RVP
FROM Goals
INNER JOIN Mapping ON
Goals.IDKey = Mapping.ID
WHERE Goals.IDKey IN
(SELECT ID
FROM Mapping
WHERE Changed = True);


Another way of doing this is to make a new query from Mapping that returns
just the records where the Changed field is True (same as Yes setting), and
use that new query in the above query:

qryMappingChanges
SELECT * FROM Mapping
WHERE Changed = True;


SELECT Goals.IDKey, Goals.Employee,
Sum(Goals.Goals) AS SumOfGoals,
qryMappingChanges.RVP
FROM Goals
INNER JOIN
qryMappingChanges ON
Goals.IDKey = qryMappingChanges.ID

 

[ryguy7272]

Thanks Ken; this is clever. The following SQL works just fine:
SELECT * FROM Mapping
WHERE Changed = True;

I tried this:
SELECT Goals.IDKey, Goals.Employee, Sum(Goals.Goals) AS SumOfGoals,
qryMappingChanges.RVP
FROM Goals
INNER JOIN qryMappingChanges ON Goals.IDKey = qryMappingChanges.ID;

Message Returned 'You tried to execute a query that does not include the
specific expression 'IDKey' as part of an aggregate function.' I tried to
fiddle with it, but couldn't get it to work.

I tried this SQL:
SELECT Goals.IDKey, Goals.Employee, Sum(Goals.Goals) AS SumOfGoals,
Mapping.RVP
FROM Goals
INNER JOIN Mapping ON Goals.IDKey = Mapping.ID WHERE Goals.IDKey IN
(SELECT ID FROM Mapping WHERE Changed = True);

Received the same message. What am I doing wrong?

Thanks,
Ryan---

 

[John Spencer]

You need to GROUP BY any field that you are not using one of the
aggregate functions (Sum,Avg, Count, ...) on.

SELECT Goals.IDKey, Goals.Employee
, Sum(Goals.Goals) AS SumOfGoals,
qryMappingChanges.RVP
FROM Goals INNER JOIN qryMappingChanges
ON Goals.IDKey = qryMappingChanges.ID
GROUP BY Goals.IDKey, Goals.Employee

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[ryguy7272]

Thanks John! Thanks again, Ken! Both posts were helpful. I switched to
'Design View', which I am a little more comfortable with, and used the QBE to
get the SQL working. the last step may be very difficult or very easy; not
sure... Please bear with me a little more.

In my ‘Mapping’ Table, I have these Fields: ID, Name, RVP, and Changed. In
my ‘Goals’ Table I have IDKey, Employee, and Goals. In the ‘Mapping’ table,
the employees listed in the ‘Name’ Field report to the employees in the ‘RVP’
Field. I would like to be able to check a yes/no box in the ‘Changed’ Field,
and do something such as this… Check yes/no box, Ben reports to Beth, Dave,
and Paul (yes one employee can report to several higher-ups), and take the
names (because the yes/no) box is checked and say Beth, Dave, and Paul are
True. Then, since Beth, Dave, and Paul are all listed in the ‘Employee’
Field in the ‘Goals’ Table, sum all of the ‘Goals’ for these three
individuals, and indicate that Ben’s Goals are sum (because he reports to
these three people). Does that make sense? I’ve been working on this for
about a week in Excel, and with some eloquent VBA code, almost got it
working, but not quite. I though I’d try to do it in Access, and because of
the power of the Queries, and all relationships, etc., I though it may be
easier. I’m sure this is doable; just not exactly sure how to do it. If
more description is needed, I can supply it. If someone needs to see the DB,
I can zip it and email it. Anyway, if someone can help me get over this last
hurdle I’d be most appreciative.

Regards,
Ryan---

 

[Ken Snell \(MVP\)]

ryguy,

It's not clear to me what you want to achieve in this "final" stage. Can you
post some examples of the data from the tables, and examples of what you
want to see in the resulting query when the Changed box = True? That should
help us better understand what you want to achieve.