Configure Drop Down to Display Values from Multiple Database Columns

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

scottchampion@gmail.com - 14 Feb 2007 23:23 GMT

I've got a drop down whose values are based on an access database
table. The access database table has the following columns:

ID, Description, Points

Currently, the values in the Description field populates the drop down
menu. I'd like the drop down to also display the values in the Points
field:

For example:

The database table contains:
ID, Description, Points
1, Happy, 2
2, Sad, 8
3, Elated, 1

Drop down displays:
Happy (2)
Sad (8)
Elated (1)

Can someone point me to a resource on this? I suppose I could write
an Access query that combines fields prior to pulling them into
InfoPath but I'd rather not clutter up my database with queries
specific to an InfoPath form.

Any other ideas?

Reply to this Message

scottchampion@gmail.com - 14 Feb 2007 23:47 GMT

On Feb 14, 3:23 pm, scottchamp...@gmail.com wrote:

> I've got a drop down whose values are based on an access database
> table. The access database table has the following columns:
[quoted text clipped - 24 lines]
>
> Any other ideas?

It appears that I was able to concat these two columns in the SQL
query in InfoPath my doing the following:

Tools --> Data Connections
Modify the data connection associated with the drop down
I then edited the sql statement

SELECT tbl.ID, [tbl].[Description] & ' (' & [tbl].[Value] & ' points)'
AS Description
FROM tbl;

I received a warning stating that the SQL statement cannot be
represented as a tree view but everything seemed to work correctly.
Are there any draw backs to my configuration?

Reply to this Message