Complex? query question

C

CEV

SELECT tblEmployees.DateLeftAgency, tblExitSurvey.ReasonPay,
tblExitSurvey.ReasonEnvironment, tblExitSurvey.ReasonSupervisor,
tblExitSurvey.ReasonCoworkers, tblExitSurvey.ReasonEducation,
tblExitSurvey.ReasonTemp, tblExitSurvey.ReasonNewJob,
tblExitSurvey.ReasonPersonal, tblExitSurvey.ReasonRelocation,
tblExitSurvey.ReasonOther, tblExitSurvey.NewJobBenefits,
tblExitSurvey.NewJobPay, tblExitSurvey.NewJobOther,
tblExitSurvey.AgencyOrient, tblExitSurvey.JobOrient, tblExitSurvey.Training,
tblExitSurvey.DeptComm, tblExitSurvey.WorkHours, tblExitSurvey.PhysWorkCond,
tblExitSurvey.EnvWorkCond, tblExitSurvey.Supervisor,
tblExitSurvey.AgencyComm, tblExitSurvey.Salary, tblExitSurvey.PromoOppor,
tblExitSurvey.PandP

FROM tblEmployees INNER JOIN tblExitSurvey ON tblEmployees.EmployeeNumber =
tblExitSurvey.EmployeeNumber

GROUP BY tblEmployees.DateLeftAgency, tblExitSurvey.ReasonPay,
tblExitSurvey.ReasonEnvironment, tblExitSurvey.ReasonSupervisor,
tblExitSurvey.ReasonCoworkers, tblExitSurvey.ReasonEducation,
tblExitSurvey.ReasonTemp, tblExitSurvey.ReasonNewJob,
tblExitSurvey.ReasonPersonal, tblExitSurvey.ReasonRelocation,
tblExitSurvey.ReasonOther, tblExitSurvey.NewJobBenefits,
tblExitSurvey.NewJobPay, tblExitSurvey.NewJobOther,
tblExitSurvey.AgencyOrient, tblExitSurvey.JobOrient, tblExitSurvey.Training,
tblExitSurvey.DeptComm, tblExitSurvey.WorkHours, tblExitSurvey.PhysWorkCond,
tblExitSurvey.EnvWorkCond, tblExitSurvey.Supervisor,
tblExitSurvey.AgencyComm, tblExitSurvey.Salary, tblExitSurvey.PromoOppor,
tblExitSurvey.PandP

HAVING (((tblEmployees.DateLeftAgency) Between
[Forms]![frmReports]![txtBeginDate] And [Forms]![frmReports]![txtEndDate]));



This is the SQL view of the query I made so I can try to come up with the
report that I would like to get. This query of course does not do it. I am
not at all familiar with creating SQL queries. I have always created queries
in the design view.



The field's ReasonPay, ReasonEnvironment, ReasonSupervisor, ReasonCoworkers,
ReasonEducation, ReasonTemp, ReasonNewJob, ReasonPersonal, ReasonRelocation,
ReasonOther, NewJobBenefits, NewJobPay, NewJobOther are Yes/No checkboxes on
the form. I want the query to count how many records are checked Yes for
each field for the dates I specify. It would be great if I could also have
it calculate the percentage of records marked Yes for each of these fields.



The field's AgencyOrient, JobOrient, Training, DeptComm, WorkHours,
PhysWorkCond, EnvWorkCond, Supervisor, AgencyComm, Salary, PromoOppor, PandP
are fields that will contain a single number of either 1,2,3,4,or 5. What I
would like to do with these fields on the report is be able to display how
many are marked with either a 1 or 2, how many are marked with a 3, and how
many are marked with either a 4 or 5.



In the design view of the query I have tried both the Sum option and the
Count option in the Total row, but neither one does what I want it to. What
I am currently getting is a seperate page for each record that shows all the
checkboxes for each field or the number entered for the other fields. I'm
assuming that there must be something more complex I need to enter while in
the SQL view. If anyone can help me out with this, I would greatly
appreciate it!!



Thanks



Chad
 
C

Charles Wang[MSFT]

Hi Chad,
My understanding of your issue is:
You want to combine those different aggregated queries into one view, so
that you can see the whole result of how many records checked Yes, how many
records marked 1 or 2, how many records marked 3 and how many records
marked 4 or 5 on those specific columns.
If I have misunderstood, please let me know.

This complex query need to be departed into several sub-views and then
combine them into one view. I would like to share my thoughts with you:
/*Get the columns records count with Yes (1) value*/
CREATE View v_getCheckYes
AS
SELECT tblEmployees.DateLeftAgency, SUM(tblExitSurvey.ReasonPay),
SUM(tblExitSurvey.ReasonEnvironment), SUM(tblExitSurvey.ReasonSupervisor),
SUM(tblExitSurvey.ReasonCoworkers), SUM(tblExitSurvey.ReasonEducation),
SUM(tblExitSurvey.ReasonTemp), SUM(tblExitSurvey.ReasonNewJob),
SUM(tblExitSurvey.ReasonPersonal), SUM(tblExitSurvey.ReasonRelocation),
SUM(tblExitSurvey.ReasonOther), SUM(tblExitSurvey.NewJobBenefits),
SUM(tblExitSurvey.NewJobPay), SUM(tblExitSurvey.NewJobOther)
FROM tblEmployees INNER JOIN tblExitSurvey ON tblEmployees.EmployeeNumber =
tblExitSurvey.EmployeeNumber
GROUP BY tblEmployees.DateLeftAgency;

/*A combined view of separately getting the columns records count with
(1,2),3 and (4,5)*/
CREATE VIEW v_getCountAgencyOrient
AS
SELECT
ISNULL(ISNULL(v1.DateLeftAgency,v2.DateLeftAgency),v3.DateLeftAgency)
DateLeftAgency,
v1.AgentOrientCount12,v2.AgentOrientCount3,v3.AgentOrientCount45
FROM
(
SELECT tblEmployees.DateLeftAgency,COUNT(tblExitSurvey.AgencyOrient) AS
AgentOrientCount12
FROM tblEmployees INNER JOIN tblExitSurvey ON tblEmployees.EmployeeNumber =
tblExitSurvey.EmployeeNumber WHERE tblExitSurvey.AgencyOrient IN (1,2)
GROUP BY tblEmployees.DateLeftAgency
) v1 FULL OUTER JOIN
(
SELECT tblEmployees.DateLeftAgency,COUNT(tblExitSurvey.AgencyOrient) AS
AgentOrientCount3
FROM tblEmployees INNER JOIN tblExitSurvey ON tblEmployees.EmployeeNumber =
tblExitSurvey.EmployeeNumber WHERE tblExitSurvey.AgencyOrient = 3
GROUP BY tblEmployees.DateLeftAgency
) v2 ON V1.DateLeftAgency = v2.DateLeftAgency
FULL OUTER JOIN
(
SELECT tblEmployees.DateLeftAgency,COUNT(tblExitSurvey.AgencyOrient) AS
AgentOrientCount45
FROM tblEmployees INNER JOIN tblExitSurvey ON tblEmployees.EmployeeNumber =
tblExitSurvey.EmployeeNumber WHERE tblExitSurvey.AgencyOrient IN (4,5)
GROUP BY tblEmployees.DateLeftAgency
) v3 ON v1.DateLeftAgency = v3.DateLeftAgency

...

CREATE VIEW v_whole
AS
SELECT ISNULL(ISNULL(ISNULL(...))) AS DateLeftAgency,
v_getCheckYes.ReasonPayCount,...,v_getCountAgencyOrient.AgentOrientCount12,.
..,v_getCountXXX.xxx12,...
FROM v_getCheckYes FULL OUTER JOIN v_getCountAgencyOrient
ON v_getCheckYes.DateLeftAgency = v_getCountAgencyOrient.DateLeftAgency
FULL OUTER JOIN ...
ON...
...

