Search results return Record & Field Name

IvenBach

Board Regular
Joined
May 31, 2008
Messages
212
I have a table containing the contents I would like to be returned in the format of "Fieldname | RecordValue". With the help of another poster on my initial post http://www.mrexcel.com/forum/showthread.php?t=588734 I have been able to piece together an import query for what I initially needed. I would like to add some more information ideally as a 2 column table. I have 6 fields (Warranty, Housing, Beam Pattern, Lumens, Watts, and Lifespan) and would like to write a UDF that would include the field name in column 1 and the subsequent info in column 2 but am very unfamiliar with Access on how to do this. Very few records will have all the fields populated, most will be blank (I believe NULL is the correct term) and I had envisioned a slight modification of Norie's original function would be able to do this. I'm lost as to how to call a field name. Is there anyone out there willing to help me along and give me a few pointers in the right direction, or if this is feasible or possible?

If anything is unclear I'll do my best to clarify.
 
Last edited:

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Iven

Sorry to ask the inevitable, but why do you want/need to do this?

Anway, are there more records in the table the original data comes from or do you want this done for one record from that table?
 
Upvote 0
Norie,

This would be somewhat of a framework function for several manufacturers. I'm having to generate a table(s) listing features, dimensions, etc... for products that are produced by manufacturers. There is no simple way to do this since each manufacturer is on a case by case basis - it had been done by hand for each product but I started using excel to do most of the 'heavy lifting' but the more linking and building I did the slower and slower my file got. Had a few files break when doing this and had to reconstruct them...:mad:

I get product information from a manufacturer and would like to have it in a table or features list (1st post you helped with) for easier reading. It can be as simple as 1 table with 1 line to any number of tables with several lines on each table, it all depends on how complex of a product it is and how much clarification or features have to be listed for them.

Edit: This is usually for every record in the table. The number of records and tables varies with each product line
 
Last edited:
Upvote 0
Sorry, I must be missing something.

If you need to generate tables of data why do you need a table with only 2 columns?

Are you trying to create something with the manufacturer(s) as the 'headers' of the table, the 'features' as the rows and the bit in the middle the actual data?

Kind of like a pivot table but not with numbers?

So the first row would be for, say, Warranty and across we would have the warranty data/information for each manufacturer?
 
Upvote 0
<table border="1"><tbody><tr><th colspan="2">Table: Specifications</th> </tr><tr><td>Field Name</td><td>Value</td></tr> <tr> <td>Warranty</td> <td>Lifetime</td> </tr> <tr> <td>Housing Color</td> <td>Red</td> </tr> <tr> <td>Beam Pattern</td> <td>Flood</td> </tr> <tr> <td>Lumens</td> <td>1800 Lm</td> </tr> <tr> <td>Watts</td> <td>40</td> </tr> </tbody></table>
<table border="1"> <tbody><tr> <th colspan="2">Table: Technical Details</th></tr><tr><td>Field Name</td><td>Value</td></tr> <tr> <td>Input Volt</td> <td>90-230V AC</td> </tr> <tr> <td>Amp Draw</td> <td>2.92A</td> </tr> <tr> <td>Power Source</td> <td>NULL (Wasn't included)</td> </tr> <tr> <td>Charge Time</td> <td>NULL (Wasn't included)</td> </tr> <tr> <td>Run Time</td> <td>105 Hours</td> </tr> <tr> <td>Operating Temp</td> <td>-20 Celsius ~ +40 Celsius</td> </tr> </tbody></table>
For the table to be created from the Tech details the rows of Power Source and Charge Time wouldn't be included since they don't apply (have no value and are NULL). If I'm lucky a manufacturer will have all their information as a spreadsheet, they usually keep everything on the same sheet. I'd have to separate each bit of information into its own Access table and those would be the tables that can vary. From those types of tables I pulled these example display tables from. I'd want to find some way of pulling the field name to be inserted into display table. These are isolated and would only be for a single manufacturer IE MacDonalds. For TacoBell it would be the same setup but with their isolated information they have.

If that doesn't make any sense it's been a hectic day and I can better explain tomorrow. Thanks again for your patience and head scratching at my intelligible gibberish.
 
Upvote 0
I'm getting a bit confused.

Are these existing tables or are these what you want using another table as a source?
 
Upvote 0
The results I gave as an example would be the output format desired. The information is in a spreadsheet format currently.
<table border="1"><tr> <td>RC</td> <td>A</td> <td>B</td> <td>C</td>
</tr><tr> <td>1</td> <td>Warranty</td> <td>Housing Color</td> <td>Beam Pattern</td></tr><tr> <td>2</td> <td>Lifetime</td> <td>Red</td> <td>Flood</td></table>
 
Upvote 0
I think the only way to do this is using code, but that could involve creating a new table every time.

You could export to Excel, transpose and reimport I suppose but if it's going to be a regular thing that would probably involve code too.

Have a look here http://support.microsoft.com/kb/202176 for 2 methods to transpose, which looks like what you want to do.
 
Upvote 0
I looked into the suggestion that you have me and think I wasn't stating clearly enough. After searching for some more I came up with these 2 links http://www.codeguru.com/forum/showthread.php?s=&threadid=219871&highlight=fieldname and http://forums.techguy.org/business-applications/526520-solved-getting-field-names-tables.html. I'd like to have a UDF similar to code below. Ideally I'd be looking for a UDF Fieldnames(ReturnsFieldName,ReturnsRecordValue) but I'm just trying to get the syntax for the field name right now. Is this a bit easier to understand or is my explaining only making it worse?
Code:
Function FieldNames(ParamArray varINPUT())
    Dim Rst As Recordset
    Dim strFIELDNAME As String
    
    Set Rst = CurrentDb.OpenRecordset("tblVSX")
    'This would return strINPUTs field name allowing me
    'to use it where I need. Syntax in incorrect and
    'I'm not sure how to set it up properly.
        strFIELDNAME = Rst.Fields(varINPUT()).Name
        
    Rst.Close
End Function
 
Upvote 0
I don't quite see how you would use either of those.

The posted code will only work if you know the name or index of the field.

It's also going to open the table every time you use it.

The code in the second link gets all the fieldnames.

Did you look at the 2nd example in the link I posted?

It shows you how to transpose a table/query.

Now I know that you only want to transpose one record, so you would just use that code with a table/query with one record.
 
Upvote 0

Forum statistics

Threads
1,226,390
Messages
6,190,735
Members
453,616
Latest member
nathancook

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top