RE: Set Values in Access w/o Query

[BIGTONE559]

RE: Set Values in Access w/o Query

Not Sure if my terminology is correct. Nevertheless i created a query with the following criteria:

Trim([FIRST_NAME]) & " " & Trim([LAST_NAME]) & " " & Right([ID_NUM],2)

It created a column of data that was exactly what i wanted. My question is. in the table itself. Is it possible to include the criteria that i stated above so that when the values are entered in the first 3 columns that it will fill the fourth in without having to run an update query (fill in Automatically basically).

Thanks in advance!

 

[jackd]

Re: Set Values in Access w/o Query

Normally you wouldn't give anyone the option to modify a table directly. You access a table via a form.
But to answer your specific question, you wouldn't store data more than once. You would create a table to store, in separate fields, the first name and the last name. If you want to concatenate field, or add punctuation, you would use a query. Now you could invoke(execute) the query by means if a button on a form.

You may wish to review Normalization. http://en.wikipedia.org/wiki/Database_normalization

 

[HiTechCoach]

Re: Set Values in Access w/o Query

Not Sure if my terminology is correct. Nevertheless i created a query with the following criteria:

Trim([FIRST_NAME]) & " " & Trim([LAST_NAME]) & " " & Right([ID_NUM],2)

It created a column of data that was exactly what i wanted. My question is. in the table itself. Is it possible to include the criteria that i stated above so that when the values are entered in the first 3 columns that it will fill the fourth in without having to run an update query (fill in Automatically basically).

Thanks in advance!

This data should NOT be stored. It violate the basic rules of database normalization and should be avoided.

I believe the reason it is not easy to do is because you really should not be doind it. There really is no need. You database will have a lot more integrity if it is calculated as needed.

 

[BIGTONE559]

Re: Set Values in Access w/o Query

This data should NOT be stored. It violate the basic rules of database normalization and should be avoided.

I believe the reason it is not easy to do is because you really should not be doind it. There really is no need. You database will have a lot more integrity if it is calculated as needed.

I'll explain the situation a little more. My end users use Excel to "analyze/process" data. I would normally concatenate fields in Excel (user is familiar with seeing FirstName Lastname and ID and it maintains a level of integrity). I was attempting to just have this field be included in the access db. minimizing any processing time on the excel side ( with all the formulas and blah blah). But if this is not practical/proper do either of you, or anyone else for that matter have any suggestions.

 

[HiTechCoach]

Re: Set Values in Access w/o Query

I'll explain the situation a little more. My end users use Excel to "analyze/process" data. I would normally concatenate fields in Excel (user is familiar with seeing FirstName Lastname and ID and it maintains a level of integrity). I was attempting to just have this field be included in the access db. minimizing any processing time on the excel side ( with all the formulas and blah blah). But if this is not practical/proper do either of you, or anyone else for that matter have any suggestions.

I would still do the concatenation in an Access query (view) that is used by Excel.

What version of Access and Excel?

How are you getting the data into Excel?

 

[BIGTONE559]

Re: Set Values in Access w/o Query

I would still do the concatenation in an Access query (view) that is used by Excel.

What version of Access and Excel?

How are you getting the data into Excel?

I have Office 2007

and i have a data link between excel and access

Notes:

Our Access db has an odbc connection to a vdf database from our software provider.

 

[HiTechCoach]

Re: Set Values in Access w/o Query

Is the data link to a Access query/view or a table?

 

[BIGTONE559]

Re: Set Values in Access w/o Query

Is the data link to a Access query/view or a table?

it's a table and quite honestly i wasn't aware that a query could be used as well. (not really sure of the benefits now but i know that there are many)

 

[HiTechCoach]

Re: Set Values in Access w/o Query

it's a table and quite honestly i wasn't aware that a query could be used as well. (not really sure of the benefits now but i know that there are many)

In a relational database that is properly normalized the raw data in the tables are not really in a format for consumption by other applications or even reports. That is why there are queries. Queries allows you to take normalized data in multiple tables and flatten it out into a un normalized record that is more friendly for Excel or Access's Reports.

 

[BIGTONE559]

Re: Set Values in Access w/o Query

In a relational database that is properly normalized the raw data in the tables are not really in a format for consumption by other applications or even reports. That is why there are queries. Queries allows you to take normalized data in multiple tables and flatten it out into a un normalized record that is more friendly for Excel or Access's Reports.

Understandable. . . so i guess my question would be is it possible to run queries when a record is added without having the database open? if that makes any sense.

Once we input data into our software providers program. The data is copied to the access database. All this is done "Behind the scenes". What i'm finding is that i have to actually "Run" The query by opening the db. is there a way to have the table updated without going through the aforementioned steps?