General - How to highlight the cell with respond to the condition

hi i have some problem here,
i plan to transfer a series of data to access from excel.

in my data have about 120k display with 250 column X 625 row.

so from all the data there will be display of 0 , 1 , and 2

so can anyone give me some idea so that i can highlight the cell with the value greater than 0

because i can do it at the excel but not the access

In addition the reason i do this is because the access is more safe in storing the data while to ensure the code that i do on excel is not disturbed. (i spent 2 month to tabulate it)

thank you in advance for the time and effort to read this thread

First and foremost, if you've converted an excel file to Access and you have 250 columns you're doing something wrong. No offense to you, I applaud you for trying to convert, it's just that there is almost no reason you would have that many columns in your database.

Second, Access does not work like Excel. A cell in Excel is a datapoint that is related to nothing else other than by your programming by virtue of it's position on your sheet. In Access individual FIELDS are related to a RECORD. RECORDS are part of a larger DATASET which can then be linked to other TABLES for easier data manipulation. So in your case you would not highlight cells, you would create a query where any of your fields of interest were zero.

So let's say you had this table:



You would create a query to show you any record that had a 0 in data point 1 (DP_1) by making a query that had the SQL code:



if you wanted to have multiple columns represented you could do this:



This query would pick out any record that had a 0 in any of the first three columns. From there you could do whatever you want to.

ok i do know that sending the data to access got limit which is 255 column.
so i have adjust my desgin to the point that i only use about 254 column only and i success ti import into it.

about the highlight stuff i understand rd (access cant do like the excel which is highlight according to condition

but one this that i dun understand is the meaning of u using querry
sorry i really new to access . pardon me if what i understand is wrong k

from ur reply, i get the meaning that by using querry i will select out only the cell with '0' then only do the action that i wan?? is this what u mean??

well if that is it, sorry i dun need that . bcos what i wan is the data of zero and non-zero is display at the same page. so that i can view all at the same time.

thus i can do the analyze easily.

ya i have another question,
what i have do is i link the excel with my access. how to do so that when excel update then the access will update ( got delay is ok but let make the delay below 30s)

note that my excel is already link with a access file that will take data from a VB program that i made. so i will enter the data through VB then it will send data to the excel to do all the calculation and then summary. ( the thing that will be display at another access file is the summary) estimate about 150k cell( but i hide most of them that at the end only about 37500 data is show other is hidden)

pls help me in this too tq for the time and effort to read this thread TQ

I really do not understand your use of english but I'll try again.



Again, if you are using over 200 columns in any Access table chances are your design is either a. Wrong or b. Meant to be handled by a statistical software package like SPSS. It doesn't really matter if you were successful in importing the data, you need to examine your structure and see if there's an easier way to organize the data. If you have no control over the input of the data that is a different matter but it sounds like you do.



If you build a report based on your data in access you can do something called 'conditional formatting' and build a formula such that if your value is within a certain range or above a certain number the color of the background can be changed. This is NOT the same as changing the color of a specific field within a specific record on a table.



A query is used to pick out portions of your data that you are interested in, in your case you wanted to see all records that had any number greater than 0. I gave you an example in my first post how to pick out any records that had a 0 you would just have to change those criteria within your query to say >0 instead of =0 in the WHERE clause of your SQL statement.



There are action queries like updates, deletes, appends what you do with your data after you figure out how to select it is up to you. You have said nothing about what you want to do with your data so my answer is very general.



Forgive me, but it's impossible to analyze data by looking at each record individually over hundreds of thousands of records (which you say you have in your spreadsheet). 250x625 = 156250 data points.



Assuming the design of your excel spreadsheet does not change your data is updated as soon as you save any changes to your spreadsheet. You just have to close the TABLE in access and reopen it or force a refresh of your database.

NOTE: Your information is really minimal to speak specifically but going from excel to access is really making things more difficult on you than just using access to start with.



This is so complicated, access is capable of providing you with data entry forms, storing the data and producing reports. The way you are doing this is extremely heavy handed and unnecessary. Through reports and queries you can show as much or as little data as you want. You can even convert your current data into something more usable (less than 250 columns). It would take some setup time but your process is extremely complicated for what I suspect is a fairly simple dataset.



I'm not sure what this means, if you unable or unwilling to put in the time and effort to learning Access I'm not sure this forum can help you. Most people come here with a specific question they are having difficulty with you're asking some very general questions which are much harder to answer.

well thanks again for the reply

hmm i know that the stuff that i do i very complicated.
but what can i say i was ordered to be doing so.

first i wan to clarify that the reason i use VB is to generate a program based with a ***.exe file so that the person that will use it will be easier.

then in the program that i do, when the person who use it enter the input, those particular number will be change to some sort of code that involve some step. thus those step i hide up in the program so the person will only see the input he in then follow by the output.

well actually the condition is like this
1) i setup a program using VB with the intention to enable the person who use it to key in his input then follow by the output.

2) then those output will be tabulated( as the conversion of the input is related with a series of code) then those tabulation process is done using excel. as the data from the access is link to an excel file. (all calculation is done at excel)

due to safety measure, i plan to transfer it to the access so that when the person who wan to analyze it will got no chance to alter the code that i do in excel.

3) those data that i mention about >0 is because i just wan to simple display it for easy reading. in between that the first 190 columns is hide thus only about 60 column is show.

beside that reason, i also found out that if using excel to open a file that have about 150k will be extremely slow. but on the access is more acceptable ( less than 1 minute).

thus i plan to do display using the access.
as for additional infomation. in 1 row may be only got 2 to 5 cell that have non-zero value. that is why i need to change it background color or anything that will make it more easily to read.

tq

I fear that your problem is that you are trying to use a very sophisticated tool, a relational database, about which you know nothing. I do not mean that in an insulting way. There are many subjects about which I know nothing. But, to emphasize rpeare's point, you cannot just ask a few questions in a forum and then use a product like Microsoft Access in a complex situation. It takes years of studying relational databases to understand what to do and how to do it. Databases ARE NOT anything like spreadsheets! You said you were given the task, but what you have to tell your boss is that it requires a highly trained person to do what he wants. Maybe he will agree to send you to classes to learn about relational databases. Good luck.