This is indeed a complex query. :)

However, I found that your database seemed to be Access which doesn't
support View. In this case, you may need to create queries based on other
sub-queries.

If you have any other questions or concerns, please feel free to let me
know. It's my pleasure to be of assistance.

Charles Wang
Microsoft Online Community Support

======================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================
 
D

Duane Hookom

Your main issue is that your tblExitSurvey is not normalized. You are
storing data values in field names. If you want to add another "reason" you
should not have to add a fields to tables and queries or controls on forms.

You can normalize this on-the-fly with a union query like
SELECT EmployeeNumber, "Pay" as Attribute, ReasonPay as theValue
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "Environmental", ReasonEnvironment
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "Supervisor", ReasonSupervisor
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "Coworkers", ReasonCoworkers
FROM tblExitSurvey
UNION ALL
--etc--;
SELECT EmployeeNumber, "AgencyOrient", AgencyOrient
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "JobOrient", JobOrient
FROM tblExitSurvey
UNION ALL
---etc--
,
You can then create a totals or crosstab query from the above union query
that groups by Attribute and TheValue to get counts, percentages, etc.

Once your table is normalized, you have much more flexibility with
publishing the data.

--
Duane Hookom
MS Access MVP


CEV said:
SELECT tblEmployees.DateLeftAgency, tblExitSurvey.ReasonPay,
tblExitSurvey.ReasonEnvironment, tblExitSurvey.ReasonSupervisor,
tblExitSurvey.ReasonCoworkers, tblExitSurvey.ReasonEducation,
tblExitSurvey.ReasonTemp, tblExitSurvey.ReasonNewJob,
tblExitSurvey.ReasonPersonal, tblExitSurvey.ReasonRelocation,
tblExitSurvey.ReasonOther, tblExitSurvey.NewJobBenefits,
tblExitSurvey.NewJobPay, tblExitSurvey.NewJobOther,
tblExitSurvey.AgencyOrient, tblExitSurvey.JobOrient,
tblExitSurvey.Training, tblExitSurvey.DeptComm, tblExitSurvey.WorkHours,
tblExitSurvey.PhysWorkCond, tblExitSurvey.EnvWorkCond,
tblExitSurvey.Supervisor, tblExitSurvey.AgencyComm, tblExitSurvey.Salary,
tblExitSurvey.PromoOppor, tblExitSurvey.PandP

FROM tblEmployees INNER JOIN tblExitSurvey ON tblEmployees.EmployeeNumber
= tblExitSurvey.EmployeeNumber

GROUP BY tblEmployees.DateLeftAgency, tblExitSurvey.ReasonPay,
tblExitSurvey.ReasonEnvironment, tblExitSurvey.ReasonSupervisor,
tblExitSurvey.ReasonCoworkers, tblExitSurvey.ReasonEducation,
tblExitSurvey.ReasonTemp, tblExitSurvey.ReasonNewJob,
tblExitSurvey.ReasonPersonal, tblExitSurvey.ReasonRelocation,
tblExitSurvey.ReasonOther, tblExitSurvey.NewJobBenefits,
tblExitSurvey.NewJobPay, tblExitSurvey.NewJobOther,
tblExitSurvey.AgencyOrient, tblExitSurvey.JobOrient,
tblExitSurvey.Training, tblExitSurvey.DeptComm, tblExitSurvey.WorkHours,
tblExitSurvey.PhysWorkCond, tblExitSurvey.EnvWorkCond,
tblExitSurvey.Supervisor, tblExitSurvey.AgencyComm, tblExitSurvey.Salary,
tblExitSurvey.PromoOppor, tblExitSurvey.PandP

HAVING (((tblEmployees.DateLeftAgency) Between
[Forms]![frmReports]![txtBeginDate] And
[Forms]![frmReports]![txtEndDate]));



This is the SQL view of the query I made so I can try to come up with the
report that I would like to get. This query of course does not do it. I am
not at all familiar with creating SQL queries. I have always created
queries in the design view.



The field's ReasonPay, ReasonEnvironment, ReasonSupervisor,
ReasonCoworkers, ReasonEducation, ReasonTemp, ReasonNewJob,
ReasonPersonal, ReasonRelocation, ReasonOther, NewJobBenefits, NewJobPay,
NewJobOther are Yes/No checkboxes on the form. I want the query to count
how many records are checked Yes for each field for the dates I specify.
It would be great if I could also have it calculate the percentage of
records marked Yes for each of these fields.



The field's AgencyOrient, JobOrient, Training, DeptComm, WorkHours,
PhysWorkCond, EnvWorkCond, Supervisor, AgencyComm, Salary, PromoOppor,
PandP are fields that will contain a single number of either 1,2,3,4,or 5.
What I would like to do with these fields on the report is be able to
display how many are marked with either a 1 or 2, how many are marked with
a 3, and how many are marked with either a 4 or 5.



In the design view of the query I have tried both the Sum option and the
Count option in the Total row, but neither one does what I want it to.
What I am currently getting is a seperate page for each record that shows
all the checkboxes for each field or the number entered for the other
fields. I'm assuming that there must be something more complex I need to
enter while in the SQL view. If anyone can help me out with this, I would
greatly appreciate it!!



Thanks



Chad
Click to expand...
 
C

CEV

Thanks for the responses. I am not familiar with Normalization, but I will
give that a try and hopefully it will work. I'll respond again with the
outcome.

Thanks,

Chad


Duane Hookom said:
Your main issue is that your tblExitSurvey is not normalized. You are
storing data values in field names. If you want to add another "reason"
you should not have to add a fields to tables and queries or controls on
forms.

You can normalize this on-the-fly with a union query like
SELECT EmployeeNumber, "Pay" as Attribute, ReasonPay as theValue
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "Environmental", ReasonEnvironment
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "Supervisor", ReasonSupervisor
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "Coworkers", ReasonCoworkers
FROM tblExitSurvey
UNION ALL
--etc--;
SELECT EmployeeNumber, "AgencyOrient", AgencyOrient
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "JobOrient", JobOrient
FROM tblExitSurvey
UNION ALL
---etc--
,
You can then create a totals or crosstab query from the above union query
that groups by Attribute and TheValue to get counts, percentages, etc.

Once your table is normalized, you have much more flexibility with
publishing the data.

--
Duane Hookom
MS Access MVP


CEV said:
SELECT tblEmployees.DateLeftAgency, tblExitSurvey.ReasonPay,
tblExitSurvey.ReasonEnvironment, tblExitSurvey.ReasonSupervisor,
tblExitSurvey.ReasonCoworkers, tblExitSurvey.ReasonEducation,
tblExitSurvey.ReasonTemp, tblExitSurvey.ReasonNewJob,
tblExitSurvey.ReasonPersonal, tblExitSurvey.ReasonRelocation,
tblExitSurvey.ReasonOther, tblExitSurvey.NewJobBenefits,
tblExitSurvey.NewJobPay, tblExitSurvey.NewJobOther,
tblExitSurvey.AgencyOrient, tblExitSurvey.JobOrient,
tblExitSurvey.Training, tblExitSurvey.DeptComm, tblExitSurvey.WorkHours,
tblExitSurvey.PhysWorkCond, tblExitSurvey.EnvWorkCond,
tblExitSurvey.Supervisor, tblExitSurvey.AgencyComm, tblExitSurvey.Salary,
tblExitSurvey.PromoOppor, tblExitSurvey.PandP

FROM tblEmployees INNER JOIN tblExitSurvey ON tblEmployees.EmployeeNumber
= tblExitSurvey.EmployeeNumber

