printing multiple iterations with a serial number

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am developing a db that will be used to print questionnaires. I have a form
that allows a user to select questions by using a series of combo boxes. I
then use a report to place the questions in text boxes and print the
questionnaire. This is working, to some degree.

What I am having difficulty with is that I want to print a quantity, say 25,
of the same questionnaire (report) with a serial number that is printed on
the questionnaire and recorded in a history table. I have no idea how to go
about this. Can someone help?
 
the "cheapest" way to do this is to create a table with, for instance,
the numbers 1-50 (or whatever is the maximum number of copies you will
allow)

*Qtys*
Qty, integer

records are

Qty
1
2
3
etc

in a query: use your table and the Qtys table as fieldlists

put the items you want printed on the grid and then, this also:

field --> Qty
table --> Qtys
show --> no
criteria --> <= QtyToPrint


This will cause Access to create the information multiple times in the
underlying recordset

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
 
Crystal,

I tested your recommendation and I see how the quantity portion works, but
I'm still in the dark on how to create a serial number for each copy that is
printed. Any ideas?

Thanks,
dc
 
if you want a seperate tracking number for each printed copy, it would
be best to make a table and use that -- you will need to match up the
answers... won't you?

Can you explain more about the purpose of your application?

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
 
My application is one of those beasts with several heads and several masters.
The portion I have tried to describe is focused on selecting questions to be
printed on a generic [paper] form with designated response areas to be read
by an OMR scanner. Through a few preliminary combo boxes where an instructor
will identify his program, course, class, test or survey, etc., subsequent
combo boxes will be filtered to only reveal appropriate questions from the
question table.

I am using ADO recordsets to write the form's combo box selections to
various tables that will make up a course history. At the same time, I am
using the same selections to populate text boxes on a report with a layout
that matches the pre-printed boxes on a laser printer form.

Right now, my form has a click event that triggers the history writing and
opens the report. It all works and I can easily create 1 questionnaire. Up to
this point, I have been building a course history, but as you surmised, I
need to be able to track the responses individually, not to mention keeping
the response data on a multi-page quesionnaire correctly matched.

So we arrive at my current issue: how to create an output that generates and
prints a serial number for each questionnaire. (In retrospect, my initial
question was innaccurate- I don't want the same thing printed say, 25 times,
but 25 unique things each printed once.)

BTW, I had an awesome day, thanks.
 
based on what you said, I see
1. print multiple copies of a survey, each with a unique serial number
2. the survey content varies with user criteria

so, each time surveys are created, you should have tables something like
this:

*SurveyContent*
ContentID, autonumber
ProgramID, long integer -- program ID from Programs table
ClassID, long integer -- class ID from Classes table
CourseID, long integer -- course ID from Courses table
(may not need to store this since Classes would probably contains it)
InstructorID, long integer
(again, may not need to store this since class identifies it)
SurveyID, long integer -- Survey set that was generated
DateCreated, datetime, DefaultValue --> =Now()

In the SurveyCriteria table, you store the criteria to create the
surveys and match that up with the survey -- I just used this criteria
as examples, you can expand on that to include other criteria of course
;) -- if you need this information for historical purposes

*Surveys*
SurveyID, autonumber
survTempID, long integer
Qty, integer
DateCreated, datetime, DefaultValue --> =Now()

*SerialNumbers*
SerialID, autonumber
SurveyID, long integer
SerialNumber, long integer
(for the user to reference if you do not want to use the SerialID)
DateCreated, datetime, DefaultValue --> =Now()

Surveys and SerialNumbers are used to store information when the system
prints surveys, and each is uniquely identified

*dfn_Questions*
QuestionID, autonumber
Question, text

*dfn_Responses*
RespID, autonumber
QuestionID, long integer
Ordr, integer (order)
Response, text

*dfn_SurveyTemplates*
survTempID, autonumber
TemplateDesc, text --> Template Description
etc... ID fields to identify it's content

*dfn_SurveyQuestions*
survQID, autonumber
survTempID, long integer
QuestionID, long integer
Ordr, integer (order)

where dfn_ means it is a definition table so that you can print the
right questions in the right order

Your user interface collects information to create a record in the
SurveyCriteria table. The set of questions is identified by the survey
template, which is an unbound combo for the user to pick, or determined
automatally by definitions in the system and will provide survTempID so
the system knows which set of questions to use.

Next, the record is created in Surveys, survTempID is filled out, and
the quantity of surveys to generate is specified by the user or
calculated from class sizes

