Data similar to this is imported into the database I am creating for my users. If you notice, in the servername field, some records have no information. Those records imply that the servername above it is the servername in question. Is there any way, via SQL to say something to the effect of:
If the previous records servername field is 'Server 1' then fill in the servername field for the current record with 'Server 1'
Ive thought about transferring the entire table into an array in VBA, and trying to manipulate it that way, since the data is never going to exceed a full page or so. However, im not even sure how to do that yet
From what I think I understand, MStef has demonstrated the converse of what you are asking for:- blanking out repetitions instead of filling them in.
You could use the same technique to fill them in, using an update query and rewriting the code to supply the 1)the previous value when the value is blank and 2)echo the actual value when it is not blank;
HOWEVER - I believe you would need to be VERY careful as I understand it (thanks Pat Hartman) tables are inherently UNORDERED recordsets. This would mean that you have NO GUARANTEE that the previous value that was processed is actually the value that you think of as the previous value. MStefs' example table uses an incremented AutoNumber Primary Key, so it may be possible, if your data also has a field that assures the order, to sort on that order before applying this technique. Note: I further understand that the unordered nature of a table is not necessarily apparent with small recordsets, so this may not pose you much problem or risk if you are dealing with a small amount of data (but if that were the case, wouldn't you just update it by hand ???) - but I also feel it worth mentioning in case the next forum reader decides that this technique is good for their 1,000,000 rows of data...
Anyway; that said: Is your data source actually in XL ?? Why not modify it there before you import it. Assuming you are impoprting XL data.....
In Excel VBA - assuming:-
a) the Active Sheet matches the picture you supplied
b) cell A1 is the "ServerName" heading
c) cell A2 is the first server name
d) there are no breaks in the IP column (i.e. the first row without an IP address is where you want to stop adding ServerNames)
Code:
'All care - NO responbsibility - back up your data before you run any code !
Public Sub PopulateFileServerNames()
Dim szLastServer As String
Dim rngCurrentCell As Range
Set rngCurrentCell = ActiveWorkbook.ActiveSheet.Cells(2, 1)
While Not IsEmpty(rngCurrentCell.Offset(0, 1).Value)
If Not (rngCurrentCell = vbNullString Or IsEmpty(rngCurrentCell)) Then
szLastServer = rngCurrentCell.Value
Else
rngCurrentCell.Value = szLastServer
End If
Set rngCurrentCell = rngCurrentCell.Offset(1, 0)
Wend
Set rngCurrentCell = Nothing
End Sub
You could use the same technique to fill them in, using an update query and rewriting the code to supply the 1)the previous value when the value is blank and 2)echo the actual value when it is not blank;
The biggest problem is that I dont fully understand how to supply the previous value. Its never going to be something I can just type in in a query, its something that gets pulled from thousands of server names in workorders 8(
Im thinking im going to have to find out how to pull the data into an array and manipulate it there. Thanks 8)
Thank you... when reading this note first thing in the morning after waking up, I didnt see that there was a module in there and was a little dumbfounded. Im an access noobie, and didnt realize I could write a procedure and call it through a query. This helps me alot, and I appreciate it .
The data I am working with in this instance will always be small, 20 lines tops or so I would imagine, and im not allowing any kind of sort on the data that gets pulled in, so this will work perfectly when I modify the code a little bit...
Once again, thank you so very much for opening my eyes to a new facet of queries that I did not know existed 8)
To be honest, don't EVER try this at home. Or at work, either.
If the amount of data is small, edit the spreadsheet by hand before you import it.
Select the box with the name. In the lower-right corner of the box, there will be a little square black dot. Drag that dot down to rapidly duplicate the contents of the highlighted field. For text fields, there is no modification of the value. (Not true for numerics...)
The problem is that unless there is another field that can be used to "lock down" the order of the records in the recordset, you cannot guarantee their order in the database. Therefore, "previous" - as in, bring down the previous value - is without firm meaning. You see, set theory - on which databases (in general) are based - lacks the concept of "order." It has groupings but not order. Words like "next" and "previous" imply order.
I won't say that this sort of thing can't be made to work, but you have to include some oddball stuff to assure proper order. It would be easier to muck about in the Excel before importing rather than import first and carry down the names later.
This problem magnifies itself terribly if the server field is a key in this table. Which, from the appearance of the data you have shown us, it well might be. That will surely complicate the importation process.
The data was simplified a bit for posting here, the key is actually a combination of the switch name, blade and port assignment which will always be a unique trio.
Thank you for the concern, I will definately do alot of testing with this functionality. The information that will come into my database will always be very small (3-4 servers with 1-3 connections listed for each server if any) and always follow the same format without variation, its a vendor approved format that we work with. The whole goal is to get as far away from excel editing as possible.
Is there anyway that I can get a hands on demonstration of what you are suggesting? So far, when importing from excel into my database it has come in line for line, and that makes me want to believe that unless user input occurs (which it cant, it was built so that the import is very canned and the data cannot be manipulated mid import) the positioning of the data will always be the same.
Once again, im not an expert, and I value everyones input, I hope im not coming off as pretentious 8)
Assuming (rather than FORCING) a sequence or order to be in a table can lead to DISASTER.
In excel, add another column with a simple formula like (assuming your range starts at A1):
D3: +iif(A3<>"",A3,A2) (and copy down as needed in D)
and use column D rather than A in your import range.
If you have experience in excel, you are going to have to be very careful as you extrapolate your knowledge to access. Some things you 'know', like that rows have an 'order', are just wrong when you move to a relational database management system like Accesss.
So it is becoming clearer you are wanting to regularly import data that another program exports. What format does the other app expot to?
The reason for my lengthy post above is that you are treading on dangerous ground. Pat Hartman is widely considered one of the foremost experts to grace this forum. May I suggest you read the below two of her posts, to aid your understanding of the issue. When I made the "1,000,000 rows" reference, I wasn't intending to infer that it would require that many rows of data to demonstrate the issue, more that it would almost certainly be an issue with that many rows.
You should strongly consider manipulating the data before you import it into a table (unordered) in your database. So, repeating my above question, what format does the other app export to? You would likeley be best off manipulating that format first, then importing it.