GROUP BY tblEmployees.DateLeftAgency, tblExitSurvey.ReasonPay,
tblExitSurvey.ReasonEnvironment, tblExitSurvey.ReasonSupervisor,
tblExitSurvey.ReasonCoworkers, tblExitSurvey.ReasonEducation,
tblExitSurvey.ReasonTemp, tblExitSurvey.ReasonNewJob,
tblExitSurvey.ReasonPersonal, tblExitSurvey.ReasonRelocation,
tblExitSurvey.ReasonOther, tblExitSurvey.NewJobBenefits,
tblExitSurvey.NewJobPay, tblExitSurvey.NewJobOther,
tblExitSurvey.AgencyOrient, tblExitSurvey.JobOrient,
tblExitSurvey.Training, tblExitSurvey.DeptComm, tblExitSurvey.WorkHours,
tblExitSurvey.PhysWorkCond, tblExitSurvey.EnvWorkCond,
tblExitSurvey.Supervisor, tblExitSurvey.AgencyComm, tblExitSurvey.Salary,
tblExitSurvey.PromoOppor, tblExitSurvey.PandP

HAVING (((tblEmployees.DateLeftAgency) Between
[Forms]![frmReports]![txtBeginDate] And
[Forms]![frmReports]![txtEndDate]));



This is the SQL view of the query I made so I can try to come up with the
report that I would like to get. This query of course does not do it. I
am not at all familiar with creating SQL queries. I have always created
queries in the design view.



The field's ReasonPay, ReasonEnvironment, ReasonSupervisor,
ReasonCoworkers, ReasonEducation, ReasonTemp, ReasonNewJob,
ReasonPersonal, ReasonRelocation, ReasonOther, NewJobBenefits, NewJobPay,
NewJobOther are Yes/No checkboxes on the form. I want the query to count
how many records are checked Yes for each field for the dates I specify.
It would be great if I could also have it calculate the percentage of
records marked Yes for each of these fields.



The field's AgencyOrient, JobOrient, Training, DeptComm, WorkHours,
PhysWorkCond, EnvWorkCond, Supervisor, AgencyComm, Salary, PromoOppor,
PandP are fields that will contain a single number of either 1,2,3,4,or
5. What I would like to do with these fields on the report is be able to
display how many are marked with either a 1 or 2, how many are marked
with a 3, and how many are marked with either a 4 or 5.



In the design view of the query I have tried both the Sum option and the
Count option in the Total row, but neither one does what I want it to.
What I am currently getting is a seperate page for each record that shows
all the checkboxes for each field or the number entered for the other
fields. I'm assuming that there must be something more complex I need to
enter while in the SQL view. If anyone can help me out with this, I would
greatly appreciate it!!



Thanks



Chad
Click to expand...
Click to expand...
 
C

CEV

You are correct Charles. I am experimenting with your suggestion of multiple
queries. I am first trying to calculate the total for the first field to see
how many are checked for ReasonPay within the specified date range. I would
then assume I need to create a seperate query for each one and then a query
that includes all those queries? I tried the following but it did not work:

SELECT tblEmployees.DateLeftAgency, Sum(Abs([ReasonPay])) AS CountOfYes
FROM tblEmployees INNER JOIN tblExitSurvey ON tblEmployees.EmployeeNumber =
tblExitSurvey.EmployeeNumber
WHERE (((tblExitSurvey.ReasonPay)=Yes))
GROUP BY tblEmployees.DateLeftAgency
HAVING (((tblEmployees.DateLeftAgency) Between
[Forms]![frmReports]![txtBeginDate] And [Forms]![frmReports]![txtEndDate]));

Where did I go wrong?

Thanks
 
D

Duane Hookom

Database table design is the foundation of your application. To understand
normalization, check some links from Jeff Conrad's site
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101.


--
Duane Hookom
MS Access MVP

CEV said:
Thanks for the responses. I am not familiar with Normalization, but I will
give that a try and hopefully it will work. I'll respond again with the
outcome.

Thanks,

Chad


Duane Hookom said:
Your main issue is that your tblExitSurvey is not normalized. You are
storing data values in field names. If you want to add another "reason"
you should not have to add a fields to tables and queries or controls on
forms.

You can normalize this on-the-fly with a union query like
SELECT EmployeeNumber, "Pay" as Attribute, ReasonPay as theValue
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "Environmental", ReasonEnvironment
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "Supervisor", ReasonSupervisor
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "Coworkers", ReasonCoworkers
FROM tblExitSurvey
UNION ALL
--etc--;
SELECT EmployeeNumber, "AgencyOrient", AgencyOrient
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "JobOrient", JobOrient
FROM tblExitSurvey
UNION ALL
---etc--
,
You can then create a totals or crosstab query from the above union query
that groups by Attribute and TheValue to get counts, percentages, etc.

Once your table is normalized, you have much more flexibility with
publishing the data.

--
Duane Hookom
MS Access MVP


CEV said:
SELECT tblEmployees.DateLeftAgency, tblExitSurvey.ReasonPay,
tblExitSurvey.ReasonEnvironment, tblExitSurvey.ReasonSupervisor,
tblExitSurvey.ReasonCoworkers, tblExitSurvey.ReasonEducation,
tblExitSurvey.ReasonTemp, tblExitSurvey.ReasonNewJob,
tblExitSurvey.ReasonPersonal, tblExitSurvey.ReasonRelocation,
tblExitSurvey.ReasonOther, tblExitSurvey.NewJobBenefits,
tblExitSurvey.NewJobPay, tblExitSurvey.NewJobOther,
tblExitSurvey.AgencyOrient, tblExitSurvey.JobOrient,
tblExitSurvey.Training, tblExitSurvey.DeptComm, tblExitSurvey.WorkHours,
tblExitSurvey.PhysWorkCond, tblExitSurvey.EnvWorkCond,
tblExitSurvey.Supervisor, tblExitSurvey.AgencyComm,
tblExitSurvey.Salary, tblExitSurvey.PromoOppor, tblExitSurvey.PandP

FROM tblEmployees INNER JOIN tblExitSurvey ON
tblEmployees.EmployeeNumber = tblExitSurvey.EmployeeNumber

GROUP BY tblEmployees.DateLeftAgency, tblExitSurvey.ReasonPay,
tblExitSurvey.ReasonEnvironment, tblExitSurvey.ReasonSupervisor,
tblExitSurvey.ReasonCoworkers, tblExitSurvey.ReasonEducation,
tblExitSurvey.ReasonTemp, tblExitSurvey.ReasonNewJob,
tblExitSurvey.ReasonPersonal, tblExitSurvey.ReasonRelocation,
tblExitSurvey.ReasonOther, tblExitSurvey.NewJobBenefits,
tblExitSurvey.NewJobPay, tblExitSurvey.NewJobOther,
tblExitSurvey.AgencyOrient, tblExitSurvey.JobOrient,
tblExitSurvey.Training, tblExitSurvey.DeptComm, tblExitSurvey.WorkHours,
tblExitSurvey.PhysWorkCond, tblExitSurvey.EnvWorkCond,
tblExitSurvey.Supervisor, tblExitSurvey.AgencyComm,
tblExitSurvey.Salary, tblExitSurvey.PromoOppor, tblExitSurvey.PandP

HAVING (((tblEmployees.DateLeftAgency) Between
[Forms]![frmReports]![txtBeginDate] And
[Forms]![frmReports]![txtEndDate]));



This is the SQL view of the query I made so I can try to come up with
the report that I would like to get. This query of course does not do
it. I am not at all familiar with creating SQL queries. I have always
created queries in the design view.