The final step is generating the records in SerialNumbers and then using
those numbers for the reports that print all the actual surveys

In this way, you have a record of what that unique number goes to for
matching answers...and you can also ensure that the same "unique" number
does not get used multiple times...

I realize that my concept of your structure is most likely different
than what you actually have, but hopefully, you can see the logic...

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*


My application is one of those beasts with several heads and several masters.
The portion I have tried to describe is focused on selecting questions to be
printed on a generic [paper] form with designated response areas to be read
by an OMR scanner. Through a few preliminary combo boxes where an instructor
will identify his program, course, class, test or survey, etc., subsequent
combo boxes will be filtered to only reveal appropriate questions from the
question table.

I am using ADO recordsets to write the form's combo box selections to
various tables that will make up a course history. At the same time, I am
using the same selections to populate text boxes on a report with a layout
that matches the pre-printed boxes on a laser printer form.

Right now, my form has a click event that triggers the history writing and
opens the report. It all works and I can easily create 1 questionnaire. Up to
this point, I have been building a course history, but as you surmised, I
need to be able to track the responses individually, not to mention keeping
the response data on a multi-page quesionnaire correctly matched.

So we arrive at my current issue: how to create an output that generates and
prints a serial number for each questionnaire. (In retrospect, my initial
question was innaccurate- I don't want the same thing printed say, 25 times,
but 25 unique things each printed once.)

BTW, I had an awesome day, thanks.

strive4peace said:
if you want a seperate tracking number for each printed copy, it would
be best to make a table and use that -- you will need to match up the
answers... won't you?

Can you explain more about the purpose of your application?

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
Click to expand...
Click to expand...
 
Wow, it's been over a week and I'm still working it out. I thought I might
have a few more questions by now, but making the transition from conceptual
model to the actual code is taking longer than I imagined.

Thanks Crystal for the help with the logic, it's gotten me going in the
right direction.

strive4peace said:
based on what you said, I see
1. print multiple copies of a survey, each with a unique serial number
2. the survey content varies with user criteria

so, each time surveys are created, you should have tables something like
this:

*SurveyContent*
ContentID, autonumber
ProgramID, long integer -- program ID from Programs table
ClassID, long integer -- class ID from Classes table
CourseID, long integer -- course ID from Courses table
(may not need to store this since Classes would probably contains it)
InstructorID, long integer
(again, may not need to store this since class identifies it)
SurveyID, long integer -- Survey set that was generated
DateCreated, datetime, DefaultValue --> =Now()

In the SurveyCriteria table, you store the criteria to create the
surveys and match that up with the survey -- I just used this criteria
as examples, you can expand on that to include other criteria of course
;) -- if you need this information for historical purposes

*Surveys*
SurveyID, autonumber
survTempID, long integer
Qty, integer
DateCreated, datetime, DefaultValue --> =Now()

*SerialNumbers*
SerialID, autonumber
SurveyID, long integer
SerialNumber, long integer
(for the user to reference if you do not want to use the SerialID)
DateCreated, datetime, DefaultValue --> =Now()

Surveys and SerialNumbers are used to store information when the system
prints surveys, and each is uniquely identified

*dfn_Questions*
QuestionID, autonumber
Question, text

*dfn_Responses*
RespID, autonumber
QuestionID, long integer
Ordr, integer (order)
Response, text

*dfn_SurveyTemplates*
survTempID, autonumber
TemplateDesc, text --> Template Description
etc... ID fields to identify it's content

*dfn_SurveyQuestions*
survQID, autonumber
survTempID, long integer
QuestionID, long integer
Ordr, integer (order)

where dfn_ means it is a definition table so that you can print the
right questions in the right order

Your user interface collects information to create a record in the
SurveyCriteria table. The set of questions is identified by the survey
template, which is an unbound combo for the user to pick, or determined
automatally by definitions in the system and will provide survTempID so
the system knows which set of questions to use.

Next, the record is created in Surveys, survTempID is filled out, and
the quantity of surveys to generate is specified by the user or
calculated from class sizes

The final step is generating the records in SerialNumbers and then using
those numbers for the reports that print all the actual surveys

In this way, you have a record of what that unique number goes to for
matching answers...and you can also ensure that the same "unique" number
does not get used multiple times...

I realize that my concept of your structure is most likely different
than what you actually have, but hopefully, you can see the logic...

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*


