Combining Records

S

Shoelaces

I am sure this is not the optimal manner for doing what I want to do, but my
lack of skills has led me to this path. Anyhow . . .

I have a query that has several records. I desire to take this query and
create another that has but one record that contains all of the fields
contained in the first table.

For instance:
Query1
Record1 ID, topic, obj1, obj2, stand1, stand2, stand3, procedure, assess
Record2 ID, topic, obj1, obj2, stand1, stand2, stand3, procedure, assess
etc.

Would become
Query2
ID-1, topic-1, obj1-1, obj2-1, stand1-1, stand2-1, stand3-1, procedure-1,
assess-1, ID-2, topic-2, obj1-2, obj2-2, stand1-2, stand2-2, stand3-2,
procedure-2, assess-2, etc.

Query2 would only have one record with many fields.

Is this possible? If so, how would I go about creating this?

Thanks for the help.
 
K

Ken Snell \(MVP\)

How does your table identify the difference between "Record1" and "Record2"?
What is the primary key in the table?
 
S

Shoelaces

Ken Snell (MVP) said:
How does your table identify the difference between "Record1" and "Record2"?
What is the primary key in the table?

I am not certain I understand the first question.

I have a table of lesson plans. Each record is a separate lesson plan. The
primary key for that table is a unique ID # (auto-incremented). I envision
this table having 1000 records.

I teach 40 lessons per week. I have created a second table with 40 periods
for the week. A second field is linked to the table of lesson plans on the
ID field. This table has no primary key.

I have a query that joins (I hope that is the correct word) the period ID,
the lesson ID, topic, objectives, standards, procedures, and assessment for
the 40 lessons for the week.

I would like to then combine all those fields (8 fields per record by 40
records) to create one record with the 320 fields. From there I think I can
merge the data into a lesson plan template in Word. As I said initially,
there is probably a better way of doing this, but I am lost. This way makes
sense with what I understand about Access and Word.

Anyhow, I am stumped how to create the one record massive query.
 
J

John Spencer

I must say that the first problem is that NO query or table in Access
can have more than 255 fields. So your goal of 320 fields in a table or
a query is not reachable.

I'm not sure how to accomplish something that will satisfy your need,
but I will mull this over and if I can understand what you want and come
up with a possible solution, I will post back.

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

Shoelaces

John Spencer said:
I must say that the first problem is that NO query or table in Access
can have more than 255 fields. So your goal of 320 fields in a table or
a query is not reachable.

Thanks. That means I need to re-think how to do this.

Here is a screen shot of the template I have to work with:
http://blog.ladow.net/test/template.jpg

The actual document is at:
http://blog.ladow.net/test/template.doc

I have a table (Lessons) set up as follows:
LessonID, AutoNumber
Subject, Text
Topic, Text
Objective1, Text
Objective2, Text
NJCCCS1, Text
NJCCCS2, Text
NJCCCS3, Text
Procedure, Memo
Assessment, Memo

I have typed in a dozen or so lessons to have some data to work with. Once
all my lessons are entered, I figure there will be about 1000 records.

I teach 40 lessons per week (8 lessons, 5 days).

My struggle is how to populate the Word document with the data of these 40
lessons each week.

A merge doesn't seem to do what I want it to do as I can easily enter one
record in the first block of the schedule, but the second lesson does not go
to the second block, but to a second page, first block.

That is why I thought if I could create one record, I could set up the 320
fields once in the master document. But if I can't do that, I am stuck with
how to populate the Word doc with the Access data.

It seems like this should be possible. It's like 40 individual merges, but
I can't figure out how to do that.

I appreciate any help that can be offered.
 
S

Shoelaces

After spending the day on this, I found a way to do what I wanted from the
Word side of the battle. I learned about directory merges and finally was
able to use that to populate the document with the 40 lessons.

Thank you both for your help. I appreciate it.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top