Extract first record on a specific column and store in in a table

[kapaw]

I just want to ask how do I get the first record of a specific column so I can store it in a new table.
ex
col a col b colc
123 456 789
abc cdf sdw

I just want to get anything that is first of col b (which is "456" in my example). I will have to define a string and store it there

dim str as string

str = colB.fields("colB").Row(1) ' i know this is wrong, I just can't figure out what's the right code to use.

 

[boblarson]

Quick question for you to start with -

How do you define first record? Do you have something to sort on to make sure that the first record is the first record?

Data in tables is not stored in any particular order. So, think of it like a big bucket of fish where you toss them in and you might see the same fish on top but you might not as some may have slid around. It is similar with the tables. You may THINK that they always have the data showing as first in is first, etc. but unless you have something like a date/time stamp or an incrementing number (don't think autonumber because it may not always increment. It is only guaranteed to give you a unique number). The date/time stamp is probably best.

So, anyway, you can't assume that the first record you see when you open the table (which is a query by the way - a system one - as you don't see the table really) is really the first.


And last - why do you need it in another table? Do you not know that you can use a query in 99% of the places you would use a table? So, you can likely get by with just a simple select query - if you can identify the record you want.

If it isn't important which record is selected you can just use a query to get the Top 1 of that table and it will return a random one to you. It may be the one you think it should be and it may not.

 

[kapaw]

For your first question: The table is imported from excel then I copied it to a temp table. The first data on that column is like a secondary title on that column so it will always be there.I'm not sure why they do it like that but it won't be changed.
And for your last question: I need that on another table since I will need a it as a storage of the data (all the first data on col b) I'm gahtering. Thanks!

 

[kapaw]

Any suggestions on how I can implement this? I wish there's a record macro option for Access so I can just copy the first record of the second column and just store it in a variable string.

 

[boblarson]

Well, you can do something of this nature:

Dim strVal As String
 
Dim rst1 As DAO.Recordset
 
Set rst = CurrentDb.OpenRecordset("Select * From tableNameHere)
 
strVal = rst(1)
 
rst.Close
 
Set rst = CurrentDb.OpenRecordset("Select * From Table2NameHere")
 
With rst
   .AddNew
   !FieldNameHere
   .Update
End With
 
rst.Close
 
Set rst = Nothing

Now again, unless you provide a sort order you will be at the mercy of whatever Access returns as the first record.

 

[kapaw]

Works perfectly!! Thanks a lot!! I don't know why they do the spreadsheet like that. I will have to tell them that they should just use that first record as the name of the spreadsheet. I have a question though, does rst(1) means row 1 column 1, therefore it selected the specific record? Thanks again!!

 

[boblarson]

No rst(1) means the second field in the recordset. When opening a recordset you are on the first row of the recordset, but like I said before, unless you are opening a query with an Order By clause, this method of opening the "table" is random at best as to which record shows up first.