pulling a Like* criteria from a table.

[kidrobot]

I'm querying off of a teradata table in Access. I have the teradata table linked to a table I made in Access which will only pull specific cars(example). How can I make the query pull.. like car* for every record I have in the table?

For example..

My made table has

201
202
203
21Q
24R
etc...


the teradata table shows this

201G101
201G102
201P202
203A120
21QP131

the record means ...the first 3 characters is the car name, the middle two char is the location, and the last 2 is the day of the week.

Btw in the query I use in my other querying system it has an option "Begins With" which works, I don't know if acces has this? Can anyone assist me on this? If I am unclear let me know!!

 

[boblarson]

If the field is text you can use:

Like "20*"

If the field were a true number then

Like 20*

 

[Simon_MT]

The normalisation conformists would say that you should breakup this teradata record into its component parts however as it stands you will need to Query this file on the lines of

TeraData.[FieldName] like Forms![FormName]![Made] like [Made] & "*"

If you need to interrogate the other components of the terradata you may need to split the Record:

TD_Made Left([Teradata].[FeildName],3)
TD_Loc Mid([Teradata].[FeildName],4,3)
TD_Day Right([TeraData].[FeildName],1)

The point about nornalisation is that if the teradata feild is broken up into its constituant parts they can be indexed and the retreival times are greatly enhanced.

Simon

 

[kidrobot]

If the field is text you can use:

Like "20*"

If the field were a true number then

Like 20*

Bob, I know about this but I want to pull Like every record in my made up table, not just one record.

 

[boblarson]

If you want every record, don't use criteria -

SELECT * FROM YourTableName

 

[kidrobot]

If you want every record, don't use criteria -

SELECT * FROM YourTableName

I think I failed to state this teradata table has probably a million records so selecting everything will probably cause my computer to freeze. But if you read my first post again I want to pull..

WHERE Like "My made up criteria table"*

 

[boblarson]

That's going to be a bit of a problem, as you will need to build the SQL query's WHERE statement on the fly to include those values. There have been some posts on here about building SQL on the fly, or "dynamic SQL" so you might have to do a little searching for how to do that.

But, essentially you would have to iterate through your table adding the values to the where clause.

 

[Simon_MT]

If the teradata table is that large I would seriously consider normalising the Table.

TD_Made Indexed Allow Duplicates
TD_Location Indexed Allow Duplicates
TD_Day Indexed Allow Duplicates

Then you can say TD_Made = Forms![FormName]![Made]

This should be a lot quicker.

Simon

 

[kidrobot]

If the teradata table is that large I would seriously consider normalising the Table.

TD_Made Indexed Allow Duplicates
TD_Location Indexed Allow Duplicates
TD_Day Indexed Allow Duplicates

Then you can say TD_Made = Forms![FormName]![Made]

This should be a lot quicker.

Simon

Thanks I'll try that out. So what object is this linked to? A listbox or textbox? If textbox I don't need one criteria I want all of them as criteria. Thanks again.

 

[Jon K]

If I read your question correctly, I think you can join the teradata table with the made table like this:-

SELECT [teradata table].*
FROM [teradata table] INNER JOIN [YourMadeTable] ON Left([teradata table].[FieldName],3)=[YourMadeTable].[FieldName]


Alternatively, you can pull the teradata like this:-

SELECT [teradata table].*
FROM [teradata table]
WHERE Left([teradata table].[FieldName],3)
IN (Select [FieldName] from [YourMadeTable])


The first query, using a function in the link, cannot be displayed in query Design View and is non-updatable.

The second query, using a subquery, can be displayed in query Design View and is updatable.
.

 

[kidrobot]

If I read your question correctly, I think you can join the teradata table with the made table like this:-

SELECT [teradata table].*
FROM [teradata table] INNER JOIN [YourMadeTable] ON Left([teradata table].[FieldName],3)=[YourMadeTable].[FieldName]


Alternatively, you can pull the teradata like this:-

SELECT [teradata table].*
FROM [teradata table]
WHERE Left([teradata table].[FieldName],3)
IN (Select [FieldName] from [YourMadeTable])


The first query, using a function in the link, cannot be displayed in query Design View and is non-updatable.

The second query, using a subquery, can be displayed in query Design View and is updatable.
.

Thanks Jon the first sql worked great, I'll see how the second one goes when I have time. Thanks!!

 

[Simon_MT]

Thinking out your problem with regard to the large number of transactions and handling non-indexed fields and the detrimental effect on performance.

It strikes me if preformance is an issue and TeraData can not be properly indexed into Made/Loc/Day to made the queries faster what about putting an index Table infront of Teradata.

4 Fields all Indexed

Made
Loc
Day
Combined field

With a simple Join on the table and you have a fully indexed table without having to touch TeraData. No, its not normalised but there are times when you don't have any control over the data architecture so you make your own.

Simon

 

[kidrobot]

Thinking out your problem with regard to the large number of transactions and handling non-indexed fields and the detrimental effect on performance.

It strikes me if preformance is an issue and TeraData can not be properly indexed into Made/Loc/Day to made the queries faster what about putting an index Table infront of Teradata.

4 Fields all Indexed

Made
Loc
Day
Combined field

With a simple Join on the table and you have a fully indexed table without having to touch TeraData. No, its not normalised but there are times when you don't have any control over the data architecture so you make your own.

Simon

Oh trust me I have tried to limit down this data every way I can. Everything I'm pulling is everything I need. I currently have 2 other tables linked to the teradata table to decrease un needed records.

 

[Simon_MT]

I was just concerned that accessing data without Indexes and having to break up composite fields is slower that using a fully indexed search.

I'm sure your probably feel you're gone to the end of the world and back again.

Simon

 

[kidrobot]

I was just concerned that accessing data without Indexes and having to break up composite fields is slower that using a fully indexed search.

I'm sure your probably feel you're gone to the end of the world and back again.

Simon

Somewhat confused on what you think I should do. Do you mean index with-in Teradata or do this in Access? Because I don't have Access to do anything in Teradata other than link it to Access and pull from it. Thanks for you time Simon.