Combo box concatenation of fields

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

Guest

Hello,
My database keeps track of students that attend classes. The main form
(frmroster) contains the class information; the subform is based on a query
of all students.
My goal is to have a combo drop down in the subform that would show the
student ID, last name (sorted), first name,phone, student type, and grade.
All these fields are in the query.

When I do the drop down, I see my six requirements, but when I tab to add
another record, only the last name shows. As my subform is in datasheet view,
I would like to view all six fields after selecting the student.

I read about concatenating fields; where do I do this: in the query or
subform? How do I get this to show after updating?

Thanks!
 
Rookie,

You do not need to do concatenation of data here. You should be able to
see the data from all the fields in your query on the subform,... but
not in the combobox. A combobox can only display one column. I was not
clear from your post... Do you have the other fields from the query
represented on the subform? If not, you will need to put columns for
the phone, student type, grade, etc onto the form that is used for the
subform. If your meaning is that you do have these columns on the
subform, but they are shown blank, please post back with the SQL view of
the query that the subform is based on, and someone may be able to spot
the cause of the problem from there.
 
Hey Steve,

I see the data from all the fields when I click on the down arrow of the
combo. However, when I move to the next record, only the last name shows. I
would like all fields to show...I guess that's why I thought I needed
concatenation.
In any case, where do I get the SQL from so I can post it here?

Thanks,

Rookie.
 
Rookie,

Ok, so this seems to clarify that you were expecting all fields to be
shown in the combobox. As you have observed, the combobox's drop-down
list can show more than 1 column, but the combobox control itself can
only show one. If you want it to all show in the combobox, you can use
a concatenation... I made the assumption that you would not want this,
based on the fact that I would myself prefer each field to be shown in
separate columns on the subform. In this case, you would put a
calculated field in the query something like this...
AllData: [student ID] & " " & [last name] & ", " & [first name] & " "
& [phone] & etc
.... and then have a column on the subform bound to this field.
My suggestion before was based on putting separate controls on the
subform for each of the fields in the query. It would seem from your
reply that at the moment you do not have such controls on the subform.

To get the SQL view of the query, just select SQL from the View menu of
the query design.
 
Hey Steve,

You're right, I just have a name combo drop down(control, I guess).
First of all, how do I put the calculated field in a query? I thought you
could
only do that on a form. How would I then bind a column from the subform to
this field?

Thanks for putting up with my rookieness.

Here's my SQL:
SELECT tblstudents.Last, tblstudents.[Employee Number], tblstudents.First,
tblstudents.Phone, tblstudents.Student_type, tblstudents.Grade,
tblstudents.RosterID
FROM tblstudents
ORDER BY tblstudents.Last;

Thanks!



Steve Schapel said:
Rookie,

Ok, so this seems to clarify that you were expecting all fields to be
shown in the combobox. As you have observed, the combobox's drop-down
list can show more than 1 column, but the combobox control itself can
only show one. If you want it to all show in the combobox, you can use
a concatenation... I made the assumption that you would not want this,
based on the fact that I would myself prefer each field to be shown in
separate columns on the subform. In this case, you would put a
calculated field in the query something like this...
AllData: [student ID] & " " & [last name] & ", " & [first name] & " "
& [phone] & etc
.... and then have a column on the subform bound to this field.
My suggestion before was based on putting separate controls on the
subform for each of the fields in the query. It would seem from your
reply that at the moment you do not have such controls on the subform.

To get the SQL view of the query, just select SQL from the View menu of
the query design.

--
Steve Schapel, Microsoft Access MVP


Access said:
Hey Steve,

I see the data from all the fields when I click on the down arrow of the
combo. However, when I move to the next record, only the last name shows. I
would like all fields to show...I guess that's why I thought I needed
concatenation.
In any case, where do I get the SQL from so I can post it here?

Thanks,

Rookie.

:
Click to expand...
Click to expand...
 
Rookie,

Is this the query that your subform is based on? If so, there is
something fundamentally missing in the overall scheme of things. Is the
purpose of the subform, as I had assumed, to show the students who are
enrolled in the class as per the current record on the main form? If
so, how is it supposed to do that? Maybe we need to go back a step, and
have a look at the design of your tables... Are you able to give a
summary overview of what you've got and the relationships between them?
 