The field's ReasonPay, ReasonEnvironment, ReasonSupervisor,
ReasonCoworkers, ReasonEducation, ReasonTemp, ReasonNewJob,
ReasonPersonal, ReasonRelocation, ReasonOther, NewJobBenefits,
NewJobPay, NewJobOther are Yes/No checkboxes on the form. I want the
query to count how many records are checked Yes for each field for the
dates I specify. It would be great if I could also have it calculate the
percentage of records marked Yes for each of these fields.



The field's AgencyOrient, JobOrient, Training, DeptComm, WorkHours,
PhysWorkCond, EnvWorkCond, Supervisor, AgencyComm, Salary, PromoOppor,
PandP are fields that will contain a single number of either 1,2,3,4,or
5. What I would like to do with these fields on the report is be able to
display how many are marked with either a 1 or 2, how many are marked
with a 3, and how many are marked with either a 4 or 5.



In the design view of the query I have tried both the Sum option and the
Count option in the Total row, but neither one does what I want it to.
What I am currently getting is a seperate page for each record that
shows all the checkboxes for each field or the number entered for the
other fields. I'm assuming that there must be something more complex I
need to enter while in the SQL view. If anyone can help me out with
this, I would greatly appreciate it!!



Thanks



Chad
Click to expand...
Click to expand...
Click to expand...
 
C

CEV

I do not understand when you say you should not have to add a field to a
table or control to a form when you want to add another "reason". If you did
not do this, then when filling out the form how would you select the
checkbox for the new "reason" and also for other "reasons" when there are
more then one? That is the part that is confusing to me.

Thanks,



Duane Hookom said:
Your main issue is that your tblExitSurvey is not normalized. You are
storing data values in field names. If you want to add another "reason"
you should not have to add a fields to tables and queries or controls on
forms.

You can normalize this on-the-fly with a union query like
SELECT EmployeeNumber, "Pay" as Attribute, ReasonPay as theValue
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "Environmental", ReasonEnvironment
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "Supervisor", ReasonSupervisor
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "Coworkers", ReasonCoworkers
FROM tblExitSurvey
UNION ALL
--etc--;
SELECT EmployeeNumber, "AgencyOrient", AgencyOrient
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "JobOrient", JobOrient
FROM tblExitSurvey
UNION ALL
---etc--
,
You can then create a totals or crosstab query from the above union query
that groups by Attribute and TheValue to get counts, percentages, etc.

Once your table is normalized, you have much more flexibility with
publishing the data.

--
Duane Hookom
MS Access MVP


CEV said:
SELECT tblEmployees.DateLeftAgency, tblExitSurvey.ReasonPay,
tblExitSurvey.ReasonEnvironment, tblExitSurvey.ReasonSupervisor,
tblExitSurvey.ReasonCoworkers, tblExitSurvey.ReasonEducation,
tblExitSurvey.ReasonTemp, tblExitSurvey.ReasonNewJob,
tblExitSurvey.ReasonPersonal, tblExitSurvey.ReasonRelocation,
tblExitSurvey.ReasonOther, tblExitSurvey.NewJobBenefits,
tblExitSurvey.NewJobPay, tblExitSurvey.NewJobOther,
tblExitSurvey.AgencyOrient, tblExitSurvey.JobOrient,
tblExitSurvey.Training, tblExitSurvey.DeptComm, tblExitSurvey.WorkHours,
tblExitSurvey.PhysWorkCond, tblExitSurvey.EnvWorkCond,
tblExitSurvey.Supervisor, tblExitSurvey.AgencyComm, tblExitSurvey.Salary,
tblExitSurvey.PromoOppor, tblExitSurvey.PandP

FROM tblEmployees INNER JOIN tblExitSurvey ON tblEmployees.EmployeeNumber
= tblExitSurvey.EmployeeNumber

GROUP BY tblEmployees.DateLeftAgency, tblExitSurvey.ReasonPay,
tblExitSurvey.ReasonEnvironment, tblExitSurvey.ReasonSupervisor,
tblExitSurvey.ReasonCoworkers, tblExitSurvey.ReasonEducation,
tblExitSurvey.ReasonTemp, tblExitSurvey.ReasonNewJob,
tblExitSurvey.ReasonPersonal, tblExitSurvey.ReasonRelocation,
tblExitSurvey.ReasonOther, tblExitSurvey.NewJobBenefits,
tblExitSurvey.NewJobPay, tblExitSurvey.NewJobOther,
tblExitSurvey.AgencyOrient, tblExitSurvey.JobOrient,
tblExitSurvey.Training, tblExitSurvey.DeptComm, tblExitSurvey.WorkHours,
tblExitSurvey.PhysWorkCond, tblExitSurvey.EnvWorkCond,
tblExitSurvey.Supervisor, tblExitSurvey.AgencyComm, tblExitSurvey.Salary,
tblExitSurvey.PromoOppor, tblExitSurvey.PandP

HAVING (((tblEmployees.DateLeftAgency) Between
[Forms]![frmReports]![txtBeginDate] And
[Forms]![frmReports]![txtEndDate]));



This is the SQL view of the query I made so I can try to come up with the
report that I would like to get. This query of course does not do it. I
am not at all familiar with creating SQL queries. I have always created
queries in the design view.



The field's ReasonPay, ReasonEnvironment, ReasonSupervisor,
ReasonCoworkers, ReasonEducation, ReasonTemp, ReasonNewJob,
ReasonPersonal, ReasonRelocation, ReasonOther, NewJobBenefits, NewJobPay,
NewJobOther are Yes/No checkboxes on the form. I want the query to count
how many records are checked Yes for each field for the dates I specify.
It would be great if I could also have it calculate the percentage of
records marked Yes for each of these fields.



The field's AgencyOrient, JobOrient, Training, DeptComm, WorkHours,
PhysWorkCond, EnvWorkCond, Supervisor, AgencyComm, Salary, PromoOppor,
PandP are fields that will contain a single number of either 1,2,3,4,or
5. What I would like to do with these fields on the report is be able to
display how many are marked with either a 1 or 2, how many are marked
with a 3, and how many are marked with either a 4 or 5.



In the design view of the query I have tried both the Sum option and the
Count option in the Total row, but neither one does what I want it to.
What I am currently getting is a seperate page for each record that shows
all the checkboxes for each field or the number entered for the other
fields. I'm assuming that there must be something more complex I need to
enter while in the SQL view. If anyone can help me out with this, I would
greatly appreciate it!!



Thanks



Chad
Click to expand...
Click to expand...
 
D

Duane Hookom

You would have tables like:

tblEmployeeExit
======================
EmplExitID pk autonumber
EmployeeNumber link to tblEmployees.EmployeeNumber
-- possibly other fields --

tblExitAttributes (one record for each of your former fields)
====================
AttribID autonumber PK
Attribute values such as Pay, Environmental,...
MaxValue
MinValue
Sequence numeric value that describes the order
you want to see the attributes

tblExitSurvey
==================
EmplExitID link to tblEmployeeExit.EmplExitID
AttribID link to tblExitAttributes.AttribID
TheValue values like 1, 2, 3, which can be limited by
the MaxValue and MinValue from tblExitAttributes

This would allow you to create any number of attributes as you want such as
Pay, Environmental, Lack of Offstreet Parking, Harassed by Normalization
Police, Poor Cafeteria, Don't let me drink on duty, ....

To see how to implement this type of a system, check "At Your Survey" found
at http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane. There
is also an employee evaluation demo at the same page that is similar.


--
Duane Hookom
MS Access MVP



CEV said:
I do not understand when you say you should not have to add a field to a
table or control to a form when you want to add another "reason". If you
did not do this, then when filling out the form how would you select the
checkbox for the new "reason" and also for other "reasons" when there are
more then one? That is the part that is confusing to me.

Thanks,