My application is one of those beasts with several heads and several masters.
The portion I have tried to describe is focused on selecting questions to be
printed on a generic [paper] form with designated response areas to be read
by an OMR scanner. Through a few preliminary combo boxes where an instructor
will identify his program, course, class, test or survey, etc., subsequent
combo boxes will be filtered to only reveal appropriate questions from the
question table.

I am using ADO recordsets to write the form's combo box selections to
various tables that will make up a course history. At the same time, I am
using the same selections to populate text boxes on a report with a layout
that matches the pre-printed boxes on a laser printer form.

Right now, my form has a click event that triggers the history writing and
opens the report. It all works and I can easily create 1 questionnaire. Up to
this point, I have been building a course history, but as you surmised, I
need to be able to track the responses individually, not to mention keeping
the response data on a multi-page quesionnaire correctly matched.

So we arrive at my current issue: how to create an output that generates and
prints a serial number for each questionnaire. (In retrospect, my initial
question was innaccurate- I don't want the same thing printed say, 25 times,
but 25 unique things each printed once.)

BTW, I had an awesome day, thanks.

strive4peace said:
if you want a seperate tracking number for each printed copy, it would
be best to make a table and use that -- you will need to match up the
answers... won't you?

Can you explain more about the purpose of your application?

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



bikeweenie wrote:
Crystal,

I tested your recommendation and I see how the quantity portion works, but
I'm still in the dark on how to create a serial number for each copy that is
printed. Any ideas?

Thanks,
dc

:

the "cheapest" way to do this is to create a table with, for instance,
the numbers 1-50 (or whatever is the maximum number of copies you will
allow)

*Qtys*
Qty, integer

records are

Qty
1
2
3
etc

in a query: use your table and the Qtys table as fieldlists

put the items you want printed on the grid and then, this also:

field --> Qty
table --> Qtys
show --> no
criteria --> <= QtyToPrint


This will cause Access to create the information multiple times in the
underlying recordset

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



bikeweenie wrote:
I am developing a db that will be used to print questionnaires. I have a form
that allows a user to select questions by using a series of combo boxes. I
then use a report to place the questions in text boxes and print the
questionnaire. This is working, to some degree.

What I am having difficulty with is that I want to print a quantity, say 25,
of the same questionnaire (report) with a serial number that is printed on
the questionnaire and recorded in a history table. I have no idea how to go
about this. Can someone help?
Click to expand...
Click to expand...
Click to expand...
 
Thanks for your help, Crystal! You've pointed me in the right direction. I'm
still working on the transition from logic/conceptual framework to the actual
code.

Warmly,
dc

strive4peace said:
based on what you said, I see
1. print multiple copies of a survey, each with a unique serial number
2. the survey content varies with user criteria

so, each time surveys are created, you should have tables something like
this:

*SurveyContent*
ContentID, autonumber
ProgramID, long integer -- program ID from Programs table
ClassID, long integer -- class ID from Classes table
CourseID, long integer -- course ID from Courses table
(may not need to store this since Classes would probably contains it)
InstructorID, long integer
(again, may not need to store this since class identifies it)
SurveyID, long integer -- Survey set that was generated
DateCreated, datetime, DefaultValue --> =Now()

In the SurveyCriteria table, you store the criteria to create the
surveys and match that up with the survey -- I just used this criteria
as examples, you can expand on that to include other criteria of course
;) -- if you need this information for historical purposes

*Surveys*
SurveyID, autonumber
survTempID, long integer
Qty, integer
DateCreated, datetime, DefaultValue --> =Now()

*SerialNumbers*
SerialID, autonumber
SurveyID, long integer
SerialNumber, long integer
(for the user to reference if you do not want to use the SerialID)
DateCreated, datetime, DefaultValue --> =Now()

Surveys and SerialNumbers are used to store information when the system
prints surveys, and each is uniquely identified

*dfn_Questions*
QuestionID, autonumber
Question, text

*dfn_Responses*
RespID, autonumber
QuestionID, long integer
Ordr, integer (order)
Response, text

*dfn_SurveyTemplates*
survTempID, autonumber
TemplateDesc, text --> Template Description
etc... ID fields to identify it's content

*dfn_SurveyQuestions*
survQID, autonumber
survTempID, long integer
QuestionID, long integer
Ordr, integer (order)

where dfn_ means it is a definition table so that you can print the
right questions in the right order

Your user interface collects information to create a record in the
SurveyCriteria table. The set of questions is identified by the survey
template, which is an unbound combo for the user to pick, or determined
automatally by definitions in the system and will provide survTempID so
the system knows which set of questions to use.

