How to get data from 2nd row

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi there,

I have a table as the following:

Name ID
Jimmy Smith
12459HL
Fred Jones
45368CA

In the table above was imported from a database system where I work. The
name is on one row and the ID is on the next row. I would like to write a
query to get the name and ID on the same row.i.e,

Name ID
Jimmy Smith 12459HL
Fred Jones 45368CA

If anyone can help me, I am really appreciate. Thanks.
 
Since records are like marbles in a bag, do you have a field that identifies
the order of the records? Can you allow Access to add a primary key field?
 
Yes, I can add a primary key field in the table.

Duane Hookom said:
Since records are like marbles in a bag, do you have a field that identifies
the order of the records? Can you allow Access to add a primary key field?
Click to expand...
 
Unless you have some other field or fields that tell you that the Name field
data belongs with this ID field data there is no reliable method to do this
in a relational database (such as Access). Records don't have a guaranteed
order unless you impose the order through a query with an order by clause.

If you are importing the data, you might be able to solve the problem by
editing the source file or using a vba routine to import the records instead
of your current method of importing. Hard to say what the solution would be
without knowing the source of the data - a text file, a spreadsheet, a link
to another database, etc.

If you want to test your luck you could use a VBA routine to step through
the recordset and grab the name from the "odd number" records, move to the
next record (even number) and update that record and continue doing that
record by record.

If the number of records is limited, you could just open the table and move
down the rows using control + quote (") to copy the contents of the field
above into the Name field. Once you have done that you can go back and
delete all the rows with no ID.
 
The records are in uniform order: name is in one row and ID is in the next
row. And name then ID....

Also the databse system at my company can only allow me to import either to
Excel or to a Text file (or ASCII). It doesn't allow to edit the source file.

Besides, I know nothing about vba routine to import the record as you
suggested. The idea of Duane to use the primary key sounds interesting
because the table's data are in uniform order, so I think it will work.

But how to query to get the primary key, I have no idea. Please show me.

In addition, if you have any suggestion, please help me. Many thanks.
 
I would export to excel, delete the top cell of ID, and then append the newly
aligned records to access.
 
Import the file into Access using the wizard and allow the wiz to add a
primary key field. I couldn't find a way to tell the wiz that the first row
contained headers so I ended up with a table like:

tblImport
ID Field1 Field2
----- ---------------------- ----------------
1 Name ID
2 Jimmy Smith
3 12459HL
4 Fred Jones
5 45368CA

I then created a totals query like:
SELECT Max(Field1) AS FullName,
Max(Field2) AS OldID,
[ID]\2 AS NewID
FROM tblImport
WHERE ID>1
GROUP BY [ID]\2;
 
Back
Top