Reporting from Queries

  • Thread starter Thread starter Rachel
  • Start date Start date
R

Rachel

I imported data from an Excel file into Access. This is a
survey database where different people will receive
different modules. There are a total of 16 modules.
Example

Person 1: Modules 1, 2, 3, 5-7, 14
Person 2: Moduless 2 and 3
Person 3: Module 1 - 9
Person 4: Modules 5
etc...

The data is compiled with all of the modules in one file.
The column headers are the questions. Because each person
may not receive all modules there are balnk field where
the person did not give a response.

I would like to pull a report by person that contains each
module the person received with their responses and leaves
out any modules that did not have responses.

I tried doing this through a query but when I tried to
pull the report from the different queries Acess did not
allow me to. Any ideas?
Thanks,
Rachel
 
It would really help if you typed or pasted some sample data with actual
field and table names into your message. I can only guess that you should
attempt to normalize the imported records with a union query:
SELECT Person, Mod1, "Mod1" as Question
FROM tblSurvey
WHERE Mod1 is not Null
UNION
SELECT Person, Mod2, "Mod2"
FROM tblSurvey
WHERE Mod2 is not Null
UNION
SELECT Person, Mod3, "Mod3"
FROM tblSurvey
WHERE Mod3 is not Null
UNION
....etc...
This union query should allow you to create your report.
 
Okay let me do this as best I can:

Mod = Module
Q = Question

Field Names = [Mod1] [Mod2] [Mod3] [Mod4] [Mod etc.]
[Mod1 Q.1] [Mod1 Q.2] [Mod1 Q.3] [Mod1 Q.etc] {Mod2 Q.1]
[Mod2 Q.2] [Mod2 Q.etc] [Mod(etc) Q.1]

Row = Person

The value that corresponds to [Mod1][Row1] is "X" - which
means that the person received that particular module.
Therefore, as you continue to make your way down the row,
depending on how many modules there is an "X" for, you may
pass up several modules with their corresponding 8 - 10
module questions until you get to a modules that was
assigned to that person.

Rather than have a report that pulls all modules and their
questions and have a list of modules for each person that
has a "NA" value, I want the report to run for only the
modules that the person received.

Thanks!
 
Did you try a union query as I suggested only substituting your field names?
Do you actually have periods in your field names? How many fields do you
have?

--
Duane Hookom
MS Access MVP


Rachel said:
Okay let me do this as best I can:

Mod = Module
Q = Question

Field Names = [Mod1] [Mod2] [Mod3] [Mod4] [Mod etc.]
[Mod1 Q.1] [Mod1 Q.2] [Mod1 Q.3] [Mod1 Q.etc] {Mod2 Q.1]
[Mod2 Q.2] [Mod2 Q.etc] [Mod(etc) Q.1]

Row = Person

The value that corresponds to [Mod1][Row1] is "X" - which
means that the person received that particular module.
Therefore, as you continue to make your way down the row,
depending on how many modules there is an "X" for, you may
pass up several modules with their corresponding 8 - 10
module questions until you get to a modules that was
assigned to that person.

Rather than have a report that pulls all modules and their
questions and have a list of modules for each person that
has a "NA" value, I want the report to run for only the
modules that the person received.

Thanks!
-----Original Message-----
It would really help if you typed or pasted some sample data with actual
field and table names into your message. I can only guess that you should
attempt to normalize the imported records with a union query:
SELECT Person, Mod1, "Mod1" as Question
FROM tblSurvey
WHERE Mod1 is not Null
UNION
SELECT Person, Mod2, "Mod2"
FROM tblSurvey
WHERE Mod2 is not Null
UNION
SELECT Person, Mod3, "Mod3"
FROM tblSurvey
WHERE Mod3 is not Null
UNION
....etc...
This union query should allow you to create your report.

--
Duane Hookom
MS Access MVP





.
Click to expand...
Click to expand...
 
There are no periods in the field names, it was just an
example. The actual field names are the questions
themselves. I tried the union query but it only works
when you want to link the same information from two
different tables or queries. I tried doing this exactly:

SELECT [Account Management],[AM Overall quality and
support]
FROM [Survey Table]
WHERE [Account Management]="AM" (that is how I tell they
received the module)
UNION SELECT [Account Development],[AD Overall quality and
support]
FROM [Survey Table]
WHERE [Account Development]="AD"

All I got were two fields. The Account Management and the
AM Overall quality and support. Any other ideas? Am I
doing something wrong?
-----Original Message-----
Did you try a union query as I suggested only substituting your field names?
Do you actually have periods in your field names? How many fields do you
have?

--
Duane Hookom
MS Access MVP


Okay let me do this as best I can:

Mod = Module
Q = Question

Field Names = [Mod1] [Mod2] [Mod3] [Mod4] [Mod etc.]
[Mod1 Q.1] [Mod1 Q.2] [Mod1 Q.3] [Mod1 Q.etc] {Mod2 Q.1]
[Mod2 Q.2] [Mod2 Q.etc] [Mod(etc) Q.1]

Row = Person

The value that corresponds to [Mod1][Row1] is "X" - which
means that the person received that particular module.
Therefore, as you continue to make your way down the row,
depending on how many modules there is an "X" for, you may
pass up several modules with their corresponding 8 - 10
module questions until you get to a modules that was
assigned to that person.

Rather than have a report that pulls all modules and their
questions and have a list of modules for each person that
has a "NA" value, I want the report to run for only the
modules that the person received.

Thanks!
-----Original Message-----
It would really help if you typed or pasted some sample data with actual
field and table names into your message. I can only
Click to expand...
guess
that you should
attempt to normalize the imported records with a union query:
SELECT Person, Mod1, "Mod1" as Question
FROM tblSurvey
WHERE Mod1 is not Null
UNION
SELECT Person, Mod2, "Mod2"
FROM tblSurvey
WHERE Mod2 is not Null
UNION
SELECT Person, Mod3, "Mod3"
FROM tblSurvey
WHERE Mod3 is not Null
UNION
....etc...
This union query should allow you to create your report.

--
Duane Hookom
MS Access MVP


I imported data from an Excel file into Access. This is a
survey database where different people will receive
different modules. There are a total of 16 modules.
Example

Person 1: Modules 1, 2, 3, 5-7, 14
Person 2: Moduless 2 and 3
Person 3: Module 1 - 9
Person 4: Modules 5
etc...

The data is compiled with all of the modules in one file.
The column headers are the questions. Because each person
may not receive all modules there are balnk field where
the person did not give a response.

I would like to pull a report by person that contains each
module the person received with their responses and leaves
out any modules that did not have responses.

I tried doing this through a query but when I tried to
pull the report from the different queries Acess did not
allow me to. Any ideas?
Thanks,
Rachel


.
Click to expand...
Click to expand...


.
Click to expand...
 
I'm not sure why you state "when you want to link the same information from
two different tables or queries" and then you show sql that proves
otherwise. You can include any of the fields from the original table(s) as
well as create your own. I would think you need to add "UNION...." for each
question in your table.

I suppose it's a bit late now but to see how you can properly normalize a
survey, check the At Your Survey demo at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

--
Duane Hookom
MS Access MVP


Rachel said:
There are no periods in the field names, it was just an
example. The actual field names are the questions
themselves. I tried the union query but it only works
when you want to link the same information from two
different tables or queries. I tried doing this exactly:

SELECT [Account Management],[AM Overall quality and
support]
FROM [Survey Table]
WHERE [Account Management]="AM" (that is how I tell they
received the module)
UNION SELECT [Account Development],[AD Overall quality and
support]
FROM [Survey Table]
WHERE [Account Development]="AD"

All I got were two fields. The Account Management and the
AM Overall quality and support. Any other ideas? Am I
doing something wrong?
-----Original Message-----
Did you try a union query as I suggested only substituting your field names?
Do you actually have periods in your field names? How many fields do you
have?

--
Duane Hookom
MS Access MVP


Okay let me do this as best I can:

Mod = Module
Q = Question

Field Names = [Mod1] [Mod2] [Mod3] [Mod4] [Mod etc.]
[Mod1 Q.1] [Mod1 Q.2] [Mod1 Q.3] [Mod1 Q.etc] {Mod2 Q.1]
[Mod2 Q.2] [Mod2 Q.etc] [Mod(etc) Q.1]