Next, the record is created in Surveys, survTempID is filled out, and
the quantity of surveys to generate is specified by the user or
calculated from class sizes

The final step is generating the records in SerialNumbers and then using
those numbers for the reports that print all the actual surveys

In this way, you have a record of what that unique number goes to for
matching answers...and you can also ensure that the same "unique" number
does not get used multiple times...

I realize that my concept of your structure is most likely different
than what you actually have, but hopefully, you can see the logic...

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*


My application is one of those beasts with several heads and several masters.
The portion I have tried to describe is focused on selecting questions to be
printed on a generic [paper] form with designated response areas to be read
by an OMR scanner. Through a few preliminary combo boxes where an instructor
will identify his program, course, class, test or survey, etc., subsequent
combo boxes will be filtered to only reveal appropriate questions from the
question table.

I am using ADO recordsets to write the form's combo box selections to
various tables that will make up a course history. At the same time, I am
using the same selections to populate text boxes on a report with a layout
that matches the pre-printed boxes on a laser printer form.

Right now, my form has a click event that triggers the history writing and
opens the report. It all works and I can easily create 1 questionnaire. Up to
this point, I have been building a course history, but as you surmised, I
need to be able to track the responses individually, not to mention keeping
the response data on a multi-page quesionnaire correctly matched.

So we arrive at my current issue: how to create an output that generates and
prints a serial number for each questionnaire. (In retrospect, my initial
question was innaccurate- I don't want the same thing printed say, 25 times,
but 25 unique things each printed once.)

BTW, I had an awesome day, thanks.

strive4peace said:
if you want a seperate tracking number for each printed copy, it would
be best to make a table and use that -- you will need to match up the
answers... won't you?

Can you explain more about the purpose of your application?

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



bikeweenie wrote:
Crystal,

I tested your recommendation and I see how the quantity portion works, but
I'm still in the dark on how to create a serial number for each copy that is
printed. Any ideas?

Thanks,
dc

:

the "cheapest" way to do this is to create a table with, for instance,
the numbers 1-50 (or whatever is the maximum number of copies you will
allow)

*Qtys*
Qty, integer

records are

Qty
1
2
3
etc

in a query: use your table and the Qtys table as fieldlists

put the items you want printed on the grid and then, this also:

field --> Qty
table --> Qtys
show --> no
criteria --> <= QtyToPrint


This will cause Access to create the information multiple times in the
underlying recordset

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



bikeweenie wrote:
I am developing a db that will be used to print questionnaires. I have a form
that allows a user to select questions by using a series of combo boxes. I
then use a report to place the questions in text boxes and print the
questionnaire. This is working, to some degree.

What I am having difficulty with is that I want to print a quantity, say 25,
of the same questionnaire (report) with a serial number that is printed on
the questionnaire and recorded in a history table. I have no idea how to go
about this. Can someone help?
Click to expand...
Click to expand...
Click to expand...
 
you're welcome ;) happy to help

remember that structuring data is an iterative process when you are
getting things set up; it is natural to change it as you see better,
more stable ways...

use the relationship diagram to help you... I find that printing it out,
studying it, changing it... is a great help. Be sure to stretch out all
the fieldlists, enforce referential integrity, and arrange tables so
that data flow one way, usually left-right since that is how we read...

I like to press PrintScreen, paste (CTRL-V) to Paint, and Save As a JPG
(about 1/10th the size of a BMP) ... then use Word or PowerPoint (for
instance) to Print -- Insert, Picture, from file...

I have a 30-page Word document on Access Basics that includes some
information on designing data structures that I will be happy to send if
you email me -- please be sure to say "basics" in the subject of the
message so I don't delete it ;)

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*


Wow, it's been over a week and I'm still working it out. I thought I might
have a few more questions by now, but making the transition from conceptual
model to the actual code is taking longer than I imagined.

Thanks Crystal for the help with the logic, it's gotten me going in the
right direction.

strive4peace said:
based on what you said, I see
1. print multiple copies of a survey, each with a unique serial number
2. the survey content varies with user criteria

so, each time surveys are created, you should have tables something like
this:

*SurveyContent*
ContentID, autonumber
ProgramID, long integer -- program ID from Programs table
ClassID, long integer -- class ID from Classes table
CourseID, long integer -- course ID from Courses table
(may not need to store this since Classes would probably contains it)
InstructorID, long integer
(again, may not need to store this since class identifies it)
SurveyID, long integer -- Survey set that was generated
DateCreated, datetime, DefaultValue --> =Now()