Duane Hookom said:
Your main issue is that your tblExitSurvey is not normalized. You are
storing data values in field names. If you want to add another "reason"
you should not have to add a fields to tables and queries or controls on
forms.

You can normalize this on-the-fly with a union query like
SELECT EmployeeNumber, "Pay" as Attribute, ReasonPay as theValue
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "Environmental", ReasonEnvironment
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "Supervisor", ReasonSupervisor
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "Coworkers", ReasonCoworkers
FROM tblExitSurvey
UNION ALL
--etc--;
SELECT EmployeeNumber, "AgencyOrient", AgencyOrient
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "JobOrient", JobOrient
FROM tblExitSurvey
UNION ALL
---etc--
,
You can then create a totals or crosstab query from the above union query
that groups by Attribute and TheValue to get counts, percentages, etc.

Once your table is normalized, you have much more flexibility with
publishing the data.

--
Duane Hookom
MS Access MVP


CEV said:
SELECT tblEmployees.DateLeftAgency, tblExitSurvey.ReasonPay,
tblExitSurvey.ReasonEnvironment, tblExitSurvey.ReasonSupervisor,
tblExitSurvey.ReasonCoworkers, tblExitSurvey.ReasonEducation,
tblExitSurvey.ReasonTemp, tblExitSurvey.ReasonNewJob,
tblExitSurvey.ReasonPersonal, tblExitSurvey.ReasonRelocation,
tblExitSurvey.ReasonOther, tblExitSurvey.NewJobBenefits,
tblExitSurvey.NewJobPay, tblExitSurvey.NewJobOther,
tblExitSurvey.AgencyOrient, tblExitSurvey.JobOrient,
tblExitSurvey.Training, tblExitSurvey.DeptComm, tblExitSurvey.WorkHours,
tblExitSurvey.PhysWorkCond, tblExitSurvey.EnvWorkCond,
tblExitSurvey.Supervisor, tblExitSurvey.AgencyComm,
tblExitSurvey.Salary, tblExitSurvey.PromoOppor, tblExitSurvey.PandP

FROM tblEmployees INNER JOIN tblExitSurvey ON
tblEmployees.EmployeeNumber = tblExitSurvey.EmployeeNumber

GROUP BY tblEmployees.DateLeftAgency, tblExitSurvey.ReasonPay,
tblExitSurvey.ReasonEnvironment, tblExitSurvey.ReasonSupervisor,
tblExitSurvey.ReasonCoworkers, tblExitSurvey.ReasonEducation,
tblExitSurvey.ReasonTemp, tblExitSurvey.ReasonNewJob,
tblExitSurvey.ReasonPersonal, tblExitSurvey.ReasonRelocation,
tblExitSurvey.ReasonOther, tblExitSurvey.NewJobBenefits,
tblExitSurvey.NewJobPay, tblExitSurvey.NewJobOther,
tblExitSurvey.AgencyOrient, tblExitSurvey.JobOrient,
tblExitSurvey.Training, tblExitSurvey.DeptComm, tblExitSurvey.WorkHours,
tblExitSurvey.PhysWorkCond, tblExitSurvey.EnvWorkCond,
tblExitSurvey.Supervisor, tblExitSurvey.AgencyComm,
tblExitSurvey.Salary, tblExitSurvey.PromoOppor, tblExitSurvey.PandP

HAVING (((tblEmployees.DateLeftAgency) Between
[Forms]![frmReports]![txtBeginDate] And
[Forms]![frmReports]![txtEndDate]));



This is the SQL view of the query I made so I can try to come up with
the report that I would like to get. This query of course does not do
it. I am not at all familiar with creating SQL queries. I have always
created queries in the design view.



The field's ReasonPay, ReasonEnvironment, ReasonSupervisor,
ReasonCoworkers, ReasonEducation, ReasonTemp, ReasonNewJob,
ReasonPersonal, ReasonRelocation, ReasonOther, NewJobBenefits,
NewJobPay, NewJobOther are Yes/No checkboxes on the form. I want the
query to count how many records are checked Yes for each field for the
dates I specify. It would be great if I could also have it calculate the
percentage of records marked Yes for each of these fields.



The field's AgencyOrient, JobOrient, Training, DeptComm, WorkHours,
PhysWorkCond, EnvWorkCond, Supervisor, AgencyComm, Salary, PromoOppor,
PandP are fields that will contain a single number of either 1,2,3,4,or
5. What I would like to do with these fields on the report is be able to
display how many are marked with either a 1 or 2, how many are marked
with a 3, and how many are marked with either a 4 or 5.



In the design view of the query I have tried both the Sum option and the
Count option in the Total row, but neither one does what I want it to.
What I am currently getting is a seperate page for each record that
shows all the checkboxes for each field or the number entered for the
other fields. I'm assuming that there must be something more complex I
need to enter while in the SQL view. If anyone can help me out with
this, I would greatly appreciate it!!



Thanks



Chad
Click to expand...
Click to expand...
Click to expand...
 
C

CEV

I tried this and when I try to save it I get the error stating "Syntax error
in query. Incomplete query clause." I have tried to search for a simliar
query example to see what I might have done wrong, but could not find
anything. I have doublechecked the spellings and everything seems to be
correct.

Thanks,

Chad

SELECT EmployeeNumber, "Pay" as Attribute, ReasonPay as TheValue
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "Environment" as Attribute, ReasonEnvironment as
TheValue
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "Supervisor" as Attribute, ReasonSupervisor as
TheValue
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "Coworkers" as Attribute, ReasonCoworkers as TheValue
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "Education" as Attribute, ReasonEducation as TheValue
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "Temp" as Attribute, ReasonTemp as TheValue
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "NewJob" as Attribute, ReasonNewJob as TheValue
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "Personal" as Attribute, ReasonPersonal as TheValue
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "Relocation" as Attribute, ReasonRelocation as
TheValue
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "Other" as Attribute, ReasonOther as TheValue
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "NewJobBenefits" as Attribute, ReasonNewJobBenefits
as TheValue
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "NewJobPay" as Attribute, ReasonNewJobPay as TheValue
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "Coworkers" as Attribute, ReasonCoworkers as TheValue
FROM tblExitSurvey
UNION ALL


Duane Hookom said:
Your main issue is that your tblExitSurvey is not normalized. You are
storing data values in field names. If you want to add another "reason"
you should not have to add a fields to tables and queries or controls on
forms.

You can normalize this on-the-fly with a union query like
SELECT EmployeeNumber, "Pay" as Attribute, ReasonPay as theValue
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "Environmental", ReasonEnvironment
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "Supervisor", ReasonSupervisor
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "Coworkers", ReasonCoworkers
FROM tblExitSurvey
UNION ALL
--etc--;
SELECT EmployeeNumber, "AgencyOrient", AgencyOrient
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "JobOrient", JobOrient
FROM tblExitSurvey
UNION ALL
---etc--
,
You can then create a totals or crosstab query from the above union query
that groups by Attribute and TheValue to get counts, percentages, etc.

Once your table is normalized, you have much more flexibility with
publishing the data.

--
Duane Hookom
MS Access MVP


CEV said:
SELECT tblEmployees.DateLeftAgency, tblExitSurvey.ReasonPay,
tblExitSurvey.ReasonEnvironment, tblExitSurvey.ReasonSupervisor,
tblExitSurvey.ReasonCoworkers, tblExitSurvey.ReasonEducation,
tblExitSurvey.ReasonTemp, tblExitSurvey.ReasonNewJob,
tblExitSurvey.ReasonPersonal, tblExitSurvey.ReasonRelocation,
tblExitSurvey.ReasonOther, tblExitSurvey.NewJobBenefits,
tblExitSurvey.NewJobPay, tblExitSurvey.NewJobOther,
tblExitSurvey.AgencyOrient, tblExitSurvey.JobOrient,
tblExitSurvey.Training, tblExitSurvey.DeptComm, tblExitSurvey.WorkHours,
tblExitSurvey.PhysWorkCond, tblExitSurvey.EnvWorkCond,
tblExitSurvey.Supervisor, tblExitSurvey.AgencyComm, tblExitSurvey.Salary,
tblExitSurvey.PromoOppor, tblExitSurvey.PandP

