query pulling wrong field

[Gary]

I have a combo box pulling from another table(lastname). Originally I did not
make a relationship. the combo box does not pull from the primary key(SSN). I
pull data into combo just fine(lastname)..... when I make a query it shows
the last name field name with the ssn's data inserted... originally the new
table did not have the ssn... I have since added the ssn and created a
relaionship in the table but still last name has ssn data. Help

 

[John Vinson]

I have a combo box pulling from another table(lastname). Originally I did not
make a relationship. the combo box does not pull from the primary key(SSN). I
pull data into combo just fine(lastname)..... when I make a query it shows
the last name field name with the ssn's data inserted... originally the new
table did not have the ssn... I have since added the ssn and created a
relaionship in the table but still last name has ssn data. Help

Please post the following properties of the Combo Box:

Control Source
Row Source (if it's a Query, post the SQL view of the query)
Column Count
Bound Column
Column Widths

John W. Vinson[MVP]

 

[Gary]

Thanks John:
here is the info .....

This comes from the form:
CS=EmployeeLastName
RS=SELECT tblEmployee.SSN, tblEmployee.EmployeeLastName,
tblEmployee.EmployeeFirstName
FROM tblEmployee;
BC=1

This comes from the table:
CC = 2
CW=0";1"

 

[John Vinson]

Thanks John:
here is the info .....

This comes from the form:
CS=EmployeeLastName

Ok, this means that the Bound Column of the combo box will be stored
into the EmployeeLastName field.

This sounds like a Very Bad Idea. You won't want to overwrite an
employee's last name, will you? It very well might not be unique - can
you be SURE that you will NEVER have two employees with the same last
name?

I would suggest that the combo's Control Source should be the SSN, if
that is the Primary Key of the Employee table. This would let you
STORE the SSN while DISPLAYING the last name, or both names.

RS=SELECT tblEmployee.SSN, tblEmployee.EmployeeLastName,
tblEmployee.EmployeeFirstName
FROM tblEmployee;
BC=1

This comes from the table:
CC = 2
CW=0";1"

And my guess is that the Bound Column is 1 - so it's storing the
*first* field in the query, the SSN, into the EmployeeLastName field.

Could you explain what purpose this combo box is intended to serve on
this form?

John W. Vinson[MVP]

 

[Gary]

Thanks John:
my primary key is the SSN
in regard to my combo box columns the ssn is hidden - so that is column 0 to
my understanding - my drop down 2 columns are Last name - Firstname - in the
form it works fine - in the table it works fine
my problem is when I run the following make table query: (which will feed a
crosstab query) - but I am dead in the water with the make table query

SELECT tblHours.DayOfWeek, tblHours.Job, tblHours.EmployeeLastName,
tblHours.EmployeeFirstName, tblHours.HoursWorked, tblHours.ForemansLastName
INTO tblforcrosstab
FROM tblHours
WHERE (((tblHours.Date) Between [Type the beginning date:] And [Type the
ending date:]) AND ((tblHours.UnnJob)=Yes))
ORDER BY tblHours.Date;

what appears in the "EmployeeLastName extrtaction field is the SSN - when I
am not even including the SSN in the query - I also tried modifying the query
to include the SSN and the SSN appears in both the SSN & EmployeeLastName
field extraction....

 

[John Vinson]

Thanks John:
my primary key is the SSN
in regard to my combo box columns the ssn is hidden - so that is column 0 to
my understanding

It's confusing: the Column() property is zero based, but the
BoundColumn property is NOT zero based. SSN is Column(0) if you're
referring to it using comboboxname.Column(), but it's 1 if you're
referring to it in any of the other combo properties. So your
understanding is (thanks to Microsoft!!) in error!

- my drop down 2 columns are Last name - Firstname - in the
form it works fine - in the table it works fine

I'd recommend NEVER using combo boxes in Tables. See
http://www.mvps.org/access/lookupfields.htm for a critique of this
misfeature.

my problem is when I run the following make table query: (which will feed a
crosstab query) - but I am dead in the water with the make table query

Why do you need a MakeTable query!? They're VERY RARELY NEEDED.

SELECT tblHours.DayOfWeek, tblHours.Job, tblHours.EmployeeLastName,
tblHours.EmployeeFirstName, tblHours.HoursWorked, tblHours.ForemansLastName
INTO tblforcrosstab
FROM tblHours
WHERE (((tblHours.Date) Between [Type the beginning date:] And [Type the
ending date:]) AND ((tblHours.UnnJob)=Yes))
ORDER BY tblHours.Date;

what appears in the "EmployeeLastName extrtaction field is the SSN -

Exactly. Because that's WHAT IS STORED IN tblHours. The accursed
Lookup Wizard is simply CONCEALING that basic fact from your view. The
table actually *contains* a SSN value; but what you *see* is the combo
box, displaying the lastname.

You'll need to include both tblHours and tblEmployees in your
MakeTable query (or in the Crosstab query, it is emphatically *NOT*
necessary to create a new table in order to build a crosstab).

when I
am not even including the SSN in the query - I also tried modifying the query
to include the SSN and the SSN appears in both the SSN & EmployeeLastName
field extraction....

I have no idea what you mean by a "field extraction" so I cannot help
you there...

John W. Vinson[MVP]