How do I make the results of a Query into field names on a table?

M

M Chahal

I have a query with just one row of data - how do I make that row of data as
the field names of an existing table? If not existing table can it be done to
a new table? Can it be done at all?

Regards
M Chahal
 
A

Allen Browne

In query design view, choose Append on the Query menu (or in Access 2007,
Append on the Design ribbon.)

Access adds a row to the design grid where you can map the fields.
 
M

M Chahal

Thanks Allen - but I don't want to append a table with this one row of data,
I want this one row of data to be field names of a table, i.e. the column
headers of a new table.

Regards
Mandip
 
D

Douglas J. Steele

You'd have to use code.

Here's how to do it using DAO.

Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset
Dim tdfCurr As DAO.TableDef
Dim fldCurr As DAO.Field

Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset("NameOfSampleTable")
Set tdfCurr = dbCurr.CreateTableDef("NewTable")
With tdfCurr
For Each fldCurr In rsCurr.Fields
.Fields.Append .CreateField(fldCurr.Name, fldCurr.Type)
Next fldCurr
End With
dbCurr.TableDefs.Append tdfCurr

rsCurr.Close
Set rsCurr = Nothing
Set dbCurr = Nothing
 
M

M Chahal

Cheers Douglas - when I run the code all it does is create a table based on
the query. i.e.
QryX =
Columns - Field1 Field2 Field3
Data Row1- FVehX FVehY FVehZ

I want the table to have its field names as the values in row1 of the query,
FVehX etc and its field type to be set as 'number', instead the code makes a
table with the field names as the columns in the query, Field1 etc, but with
no data.

Am I doing something wrong?

Regards
Mandip
 
K

Kerry

I have a query with just one row of data - how do I make that row of data as
the field names of an existing table? If not existing table can it be done to
a new table? Can it be done at all?

Regards
M Chahal
Click to expand...

Export the query to Excel.
In Excel delete the row with the field names, so in row 1, you have
the row of data.
In Excel in row 2 enter numbers.
Save the Excel file.
In Access, import the Excel file to a new table.
In Access, delete the row of data with numbers.
 
D

Douglas J. Steele

Sorry, I misunderstood what you had.

"Number" isn't sufficient detail for creating a field. What type of number:
Integer, Long Integer, Single, Double, etc.?

The following assumes that all the fields should be Long Integers:

Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset
Dim tdfCurr As DAO.TableDef
Dim lngLoop As Long

Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset("NameOfSampleTable")
If rsCurr.EOF = False Then
Set tdfCurr = dbCurr.CreateTableDef("NewTable")
With tdfCurr
For lngLoop = 0 To (rsCurr.Fields.Count - 1)
.Fields.Append .CreateField(rsCurr.Fields(lngLoop).Name, dbLong)
Next lngLoop
End With
dbCurr.TableDefs.Append tdfCurr
End if
rsCurr.Close
Set rsCurr = Nothing
Set dbCurr = Nothing

(if that assumption's wrong, replace dbLong with the appropriate type)
 
M

M Chahal

Hi Doug,

Thanks for your help, but it still isn't pulling the row1 data as the new
table's fields names, it's creating a new table, with the correct field types
but with the field names as they are in the first table.

Current table - new table should have row1 as its field names:
V1 V2 V3
1 Bel Ger Spn


New Table - created by running code:
V1 V2 V3

I hope I am explaining it correctly.

Regards
M Chahal
 
D

Douglas J. Steele

Sorry, don't know what I was thinking.

..Fields.Append .CreateField(rsCurr.Fields(lngLoop).Name, dbLong)

should be

..Fields.Append .CreateField(rsCurr.Fields(lngLoop), dbLong)
 
M

M Chahal

Hi Doug,

I replaced the code and now I get an error message - "Item not found in this
collection"

New Code:
Fields.Append .CreateField(rsCurr.Fields(lngLoop), dbDouble)

Regards
M Chahal
 
M

M Chahal

Cheers Doug - ignore that other email, I fixed it.
Thanks for your help

Regards
M Chahal
 
M

M Chahal

Hi Doug,

I need some more help re this - is it possible to have different types of
fields in this code? i.e. The first five fields as dbtext, the next three as
dblong and the rest as dbdouble.

OR - is there a way of changing the field types using code after the table
has been created?

Regards
M Chahal
 
M

M Chahal

Hi Doug,

I've managed to answer my own question in regards to changing field
properties, but (here I go again), can I some how have a query(or table)
where the row 1 data is the header row(which is what we've done), and also
have the rest of the rows of data (Row2 to the end) as the data of this new
table?

It'll be like appending into a table where the fields names are determined
at the same time - so can't use a normal append query.

I apoligise for hassling you constantly.

Regards
M Chahal
 
D

Douglas J. Steele

It's certainly doable, but where is this data coming from? What you're doing
seems a very unusual thing to require. Perhaps there are better ways to
accomplish what you're trying to do.
 
M

M Chahal

Doug,

Basically, I'm importing a text file (tab seperated) which is split into
two, header info and then the data - see below.
Text file format:

"Veh Desc" V1 V2 V3
"Veh Mrkt" Ger Spn Eng

Part Desc TotPrice ? ? ?
1X J 20 5 10 5
2X K 10 0 5 5
3X L 1 1 0 0

The data has the field names then the relevent data for that field
underneath, and also the individual price per vehicle but the price per
vehicle has no direct field name - that comes from a combo of the header part
(VehDesc + VehMrkt).

The table that we created was the a combo of the header in the right order
after 'TotPrice', now we need to add the data in this table.

The text file will have diff vehicles/descriptions each time - so can't
create a static table.
I hope I've explained it ok.

Regards
M Chahal
 
D

Douglas J. Steele

How are you getting that as a recordset?

Given that your first 2 lines have 4 columns, while the remainder have 6,
what's actually coming through?
 
M

M Chahal

Doug,

It's a report generated from a system, where the first 10-15 lines are
header details, then from then its the core data. I don't have access to the
system to change the format of the report.

Depending on the number of vehicles in the report there will be more
columns. I run two seperate queries, one to get the header info in the
correct columns and the other to get the core data, then a union query to
combine the two - the first row being the field names of the table, and the
remaining rows being the data I want in that table.

Regards
M Chahal
 
Top