FROM tblEmployees INNER JOIN tblExitSurvey ON tblEmployees.EmployeeNumber
= tblExitSurvey.EmployeeNumber

GROUP BY tblEmployees.DateLeftAgency, tblExitSurvey.ReasonPay,
tblExitSurvey.ReasonEnvironment, tblExitSurvey.ReasonSupervisor,
tblExitSurvey.ReasonCoworkers, tblExitSurvey.ReasonEducation,
tblExitSurvey.ReasonTemp, tblExitSurvey.ReasonNewJob,
tblExitSurvey.ReasonPersonal, tblExitSurvey.ReasonRelocation,
tblExitSurvey.ReasonOther, tblExitSurvey.NewJobBenefits,
tblExitSurvey.NewJobPay, tblExitSurvey.NewJobOther,
tblExitSurvey.AgencyOrient, tblExitSurvey.JobOrient,
tblExitSurvey.Training, tblExitSurvey.DeptComm, tblExitSurvey.WorkHours,
tblExitSurvey.PhysWorkCond, tblExitSurvey.EnvWorkCond,
tblExitSurvey.Supervisor, tblExitSurvey.AgencyComm, tblExitSurvey.Salary,
tblExitSurvey.PromoOppor, tblExitSurvey.PandP

HAVING (((tblEmployees.DateLeftAgency) Between
[Forms]![frmReports]![txtBeginDate] And
[Forms]![frmReports]![txtEndDate]));



This is the SQL view of the query I made so I can try to come up with the
report that I would like to get. This query of course does not do it. I
am not at all familiar with creating SQL queries. I have always created
queries in the design view.



The field's ReasonPay, ReasonEnvironment, ReasonSupervisor,
ReasonCoworkers, ReasonEducation, ReasonTemp, ReasonNewJob,
ReasonPersonal, ReasonRelocation, ReasonOther, NewJobBenefits, NewJobPay,
NewJobOther are Yes/No checkboxes on the form. I want the query to count
how many records are checked Yes for each field for the dates I specify.
It would be great if I could also have it calculate the percentage of
records marked Yes for each of these fields.



The field's AgencyOrient, JobOrient, Training, DeptComm, WorkHours,
PhysWorkCond, EnvWorkCond, Supervisor, AgencyComm, Salary, PromoOppor,
PandP are fields that will contain a single number of either 1,2,3,4,or
5. What I would like to do with these fields on the report is be able to
display how many are marked with either a 1 or 2, how many are marked
with a 3, and how many are marked with either a 4 or 5.



In the design view of the query I have tried both the Sum option and the
Count option in the Total row, but neither one does what I want it to.
What I am currently getting is a seperate page for each record that shows
all the checkboxes for each field or the number entered for the other
fields. I'm assuming that there must be something more complex I need to
enter while in the SQL view. If anyone can help me out with this, I would
greatly appreciate it!!



Thanks



Chad
Click to expand...
Click to expand...
 
D

Duane Hookom

You only need the "As..." in the first select and your sql should finish at:

SELECT EmployeeNumber, "Coworkers", ReasonCoworkers
FROM tblExitSurvey;


--
Duane Hookom
MS Access MVP


CEV said:
I tried this and when I try to save it I get the error stating "Syntax
error in query. Incomplete query clause." I have tried to search for a
simliar query example to see what I might have done wrong, but could not
find anything. I have doublechecked the spellings and everything seems to
be correct.

Thanks,

Chad

SELECT EmployeeNumber, "Pay" as Attribute, ReasonPay as TheValue
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "Environment" as Attribute, ReasonEnvironment as
TheValue
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "Supervisor" as Attribute, ReasonSupervisor as
TheValue
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "Coworkers" as Attribute, ReasonCoworkers as
TheValue
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "Education" as Attribute, ReasonEducation as
TheValue
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "Temp" as Attribute, ReasonTemp as TheValue
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "NewJob" as Attribute, ReasonNewJob as TheValue
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "Personal" as Attribute, ReasonPersonal as TheValue
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "Relocation" as Attribute, ReasonRelocation as
TheValue
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "Other" as Attribute, ReasonOther as TheValue
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "NewJobBenefits" as Attribute, ReasonNewJobBenefits
as TheValue
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "NewJobPay" as Attribute, ReasonNewJobPay as
TheValue
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "Coworkers" as Attribute, ReasonCoworkers as
TheValue
FROM tblExitSurvey
UNION ALL


Duane Hookom said:
Your main issue is that your tblExitSurvey is not normalized. You are
storing data values in field names. If you want to add another "reason"
you should not have to add a fields to tables and queries or controls on
forms.

You can normalize this on-the-fly with a union query like
SELECT EmployeeNumber, "Pay" as Attribute, ReasonPay as theValue
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "Environmental", ReasonEnvironment
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "Supervisor", ReasonSupervisor
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "Coworkers", ReasonCoworkers
FROM tblExitSurvey
UNION ALL
--etc--;
SELECT EmployeeNumber, "AgencyOrient", AgencyOrient
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "JobOrient", JobOrient
FROM tblExitSurvey
UNION ALL
---etc--
,
You can then create a totals or crosstab query from the above union query
that groups by Attribute and TheValue to get counts, percentages, etc.

Once your table is normalized, you have much more flexibility with
publishing the data.

--
Duane Hookom
MS Access MVP


CEV said:
SELECT tblEmployees.DateLeftAgency, tblExitSurvey.ReasonPay,
tblExitSurvey.ReasonEnvironment, tblExitSurvey.ReasonSupervisor,
tblExitSurvey.ReasonCoworkers, tblExitSurvey.ReasonEducation,
tblExitSurvey.ReasonTemp, tblExitSurvey.ReasonNewJob,
tblExitSurvey.ReasonPersonal, tblExitSurvey.ReasonRelocation,
tblExitSurvey.ReasonOther, tblExitSurvey.NewJobBenefits,
tblExitSurvey.NewJobPay, tblExitSurvey.NewJobOther,
tblExitSurvey.AgencyOrient, tblExitSurvey.JobOrient,
tblExitSurvey.Training, tblExitSurvey.DeptComm, tblExitSurvey.WorkHours,
tblExitSurvey.PhysWorkCond, tblExitSurvey.EnvWorkCond,
tblExitSurvey.Supervisor, tblExitSurvey.AgencyComm,
tblExitSurvey.Salary, tblExitSurvey.PromoOppor, tblExitSurvey.PandP

FROM tblEmployees INNER JOIN tblExitSurvey ON
tblEmployees.EmployeeNumber = tblExitSurvey.EmployeeNumber

GROUP BY tblEmployees.DateLeftAgency, tblExitSurvey.ReasonPay,
tblExitSurvey.ReasonEnvironment, tblExitSurvey.ReasonSupervisor,
tblExitSurvey.ReasonCoworkers, tblExitSurvey.ReasonEducation,
tblExitSurvey.ReasonTemp, tblExitSurvey.ReasonNewJob,
tblExitSurvey.ReasonPersonal, tblExitSurvey.ReasonRelocation,
tblExitSurvey.ReasonOther, tblExitSurvey.NewJobBenefits,
tblExitSurvey.NewJobPay, tblExitSurvey.NewJobOther,
tblExitSurvey.AgencyOrient, tblExitSurvey.JobOrient,
tblExitSurvey.Training, tblExitSurvey.DeptComm, tblExitSurvey.WorkHours,
tblExitSurvey.PhysWorkCond, tblExitSurvey.EnvWorkCond,
tblExitSurvey.Supervisor, tblExitSurvey.AgencyComm,
tblExitSurvey.Salary, tblExitSurvey.PromoOppor, tblExitSurvey.PandP