Sure Steve...here's my description:
The DB is supposed to track what students have taken what classes. For this
arrangement, I have a student table (PK Employee No) and a teacher table (PK
Teacher ID) with a training table (PK Training ID) between. The training
table has Employee No and Teacher as foreign keys from the student table and
teacher table respectively.
Now, each class needs to generate a class roster. So, I created a roster
table (PK rosterID) and now am attempting to put a subform into the form
based on the roster table. Each record of the subform is to contain names of
students. Now, instead of typing each student in again, I am attempting to
create the combo to look up the student ID (from the query you saw)and have
it display that info in the record (in datasheet view) in the subform. I made
sure the student table had rosterID as a foreign key.
The subform in the roster form is based on a table called rosterattend (PK
RosterAttendID) and only has fields Name and RosterID (which is the foreign
key for the roster table.)
In the rosterattend form (now a subform of roster form), I have just one
field called name. I built this name to be the combo to look up the
infomation specified in the SQL statement I sent.

I can see the 6 fields that I want to see when the combo drops down (from
the query I sent earlier) but when I go to another field, only the last name
remains.

I hope this isn't too convoluted. Thank you for your assistance!

John.

**
Steve Schapel said:
Rookie,

Is this the query that your subform is based on? If so, there is
something fundamentally missing in the overall scheme of things. Is the
purpose of the subform, as I had assumed, to show the students who are
enrolled in the class as per the current record on the main form? If
so, how is it supposed to do that? Maybe we need to go back a step, and
have a look at the design of your tables... Are you able to give a
summary overview of what you've got and the relationships between them?

--
Steve Schapel, Microsoft Access MVP


Access said:
Hey Steve,

You're right, I just have a name combo drop down(control, I guess).
First of all, how do I put the calculated field in a query? I thought you
could
only do that on a form. How would I then bind a column from the subform to
this field?

Thanks for putting up with my rookieness.

Here's my SQL:
SELECT tblstudents.Last, tblstudents.[Employee Number], tblstudents.First,
tblstudents.Phone, tblstudents.Student_type, tblstudents.Grade,
tblstudents.RosterID
FROM tblstudents
ORDER BY tblstudents.Last;

Thanks!
Click to expand...
Click to expand...
 
Sure Steve, you're right: the purpose of the subform is to display the
students enrolled in the class as per the current record on the main form.

The Main form is based on a table called Roster (PK RosterID). The subform
is called rosterattend, based on table rosterattend. In table rosterattend
(PK RosterAttendID), I have just 2 fields, Name and RosterID (foreign key for
Roster table). Table rosterattend is the basis of the form that becomes the
subform for the main form. They are linked by RosterID.

The combo box I have tried to implement is collecting information on the
query whose SQL you saw earlier. While I can see all six fields (pulled from
the query: first name, last name, phone, employee number, student type and
grade), when I move to the next field, only the last name shows. The above
named qry pulls from the student table that contains those six fields.

The relationship between roster and rosterattend is one to many without
referential integrity. They are linked by RosterID.

Thanks for all your assistance with this!


Steve Schapel said:
Rookie,

Is this the query that your subform is based on? If so, there is
something fundamentally missing in the overall scheme of things. Is the
purpose of the subform, as I had assumed, to show the students who are
enrolled in the class as per the current record on the main form? If
so, how is it supposed to do that? Maybe we need to go back a step, and
have a look at the design of your tables... Are you able to give a
summary overview of what you've got and the relationships between them?

--
Steve Schapel, Microsoft Access MVP


Access said:
Hey Steve,

You're right, I just have a name combo drop down(control, I guess).
First of all, how do I put the calculated field in a query? I thought you
could
only do that on a form. How would I then bind a column from the subform to
this field?

Thanks for putting up with my rookieness.

Here's my SQL:
SELECT tblstudents.Last, tblstudents.[Employee Number], tblstudents.First,
tblstudents.Phone, tblstudents.Student_type, tblstudents.Grade,
tblstudents.RosterID
FROM tblstudents
ORDER BY tblstudents.Last;

Thanks!
Click to expand...
Click to expand...
 
John,

Well, nice to be on first name terms now :-)

Sorry, I misunderstood you before... I thought the query you were
showing me is the record source of the subform, not the row source of
the combobox.

A few general comments on your design...
1. The training table will only work if any given student only takes
one class taught by any given teacher. If a teacher can teach more than
one class, and if a student can take more than one of these classes by
the same teacher, you might need to review your table structure.
2. The Employee ID field is the key field of the Student table, and as
such this should be the field used to link with the RosterAttend table.
Name is not a good choice here.
3. In any case, 'name' is a reserved word (i.e. has a special meaning)
in Access, and as such should not be used as the name of a field or
control or database object.
4. To get what you want happening on the RosterAttend subform, you will
need to base this form on a query which includes the Student table. But
there's a bit of tidying up to do before we get to this point.

