Using Query Output to Generate Report

[Archer]

Hi all,
I have an uninteresting problem for which i dunno how to implement certain
parts


I want to execute an existing query (doCmd,"queryName" )

obtain results of the query(dunno how???)

and for each result entry, (a eof while loop should do this trick)
compare two columns check for criteria (know how to do it)
and based on it, generate a report (would [docmd. OpenReport] so the trick)

and populate it with the entry identifier, and some data (dunno how to do it?)

before going to the next entry and repeating the same.

And i'd want it to be done on demand so writing a macro VB code to perform
this seems ideal.

Awaiting your replies

Thanks

 

[Ken Snell \(MVP\)]

Can you not write a query that will do the check of the data, and then base
a report on that query? If not, post more details about the data, the type
of checks, the source of the data used for the checks, what you mean by
"compare two columns", etc. so that we can make suggestions.

 

[Archer]

I have a students database in which I have a query to gather for batch, for
every student, information about total number of courses taken, around 20
columns with the possible courses enrolled (Yes/No Field)and 20 columns for
the possible courses that are registered(Yes/No)

Thus for ever course there exists one entry about enrollment[Course No.
Enrolled?] and one entry for registered [Course No. Registered?]

Although not every student takes all the courses, each record chooses
default value of courses not taken as NO. That's how the existing structure
is and thus can't be changed

For a statistical information survey, I need to create a report that will
let me generate the following information
For every batch,
for every student,
check for the the number of courses enrolled
and check for the courses that are Enrolled but not Registered
and based on it return The Course Number to two fields in the report namely,
Not Enrolled
Not Registered

I am writing a macro to do this. But what I need to know is how can i access
the data from the query and compare it.
And based on the comparison, generate this data into the report.


-Archer

Can you not write a query that will do the check of the data, and then base
a report on that query? If not, post more details about the data, the type
of checks, the source of the data used for the checks, what you mean by
"compare two columns", etc. so that we can make suggestions.

--

Ken Snell
<MS ACCESS MVP>

Hi all,
I have an uninteresting problem for which i dunno how to implement certain
parts


I want to execute an existing query (doCmd,"queryName" )

obtain results of the query(dunno how???)

and for each result entry, (a eof while loop should do this trick)
compare two columns check for criteria (know how to do it)
and based on it, generate a report (would [docmd. OpenReport] so the
trick)

and populate it with the entry identifier, and some data (dunno how to do
it?)

before going to the next entry and repeating the same.

And i'd want it to be done on demand so writing a macro VB code to perform
this seems ideal.

Awaiting your replies

Thanks

 

[KARL DEWEY]

It sounds like your table is laid out as a spreadsheet and not as a
relational database structure.
You need to revise the structure to something like this ----
Student --
StudentID - autonumber - primary key
LName - text
FName -
DOB - DateTime
etc.

Courses --
CourseID - autonumber - primary key
Name - text

Attendance --
CourseID - number - integer - foreign key
StudentID - number - integer - foreign key
ClassDate - DateTime
Grade - Text
Remarks - text

--
KARL DEWEY
Build a little - Test a little

I have a students database in which I have a query to gather for batch, for
every student, information about total number of courses taken, around 20
columns with the possible courses enrolled (Yes/No Field)and 20 columns for
the possible courses that are registered(Yes/No)

Thus for ever course there exists one entry about enrollment[Course No.
Enrolled?] and one entry for registered [Course No. Registered?]

Although not every student takes all the courses, each record chooses
default value of courses not taken as NO. That's how the existing structure
is and thus can't be changed

For a statistical information survey, I need to create a report that will
let me generate the following information
For every batch,
for every student,
check for the the number of courses enrolled
and check for the courses that are Enrolled but not Registered
and based on it return The Course Number to two fields in the report namely,
Not Enrolled
Not Registered

I am writing a macro to do this. But what I need to know is how can i access
the data from the query and compare it.
And based on the comparison, generate this data into the report.


-Archer

Can you not write a query that will do the check of the data, and then base
a report on that query? If not, post more details about the data, the type
of checks, the source of the data used for the checks, what you mean by
"compare two columns", etc. so that we can make suggestions.

--

Ken Snell
<MS ACCESS MVP>

Hi all,
I have an uninteresting problem for which i dunno how to implement certain
parts


I want to execute an existing query (doCmd,"queryName" )

obtain results of the query(dunno how???)

and for each result entry, (a eof while loop should do this trick)
compare two columns check for criteria (know how to do it)
and based on it, generate a report (would [docmd. OpenReport] so the
trick)

and populate it with the entry identifier, and some data (dunno how to do
it?)

before going to the next entry and repeating the same.

And i'd want it to be done on demand so writing a macro VB code to perform
this seems ideal.

Awaiting your replies

Thanks

 

[Archer]

Hey Karl,
As I said in the earlier mail, the current structure isn't all that great
but it can't be changed.

I Just need to know how can the output values of each row from a query
result be accessed.

and how do i iterateively push values into a report.

It sounds like your table is laid out as a spreadsheet and not as a
relational database structure.
You need to revise the structure to something like this ----
Student --
StudentID - autonumber - primary key
LName - text
FName -
DOB - DateTime
etc.

Courses --
CourseID - autonumber - primary key
Name - text

Attendance --
CourseID - number - integer - foreign key
StudentID - number - integer - foreign key
ClassDate - DateTime
Grade - Text
Remarks - text

--
KARL DEWEY
Build a little - Test a little

I have a students database in which I have a query to gather for batch, for
every student, information about total number of courses taken, around 20
columns with the possible courses enrolled (Yes/No Field)and 20 columns for
the possible courses that are registered(Yes/No)

Thus for ever course there exists one entry about enrollment[Course No.
Enrolled?] and one entry for registered [Course No. Registered?]

Although not every student takes all the courses, each record chooses
default value of courses not taken as NO. That's how the existing structure
is and thus can't be changed

For a statistical information survey, I need to create a report that will
let me generate the following information
For every batch,
for every student,
check for the the number of courses enrolled
and check for the courses that are Enrolled but not Registered
and based on it return The Course Number to two fields in the report namely,
Not Enrolled
Not Registered

I am writing a macro to do this. But what I need to know is how can i access
the data from the query and compare it.
And based on the comparison, generate this data into the report.


-Archer

Can you not write a query that will do the check of the data, and then base
a report on that query? If not, post more details about the data, the type
of checks, the source of the data used for the checks, what you mean by
"compare two columns", etc. so that we can make suggestions.

--

Ken Snell
<MS ACCESS MVP>


Hi all,
I have an uninteresting problem for which i dunno how to implement certain
parts


I want to execute an existing query (doCmd,"queryName" )

obtain results of the query(dunno how???)

and for each result entry, (a eof while loop should do this trick)
compare two columns check for criteria (know how to do it)
and based on it, generate a report (would [docmd. OpenReport] so the
trick)

and populate it with the entry identifier, and some data (dunno how to do
it?)

before going to the next entry and repeating the same.

And i'd want it to be done on demand so writing a macro VB code to perform
this seems ideal.

Awaiting your replies

Thanks