Combo Box value shows in all records

[Guest]

When I select a combo box value, the same value shows in all the records in
the datasheet. I've never seen a combo box behave this way. The combo box
is connected to a lookup in a table. What's going on please?

 

[Allen Browne]

Sounds like your combo is unbound.

Although its RowSource is set to the lookup table, its Control Source is not
bound to any field. Since there is no field to store a different value for
each record, Access shows the same thing on all rows of the datasheet.

 

[Jeff Boyce]

Hughie

When a control displays the same value for all records, it usually means
that the control is NOT connected to any underlying field in the table.
This is also known as an "unbound" control.

If you are using a lookup field in one of your tables, you may be running
into issues discussed in the access.tablesdbdesign newsgroup on the subject.
You may want to reconsider using lookup data types.

 

[Guest]

Okay, done deal. I've tried to use a simple text box. In this form the
record source is a query built on underlying queries and tables. All I want
from this field I'm trying to set is that it be able to receive and hold a
unique character, "x", to mark that record so I can later gather the marked
records for further examination. I tried to bind the control to a field in
an underlying table that has the same number of records as in the form and
which is a simple text field, but then Access gives places #Name?

I've gone round in circles every spare minute this weekend thying to solve
this. Help me off this merry-go-round. H

 

[Jeff Boyce]

I don't understand. Why would you need to use a field that has the same
number of records? I don't understand how a "field" can hold records?

Perhaps you've already decided the "how" of what you're trying to do, before
nailing down the what. If you're willing, please post back with a
description of what you are trying to do -- and imagine you were explaining
it to your mother! No technology at all, just what you hope to be able to
do (e.g., I want a list of all the people with blue eyes).

 

[Guest]

First let me thank you for your offer to spend more time with this. I won't
be able to give the "here it is mom" version til I leave work tonight. So
I'll appreciate it if you keep an eye open for it.
Meantime, it may help to clarify my last post which said, "I tried to bind
the control to a field in an underlying table that has the same number of
records as in the form and which is a simple text field, but then Access
gives places #Name?"
I see the possible of a grammatical error. It's not the field that has the
records but the underlying table. This seems important because each record
in that table would ba a candidate to receive the "marker" and I had earlier
put the field in a table that only contained a subsection of the larger
table. At any rate, I still need to set a field in the larger underlying
table that can receive a marker. If this doesn't clarify I'll disect it
tonight. Thanks again. H

 

[Jeff Boyce]

Hughie

I'm not sure I fully grok your situation, but it sounds a little like you
might be able to use a table to collect the IDs for the rows-of-interest.
I've used paired listboxes to track which events participants are signing up
for. The first listbox lists the events, the second listbox lists the
events signed up for by the participant.

 

[Guest]

Sorry I couldn't explain it as I would to my 87 year old technophobic mother,
but I've tried to keep it as simple as possible.

My goal has been to associate various tables together to allow teachers to
view standardized test scores of students in each of their current classes.
Lists of student data were imported from Excel documents. The lists included:
1. A list of all students with their scores on each of three general
categories (about 12 data items per general category). Thus there were 3
lists imported. Each record in the list included the students' "exchange ID"
number that connects the database of the testing agency with our school data
base.
2. A list of each student and class ID for all his or her classes. So
there are approximately 5000 records, each record showing a student name and
a class section number. Each student has about 7 records
3. A list that contains the student "exchange ID" associated with the
student's name as it appears in our school database (different from the name
as it appears in the test agency database).
4. A list of the students that had a D- or F on the most recent report card.

I made 4 queries, each of which includes the student schedule records with
various combinations of student test scores plus indicators of students
receiving low report card marks.

My goal has been to enable teachers to view the pertinent data regarding
each student and mark, in a field, which of the students need extra help. I
was unable to put that field in the queries and someone else on the forum
suggested it couldn't be done. So I have made a form that includes one of
the queries and I've been trying to create a control in the form that
connects with a field in the table of all the student schedule records. My
last attempt, as mentioned, was to bind a text box in the form to the field
in the table. That's what I need help doing.

 

[Jeff Boyce]

Hughie

OK, so now my mental picture is not as fuzzy (perhaps a shortage of caffeine
yet this morning). To paraphrase (correct my misperceptions):