In the SurveyCriteria table, you store the criteria to create the
surveys and match that up with the survey -- I just used this criteria
as examples, you can expand on that to include other criteria of course
;) -- if you need this information for historical purposes

*Surveys*
SurveyID, autonumber
survTempID, long integer
Qty, integer
DateCreated, datetime, DefaultValue --> =Now()

*SerialNumbers*
SerialID, autonumber
SurveyID, long integer
SerialNumber, long integer
(for the user to reference if you do not want to use the SerialID)
DateCreated, datetime, DefaultValue --> =Now()

Surveys and SerialNumbers are used to store information when the system
prints surveys, and each is uniquely identified

*dfn_Questions*
QuestionID, autonumber
Question, text

*dfn_Responses*
RespID, autonumber
QuestionID, long integer
Ordr, integer (order)
Response, text

*dfn_SurveyTemplates*
survTempID, autonumber
TemplateDesc, text --> Template Description
etc... ID fields to identify it's content

*dfn_SurveyQuestions*
survQID, autonumber
survTempID, long integer
QuestionID, long integer
Ordr, integer (order)

where dfn_ means it is a definition table so that you can print the
right questions in the right order

Your user interface collects information to create a record in the
SurveyCriteria table. The set of questions is identified by the survey
template, which is an unbound combo for the user to pick, or determined
automatally by definitions in the system and will provide survTempID so
the system knows which set of questions to use.

Next, the record is created in Surveys, survTempID is filled out, and
the quantity of surveys to generate is specified by the user or
calculated from class sizes

The final step is generating the records in SerialNumbers and then using
those numbers for the reports that print all the actual surveys

In this way, you have a record of what that unique number goes to for
matching answers...and you can also ensure that the same "unique" number
does not get used multiple times...

I realize that my concept of your structure is most likely different
than what you actually have, but hopefully, you can see the logic...

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*


My application is one of those beasts with several heads and several masters.
The portion I have tried to describe is focused on selecting questions to be
printed on a generic [paper] form with designated response areas to be read
by an OMR scanner. Through a few preliminary combo boxes where an instructor
will identify his program, course, class, test or survey, etc., subsequent
combo boxes will be filtered to only reveal appropriate questions from the
question table.

I am using ADO recordsets to write the form's combo box selections to
various tables that will make up a course history. At the same time, I am
using the same selections to populate text boxes on a report with a layout
that matches the pre-printed boxes on a laser printer form.

Right now, my form has a click event that triggers the history writing and
opens the report. It all works and I can easily create 1 questionnaire. Up to
this point, I have been building a course history, but as you surmised, I
need to be able to track the responses individually, not to mention keeping
the response data on a multi-page quesionnaire correctly matched.

So we arrive at my current issue: how to create an output that generates and
prints a serial number for each questionnaire. (In retrospect, my initial
question was innaccurate- I don't want the same thing printed say, 25 times,
but 25 unique things each printed once.)

BTW, I had an awesome day, thanks.

:

if you want a seperate tracking number for each printed copy, it would
be best to make a table and use that -- you will need to match up the
answers... won't you?

Can you explain more about the purpose of your application?

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



bikeweenie wrote:
Crystal,

I tested your recommendation and I see how the quantity portion works, but
I'm still in the dark on how to create a serial number for each copy that is
printed. Any ideas?

Thanks,
dc

:

the "cheapest" way to do this is to create a table with, for instance,
the numbers 1-50 (or whatever is the maximum number of copies you will
allow)

*Qtys*
Qty, integer

records are

Qty
1
2
3
etc

in a query: use your table and the Qtys table as fieldlists

put the items you want printed on the grid and then, this also:

field --> Qty
table --> Qtys
show --> no
criteria --> <= QtyToPrint


This will cause Access to create the information multiple times in the
underlying recordset

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



bikeweenie wrote:
I am developing a db that will be used to print questionnaires. I have a form
that allows a user to select questions by using a series of combo boxes. I
then use a report to place the questions in text boxes and print the
questionnaire. This is working, to some degree.

What I am having difficulty with is that I want to print a quantity, say 25,
of the same questionnaire (report) with a serial number that is printed on
the questionnaire and recorded in a history table. I have no idea how to go
about this. Can someone help?
Click to expand...
Click to expand...
Click to expand...
 