HAVING (((tblEmployees.DateLeftAgency) Between
[Forms]![frmReports]![txtBeginDate] And
[Forms]![frmReports]![txtEndDate]));



This is the SQL view of the query I made so I can try to come up with
the report that I would like to get. This query of course does not do
it. I am not at all familiar with creating SQL queries. I have always
created queries in the design view.



The field's ReasonPay, ReasonEnvironment, ReasonSupervisor,
ReasonCoworkers, ReasonEducation, ReasonTemp, ReasonNewJob,
ReasonPersonal, ReasonRelocation, ReasonOther, NewJobBenefits,
NewJobPay, NewJobOther are Yes/No checkboxes on the form. I want the
query to count how many records are checked Yes for each field for the
dates I specify. It would be great if I could also have it calculate the
percentage of records marked Yes for each of these fields.



The field's AgencyOrient, JobOrient, Training, DeptComm, WorkHours,
PhysWorkCond, EnvWorkCond, Supervisor, AgencyComm, Salary, PromoOppor,
PandP are fields that will contain a single number of either 1,2,3,4,or
5. What I would like to do with these fields on the report is be able to
display how many are marked with either a 1 or 2, how many are marked
with a 3, and how many are marked with either a 4 or 5.



In the design view of the query I have tried both the Sum option and the
Count option in the Total row, but neither one does what I want it to.
What I am currently getting is a seperate page for each record that
shows all the checkboxes for each field or the number entered for the
other fields. I'm assuming that there must be something more complex I
need to enter while in the SQL view. If anyone can help me out with
this, I would greatly appreciate it!!



Thanks



Chad
Click to expand...
Click to expand...
Click to expand...
 
C

CEV

That did it. I am now able to save it. I am going to finish up that query
and then experiment with a totals query based on this one like you
suggested.

Thanks for helping Duane!!

Chad

Duane Hookom said:
You only need the "As..." in the first select and your sql should finish
at:

SELECT EmployeeNumber, "Coworkers", ReasonCoworkers
FROM tblExitSurvey;


--
Duane Hookom
MS Access MVP


CEV said:
I tried this and when I try to save it I get the error stating "Syntax
error in query. Incomplete query clause." I have tried to search for a
simliar query example to see what I might have done wrong, but could not
find anything. I have doublechecked the spellings and everything seems to
be correct.

Thanks,

Chad

SELECT EmployeeNumber, "Pay" as Attribute, ReasonPay as TheValue
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "Environment" as Attribute, ReasonEnvironment as
TheValue
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "Supervisor" as Attribute, ReasonSupervisor as
TheValue
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "Coworkers" as Attribute, ReasonCoworkers as
TheValue
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "Education" as Attribute, ReasonEducation as
TheValue
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "Temp" as Attribute, ReasonTemp as TheValue
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "NewJob" as Attribute, ReasonNewJob as TheValue
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "Personal" as Attribute, ReasonPersonal as
TheValue
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "Relocation" as Attribute, ReasonRelocation as
TheValue
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "Other" as Attribute, ReasonOther as TheValue
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "NewJobBenefits" as Attribute,
ReasonNewJobBenefits as TheValue
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "NewJobPay" as Attribute, ReasonNewJobPay as
TheValue
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "Coworkers" as Attribute, ReasonCoworkers as
TheValue
FROM tblExitSurvey
UNION ALL


Duane Hookom said:
Your main issue is that your tblExitSurvey is not normalized. You are
storing data values in field names. If you want to add another "reason"
you should not have to add a fields to tables and queries or controls on
forms.

You can normalize this on-the-fly with a union query like
SELECT EmployeeNumber, "Pay" as Attribute, ReasonPay as theValue
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "Environmental", ReasonEnvironment
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "Supervisor", ReasonSupervisor
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "Coworkers", ReasonCoworkers
FROM tblExitSurvey
UNION ALL
--etc--;
SELECT EmployeeNumber, "AgencyOrient", AgencyOrient
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "JobOrient", JobOrient
FROM tblExitSurvey
UNION ALL
---etc--
,
You can then create a totals or crosstab query from the above union
query that groups by Attribute and TheValue to get counts, percentages,
etc.

Once your table is normalized, you have much more flexibility with
publishing the data.

--
Duane Hookom
MS Access MVP


SELECT tblEmployees.DateLeftAgency, tblExitSurvey.ReasonPay,
tblExitSurvey.ReasonEnvironment, tblExitSurvey.ReasonSupervisor,
tblExitSurvey.ReasonCoworkers, tblExitSurvey.ReasonEducation,
tblExitSurvey.ReasonTemp, tblExitSurvey.ReasonNewJob,
tblExitSurvey.ReasonPersonal, tblExitSurvey.ReasonRelocation,
tblExitSurvey.ReasonOther, tblExitSurvey.NewJobBenefits,
tblExitSurvey.NewJobPay, tblExitSurvey.NewJobOther,
tblExitSurvey.AgencyOrient, tblExitSurvey.JobOrient,
tblExitSurvey.Training, tblExitSurvey.DeptComm,
tblExitSurvey.WorkHours, tblExitSurvey.PhysWorkCond,
tblExitSurvey.EnvWorkCond, tblExitSurvey.Supervisor,
tblExitSurvey.AgencyComm, tblExitSurvey.Salary,
tblExitSurvey.PromoOppor, tblExitSurvey.PandP

FROM tblEmployees INNER JOIN tblExitSurvey ON
tblEmployees.EmployeeNumber = tblExitSurvey.EmployeeNumber

GROUP BY tblEmployees.DateLeftAgency, tblExitSurvey.ReasonPay,
tblExitSurvey.ReasonEnvironment, tblExitSurvey.ReasonSupervisor,
tblExitSurvey.ReasonCoworkers, tblExitSurvey.ReasonEducation,
tblExitSurvey.ReasonTemp, tblExitSurvey.ReasonNewJob,
tblExitSurvey.ReasonPersonal, tblExitSurvey.ReasonRelocation,
tblExitSurvey.ReasonOther, tblExitSurvey.NewJobBenefits,
tblExitSurvey.NewJobPay, tblExitSurvey.NewJobOther,
tblExitSurvey.AgencyOrient, tblExitSurvey.JobOrient,
tblExitSurvey.Training, tblExitSurvey.DeptComm,
tblExitSurvey.WorkHours, tblExitSurvey.PhysWorkCond,
tblExitSurvey.EnvWorkCond, tblExitSurvey.Supervisor,
tblExitSurvey.AgencyComm, tblExitSurvey.Salary,
tblExitSurvey.PromoOppor, tblExitSurvey.PandP

HAVING (((tblEmployees.DateLeftAgency) Between
[Forms]![frmReports]![txtBeginDate] And
[Forms]![frmReports]![txtEndDate]));



This is the SQL view of the query I made so I can try to come up with
the report that I would like to get. This query of course does not do
it. I am not at all familiar with creating SQL queries. I have always
created queries in the design view.



The field's ReasonPay, ReasonEnvironment, ReasonSupervisor,
ReasonCoworkers, ReasonEducation, ReasonTemp, ReasonNewJob,
ReasonPersonal, ReasonRelocation, ReasonOther, NewJobBenefits,
NewJobPay, NewJobOther are Yes/No checkboxes on the form. I want the
query to count how many records are checked Yes for each field for the
dates I specify. It would be great if I could also have it calculate
the percentage of records marked Yes for each of these fields.