I am sorry, I haven't quite grasped the concept of "Roster". What sort
of information is in the Roster table, apart from rosterID?
 
Hey Steve,
First of all, thanks a million, this is helping so much.

On my student main form (built on my student table) I have a subform called
training. This form holds all classes taken by the student indicated in the
main section of the form. In the subform (displayed in datasheet view), my
class field is a combo lookup to my classes table, my teacher field a combo
lookup to my teacher's table, etc. This design means that every record in my
training form (subform for student main) shows the training that each student
has taken. It's working successfully; both are linked by Employee Number (PK
for Students, foreign key for training).

I am trying to replicate this in the roster issue.
The roster is a report generated before the class. It's kind of like a
sign-in sheet. Students are entered in and then the "generate report" button
is clicked. This report picks up the names of the students entered (in
subform rosterattend) for the class (roster). Since the students are already
in the student table, the combo box I seek to create will drop down and allow
the data-entry person to simply choose the student instead of typing the name
in again, thus defeating the purpose of having it look them up from the
student table in the first place!

I will change that field called "Name" to something else.

By the way, what time zone are you in? I hope I'm not keeping you up...thanks!

***
 
Hey Steve,

I forgot to include information about the roster table. It simply shows the
date, day, location, instructor (lookup to instructor table), and class
(lookup to class table). It is linked to the student table by RosterID.
(i.e. the student table has a foreign key called RosterID)

Thanks again,

John.
 
John,

I strongly suspect that you have been working on this project a bit
backwards (if you don't mind me saying so :-) ). Some of the problems
seem to stem from starting in your thinking with the requirements of the
forms/reports/user interrface in mind, and trying to fit your data
structure around this. Whereas a better process is to get your table
design to precisely reflect the actiual data and the actual
relationships between the different data elements before you even let
yourself think about the user, and then when your tables are set up
correctly, then you design your forms so they relate to the tables.
By the way, what time zone are you in? I hope I'm not keeping you
Click to expand...
up...thanks!

Right now, it's 1:08 pm Saturday afternoon here. :-)
 
John,

This does not appear to be a correct design. To have a RosterID field
in the Student table implies that any given student can only be on one
Roster, which is clearly not true.

This is the first time I have seen mention of a Class table... is this
the same as the Training table? And is Instructor the same as Teacher?

--
Steve Schapel, Microsoft Access MVP


Access said:
Hey Steve,

I forgot to include information about the roster table. It simply shows the
date, day, location, instructor (lookup to instructor table), and class
(lookup to class table). It is linked to the student table by RosterID.
(i.e. the student table has a foreign key called RosterID)

Thanks again,

John.
Click to expand...
\
 
Hey Steve,

Good morning.

Actually, it is the right design. Students can only attend one class at a
time. Therefore, on the roster for that particular class, their names can
only appear once. Even if they have another class that same day, the roster
generated will be for that different class. So, it's one roster...many
students.

My class table is a table containing the names of the classes that are
offered. The training table has a class name field...it simply looks up the
class name from the classes table. And yes, an instructor is the same as a
teacher...it was a linguistic error.

I hope to hear from you soon.

John.
 
John,

Access said:
Actually, it is the right design. Students can only attend one class at a
time. Therefore, on the roster for that particular class, their names can
only appear once. Even if they have another class that same day, the roster
generated will be for that different class. So, it's one roster...many
students.
Click to expand...

Do you mean there's is only ever one record in the Roster table, and
that every time you do a new roster, the previous one is deleted or
over-written?
 
Hey Steve,

Good morning.

Actually, no...each roster is a record in the roster table; so we can trace
back to what classes were offered on which day. The rosterattend subform
contains records of students that attended that class on that day. The
original problem was having a combo lookup in that subform to pick up the
name of the student, instead of typing it in again. I would have liked to
send you a picture of my relationships...is there a way to do that here?

Thanks,

John.
 
Hey Steve,

I've figured out the problem...you were right; my roster ID had no business
being in my student table. That produced the entire problem!

Many thanks!

John.
 
John,

It sounds like you've got it under control now, which I am happy to
hear. But please post back if you need any more assistance with this.
 
Back
Top