When I look at my relationship diagram, there are portions that look like a
bird's nest, with data flowing in more than one direction. Currently, my
relationship diagram fits best on a rather large sheet of paper because it is
seemingly very convoluted.

Is it common and/or acceptable to have a field in one table pointing to more
than one other table?

strive4peace said:
you're welcome ;) happy to help

remember that structuring data is an iterative process when you are
getting things set up; it is natural to change it as you see better,
more stable ways...

use the relationship diagram to help you... I find that printing it out,
studying it, changing it... is a great help. Be sure to stretch out all
the fieldlists, enforce referential integrity, and arrange tables so
that data flow one way, usually left-right since that is how we read...

I like to press PrintScreen, paste (CTRL-V) to Paint, and Save As a JPG
(about 1/10th the size of a BMP) ... then use Word or PowerPoint (for
instance) to Print -- Insert, Picture, from file...

I have a 30-page Word document on Access Basics that includes some
information on designing data structures that I will be happy to send if
you email me -- please be sure to say "basics" in the subject of the
message so I don't delete it ;)

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*


Wow, it's been over a week and I'm still working it out. I thought I might
have a few more questions by now, but making the transition from conceptual
model to the actual code is taking longer than I imagined.

Thanks Crystal for the help with the logic, it's gotten me going in the
right direction.

strive4peace said:
based on what you said, I see
1. print multiple copies of a survey, each with a unique serial number
2. the survey content varies with user criteria

so, each time surveys are created, you should have tables something like
this:

*SurveyContent*
ContentID, autonumber
ProgramID, long integer -- program ID from Programs table
ClassID, long integer -- class ID from Classes table
CourseID, long integer -- course ID from Courses table
(may not need to store this since Classes would probably contains it)
InstructorID, long integer
(again, may not need to store this since class identifies it)
SurveyID, long integer -- Survey set that was generated
DateCreated, datetime, DefaultValue --> =Now()

In the SurveyCriteria table, you store the criteria to create the
surveys and match that up with the survey -- I just used this criteria
as examples, you can expand on that to include other criteria of course
;) -- if you need this information for historical purposes

*Surveys*
SurveyID, autonumber
survTempID, long integer
Qty, integer
DateCreated, datetime, DefaultValue --> =Now()

*SerialNumbers*
SerialID, autonumber
SurveyID, long integer
SerialNumber, long integer
(for the user to reference if you do not want to use the SerialID)
DateCreated, datetime, DefaultValue --> =Now()

Surveys and SerialNumbers are used to store information when the system
prints surveys, and each is uniquely identified

*dfn_Questions*
QuestionID, autonumber
Question, text

*dfn_Responses*
RespID, autonumber
QuestionID, long integer
Ordr, integer (order)
Response, text

*dfn_SurveyTemplates*
survTempID, autonumber
TemplateDesc, text --> Template Description
etc... ID fields to identify it's content

*dfn_SurveyQuestions*
survQID, autonumber
survTempID, long integer
QuestionID, long integer
Ordr, integer (order)

where dfn_ means it is a definition table so that you can print the
right questions in the right order

Your user interface collects information to create a record in the
SurveyCriteria table. The set of questions is identified by the survey
template, which is an unbound combo for the user to pick, or determined
automatally by definitions in the system and will provide survTempID so
the system knows which set of questions to use.

Next, the record is created in Surveys, survTempID is filled out, and
the quantity of surveys to generate is specified by the user or
calculated from class sizes

The final step is generating the records in SerialNumbers and then using
those numbers for the reports that print all the actual surveys

In this way, you have a record of what that unique number goes to for
matching answers...and you can also ensure that the same "unique" number
does not get used multiple times...

I realize that my concept of your structure is most likely different
than what you actually have, but hopefully, you can see the logic...

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



bikeweenie wrote:
My application is one of those beasts with several heads and several masters.
The portion I have tried to describe is focused on selecting questions to be
printed on a generic [paper] form with designated response areas to be read
by an OMR scanner. Through a few preliminary combo boxes where an instructor
will identify his program, course, class, test or survey, etc., subsequent
combo boxes will be filtered to only reveal appropriate questions from the
question table.

I am using ADO recordsets to write the form's combo box selections to
various tables that will make up a course history. At the same time, I am
using the same selections to populate text boxes on a report with a layout
that matches the pre-printed boxes on a laser printer form.