The field's AgencyOrient, JobOrient, Training, DeptComm, WorkHours,
PhysWorkCond, EnvWorkCond, Supervisor, AgencyComm, Salary, PromoOppor,
PandP are fields that will contain a single number of either 1,2,3,4,or
5. What I would like to do with these fields on the report is be able
to display how many are marked with either a 1 or 2, how many are
marked with a 3, and how many are marked with either a 4 or 5.



In the design view of the query I have tried both the Sum option and
the Count option in the Total row, but neither one does what I want it
to. What I am currently getting is a seperate page for each record that
shows all the checkboxes for each field or the number entered for the
other fields. I'm assuming that there must be something more complex I
need to enter while in the SQL view. If anyone can help me out with
this, I would greatly appreciate it!!



Thanks



Chad
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

Good luck, let us know how it works.

--
Duane Hookom
MS Access MVP

CEV said:
That did it. I am now able to save it. I am going to finish up that query
and then experiment with a totals query based on this one like you
suggested.

Thanks for helping Duane!!

Chad

Duane Hookom said:
You only need the "As..." in the first select and your sql should finish
at:

SELECT EmployeeNumber, "Coworkers", ReasonCoworkers
FROM tblExitSurvey;


--
Duane Hookom
MS Access MVP


CEV said:
I tried this and when I try to save it I get the error stating "Syntax
error in query. Incomplete query clause." I have tried to search for a
simliar query example to see what I might have done wrong, but could not
find anything. I have doublechecked the spellings and everything seems to
be correct.

Thanks,

Chad

SELECT EmployeeNumber, "Pay" as Attribute, ReasonPay as TheValue
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "Environment" as Attribute, ReasonEnvironment as
TheValue
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "Supervisor" as Attribute, ReasonSupervisor as
TheValue
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "Coworkers" as Attribute, ReasonCoworkers as
TheValue
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "Education" as Attribute, ReasonEducation as
TheValue
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "Temp" as Attribute, ReasonTemp as TheValue
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "NewJob" as Attribute, ReasonNewJob as TheValue
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "Personal" as Attribute, ReasonPersonal as
TheValue
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "Relocation" as Attribute, ReasonRelocation as
TheValue
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "Other" as Attribute, ReasonOther as TheValue
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "NewJobBenefits" as Attribute,
ReasonNewJobBenefits as TheValue
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "NewJobPay" as Attribute, ReasonNewJobPay as
TheValue
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "Coworkers" as Attribute, ReasonCoworkers as
TheValue
FROM tblExitSurvey
UNION ALL


"Duane Hookom" <DuaneAtNoSpanHookomDotNet> wrote in message
Your main issue is that your tblExitSurvey is not normalized. You are
storing data values in field names. If you want to add another "reason"
you should not have to add a fields to tables and queries or controls
on forms.

You can normalize this on-the-fly with a union query like
SELECT EmployeeNumber, "Pay" as Attribute, ReasonPay as theValue
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "Environmental", ReasonEnvironment
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "Supervisor", ReasonSupervisor
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "Coworkers", ReasonCoworkers
FROM tblExitSurvey
UNION ALL
--etc--;
SELECT EmployeeNumber, "AgencyOrient", AgencyOrient
FROM tblExitSurvey
UNION ALL
SELECT EmployeeNumber, "JobOrient", JobOrient
FROM tblExitSurvey
UNION ALL
---etc--
,
You can then create a totals or crosstab query from the above union
query that groups by Attribute and TheValue to get counts, percentages,
etc.

Once your table is normalized, you have much more flexibility with
publishing the data.

--
Duane Hookom
MS Access MVP


SELECT tblEmployees.DateLeftAgency, tblExitSurvey.ReasonPay,
tblExitSurvey.ReasonEnvironment, tblExitSurvey.ReasonSupervisor,
tblExitSurvey.ReasonCoworkers, tblExitSurvey.ReasonEducation,
tblExitSurvey.ReasonTemp, tblExitSurvey.ReasonNewJob,
tblExitSurvey.ReasonPersonal, tblExitSurvey.ReasonRelocation,
tblExitSurvey.ReasonOther, tblExitSurvey.NewJobBenefits,
tblExitSurvey.NewJobPay, tblExitSurvey.NewJobOther,
tblExitSurvey.AgencyOrient, tblExitSurvey.JobOrient,
tblExitSurvey.Training, tblExitSurvey.DeptComm,
tblExitSurvey.WorkHours, tblExitSurvey.PhysWorkCond,
tblExitSurvey.EnvWorkCond, tblExitSurvey.Supervisor,
tblExitSurvey.AgencyComm, tblExitSurvey.Salary,
tblExitSurvey.PromoOppor, tblExitSurvey.PandP

FROM tblEmployees INNER JOIN tblExitSurvey ON
tblEmployees.EmployeeNumber = tblExitSurvey.EmployeeNumber

GROUP BY tblEmployees.DateLeftAgency, tblExitSurvey.ReasonPay,
tblExitSurvey.ReasonEnvironment, tblExitSurvey.ReasonSupervisor,
tblExitSurvey.ReasonCoworkers, tblExitSurvey.ReasonEducation,
tblExitSurvey.ReasonTemp, tblExitSurvey.ReasonNewJob,
tblExitSurvey.ReasonPersonal, tblExitSurvey.ReasonRelocation,
tblExitSurvey.ReasonOther, tblExitSurvey.NewJobBenefits,
tblExitSurvey.NewJobPay, tblExitSurvey.NewJobOther,
tblExitSurvey.AgencyOrient, tblExitSurvey.JobOrient,
tblExitSurvey.Training, tblExitSurvey.DeptComm,
tblExitSurvey.WorkHours, tblExitSurvey.PhysWorkCond,
tblExitSurvey.EnvWorkCond, tblExitSurvey.Supervisor,
tblExitSurvey.AgencyComm, tblExitSurvey.Salary,
tblExitSurvey.PromoOppor, tblExitSurvey.PandP

HAVING (((tblEmployees.DateLeftAgency) Between
[Forms]![frmReports]![txtBeginDate] And
[Forms]![frmReports]![txtEndDate]));



This is the SQL view of the query I made so I can try to come up with
the report that I would like to get. This query of course does not do
it. I am not at all familiar with creating SQL queries. I have always
created queries in the design view.



The field's ReasonPay, ReasonEnvironment, ReasonSupervisor,
ReasonCoworkers, ReasonEducation, ReasonTemp, ReasonNewJob,
ReasonPersonal, ReasonRelocation, ReasonOther, NewJobBenefits,
NewJobPay, NewJobOther are Yes/No checkboxes on the form. I want the
query to count how many records are checked Yes for each field for the
dates I specify. It would be great if I could also have it calculate
the percentage of records marked Yes for each of these fields.



The field's AgencyOrient, JobOrient, Training, DeptComm, WorkHours,
PhysWorkCond, EnvWorkCond, Supervisor, AgencyComm, Salary, PromoOppor,
PandP are fields that will contain a single number of either
1,2,3,4,or 5. What I would like to do with these fields on the report
is be able to display how many are marked with either a 1 or 2, how
many are marked with a 3, and how many are marked with either a 4 or
5.



In the design view of the query I have tried both the Sum option and
the Count option in the Total row, but neither one does what I want it
to. What I am currently getting is a seperate page for each record
that shows all the checkboxes for each field or the number entered for
the other fields. I'm assuming that there must be something more
complex I need to enter while in the SQL view. If anyone can help me
out with this, I would greatly appreciate it!!



Thanks



Chad
Click to expand...
Click to expand...
Click to expand...
 
Top