You are importing Excel data
(this may be inaccurate) you are NOT parsing the import data into normalized Access tables
(it isn't clear if) the various lists you import do NOT have an ID field

in common -- for some lists, you only have a student name to compare between
lists

you want a way to associate related data into a view of multiple data

(from multiple lists)

First, unless you have a common ID field, it doesn't sound like you can form
the big picture ... and it sounds like the "student name" you keep, and the
"student name" sent by the testing facility are not a reliable matching
field.

Second, there's little guarantee that the data imported from Excel fits a
well-normalized structure. I think you'll find that Access' functions and
features are designed to work against a well-normalized data structure. If
you haven't already, invest the time to "parse" your import data into a
relational data design.

 

[Guest]

Actually I do have matching ID fields. The testing agency gets its data from
our school database at the beginning of each year and they receive from us a
field containing an "exchange ID" which then resides in both theirs and our
databases.

When I build tables for my databases, I print reports into text files and
open them and save them into Excel format documents. When the text files
open in Excel they are automatically parsed into unique columns and I delete
most of the columns that won't be used. Not every table contains the
exchange ID field but can be related so the data in all tables is relatable.

Here's a part I don't understand. Your replys suggest that the data
imported from Excel won't fit nicely into Access. But Access appears to be
fine during the import process and the resulting tables appear to accurately
represent the Excel files. And, except for the lack of a marker field, all
the queries seem work perfectly. I wish I could attach a screen shot of the
relationship view to send to you. Then you could tell me if I've gone astray
in relating the tables and queries.

It seems like I'm hearing that, as a tenacious newbie, I've managed to put
together something that seems to work but may be fundamentally flawed. If
that were true, I don't currently have a way to detect it. And because
others are using the info, of course, the database needs to be unflawed.
I've done numerous data comparisons from the queries to the original data
sources and have found no problems. And, again the queries seem to work
exactly as intended except for the lack of a field to use as a marker.
Will continue to appreciate your sticking with me. Seems like there's a big
lesson for me. Thanks, H

 

[Jeff Boyce]

(see comments in line below)

Actually I do have matching ID fields. The testing agency gets its data from
our school database at the beginning of each year and they receive from us a
field containing an "exchange ID" which then resides in both theirs and our
databases.

If every list you receive includes an ID, you should be able to join
together tables in Access and see all the list data.

When I build tables for my databases, I print reports into text files and
open them and save them into Excel format documents.

I don't understand. What tables? Where? Reports into text files?

When the text files
open in Excel they are automatically parsed into unique columns and I delete
most of the columns that won't be used. Not every table contains the
exchange ID field but can be related so the data in all tables is

relatable.

I thought every table/list contained the ID.

Here's a part I don't understand. Your replys suggest that the data
imported from Excel won't fit nicely into Access.

You can import Excel data into Access, no worries. But that doesn't mean
the data is well-normalized (in Excel).

But Access appears to be
fine during the import process and the resulting tables appear to accurately
represent the Excel files.

Again, the structure of the data in Excel is necessary for Excel. But Excel
is a spreadsheet, and Access is a relational database. Different tools.

And, except for the lack of a marker field, all
the queries seem work perfectly. I wish I could attach a screen shot of the
relationship view to send to you. Then you could tell me if I've gone astray
in relating the tables and queries.

It is physically possible to import Excel lists, relate them together, and
create/run queries. None of this ensures that the data is well-normalized
in Access. It is also possible to drive nails with a chain saw... not a
good use of the tool, and potentially dangerous!

It seems like I'm hearing that, as a tenacious newbie, I've managed to put
together something that seems to work but may be fundamentally flawed. If
that were true, I don't currently have a way to detect it.

If the terms "relational" and "well-normalized" don't mean anything to you,
then there's an excellent chance you are not getting as much out of Access
as you could. And you may be doing your work the hard way.

And because
others are using the info, of course, the database needs to be unflawed.

"Flawed" may mean something different to you and to me -- if you can
successfully report on the data you've brought over from Excel, and the
reports are accurate, I don't see a flaw. However, you may be working much
harder to make things happen than you need to ... but hey! if it's
working...

I've done numerous data comparisons from the queries to the original data
sources and have found no problems. And, again the queries seem to work
exactly as intended except for the lack of a field to use as a marker.

Can you re-describe your idea of a "marker field", in light of the thread so
far?

Will continue to appreciate your sticking with me. Seems like there's a big
lesson for me. Thanks, H

Regards

Jeff Boyce
<Office/Access MVP>