Right now, my form has a click event that triggers the history writing and
opens the report. It all works and I can easily create 1 questionnaire. Up to
this point, I have been building a course history, but as you surmised, I
need to be able to track the responses individually, not to mention keeping
the response data on a multi-page quesionnaire correctly matched.

So we arrive at my current issue: how to create an output that generates and
prints a serial number for each questionnaire. (In retrospect, my initial
question was innaccurate- I don't want the same thing printed say, 25 times,
but 25 unique things each printed once.)

BTW, I had an awesome day, thanks.

:

if you want a seperate tracking number for each printed copy, it would
be best to make a table and use that -- you will need to match up the
answers... won't you?

Can you explain more about the purpose of your application?

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



bikeweenie wrote:
Crystal,

I tested your recommendation and I see how the quantity portion works, but
I'm still in the dark on how to create a serial number for each copy that is
printed. Any ideas?

Thanks,
dc

:

the "cheapest" way to do this is to create a table with, for instance,
the numbers 1-50 (or whatever is the maximum number of copies you will
allow)

*Qtys*
Qty, integer

records are

Qty
1
2
3
etc

in a query: use your table and the Qtys table as fieldlists

put the items you want printed on the grid and then, this also:

field --> Qty
table --> Qtys
show --> no
criteria --> <= QtyToPrint


This will cause Access to create the information multiple times in the
underlying recordset

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



bikeweenie wrote:
I am developing a db that will be used to print questionnaires. I have a form
that allows a user to select questions by using a series of combo boxes. I
then use a report to place the questions in text boxes and print the
questionnaire. This is working, to some degree.

What I am having difficulty with is that I want to print a quantity, say 25,
of the same questionnaire (report) with a serial number that is printed on
the questionnaire and recorded in a history table. I have no idea how to go
about this. Can someone help?
Click to expand...
Click to expand...
Click to expand...
 
Hi,

"Is it common and/or acceptable to have a field in one table pointing to
more than one other table?"

sure, especially for key fields like PersonID from a Persons table --
you would have a foreign key in Addresses, Phones, Emails, Personal, etc...

With referential integrity enforced, all the link lines should be labeled.

If I have too many tables to stretch out on one screen, I use two... or
more. Each set of tables is grouped by functionality.

I like to put all key fields at or near the top of a fieldlist -- and I
rearange them so join lines don't cross as much on the diagram.

If you make your resolution higher, don't make tables wider than they
need to be -- hopefully, you were concise with naming ;) -- you would be
amazed at what you can fit on one screen.

To print, I use PrintScreen, paste to Paint, change gray background to
white with the Paint bucket icon (use less ink to print), save as a JPG
(about 1/10th the size of a BMP), then put into Word or PowerPoint -- as
opposed to using File, Print from the menu of the Relationships window

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*


When I look at my relationship diagram, there are portions that look like a
bird's nest, with data flowing in more than one direction. Currently, my
relationship diagram fits best on a rather large sheet of paper because it is
seemingly very convoluted.

Is it common and/or acceptable to have a field in one table pointing to more
than one other table?

strive4peace said:
you're welcome ;) happy to help

remember that structuring data is an iterative process when you are
getting things set up; it is natural to change it as you see better,
more stable ways...

use the relationship diagram to help you... I find that printing it out,
studying it, changing it... is a great help. Be sure to stretch out all
the fieldlists, enforce referential integrity, and arrange tables so
that data flow one way, usually left-right since that is how we read...

I like to press PrintScreen, paste (CTRL-V) to Paint, and Save As a JPG
(about 1/10th the size of a BMP) ... then use Word or PowerPoint (for
instance) to Print -- Insert, Picture, from file...

I have a 30-page Word document on Access Basics that includes some
information on designing data structures that I will be happy to send if
you email me -- please be sure to say "basics" in the subject of the
message so I don't delete it ;)

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*


Wow, it's been over a week and I'm still working it out. I thought I might
have a few more questions by now, but making the transition from conceptual
model to the actual code is taking longer than I imagined.

Thanks Crystal for the help with the logic, it's gotten me going in the
right direction.

:

based on what you said, I see
1. print multiple copies of a survey, each with a unique serial number
2. the survey content varies with user criteria

so, each time surveys are created, you should have tables something like
this:

*SurveyContent*
ContentID, autonumber
ProgramID, long integer -- program ID from Programs table
ClassID, long integer -- class ID from Classes table
CourseID, long integer -- course ID from Courses table
(may not need to store this since Classes would probably contains it)
InstructorID, long integer
(again, may not need to store this since class identifies it)
SurveyID, long integer -- Survey set that was generated
DateCreated, datetime, DefaultValue --> =Now()