Row = Person

The value that corresponds to [Mod1][Row1] is "X" - which
means that the person received that particular module.
Therefore, as you continue to make your way down the row,
depending on how many modules there is an "X" for, you may
pass up several modules with their corresponding 8 - 10
module questions until you get to a modules that was
assigned to that person.

Rather than have a report that pulls all modules and their
questions and have a list of modules for each person that
has a "NA" value, I want the report to run for only the
modules that the person received.

Thanks!

-----Original Message-----
It would really help if you typed or pasted some sample
data with actual
field and table names into your message. I can only guess
that you should
attempt to normalize the imported records with a union
query:
SELECT Person, Mod1, "Mod1" as Question
FROM tblSurvey
WHERE Mod1 is not Null
UNION
SELECT Person, Mod2, "Mod2"
FROM tblSurvey
WHERE Mod2 is not Null
UNION
SELECT Person, Mod3, "Mod3"
FROM tblSurvey
WHERE Mod3 is not Null
UNION
....etc...
This union query should allow you to create your report.

--
Duane Hookom
MS Access MVP


I imported data from an Excel file into Access. This
is a
survey database where different people will receive
different modules. There are a total of 16 modules.
Example

Person 1: Modules 1, 2, 3, 5-7, 14
Person 2: Moduless 2 and 3
Person 3: Module 1 - 9
Person 4: Modules 5
etc...

The data is compiled with all of the modules in one
file.
The column headers are the questions. Because each
person
may not receive all modules there are balnk field where
the person did not give a response.

I would like to pull a report by person that contains
each
module the person received with their responses and
leaves
out any modules that did not have responses.

I tried doing this through a query but when I tried to
pull the report from the different queries Acess did not
allow me to. Any ideas?
Thanks,
Rachel


.
Click to expand...


.
Click to expand...
Click to expand...
 
Thanks, I am working with it now. I will let you know how
it goes.
-----Original Message-----
I'm not sure why you state "when you want to link the same information from
two different tables or queries" and then you show sql that proves
otherwise. You can include any of the fields from the original table(s) as
well as create your own. I would think you need to add "UNION...." for each
question in your table.

I suppose it's a bit late now but to see how you can properly normalize a
survey, check the At Your Survey demo at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hook om,Duane.

--
Duane Hookom
MS Access MVP


There are no periods in the field names, it was just an
example. The actual field names are the questions
themselves. I tried the union query but it only works
when you want to link the same information from two
different tables or queries. I tried doing this exactly:

SELECT [Account Management],[AM Overall quality and
support]
FROM [Survey Table]
WHERE [Account Management]="AM" (that is how I tell they
received the module)
UNION SELECT [Account Development],[AD Overall quality and
support]
FROM [Survey Table]
WHERE [Account Development]="AD"

All I got were two fields. The Account Management and the
AM Overall quality and support. Any other ideas? Am I
doing something wrong?
-----Original Message-----
Did you try a union query as I suggested only substituting your field names?
Do you actually have periods in your field names? How many fields do you
have?

--
Duane Hookom
MS Access MVP


Okay let me do this as best I can:

Mod = Module
Q = Question

Field Names = [Mod1] [Mod2] [Mod3] [Mod4] [Mod etc.]
[Mod1 Q.1] [Mod1 Q.2] [Mod1 Q.3] [Mod1 Q.etc] {Mod2 Q.1]
[Mod2 Q.2] [Mod2 Q.etc] [Mod(etc) Q.1]

Row = Person

The value that corresponds to [Mod1][Row1] is "X" - which
means that the person received that particular module.
Therefore, as you continue to make your way down the row,
depending on how many modules there is an "X" for,
Click to expand...
you
may
pass up several modules with their corresponding 8 - 10
module questions until you get to a modules that was
assigned to that person.

Rather than have a report that pulls all modules and their
questions and have a list of modules for each person that
has a "NA" value, I want the report to run for only the
modules that the person received.

Thanks!

