Query to return value of adjacent field?

[Bryan]

Let's just say....

I have 10 tables. Each table has two columns. The first column is a
written description of an item. The second column is a two-digit code
representing the item.

I have a form wherein the user inputs ten pieces of information: one code
for each table. I want to return to the user a concatenation (?) of the
DESCRIPTIONS. I'm fine with outputting a concatenation. I don't know how to
use the codes that have been input to call up the respective description.

You guys rock.

 

[tina]

well, you could use DLookup(). something like

Dim str As String
str = DLook("DescriptionFieldName", "Table1Name", _
"CodeFieldName = " & Me!Controlname1)
str = str & ", " & DLook("DescriptionFieldName", "Table2Name", _
"CodeFieldName = " & Me!Controlname2)
str = str & ", " & DLook("DescriptionFieldName", "Table3Name", _
"CodeFieldName = " & Me!Controlname3)
...
str = str & ", " & DLook("DescriptionFieldName", "Table10Name", _
"CodeFieldName = " & Me!Controlname10)

you'll have to enter the correct name of the description field, and table,
and code field, for each table, of course - as well as the correct name of
the control in the form for each two-digit code. also, the above code
assumes that the code field is a Number data type; if it's Text data type,
change the syntax to

DLook("DescriptionFieldName", "Table1Name", _
"CodeFieldName = '" & Me!Controlname1 & "'")

hth

 

[Bryan]

Thanks!

 

[tina]

you're welcome

 

[John Spencer]

My first question is WHY do you have ten separate tables?

You should have all the data in one table. If there is something that
distinguishes records in one table from records in one of the other nine
tables, then you would need a third field to identify that difference.
And possibly a fourth field as a primary key if you don't want to use a
compound key based on Code + RecordType fields.

The structure you have now is going to make for great difficulty in
doing most things in a database. Tina posted a suggestion on a way to
handle this one problem.

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

 

[Bryan]

I'm admittedly new to Access. I made the decision to split the data into
separate tables because the 10 tables design an aspect of one item. I want
to be able to add/delete from each of the 10 lists of aspects. Each of the
lists has a different number of records, and for the records that aren't the
2-digit code, they have different field lengths.
I haven't seen how I could design my database otherwise... pointers?

Thanks,
Bryan

 

[Bryan]

I'm admittedly new to Access, and I appreciate the question!

I made the decision to split the data into separate tables because the 10
tables describe an aspect of one item.

The concatenation I mentioned in my initial post results in a 20 digit code
that uniquely identifies a single item based on its unique combination of
aspects.

I want to be able to add/delete/modify data in each of the 10 lists of
aspects. Each of the lists has a different number of records, and for the
records that aren't the 2-digit code, they have different field lengths.

I haven't seen how I could design my database otherwise... pointers?

 

[huangda 晋江兴达机械]

Let's just say....

I have 10 tables. Each table has two columns. The first column is a
written description of an item. The second column is a two-digit code
representing the item.

I have a form wherein the user inputs ten pieces of information: one code
for each table. I want to return to the user a concatenation (?) of the
DESCRIPTIONS. I'm fine with outputting a concatenation. I don't know how
to
use the codes that have been input to call up the respective description.

You guys rock.