In the SurveyCriteria table, you store the criteria to create the
surveys and match that up with the survey -- I just used this criteria
as examples, you can expand on that to include other criteria of course
;) -- if you need this information for historical purposes

*Surveys*
SurveyID, autonumber
survTempID, long integer
Qty, integer
DateCreated, datetime, DefaultValue --> =Now()

*SerialNumbers*
SerialID, autonumber
SurveyID, long integer
SerialNumber, long integer
(for the user to reference if you do not want to use the SerialID)
DateCreated, datetime, DefaultValue --> =Now()

Surveys and SerialNumbers are used to store information when the system
prints surveys, and each is uniquely identified

*dfn_Questions*
QuestionID, autonumber
Question, text

*dfn_Responses*
RespID, autonumber
QuestionID, long integer
Ordr, integer (order)
Response, text

*dfn_SurveyTemplates*
survTempID, autonumber
TemplateDesc, text --> Template Description
etc... ID fields to identify it's content

*dfn_SurveyQuestions*
survQID, autonumber
survTempID, long integer
QuestionID, long integer
Ordr, integer (order)

where dfn_ means it is a definition table so that you can print the
right questions in the right order

Your user interface collects information to create a record in the
SurveyCriteria table. The set of questions is identified by the survey
template, which is an unbound combo for the user to pick, or determined
automatally by definitions in the system and will provide survTempID so
the system knows which set of questions to use.

Next, the record is created in Surveys, survTempID is filled out, and
the quantity of surveys to generate is specified by the user or
calculated from class sizes

The final step is generating the records in SerialNumbers and then using
those numbers for the reports that print all the actual surveys

In this way, you have a record of what that unique number goes to for
matching answers...and you can also ensure that the same "unique" number
does not get used multiple times...

I realize that my concept of your structure is most likely different
than what you actually have, but hopefully, you can see the logic...

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



bikeweenie wrote:
My application is one of those beasts with several heads and several masters.
The portion I have tried to describe is focused on selecting questions to be
printed on a generic [paper] form with designated response areas to be read
by an OMR scanner. Through a few preliminary combo boxes where an instructor
will identify his program, course, class, test or survey, etc., subsequent
combo boxes will be filtered to only reveal appropriate questions from the
question table.

I am using ADO recordsets to write the form's combo box selections to
various tables that will make up a course history. At the same time, I am
using the same selections to populate text boxes on a report with a layout
that matches the pre-printed boxes on a laser printer form.

Right now, my form has a click event that triggers the history writing and
opens the report. It all works and I can easily create 1 questionnaire. Up to
this point, I have been building a course history, but as you surmised, I
need to be able to track the responses individually, not to mention keeping
the response data on a multi-page quesionnaire correctly matched.

So we arrive at my current issue: how to create an output that generates and
prints a serial number for each questionnaire. (In retrospect, my initial
question was innaccurate- I don't want the same thing printed say, 25 times,
but 25 unique things each printed once.)

BTW, I had an awesome day, thanks.

:

if you want a seperate tracking number for each printed copy, it would
be best to make a table and use that -- you will need to match up the
answers... won't you?

Can you explain more about the purpose of your application?

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



bikeweenie wrote:
Crystal,

I tested your recommendation and I see how the quantity portion works, but
I'm still in the dark on how to create a serial number for each copy that is
printed. Any ideas?

Thanks,
dc

:

the "cheapest" way to do this is to create a table with, for instance,
the numbers 1-50 (or whatever is the maximum number of copies you will
allow)

*Qtys*
Qty, integer

records are

Qty
1
2
3
etc

in a query: use your table and the Qtys table as fieldlists

put the items you want printed on the grid and then, this also:

field --> Qty
table --> Qtys
show --> no
criteria --> <= QtyToPrint


This will cause Access to create the information multiple times in the
underlying recordset

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



bikeweenie wrote:
I am developing a db that will be used to print questionnaires. I have a form
that allows a user to select questions by using a series of combo boxes. I
then use a report to place the questions in text boxes and print the
questionnaire. This is working, to some degree.

What I am having difficulty with is that I want to print a quantity, say 25,
of the same questionnaire (report) with a serial number that is printed on
the questionnaire and recorded in a history table. I have no idea how to go
about this. Can someone help?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top