-----Original Message-----
It would really help if you typed or pasted some sample
data with actual
field and table names into your message. I can only guess
that you should
attempt to normalize the imported records with a union
query:
SELECT Person, Mod1, "Mod1" as Question
FROM tblSurvey
WHERE Mod1 is not Null
UNION
SELECT Person, Mod2, "Mod2"
FROM tblSurvey
WHERE Mod2 is not Null
UNION
SELECT Person, Mod3, "Mod3"
FROM tblSurvey
WHERE Mod3 is not Null
UNION
....etc...
This union query should allow you to create your report.

--
Duane Hookom
MS Access MVP


I imported data from an Excel file into Access. This
is a
survey database where different people will receive
different modules. There are a total of 16 modules.
Example

Person 1: Modules 1, 2, 3, 5-7, 14
Person 2: Moduless 2 and 3
Person 3: Module 1 - 9
Person 4: Modules 5
etc...

The data is compiled with all of the modules in one
file.
The column headers are the questions. Because each
person
may not receive all modules there are balnk field where
the person did not give a response.

I would like to pull a report by person that contains
each
module the person received with their responses and
leaves
out any modules that did not have responses.

I tried doing this through a query but when I
Click to expand...
tried
to
pull the report from the different queries Acess
Click to expand...
did
not
allow me to. Any ideas?
Thanks,
Rachel


.



.
Click to expand...
Click to expand...


.
Click to expand...
 
Ok, so I was able to enter my data into your access
database and pull some reports. The reports I have a
question on is the rptIndividualSurvey. That is exactly
what I would like the report to like like however, I would
like to create a separate report that includes ALL
responses the respondent gave for each survey. So it
would look like this

Survey Results

Repondent: 3ZPTKU
Question# Question Text
---------------------------------------
Survey Name: AM
Description: Account Mgmt.

01 Overall ability to ....
02 Knowledge of ....
03 Ability to....
etc.

Survey Name: AD
Description: Account Development

01 Overall ability to ....
02 Knowledge of ....
03 Ability to....
etc.

Survey Name: VB
Description: Visual Basic

01 Overall ability to ....
02 Knowledge of ....
03 Ability to....
etc.

How can that happen?
-----Original Message-----
Thanks, I am working with it now. I will let you know how
it goes.
-----Original Message-----
I'm not sure why you state "when you want to link the same information from
two different tables or queries" and then you show sql that proves
otherwise. You can include any of the fields from the original table(s) as
well as create your own. I would think you need to add "UNION...." for each
question in your table.

I suppose it's a bit late now but to see how you can properly normalize a
survey, check the At Your Survey demo at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hoo
Click to expand...
k
om,Duane.

--
Duane Hookom
MS Access MVP


There are no periods in the field names, it was just an
example. The actual field names are the questions
themselves. I tried the union query but it only works
when you want to link the same information from two
different tables or queries. I tried doing this exactly:

SELECT [Account Management],[AM Overall quality and
support]
FROM [Survey Table]
WHERE [Account Management]="AM" (that is how I tell they
received the module)
UNION SELECT [Account Development],[AD Overall quality and
support]
FROM [Survey Table]
WHERE [Account Development]="AD"

All I got were two fields. The Account Management and the
AM Overall quality and support. Any other ideas? Am I
doing something wrong?
-----Original Message-----
Did you try a union query as I suggested only
substituting your field names?
Do you actually have periods in your field names? How
many fields do you
have?

--
Duane Hookom
MS Access MVP


message
Okay let me do this as best I can:

Mod = Module
Q = Question

Field Names = [Mod1] [Mod2] [Mod3] [Mod4] [Mod etc.]
[Mod1 Q.1] [Mod1 Q.2] [Mod1 Q.3] [Mod1 Q.etc] {Mod2 Q.1]
[Mod2 Q.2] [Mod2 Q.etc] [Mod(etc) Q.1]

Row = Person

The value that corresponds to [Mod1][Row1] is "X" -
which
means that the person received that particular module.
Therefore, as you continue to make your way down the
row,
depending on how many modules there is an "X" for, you
may
pass up several modules with their corresponding 8 -
Click to expand...
Click to expand...
10


.
Click to expand...
.
Click to expand...
 
Back
Top