Graphics

Hi all,

We have just scratched the surface of what is possible with Datalinq in regards to keying data. So far we have only been able to key data in our Excel databases with numerical characters (i.e. 1 = Player Name 1, 2 = Player Name 2, etc.) However, we would like to key data in these same Excel spreadsheets with letters (i.e. PName1 = Player Name 1, PName2 = Player Name 2, etc.) Has anyone found any success trying to achieve this? If so how do we apply this to our Excel spreadsheet workflow?

Thanks!

Normally the datalinq key is used to select a specific row from the excel sheet and that is why typically it is a number.. It's not entirely clear what you are trying to achieve. Are you trying to use the datalinq key to choose different columns from the sheet? If so, you can just put the %key% into the Column field of the datalinq setup.. You could also pass the key directly into an SQL query which could retrieve data from the sheet via an SQL query. There are a lot of possibilities but I'm afraid I don't quite understand what you are trying to achieve.

#XPression

example: Lets say you had an excel sheet with a roster of players (and columns called LastName, Position, etc), and you wanted to select the first player alphabetically with a specific position.. You would use an SQL query something like this and configure the datalinq fields as such:

`Column = LastName

Row = 1

Table = SELECT * FROM ROSTER WHERE [Position]=%position% ORDER BY [LastName]

`

Then you could set the position datalinq key to QB to select the first quarterback..

#XPression

Not a problem I apologize for my less than adequate explanation. In sequence we are keying by Row number with a string. We want to be able to take the first initial and last name of a player and use that as the key rather than the row number.

It does look like the SQL Query is the best bet. Unfortunately, Im not entirely sure how to go about applying it. Do I just set the parameter for an SQL database in DataLinq and set that database source to the Excel spreadsheet? Again I apologize this is still an area we are learning.

#XPression

@bford Thanks again for your input on the Visual Logic question. It is a big step for us. In regards to this DataLinq question did you have anything further you could point me towards? Again, I think this SQL server is our best bet to key with alphanumerics instead of just numerics by data row.

#XPression

Have you watched this video? http://xpression.rossvideo.com/videos/Datalinq_SQL_Queries/Datalinq_SQL_Queries.html

It will show you the basics of using an SQL query with the ADODB datalinq.

Your query would be something like `SELECT * FROM ROSTER WHERE [NAME]=%NAME%`

I can't think of a way to combine the last name with the first initial to do the lookup, unless that is already in your excel sheet like that. I think you would need to make an excel column that automatically combined the lastname/first initial into a column of the excel sheet. Then you could have the SQL query lookup from that column using an SQL query.

#XPression

Fantastic! This is exactly what we needed. Thanks @bford!

#XPression

If you need to combine two columns in Excel (Lastname and Firstname) into a First Inital and Last Name column, you can use this excel formula: `=LEFT(A1;1)&B1` (assuming Firstname is column A, and lastname is column B)

#XPression